Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output
On 21 Dec 2019, at 21:42, Michael Walker (barrucadu) wrote: > I'm not sure the attachment to my first email got through ... Correct. The list strips them. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output
The mailing list strips attachments, btw.
Anyways, looking at that, yeah, they're all text values:
sqlite> SELECT bookIsbn, typeof(bookIsbn) FROM books WHERE bookAuthor LIKE
'%Ambrose%';
bookIsbntypeof(bookIsbn)
--
0486280381 text
9781496030 text
9781496030 text
There are other problems with the database too:
sqlite> PRAGMA integrity_check;
integrity_check
---
row 649 missing from index sqlite_autoindex_books_1
row 659 missing from index sqlite_autoindex_books_1
row 665 missing from index sqlite_autoindex_books_1
row 667 missing from index sqlite_autoindex_books_1
row 674 missing from index sqlite_autoindex_books_1
row 676 missing from index sqlite_autoindex_books_1
I'd start going through https://www.sqlite.org/howtocorrupt.html and trying
to figure out if anything there might have happened.
On Sat, Dec 21, 2019 at 1:43 PM Michael Walker (barrucadu) <
m...@barrucadu.co.uk> wrote:
> Hi Shawn,
>
> Thanks for your response. Though that doesn't seem to be the case:
>
> sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
> "9781496030825";
> 9781496030825|text
> sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
> "9780099477310";
> 9780099477310|text
>
> The column is a VARCHAR:
>
> CREATE TABLE `books` (
>`bookIsbn` VARCHAR NOT NULL PRIMARY KEY,
>`bookTitle` VARCHAR NOT NULL,
>`bookSubtitle` VARCHAR NOT NULL,
>`bookCover` VARCHAR NULL,
>`bookVolume` VARCHAR NOT NULL,
>`bookFascicle` VARCHAR NOT NULL,
>`bookVoltitle` VARCHAR NOT NULL,
>`bookAuthor` VARCHAR NOT NULL,
>`bookTranslator` VARCHAR NULL,
>`bookEditor` VARCHAR NULL,
>`bookSorting` VARCHAR NULL,
>`bookRead` BOOLEAN NOT NULL,
>`bookLastRead` TIMESTAMP NULL,
>`bookNowReading` BOOLEAN NOT NULL,
>`bookLocation` VARCHAR NOT NULL,
>`bookBorrower` VARCHAR NOT NULL,
>`bookCategoryCode` VARCHAR NOT NULL,
>FOREIGN KEY(`bookCategoryCode`) REFERENCES
> `book_categories`(`categoryCode`)
> );
>
> I'm not sure the attachment to my first email got through, so here's the
> database:
> https://misc.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite
>
>
> ‐‐‐ Original Message ‐‐‐
> On Saturday, 21 December 2019 21:37, Shawn Wagner
> wrote:
>
> > Without seeing your table definition, this is just a guess, but maybe the
> > duplicate keys are stored as different types, with the primary key column
> > having an affinity that doesn't force one particular storage class:
> >
> > sqlite> CREATE TABLE test(id PRIMARY KEY);
> > sqlite> INSERT INTO test VALUES('12345');
> > sqlite> INSERT INTO test VALUES(12345);
> > sqlite> SELECT id, typeof(id) FROM test;
> > id typeof(id)
> >
> > 12345 text
> > 12345 integer
> > sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
> > id typeof(id)
> >
> > 12345 text
> >
> > On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
> > m...@barrucadu.co.uk> wrote:
> >
> > > 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.jpgBierce,
> > > Ambrose0||0|London||F
> > > sqlite> select * from books where bookIsbn = "9780099477310";
> > > 9780099477310|Catch-22||9780099477310.jpgHeller,
> > > Joseph0||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
Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output
Hi Shawn,
Thanks for your response. Though that doesn't seem to be the case:
sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
"9781496030825";
9781496030825|text
sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
"9780099477310";
9780099477310|text
The column is a VARCHAR:
CREATE TABLE `books` (
`bookIsbn` VARCHAR NOT NULL PRIMARY KEY,
`bookTitle` VARCHAR NOT NULL,
`bookSubtitle` VARCHAR NOT NULL,
`bookCover` VARCHAR NULL,
`bookVolume` VARCHAR NOT NULL,
`bookFascicle` VARCHAR NOT NULL,
`bookVoltitle` VARCHAR NOT NULL,
`bookAuthor` VARCHAR NOT NULL,
`bookTranslator` VARCHAR NULL,
`bookEditor` VARCHAR NULL,
`bookSorting` VARCHAR NULL,
`bookRead` BOOLEAN NOT NULL,
`bookLastRead` TIMESTAMP NULL,
`bookNowReading` BOOLEAN NOT NULL,
`bookLocation` VARCHAR NOT NULL,
`bookBorrower` VARCHAR NOT NULL,
`bookCategoryCode` VARCHAR NOT NULL,
FOREIGN KEY(`bookCategoryCode`) REFERENCES
`book_categories`(`categoryCode`)
);
I'm not sure the attachment to my first email got through, so here's the
database:
https://misc.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite
‐‐‐ Original Message ‐‐‐
On Saturday, 21 December 2019 21:37, Shawn Wagner
wrote:
> Without seeing your table definition, this is just a guess, but maybe the
> duplicate keys are stored as different types, with the primary key column
> having an affinity that doesn't force one particular storage class:
>
> sqlite> CREATE TABLE test(id PRIMARY KEY);
> sqlite> INSERT INTO test VALUES('12345');
> sqlite> INSERT INTO test VALUES(12345);
> sqlite> SELECT id, typeof(id) FROM test;
> id typeof(id)
>
> 12345 text
> 12345 integer
> sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
> id typeof(id)
>
> 12345 text
>
> On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
> m...@barrucadu.co.uk> wrote:
>
> > 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.jpgBierce,
> > Ambrose0||0|London||F
> > sqlite> select * from books where bookIsbn = "9780099477310";
> > 9780099477310|Catch-22||9780099477310.jpgHeller,
> > Joseph0||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
>
> sqlite-users mailing list
>
Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output
Without seeing your table definition, this is just a guess, but maybe the
duplicate keys are stored as different types, with the primary key column
having an affinity that doesn't force one particular storage class:
sqlite> CREATE TABLE test(id PRIMARY KEY);
sqlite> INSERT INTO test VALUES('12345');
sqlite> INSERT INTO test VALUES(12345);
sqlite> SELECT id, typeof(id) FROM test;
id typeof(id)
-- --
12345 text
12345 integer
sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
id typeof(id)
-- --
12345 text
On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
m...@barrucadu.co.uk> wrote:
> 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.jpgBierce,
> Ambrose0||0|London||F
> sqlite> select * from books where bookIsbn = "9780099477310";
> 9780099477310|Catch-22||9780099477310.jpgHeller,
> Joseph0||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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output
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.jpgBierce,
Ambrose0||0|London||F
sqlite> select * from books where bookIsbn = "9780099477310";
9780099477310|Catch-22||9780099477310.jpgHeller, Joseph0||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
