database schema has changed

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

database schema has changed

Ben Clewett
Dear SQLite,

I am experiencing a problem.  Every so often, say one in 100,000
'SELECT' queries, I get this error:

"database schema has changed"

This is false, the schema has not changed.

Can any person offer a reason why this might be, and possibly a solution
to this problem?

I am using SQLite 3.1.6 and storing my database on /dev/shm.

Kind regards,

Ben Clewett.



Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

Ben Clewett
Some extra information:

I also note I run a VACUUM every 50 minutes.  Could this result in the
"database schema has changed" message I sometimes see?  Possibly one
thread accessing database during VACUUM?

Kind regards,

Ben Clewett.

Ben Clewett wrote:

> Dear SQLite,
>
> I am experiencing a problem.  Every so often, say one in 100,000
> 'SELECT' queries, I get this error:
>
> "database schema has changed"
>
> This is false, the schema has not changed.
>
> Can any person offer a reason why this might be, and possibly a solution
> to this problem?
>
> I am using SQLite 3.1.6 and storing my database on /dev/shm.
>
> Kind regards,
>
> Ben Clewett.
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

D. Richard Hipp
On Wed, 2005-06-01 at 17:16 +0100, Ben Clewett wrote:
> Some extra information:
>
> I also note I run a VACUUM every 50 minutes.  Could this result in the
> "database schema has changed" message I sometimes see?  Possibly one
> thread accessing database during VACUUM?
>

Yes.  Strange though it may seem, VACUUM changes the
schema.

What happens is that VACUUM moves tables and indices
around inside the file.  Even though the schema as seen
by us humans remains the same, the way the schema is
implemented on the inside does change.  And it is the
inside implementation that the SQLITE_SCHEMA error
cares about.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

Tito Ciuro
In reply to this post by Ben Clewett
Hi Ben,

Check this post and see if it helps: http://www.mail-archive.com/ 
[hidden email]/msg04887.html

Regards,

-- Tito

On 01/06/2005, at 12:16, Ben Clewett wrote:

> Some extra information:
>
> I also note I run a VACUUM every 50 minutes.  Could this result in  
> the "database schema has changed" message I sometimes see?  
> Possibly one thread accessing database during VACUUM?
>
> Kind regards,
>
> Ben Clewett.
>
> Ben Clewett wrote:
>
>> Dear SQLite,
>> I am experiencing a problem.  Every so often, say one in 100,000  
>> 'SELECT' queries, I get this error:
>> "database schema has changed"
>> This is false, the schema has not changed.
>> Can any person offer a reason why this might be, and possibly a  
>> solution to this problem?
>> I am using SQLite 3.1.6 and storing my database on /dev/shm.
>> Kind regards,
>> Ben Clewett.
>>
>
>

______________________________________

Tito Ciuro
http://www.webbo.com

Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

Ben Clewett
In reply to this post by D. Richard Hipp
Thanks, I understand.

I will run my VACUUM far less often and try and look at some locking
options.

Regards,

Ben

D. Richard Hipp wrote:

> On Wed, 2005-06-01 at 17:16 +0100, Ben Clewett wrote:
>
>>Some extra information:
>>
>>I also note I run a VACUUM every 50 minutes.  Could this result in the
>>"database schema has changed" message I sometimes see?  Possibly one
>>thread accessing database during VACUUM?
>>
>
>
> Yes.  Strange though it may seem, VACUUM changes the
> schema.
>
> What happens is that VACUUM moves tables and indices
> around inside the file.  Even though the schema as seen
> by us humans remains the same, the way the schema is
> implemented on the inside does change.  And it is the
> inside implementation that the SQLITE_SCHEMA error
> cares about.

Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

Ben Clewett
In reply to this post by Tito Ciuro
So as I understand the posting.  After each VACUUM it is advisable to
shutdown and restart SQLite, or run a single INSERT to clear the error...

I'll do some testing :)

Ben

Tito Ciuro wrote:

> Hi Ben,
>
> Check this post and see if it helps: http://www.mail-archive.com/ 
> [hidden email]/msg04887.html
>
> Regards,
>
> -- Tito
>
> On 01/06/2005, at 12:16, Ben Clewett wrote:
>
>> Some extra information:
>>
>> I also note I run a VACUUM every 50 minutes.  Could this result in  
>> the "database schema has changed" message I sometimes see?   Possibly
>> one thread accessing database during VACUUM?
>>
>> Kind regards,
>>
>> Ben Clewett.
>>
>> Ben Clewett wrote:
>>
>>> Dear SQLite,
>>> I am experiencing a problem.  Every so often, say one in 100,000  
>>> 'SELECT' queries, I get this error:
>>> "database schema has changed"
>>> This is false, the schema has not changed.
>>> Can any person offer a reason why this might be, and possibly a  
>>> solution to this problem?
>>> I am using SQLite 3.1.6 and storing my database on /dev/shm.
>>> Kind regards,
>>> Ben Clewett.
>>>
>>
>>
>
> ______________________________________
>
> Tito Ciuro
> http://www.webbo.com
>

Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

Ben Clewett
Ben Clewett wrote:
> So as I understand the posting.  After each VACUUM it is advisable to
> shutdown and restart SQLite, or run a single INSERT to clear the error...

After a lot of testing, I can find no instance of where SQLite needs
restarting, or anything else after a VACUUM.  Although maybe some
locking may be needed during the VACUUM in a multi-thread scenario.

Ben


>
> I'll do some testing :)
>
> Ben
>
> Tito Ciuro wrote:
>
>> Hi Ben,
>>
>> Check this post and see if it helps: http://www.mail-archive.com/ 
>> [hidden email]/msg04887.html
>>
>> Regards,
>>
>> -- Tito
>>
>> On 01/06/2005, at 12:16, Ben Clewett wrote:
>>
>>> Some extra information:
>>>
>>> I also note I run a VACUUM every 50 minutes.  Could this result in  
>>> the "database schema has changed" message I sometimes see?   Possibly
>>> one thread accessing database during VACUUM?
>>>
>>> Kind regards,
>>>
>>> Ben Clewett.
>>>
>>> Ben Clewett wrote:
>>>
>>>> Dear SQLite,
>>>> I am experiencing a problem.  Every so often, say one in 100,000  
>>>> 'SELECT' queries, I get this error:
>>>> "database schema has changed"
>>>> This is false, the schema has not changed.
>>>> Can any person offer a reason why this might be, and possibly a  
>>>> solution to this problem?
>>>> I am using SQLite 3.1.6 and storing my database on /dev/shm.
>>>> Kind regards,
>>>> Ben Clewett.
>>>>
>>>
>>>
>>
>> ______________________________________
>>
>> Tito Ciuro
>> http://www.webbo.com
>>
>

Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

Tito Ciuro
Hi Ben,

What do you mean? Try to reopen the database after VACUUM. That  
should do it...

-- Tito

On 02/06/2005, at 9:36, Ben Clewett wrote:

> Ben Clewett wrote:
>
>> So as I understand the posting.  After each VACUUM it is advisable  
>> to shutdown and restart SQLite, or run a single INSERT to clear  
>> the error...
>>
>
> After a lot of testing, I can find no instance of where SQLite  
> needs restarting, or anything else after a VACUUM.  Although maybe  
> some locking may be needed during the VACUUM in a multi-thread  
> scenario.
>
> Ben
>
>
>
>> I'll do some testing :)
>> Ben
>> Tito Ciuro wrote:
>>
>>> Hi Ben,
>>>
>>> Check this post and see if it helps: http://www.mail-archive.com/ 
>>> [hidden email]/msg04887.html
>>>
>>> Regards,
>>>
>>> -- Tito
>>>
>>> On 01/06/2005, at 12:16, Ben Clewett wrote:
>>>
>>>
>>>> Some extra information:
>>>>
>>>> I also note I run a VACUUM every 50 minutes.  Could this result  
>>>> in  the "database schema has changed" message I sometimes see?    
>>>> Possibly one thread accessing database during VACUUM?
>>>>
>>>> Kind regards,
>>>>
>>>> Ben Clewett.
>>>>
>>>> Ben Clewett wrote:
>>>>
>>>>
>>>>> Dear SQLite,
>>>>> I am experiencing a problem.  Every so often, say one in  
>>>>> 100,000  'SELECT' queries, I get this error:
>>>>> "database schema has changed"
>>>>> This is false, the schema has not changed.
>>>>> Can any person offer a reason why this might be, and possibly  
>>>>> a  solution to this problem?
>>>>> I am using SQLite 3.1.6 and storing my database on /dev/shm.
>>>>> Kind regards,
>>>>> Ben Clewett.
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>> ______________________________________
>>>
>>> Tito Ciuro
>>> http://www.webbo.com
>>>
>>>
>
>

______________________________________

Tito Ciuro
http://www.webbo.com

Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

thatsanicehatyouhave
In reply to this post by Ben Clewett
On the subject of "database schema has changed" error messages....  
I'm getting the same whenever I try to use precompiled queries in  
3.2.1. Is anyone else seeing the same thing? Is there a reason that  
this should be happening? Taking the time to close/reopen the db  
seems to defeat the purpose of precompiling the query.

Cheers,

Demitri
Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

Ben Clewett
In reply to this post by Tito Ciuro
Tito Ciuro wrote:
> Hi Ben,
>
> What do you mean? Try to reopen the database after VACUUM. That  should
> do it...

The posting seemed to indicate a user was experiencing the 'database
schema has changed' during the first INSERT after a VACUUM.

The suggested solution was to close/open the db after the VACUUM.

I've tried creating tables with millions of rows, deleting various
amounts of data, VACUUM and then INSERT.  I cannot replicate this error.
    It always works.  Possible this was related to an older version of
SQLite, or there is some special case of VACUUM which I am not encountering.

The only time I get this message is when another thread executes a query
during a VACUUM.  I use the 'prepare statement' method, and my guess is
that the prepare and execute on one thread, straddle the VACUUM from
another, and hence fail.

But maybe I am missing something... :)

Ben



>
> -- Tito
>
> On 02/06/2005, at 9:36, Ben Clewett wrote:
>
>> Ben Clewett wrote:
>>
>>> So as I understand the posting.  After each VACUUM it is advisable  
>>> to shutdown and restart SQLite, or run a single INSERT to clear  the
>>> error...
>>>
>>
>> After a lot of testing, I can find no instance of where SQLite  needs
>> restarting, or anything else after a VACUUM.  Although maybe  some
>> locking may be needed during the VACUUM in a multi-thread  scenario.
>>
>> Ben
>>
>>
>>
>>> I'll do some testing :)
>>> Ben
>>> Tito Ciuro wrote:
>>>
>>>> Hi Ben,
>>>>
>>>> Check this post and see if it helps: http://www.mail-archive.com/ 
>>>> [hidden email]/msg04887.html
>>>>
>>>> Regards,
>>>>
>>>> -- Tito
>>>>
>>>> On 01/06/2005, at 12:16, Ben Clewett wrote:
>>>>
>>>>
>>>>> Some extra information:
>>>>>
>>>>> I also note I run a VACUUM every 50 minutes.  Could this result  
>>>>> in  the "database schema has changed" message I sometimes see?    
>>>>> Possibly one thread accessing database during VACUUM?
>>>>>
>>>>> Kind regards,
>>>>>
>>>>> Ben Clewett.
>>>>>
>>>>> Ben Clewett wrote:
>>>>>
>>>>>
>>>>>> Dear SQLite,
>>>>>> I am experiencing a problem.  Every so often, say one in  100,000  
>>>>>> 'SELECT' queries, I get this error:
>>>>>> "database schema has changed"
>>>>>> This is false, the schema has not changed.
>>>>>> Can any person offer a reason why this might be, and possibly  a  
>>>>>> solution to this problem?
>>>>>> I am using SQLite 3.1.6 and storing my database on /dev/shm.
>>>>>> Kind regards,
>>>>>> Ben Clewett.
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>> ______________________________________
>>>>
>>>> Tito Ciuro
>>>> http://www.webbo.com
>>>>
>>>>
>>
>>
>
> ______________________________________
>
> Tito Ciuro
> http://www.webbo.com
>

Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

D. Richard Hipp
On Thu, 2005-06-02 at 15:11 +0100, Ben Clewett wrote:
> The posting seemed to indicate a user was experiencing the 'database
> schema has changed' during the first INSERT after a VACUUM.
>
> The suggested solution was to close/open the db after the VACUUM.
>

I do not know who suggested a close/open of the database
after a VACUUM, but that seems like bad advice to me.
It is harmless though unnecessary.

An SQLITE_SCHEMA error simply means that you need to
rerun sqlite3_prepare in order to regenerate the statement
because the structure of the database has changed since
the last time sqlit3_prepare was run.  All you have
to do is rerun sqlite3_prepare.  Closing and opening
the database first is not necessary.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: database schema has changed

Dan Kennedy
In reply to this post by thatsanicehatyouhave
This might be redundant, but:

http://www.sqlite.org/faq.html#q17

As was mentioned earlier in the thread, for non-intuitive reasons
a VACUUM command counts as modifying the schema.

For maximum robustness, every time an SQL statement is executed using
the prepare()/reset()/finalize() API the code should handle the
SQLITE_SCHEMA error.

Dan.


--- Demitri Muna <[hidden email]> wrote:

> On the subject of "database schema has changed" error messages....  
> I'm getting the same whenever I try to use precompiled queries in  
> 3.2.1. Is anyone else seeing the same thing? Is there a reason that  
> this should be happening? Taking the time to close/reopen the db  
> seems to defeat the purpose of precompiling the query.
>
> Cheers,
>
> Demitri
>



               
__________________________________
Discover Yahoo!
Find restaurants, movies, travel and more fun for the weekend. Check it out!
http://discover.yahoo.com/weekend.html