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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
