About once every few months I get this error (under OS X 10.7.1):
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).
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'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?
--
Cheers -- Tim
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
