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
