Re: [sqlite] Database schema has changed?

[Brad Stiles]

> 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?

[Tim Streater]

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?

[Gerry Snyder]

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?

[Simon Slavin]

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?

[Tim Streater]

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?

[Simon Slavin]

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?

[Igor Tandetnik]

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

[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 <[EMAIL PROTECTED]>

Re: [sqlite] database schema has changed

[Ben Clewett]

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

[Demitri Muna]

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

[mike cariotoglou]

> 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

[Randall Fox]

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

[D. Richard Hipp]

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

[Randall Fox]

>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

[P. Morandi]

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

[Tito Ciuro]

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