Re: [sqlite] Automatic index detail

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/15/2010 08:58 PM, Cory Nelson wrote:
> This might not be doing what you think it is.

It does exactly what I think it does and as is documented.

> When you have a query that will do a full table scan multiple times,
> SQLite can sometimes create an index to make it only do a full table
> scan once, and use the index every time after that.  The index doesn't
> stick around though -- it is deleted as soon as your query ends, and
> remade every time you run it.  

Yes, that is exactly what the documentation says.

> Since they are created per-query,
> there's no way to tune them prior to when you run them.

I don't want to tune the automatic indices - I want to see what columns they
are on.

> While faster than the alternative, it's still very expensive and
> really only useful if your queries are very rare and can stand to run
> slowly, 

I'll be the judge of if they are useful to me :-)  I've been playing around
with my data set imported from somewhere else, and before I have created
indices.  I was pleasantly surprised by the performance, and this was why.

> or if you've got no clue what queries will be run.

I don't actually know what queries will be run, yet.  The data will be
somewhat denormalised and exported to yet another database with a better
schema.  The current one grew over many years and hence is a mess of
spaghetti tables, joins, duplication and inconsistencies.

> They're
> not meant to make development easier by replacing permanent indexes.

The time to create the automatic indices is pretty quick (a second or two)
and I will initially develop my code without permanent ones unless
performance appears to be an issue.  This will make development easier since
I won't have to keep adding permanent indices nor repeatedly check that the
permanent indices are still appropriate to the queries being developed.

I'm all for SQLite making development easier!

> It sounds like you already know which columns will be queried against.

I won't know until my code is finished, the export has been hand inspected etc.

>  You should probably be creating permanent indexes.

If I knew what columns the automatic indices were on then a first approach
is to automate the generation of the permanent ones.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkyRq+kACgkQmOOfHg372QS7OACeKicD+LdZ59ue03kevg8S3sTa
1CcAoIaJLBIDCflXPU3nBbS8dnGjECk/
=xixA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic index detail

[Drake Wilson]

Quoth Cory Nelson , on 2010-09-15 20:58:24 -0700:
> It sounds like you already know which columns will be queried against.
>  You should probably be creating permanent indexes.

I'm pretty sure his question was asking whether he could verify the
set of relevant columns empirically via asking which columns would be
used in the temporary index creation, for the purpose of creating
permanent indices.  This is useful as a secondary check even if one
should theoretically know which columns will be used beforehand.

  ---> Drake Wilson

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic index detail

[Cory Nelson]

On Wed, Sep 15, 2010 at 8:25 PM, Roger Binns  wrote:
> On 09/15/2010 06:00 PM, Cory Nelson wrote:
>> SQLite doesn't support automatic indexing,
>
> And your basis for that claim is?
>

This might not be doing what you think it is.

When you have a query that will do a full table scan multiple times,
SQLite can sometimes create an index to make it only do a full table
scan once, and use the index every time after that.  The index doesn't
stick around though -- it is deleted as soon as your query ends, and
remade every time you run it.  Since they are created per-query,
there's no way to tune them prior to when you run them.

While faster than the alternative, it's still very expensive and
really only useful if your queries are very rare and can stand to run
slowly, or if you've got no clue what queries will be run.  They're
not meant to make development easier by replacing permanent indexes.

It sounds like you already know which columns will be queried against.
 You should probably be creating permanent indexes.


-- 
Cory Nelson
http://int64.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic index detail

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/15/2010 06:00 PM, Cory Nelson wrote:
> SQLite doesn't support automatic indexing, 

And your basis for that claim is?

The basis of mine is that it did for me.

And this page:

  http://www.sqlite.org/optoverview.html#autoindex

And these pragmas:

  http://www.sqlite.org/pragma.html#pragma_automatic_index

And the release notes for 3.7.0:

  http://www.sqlite.org/changes.html#version_3_7_0

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkyRjjAACgkQmOOfHg372QQWCACdFSVYMKlmSbf1wa41QEfxDdVh
AYQAoONSBUJpbWc/3rB0XgWs2BylMGKu
=xAxK
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Logic error for in-memory database

[Hemant Shah]

After executing sqlite3_step(), I check to see if the return code is SQLITE_OK

if (rc != SQLITE_OK)
{
   printf("%s\n", sqlite3_errmsg(dbhandle);
}

I will add sqlite3_reset() before calling sqlite3_errmsg().

Hemant Shah
E-mail: hj...@yahoo.com


--- On Wed, 9/15/10, Roger Binns  wrote:

> From: Roger Binns 
> Subject: Re: [sqlite] SQL Logic error for in-memory database
> To: "General Discussion of SQLite Database" 
> Date: Wednesday, September 15, 2010, 3:05 PM
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 09/15/2010 11:53 AM, Hemant Shah wrote:
> > The error occurs while calling sqlite3_step().
> 
> Call sqlite3_reset and then get the error message text.
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> 
> iEYEARECAAYFAkyRJu8ACgkQmOOfHg372QQV/gCeP+54VCTxuNoQMh2oClW07Tr/
> XA8Ani+2fvvjxo2hvBO6/N3SfkVXgUeR
> =/Hs4
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic index detail

[Cory Nelson]

On Wed, Sep 15, 2010 at 5:46 PM, Roger Binns  wrote:
> I've got a large third party data set I imported into SQLite.  Queries are
> surprising fast especially since I haven't created any indices yet.  The
> reason turned out to be because of the automatic index functionality.
>
> When doing an "explain query plan", the text comes out like this:
>
>   TABLE toys WITH AUTOMATIC INDEX
>
> Would it be possible to add more detail to that such as which columns the
> index is on?  eg
>
>   TABLE amw_track WITH AUTOMATIC INDEX on (weight, colour)
>
> This would make it a lot easier to manually or automagically create indices
> if I know my queries in advance.

SQLite doesn't support automatic indexing, are you sure SQLite is what
you're using?  If you already know which columns to create an index
on, run CREATE INDEX manually.

-- 
Cory Nelson
http://int64.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New to SQLite and I have a question

[Mike Owens]

FWIW, there is a second edition of the Definitive Guide to SQLite,
apparently coming out in Nov:

http://www.amazon.com/Definitive-Guide-SQLite-Mike-Owens/dp/1430232250/

Allen Grant is the author doing the work. I don't have any details other
than that. I hear he's a good guy for the job though.

-- Mike

On Sun, Sep 12, 2010 at 2:53 PM, Bob Keeland  wrote:

> Yes, I got an email from Olaf, and I will definitely take any help I can
> get. Such help from the VBForum was instrumental in my program for reading
> EXIF data from digital photographs.
>
> Bob
>
> --- On Sun, 9/12/10, Bart Smissaert  wrote:
>
>
> From: Bart Smissaert 
> Subject: Re: [sqlite] New to SQLite and I have a question
> To: "General Discussion of SQLite Database" 
> Date: Sunday, September 12, 2010, 11:28 AM
>
>
> To use SQLite in classic VB or VBA you need this free wrapper:
> http://www.thecommon.net/3.html
> I have been using it for a few years now (in a commercial application)
> and it is fast and easy plus excellent support from the author Olaf
> Schmidt.
>
> RBS
>
>
> On Sat, Sep 11, 2010 at 9:27 PM, Bob Keeland  wrote:
> > Hi all,
> > I am new to using SQLite but think that it may be good for a project that
> I'm working on. I do my programming in Visual Basic and don't know any
> C\C++. Is there any problem with connecting with SQLite from Visual Basic?
> >
> > Basically my program will conduct repeatitive searches on a database
> based on user input. In most cases the search will be on one variable (one
> column) at a time. The database will be relatively small with less than 3000
> records and perhaps a couple hundred columns. For each query of the database
> I need to keep the records that match and eliminate all other records. A
> followon search will just search the records remaining from the previous
> search. Of course I could have the user select several variables and then do
> a more thorough search, but for my data that would not work in a lot of
> cases.
> >
> > With enough queries (sometimes just a few and sometimes a lot) the number
> of remaining records will approach 1. The one remaining record will contain
> the information that the user will be looking for. There will not be any
> adding, deleting, or modifying records. This will justy be a tool for
> finding information based on variables related to the wanted data. The
> database is plant characteristics data and the program will be used by plant
> ecologists to determine the species of plant in hand. There are lots of
> books that help users do this, but the books use dichotomous keys and the
> same thing could be done, in some respects easier, in a program, especially
> if the program could eventually be written for a smart phone. I've had good
> luck with a similar program on PDAs.
> >
> > My main question - Does this sound like SQLite would be appropriate? I've
> been using Access as my database up to now. If SQLite sounds appropriate can
> anyone recommend a good book on SQLite? Any help or suggestions welcome
> (even if the answer is go away).
> >
> > Bob Keeland
> > Forest Dynamics, Inc.
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Automatic index detail

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

I've got a large third party data set I imported into SQLite.  Queries are
surprising fast especially since I haven't created any indices yet.  The
reason turned out to be because of the automatic index functionality.

When doing an "explain query plan", the text comes out like this:

   TABLE toys WITH AUTOMATIC INDEX

Would it be possible to add more detail to that such as which columns the
index is on?  eg

   TABLE amw_track WITH AUTOMATIC INDEX on (weight, colour)

This would make it a lot easier to manually or automagically create indices
if I know my queries in advance.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkyRaPsACgkQmOOfHg372QT6cQCg5PBdg1O729p1XEcOnqViGibw
mCQAoIiPY9iZrXgLNsomyVVRy+Dgx+U0
=BcNd
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 15-09-2010 20:12, Nicolas Williams wrote:
> On Wed, Sep 15, 2010 at 08:05:26PM +0200, Stef Mientki wrote:
>>  On 15-09-2010 11:36, Benoit Mortgat wrote:
>>> Are you sure that after altering your tables adding columns, natural
>>> join still only joins on vlid?
>>>
>> no, very stupid of me !!
>> I added a column to each of the tables, with the same name, they
>> should have the same value, but due to another programming error, the
>> values were different.  again, thank all very much !!
>>
>> That brings another issue to my mind, which I still don't understand:
>> "why can we so easily manipulate complex pages of text, by adding /
>> deleting / copying / pasting etc, and is manipulating of a few tables
>> in a database so difficult?"
> You shouldn't select * and you shouldn't natural joins precisely to
> avoid these sorts of surprises.  Select * is handy for manual queries
> from a shell; ditto natural joins.
>
> Of course, you can always change your schema in such a way as to break
> existing statements.  But the point here is to write statements such
> that they will be stable in the face of a schema that evolves in a
> backwards compatible way.  When you have to radically change your schema
> you know you have to update your code; when you're merely adding columns
> that don't contribute to primary keys then you shouldn't have to go
> change any statements other than the ones where you specifically need
> the new columns.  SQL shortcuts like '*' and natural join are just not
> compatible with such a schema evolution model.
>
thanks Nico,
I think these are very valuable tips.

cheers,
Stef
> Nico

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Logic error for in-memory database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/15/2010 11:53 AM, Hemant Shah wrote:
> The error occurs while calling sqlite3_step().

Call sqlite3_reset and then get the error message text.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkyRJu8ACgkQmOOfHg372QQV/gCeP+54VCTxuNoQMh2oClW07Tr/
XA8Ani+2fvvjxo2hvBO6/N3SfkVXgUeR
=/Hs4
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Logic error for in-memory database

[Hemant Shah]

The error occurs while calling sqlite3_step().


Hemant Shah
E-mail: hj...@yahoo.com


--- On Wed, 9/15/10, Roger Binns  wrote:

> From: Roger Binns 
> Subject: Re: [sqlite] SQL Logic error for in-memory database
> To: "General Discussion of SQLite Database" 
> Date: Wednesday, September 15, 2010, 1:29 AM
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 09/14/2010 07:41 PM, Hemant Shah wrote:
> > Sometimes I get following error:
> > 
> > SQL logic error or missing database.
> 
> That text corresponds to the error code SQLITE_ERROR which
> is the code used
> for a wide variety of error conditions that don't have a
> more specific code.
> 
> In any event you haven't even told us which API is
> returning the code.
> 
> http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> 
> iEYEARECAAYFAkyQZ9EACgkQmOOfHg372QRwlQCg3abzvZCSB83x4gxJ9422oiiQ
> BrwAn0g4/NSB3XqALkDYx641B7jTNfIn
> =hMN8
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Logic error for in-memory database

[Hemant Shah]

--- On Tue, 9/14/10, Simon Slavin  wrote:

> From: Simon Slavin 
> Subject: Re: [sqlite] SQL Logic error for in-memory database
> To: "General Discussion of SQLite Database" 
> Date: Tuesday, September 14, 2010, 9:51 PM
> 
> On 15 Sep 2010, at 3:41am, Hemant Shah wrote:
> 
> > I have written a C program that creates in-memory
> database. It reads packets from the network and insert some
> info in the database. 
> > 
> > Sometimes I get following error:
> > 
> > SQL logic error or missing database.
> 
> Does the error occur only when starting your program, or
> sometimes during a long run which had gone okay up to that
> point ?

It occurs after it is running for a while. If I ignore the error and continue, 
the inserts are successful for few more hours then I again get error.

> 
> Is the database accessed from more than one thread or
> process ?
> 

  No it is only accessed by one process (no threads).


> Are you intentionally using NULL values anywhere ?
> 

  No. all columns are defined with NOT NULL.


> Simon.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


Hemant Shah
E-mail: hj...@yahoo.com




  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

[Nicolas Williams]

On Wed, Sep 15, 2010 at 08:05:26PM +0200, Stef Mientki wrote:
>  On 15-09-2010 11:36, Benoit Mortgat wrote:
> > Are you sure that after altering your tables adding columns, natural
> > join still only joins on vlid?
> >
> no, very stupid of me !!
> I added a column to each of the tables, with the same name, they
> should have the same value, but due to another programming error, the
> values were different.  again, thank all very much !!
> 
> That brings another issue to my mind, which I still don't understand:
> "why can we so easily manipulate complex pages of text, by adding /
> deleting / copying / pasting etc, and is manipulating of a few tables
> in a database so difficult?"

You shouldn't select * and you shouldn't natural joins precisely to
avoid these sorts of surprises.  Select * is handy for manual queries
from a shell; ditto natural joins.

Of course, you can always change your schema in such a way as to break
existing statements.  But the point here is to write statements such
that they will be stable in the face of a schema that evolves in a
backwards compatible way.  When you have to radically change your schema
you know you have to update your code; when you're merely adding columns
that don't contribute to primary keys then you shouldn't have to go
change any statements other than the ones where you specifically need
the new columns.  SQL shortcuts like '*' and natural join are just not
compatible with such a schema evolution model.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 15-09-2010 11:36, Benoit Mortgat wrote:
> On Tue, Sep 14, 2010 at 23:41, Stef Mientki  wrote:
>
>> until a few moments ago, this worked perfectly
>>
>> select Header from vraag
>>  natural join vraaglist
>>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
>> but now it returns an empty string (while the string shouldn't be empty).
>>
>> I test that by doing an explicit join
>>
>> select Header from vraag
>>  join vraaglist on vraag.vlid = vraaglist.vlid
>>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>>
> Are you sure that after altering your tables adding columns, natural join
> still only joins on vlid?
>
no, very stupid of me !!
I added a column to each of the tables, with the same name,
they should have the same value, but due to another programming error, the 
values were different.
again, thank all very much !!

That brings another issue to my mind, which I still don't understand:
"why can we so easily manipulate complex pages of text, by adding / deleting / 
copying / pasting etc,
and is manipulating of a few tables in a database so difficult?"

cheers,
Stef
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

[Benoit Mortgat]

On Tue, Sep 14, 2010 at 23:41, Stef Mientki  wrote:

>
> until a few moments ago, this worked perfectly
>
> select Header from vraag
>  natural join vraaglist
>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>
> but now it returns an empty string (while the string shouldn't be empty).
>
> I test that by doing an explicit join
>
> select Header from vraag
>  join vraaglist on vraag.vlid = vraaglist.vlid
>  where Nr = 0 and vraaglist.Name = 'eortc_br23'
>

Are you sure that after altering your tables adding columns, natural join
still only joins on vlid?

-- 
Benoit Mortgat
20, avenue Marcel Paul
69200 Vénissieux, France
+33 6 17 15 41 58
+33 4 27 11 61 23
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB file locked when no other process using it - Found word(s) list error in the Text body

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/15/2010 01:43 AM, Nick Shaw wrote:
> I assume sqlite should no longer have any handles
> open to the database; however Windows (which I'm running under) may well
> still have open handles to the file waiting to flush cached changes to
> disk.  The way I resolve this is to try deleting the file over and over
> until either it succeeds, or it has still failed to delete after a set
> time (say a few seconds).

The cause of this is almost always tag alongs.  They register with the
kernel and get notified when a file is about to be closed, so they promptly
open it and then do whatever with it.

Number one whatever is virus scanners, but the culprits can also include
backup tools, source control tools and various Explorer extensions that like
to display status/icons for files.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkyQjegACgkQmOOfHg372QSdzwCg1ol6P7DZfCv0tXexuyVYaRI6
EOkAoKmVDOl8oVbTwUoDsVKefXEUPn8I
=zXNt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB file locked when no other process using it - Found word(s) list error in the Text body

[Nick Shaw]

I've seen this kind of issue before - after closing the sqlite3
database, I can't delete the file (I would want to do this if I opened
an sqlite3 database and found it to be corrupt).  As sqlite's connection
handle has closed, I assume sqlite should no longer have any handles
open to the database; however Windows (which I'm running under) may well
still have open handles to the file waiting to flush cached changes to
disk.  The way I resolve this is to try deleting the file over and over
until either it succeeds, or it has still failed to delete after a set
time (say a few seconds).

Under Windows, you can see if any processes have a handle open to the
file through SysInternals' Process Explorer (go to Find->Find Handle or
Dll... and enter the sqlite file name - it'll highlight the first
process it finds with an open handle to that file).  You can then force
close the handle.  I'm not sure how to do this programmatically though,
or how to find/release locks on other operating systems.

Nick.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrew Wood
Sent: 14 September 2010 14:46
To: General Discussion of SQLite Database
Subject: [sqlite] DB file locked when no other process using it - Found
word(s) list error in the Text body

Im getting an error saying the database is locked even though no other
process is accessing the file. I think it was probably caused by a
process crashing part way through. Is there a way to force release the
lock?

Andrew

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

[Stef Mientki]

 On 15-09-2010 03:42, Pavel Ivanov wrote:
> Did you by any chance introduced some unique constraint or unique
> index on a set of columns one of which is primary key? AFAIK, there
> was a problem in SQLite until some recent versions in processing of
> redundant unique constraints in conjunction with natural joins.
thanks, that's a good idea,
I indeed, removed a unique constraint of 1 of the columns (and also renamed 
that column).
So I'll try to replicate the transformations and let you know.

cheers,
Stef
> But the best idea would be to post the full definition of those two
> tables along with their indexes and constraints.
>
> Pavel
>
> On Tue, Sep 14, 2010 at 6:09 PM, Stef Mientki  wrote:
>>  On 14-09-2010 23:50, Oliver Peters wrote:
>>> your version of sqlite?
>> I used several programs
>> SQLiteSpy,
>> SQLcc,
>> Python-programs,
>> so I don't know which versions of sqlite I used,
>> might that be the problem ?
>>> your OS?
>> windows XP
>>> your backend?
>> several, see above
>>> What exactly have you done before the Natural Join stopped working?
>> converted tables from string to unicode,
>> column rename + columns added + changed column constraints ( through copy 
>> table / drop table /
>> rename table )
>>
>> thanks,
>> Stef
>>> Oliver
>>>
>>>
>>> Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki:
 hello,

 after modifying some tables, natural join stopped working  ???

 I've 2 tables, each with a field named "vlid", which is the primary key in 
 one of the tables

 until a few moments ago, this worked perfectly

 select Header from vraag
   natural join vraaglist
   where Nr = 0 and vraaglist.Name = 'eortc_br23'

 but now it returns an empty string (while the string shouldn't be empty).

 I test that by doing an explicit join

 select Header from vraag
   join vraaglist on vraag.vlid = vraaglist.vlid
   where Nr = 0 and vraaglist.Name = 'eortc_br23'

 which gives me the correct string of the field Header.

 exchanging the 2 tables in the above statements, gives exactly the same 
 results.

 Is my database corrupt ?

 thanks,
 Stef Mientki
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB file locked when no other process using it

[Benoit Mortgat]

Do you have SQLite Manager extension for Firefox installed? I had problems
with it having the database open until Firefox is restarted.

On Tue, Sep 14, 2010 at 15:45, Andrew Wood  wrote:

> Im getting an error saying the database is locked even though no other
> process is accessing the file. I think it was probably caused by a
> process crashing part way through. Is there a way to force release the
> lock?
>
> Andrew
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Benoit Mortgat
20, avenue Marcel Paul
69200 Vénissieux, France
+33 6 17 15 41 58
+33 4 27 11 61 23
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Logic error for in-memory database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/14/2010 07:41 PM, Hemant Shah wrote:
> Sometimes I get following error:
> 
> SQL logic error or missing database.

That text corresponds to the error code SQLITE_ERROR which is the code used
for a wide variety of error conditions that don't have a more specific code.

In any event you haven't even told us which API is returning the code.

http://www.chiark.greenend.org.uk/~sgtatham/bugs.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkyQZ9EACgkQmOOfHg372QRwlQCg3abzvZCSB83x4gxJ9422oiiQ
BrwAn0g4/NSB3XqALkDYx641B7jTNfIn
=hMN8
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users