Re: [sqlite] Database schema has changed?
> Mmmm. Looks like there's no elegant way to do it. I looked into this a couple > of years ago when designing the setup. So: > > 1) Leave things as they are. Downside is the unexplained error every few > months and it's a slightly clumsy method. Upside is if the schema changes > there's no extra work to do. > > 2) Gerry suggests listing out the columns explicitly. Upside is this > simplifies the move operation, downside is extra maintenance. I suppose I > could get clever and store the column names in a Settings database I already > have and use that to generate the SQL. > > 3) Use your suggestion. Upsides as you describe - simple move. Downside is an > extra column with the same value in it for all rows. The irritating part is > that there is a unique value for each database stored in another table in the > same db. But it appears I can't do: PRIMARY KEY (OTHERTABLE.ORIG, ABSID) > which would have been nice. 4. Use one of the myriad code generators available on the web to generate the code for this specific task. When the schema changes, regenerate the code. 5. Dynamically build your SQL statement based on the schema as it exists in the copy of the database you're using. This has the advantage of allowing the same code to service multiple database versions. Reading the schema and building the query could be done when the DB is opened and saved, perhaps even in the DB itself, rather than doing every time the query is called. /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database schema has changed?
On 13 Sep 2011 at 17:03, Simon Slavin wrote: > On 13 Sep 2011, at 4:38pm, Tim Streater wrote: > >> In general the row could get moved around any number of databases and then >> back to the original one. > > That's one reason I think making absid unique then manually manipulating absid > values is a bad idea. Labelling which system each row originated in, and > including that column in the unique key, is enough for you to avoid all > collisions. Then you no longer have to worry about changing any absids: leave > the absid exactly as it is. Move your rows from one database file to another > with a simple INSERT, never worrying about changing anything in the record. Mmmm. Looks like there's no elegant way to do it. I looked into this a couple of years ago when designing the setup. So: 1) Leave things as they are. Downside is the unexplained error every few months and it's a slightly clumsy method. Upside is if the schema changes there's no extra work to do. 2) Gerry suggests listing out the columns explicitly. Upside is this simplifies the move operation, downside is extra maintenance. I suppose I could get clever and store the column names in a Settings database I already have and use that to generate the SQL. 3) Use your suggestion. Upsides as you describe - simple move. Downside is an extra column with the same value in it for all rows. The irritating part is that there is a unique value for each database stored in another table in the same db. But it appears I can't do: PRIMARY KEY (OTHERTABLE.ORIG, ABSID) which would have been nice. > I believe the latter. Five columns will be copied into five columns in column > order, regardless of column names. If that's the case I can't see where the occasional error is coming from. Thanks for your responses. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database schema has changed?
On 9/13/2011 8:38 AM, Tim Streater wrote: I don't see an easy way of solving conflicting absids. The hard way of solving this is to select all the fields of the messages table explicitly (except absid), so I can then insert them into a new row in the destination table. But I'm trying to avoid this as a maintenance headache (I may wish to change the schema for messages from time to time). The explicit column selection sure seems like the right way of solving it to me. What you are doing to avoid it is (obviously from the discussion) difficult and error-prone. Yes, you will have to change the statement when the schema changes. I think of that as a task, not a headache. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database schema has changed?
On 13 Sep 2011, at 4:38pm, Tim Streater wrote: > In general the row could get moved around any number of databases and then > back to the original one. That's one reason I think making absid unique then manually manipulating absid values is a bad idea. Labelling which system each row originated in, and including that column in the unique key, is enough for you to avoid all collisions. Then you no longer have to worry about changing any absids: leave the absid exactly as it is. Move your rows from one database file to another with a simple INSERT, never worrying about changing anything in the record. SQLite will use its own internal 'rowid' for unique row numbering and your app need never care about them. > Here's a simple question. If I do this: > >insert into dst.messages select * from src.messages > > do the databases as represented by src and dst have to have the same schema, > or merely the same number of columns? I believe the latter. Five columns will be copied into five columns in column order, regardless of column names. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database schema has changed?
On 13 Sep 2011 at 13:04, Simon Slavin wrote:
> On 13 Sep 2011, at 12:13pm, Tim Streater wrote:
>
>> SQLite error: general code: HY000 error: 17, database schema has changed
>>
>> and since all the rest of the time the code works fine I'm having some
>> trouble pinning down whether it's my bug (more likely) and if so where, or an
>> SQLite bug (less likely).
>
> The schema of a database is not the data in it -- values of fields, new rows
> inserted, etc. -- but things like what columns there are in a database.
Yeah, I know.
>> What I have is two databases (each is a mailbox) and I wish, from time to
>> time, to move a row from one database to the other (the schemas are the same
>> - but see below). Each row has a unique id, (defined as: absid integer
>> PRIMARY KEY - but see below) and when the row is moved, I want to allocate a
>> new absid value to the row in its new location. I have to do this to avoid
>> clashing with absid values for already-existing rows.
>
> I don't see an easy way of solving conflicting absids.
The hard way of solving this is to select all the fields of the messages table
explicitly (except absid), so I can then insert them into a new row in the
destination table. But I'm trying to avoid this as a maintenance headache (I
may wish to change the schema for messages from time to time).
> Whatever strategy you
> use, there's still a chance you'll get a crash. And you have the problem of
> each copy of the database showing different absid numbers for the same data.
This is not a problem.
> I'd like to suggest an alternative strategy:
>
> Create a new column which indicates which database the row originated in. For
> each of those two databases, when you create a new row, set the right value
> for this 'origin' column. Make your primary key not just 'absid' but
> '(origin,absid)'.
>
> You can still define 'absid' as 'INTEGER AUTOINCREMENT', but not 'INTEGER
> PRIMARY KEY'. Something like
>
> CREATE TABLE myTable (orig TEXT, absid INTEGER AUTOINCREMENT, a, b, c, d,
> PRIMARY KEY (orig, absid))
In general the row could get moved around any number of databases and then back
to the original one.
>> I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the
>> source database):
>>
>> $dbh->query ("attach database ':memory:' as mem");
>> $dbh->query ($create_messages); // Create the messages table in the
>> memory database
>> $dbh->query ("attach database '" . $mailbox . "' as dst");
>> $dbh->query ("insert into mem.messages select * from main.messages where
>> absid='$absid'");// *** The failing statement ***
>> $dbh->query ("update mem.messages set absid=null");
>> $dbh->query ("insert into dst.messages select * from mem.messages");
>> $absid = $dbh->lastInsertId ();
>>
>> The only way I could find to do what I need regarding a new absid value is,
>> as above, to copy the row to a memory database, set its absid to null, and
>> then copy to the destination database. Even to do this I've had to define
>> absid in the memory database as "absid integer" rather than "absid integer
>> PRIMARY KEY". Is this the cause of the error message? If so, why does it work
>> 99.9% of the time?
>
> Two records with the same absid will violate your PRIMARY KEY contraint,
> because they break the UNIQUE requirement. Perhaps the time they fail is when
> by coincidence both databases generate entries with the same absid.
I don't see how this can have an impact. The row is copied to an intermediate
database (the memory one). Its absid is then set to null (in the memory
database), so that when the second insert is done, the destination database can
choose a new absid value.
Here's a simple question. If I do this:
insert into dst.messages select * from src.messages
do the databases as represented by src and dst have to have the same schema, or
merely the same number of columns?
Meanwhile I think I'll refresh my understanding of INTEGER PRIMARY KEY and
INTEGER AUTOINCREMENT.
--
Cheers -- Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database schema has changed?
On 13 Sep 2011, at 12:13pm, Tim Streater wrote:
> SQLite error: general code: HY000 error: 17, database schema has changed
>
> and since all the rest of the time the code works fine I'm having some
> trouble pinning down whether it's my bug (more likely) and if so where, or an
> SQLite bug (less likely).
The schema of a database is not the data in it -- values of fields, new rows
inserted, etc. -- but things like what columns there are in a database. If
you're not issuing any 'CREATE' or 'DROP' instructions and you're getting the
above error, there may be a big underlying problem with your system.
I'd recommend running PRAGMA integrity_check() on your database, at the very
least.
> What I have is two databases (each is a mailbox) and I wish, from time to
> time, to move a row from one database to the other (the schemas are the same
> - but see below). Each row has a unique id, (defined as: absid integer
> PRIMARY KEY - but see below) and when the row is moved, I want to allocate a
> new absid value to the row in its new location. I have to do this to avoid
> clashing with absid values for already-existing rows.
I don't see an easy way of solving conflicting absids. Whatever strategy you
use, there's still a chance you'll get a crash. And you have the problem of
each copy of the database showing different absid numbers for the same data.
I'd like to suggest an alternative strategy:
Create a new column which indicates which database the row originated in. For
each of those two databases, when you create a new row, set the right value for
this 'origin' column. Make your primary key not just 'absid' but
'(origin,absid)'.
You can still define 'absid' as 'INTEGER AUTOINCREMENT', but not 'INTEGER
PRIMARY KEY'. Something like
CREATE TABLE myTable (orig TEXT, absid INTEGER AUTOINCREMENT, a, b, c, d,
PRIMARY KEY (orig, absid))
> I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the
> source database):
>
> $dbh->query ("attach database ':memory:' as mem");
> $dbh->query ($create_messages); // Create the messages table in the
> memory database
> $dbh->query ("attach database '" . $mailbox . "' as dst");
> $dbh->query ("insert into mem.messages select * from main.messages where
> absid='$absid'");// *** The failing statement ***
> $dbh->query ("update mem.messages set absid=null");
> $dbh->query ("insert into dst.messages select * from mem.messages");
> $absid = $dbh->lastInsertId ();
>
> The only way I could find to do what I need regarding a new absid value is,
> as above, to copy the row to a memory database, set its absid to null, and
> then copy to the destination database. Even to do this I've had to define
> absid in the memory database as "absid integer" rather than "absid integer
> PRIMARY KEY". Is this the cause of the error message? If so, why does it work
> 99.9% of the time?
Two records with the same absid will violate your PRIMARY KEY contraint,
because they break the UNIQUE requirement. Perhaps the time they fail is when
by coincidence both databases generate entries with the same absid. However, I
don't know that this should lead to the error message you reported: a message
about SCHEMA should really be about something else.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database schema has changed?
Tim Streater wrote:
> I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the
> source database):
>
> $dbh->query ("attach database ':memory:' as mem");
> $dbh->query ($create_messages); // Create the messages table in
> the memory database
> $dbh->query ("attach database '" . $mailbox . "' as dst");
> $dbh->query ("insert into mem.messages select * from main.messages where
> absid='$absid'");// *** The failing statement ***
I'm not familiar with PDO and PHP, but my educated guess is, the language
binding layer running on top of SQLite has cached the prepared INSERT statement
from prior execution. But that statement's handle has been invalidated by
intervening ATTACH statements (see http://www.sqlite.org/c3ref/prepare.html ,
in particular the difference between sqlite3_prepare and sqlite3_prepare_v2).
See if there's any way to instruct the binding to clear its cache of prepared
statements.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database schema has changed
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 <[EMAIL PROTECTED]> 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
Re: [sqlite] database schema has changed
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 <[EMAIL PROTECTED]>
Re: [sqlite] database schema has changed
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/ sqlite-users@sqlite.org/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
Re: [sqlite] database schema has changed
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
Re: [sqlite] database schema has changed
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/ sqlite-users@sqlite.org/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
Re: [sqlite] database schema has changed
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/ sqlite-users@sqlite.org/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
Re: [sqlite] database schema has changed
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/ sqlite-users@sqlite.org/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
Re: [sqlite] database schema has changed
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.
Re: [sqlite] database schema has changed
Hi Ben, Check this post and see if it helps: http://www.mail-archive.com/ sqlite-users@sqlite.org/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
Re: [sqlite] database schema has changed
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 <[EMAIL PROTECTED]>
Re: [sqlite] database schema has changed
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.
RE: [sqlite] DATABASE SCHEMA HAS CHANGED
> In SQLite version 3.0, when a schema change occurs, SQLite > automatically goes back to step 1, rereads the schema, and > tries again. So you should never get an SQLITE_SCHEMA error > in version 3.0. Back in version 2.8, you could get an > SQLITE_SCHEMA error in some circumstances. When you do, all > you have to do is retry the command and it should work. > > So to answer your questions: > >No, this is not a serious bug. You just need to be prepared to >reissue any SQL statement that returns SQLITE_SCHEMA. > >Yes, this issue is fixed in version 3.0. > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > This is not entirely correct IMHO. I can easily re-create an SQLITE_SCHEMA error in version 3.8. you just need two processes to do it: *. Open a database from one processs *. Do a select * from a table *. Open the same database from another process *. Create a new table from the second process *. Go back to the first process, and do a select * on the same table you used before. You will get an SQLITE_SCHEMA error, once. DRH, can you confirm this ? According to your description above , it should not happen.
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
That's true, I didn't mention the version, sorry ... It's 2.8. I need to execute again the statement to solve the problem. Thanks to everybody Paolo - Original Message - From: "Randall Fox" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, November 21, 2004 6:21 AM Subject: Re: [sqlite] DATABASE SCHEMA HAS CHANGED On Sat, 20 Nov 2004 08:08:48 -0500, you wrote: >So to answer your questions: > > No, this is not a serious bug. You just need to be prepared to > reissue any SQL statement that returns SQLITE_SCHEMA. > > Yes, this issue is fixed in version 3.0. Thank you.. It seems the original poster didn't say what version, and a follow up mentioned the sqlite3 structure, so I assumed it was v3. Good to know it is not a problem. Randall Fox
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
On Sat, 20 Nov 2004 08:08:48 -0500, you wrote: >So to answer your questions: > > No, this is not a serious bug. You just need to be prepared to > reissue any SQL statement that returns SQLITE_SCHEMA. > > Yes, this issue is fixed in version 3.0. Thank you.. It seems the original poster didn't say what version, and a follow up mentioned the sqlite3 structure, so I assumed it was v3. Good to know it is not a problem. Randall Fox
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
Randall Fox wrote: I believe VACUUM changes the schema version numbering. After you VACUUM, your sqlite3 struct holds information about your previous database version. Solution: reopen the database and SQLite will pick up the new changes. After that, INSERT will not report an error (until you VACUUM again, that is.) This sounds like a major bug. I use Sqlite embedded in my app and an error like that could be catastrophic. Should I close and reopen the DB every time after a vacuum? Is there a fix? I was not able to recreate the error but I just upgraded to v3 today so I am still testing.. The steps SQLite uses to process a single statement of SQL are roughly as follows: 1) Parse the SQL statement 2) Generte virtual machine code to execute the statement 3) Open the database file 4) Execute the virtual machine code 5) Close the database files Step (2) is based on the last known schema for the database. When step (4) begins, the first thing it does is make sure that the schema used in step (2) is the same as the current schema. If the schema has changed, then the generate virtual machine code might be incorrect so execution aborts with an SQLITE_SCHEMA error. It also sets a flag so that the schema will be automatically reread from the database file prior to doing another parse. Note that the parser cannot check to see if it has the current schema because at the time the parser and code generator are running, the database file is not yet open. In SQLite version 3.0, when a schema change occurs, SQLite automatically goes back to step 1, rereads the schema, and tries again. So you should never get an SQLITE_SCHEMA error in version 3.0. Back in version 2.8, you could get an SQLITE_SCHEMA error in some circumstances. When you do, all you have to do is retry the command and it should work. So to answer your questions: No, this is not a serious bug. You just need to be prepared to reissue any SQL statement that returns SQLITE_SCHEMA. Yes, this issue is fixed in version 3.0. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
>I believe VACUUM changes the schema version numbering. After you >VACUUM, your sqlite3 struct holds information about your previous >database version. Solution: reopen the database and SQLite will pick up >the new changes. After that, INSERT will not report an error (until you >VACUUM again, that is.) This sounds like a major bug. I use Sqlite embedded in my app and an error like that could be catastrophic. Should I close and reopen the DB every time after a vacuum? Is there a fix? I was not able to recreate the error but I just upgraded to v3 today so I am still testing.. Randall Fox
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
Thank you Tito. I'll try it. Bye - Original Message - From: "Tito Ciuro" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, November 19, 2004 12:38 PM Subject: Re: [sqlite] DATABASE SCHEMA HAS CHANGED > Hello Paolo, > > On Nov 19, 2004, at 10:24, P. Morandi wrote: > > > Hi everybody. > > > > After using VACUUM on a sqlite db, the first time I try to do an > > INSERT in a table > > (always the same table) I get the "DATABASE SCHEMA HAS CHANGED" error, > > but later > > queries have no problems, only the first one. > > If I don't pack the db (no using of VACUUM) no problem. > > Does anyone have any idea of the causes of this error message? > > I believe VACUUM changes the schema version numbering. After you > VACUUM, your sqlite3 struct holds information about your previous > database version. Solution: reopen the database and SQLite will pick up > the new changes. After that, INSERT will not report an error (until you > VACUUM again, that is.) > > I hope this helps, > > -- Tito > > >
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
Hello Paolo, On Nov 19, 2004, at 10:24, P. Morandi wrote: Hi everybody. After using VACUUM on a sqlite db, the first time I try to do an INSERT in a table (always the same table) I get the "DATABASE SCHEMA HAS CHANGED" error, but later queries have no problems, only the first one. If I don't pack the db (no using of VACUUM) no problem. Does anyone have any idea of the causes of this error message? I believe VACUUM changes the schema version numbering. After you VACUUM, your sqlite3 struct holds information about your previous database version. Solution: reopen the database and SQLite will pick up the new changes. After that, INSERT will not report an error (until you VACUUM again, that is.) I hope this helps, -- Tito
