Hi, I've somehow ended up with a table which contains two records for the same primary key - well actually I've got two primary keys like that, so I have four records with two primary keys between them.
I've been unable to reproduce this from a clean database, so I attach my database file to this email. Here are some oddities: ``` $ sqlite3 bookdb.sqlite SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. sqlite> select * from books where bookIsbn = "9781496030825"; 9781496030825|Can Such Things Be?||9781496030825.jpg||||Bierce, Ambrose||||0||0|London||F sqlite> select * from books where bookIsbn = "9780099477310"; 9780099477310|Catch-22||9780099477310.jpg||||Heller, Joseph||||0||0|London||F sqlite> .output books_issue sqlite> .dump books sqlite> .quit $ grep "9781496030825" < books_issue INSERT INTO books VALUES('9781496030825','Can Such Things Be?','','9781496030825.jpg','','','','Bierce, Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F'); INSERT INTO books VALUES('9781496030825','Can Such Things Be?','','9781496030825.jpg','','','','Bierce, Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F'); $ grep "9780099477310" < books_issue INSERT INTO books VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller, Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F'); INSERT INTO books VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller, Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F'); $ sqlite3 bookdb.sqlite SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. sqlite> drop table books; sqlite> $ sqlite3 bookdb.sqlite < books_issue Error: near line 697: UNIQUE constraint failed: books.bookIsbn Error: near line 698: UNIQUE constraint failed: books.bookIsbn ``` Updating either affected record results in the second copy in the .dump output being updated, the first copy has the original state. The table has always had a primary key constraint, so I'm not sure how it's ended up in its current state. However, even if there were not a primary key constraint, there do seem to be two very real bugs here: SELECT gives different results to .dump, and .dump is producing output which can't be restored. I'm not sure if you'll be able to make anything of this, as I say I haven't been able to reproduce it from a blank database, but I figure you'll be better at debugging this than me. Thanks -- Michael Walker (http://www.barrucadu.co.uk) _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users