0

I have the following SQLite db:

sqlite> .schema
...
CREATE TABLE history (
    date_created DATETIME,
    date_updated DATETIME,
    id INTEGER NOT NULL,
    ...
    PRIMARY KEY (id),
    FOREIGN KEY ... ,
    ...
);
...

sqlite> SELECT * FROM pragma_table_info('history');
0|date_created|DATETIME|0||0
1|date_updated|DATETIME|0||0
2|id|INTEGER|1||1
...

Even though the primary key is id, doing a query on it leads to duplicate values:

sqlite> select rowid, id, date_updated from history;
...
499|499|2021-03-12 18:45:46.433
500|500|2021-03-12 18:47:49.616
501|501|2021-03-12 18:47:50.322
500|500|2021-03-12 19:37:14.320
501|501|2021-03-12 19:37:15.153

Total row count also somehow 503.

How can this even be possible? I have a server running on SQLAlchemy to use this DB, if that makes any difference.

Mazyod
  • 22,319
  • 10
  • 92
  • 157
  • This is not possible. You are querying a table which does not have `id` as primary key. – forpas Mar 12 '21 at 20:01
  • @forpas I added `rowid` as well, it's also duplicated. – Mazyod Mar 12 '21 at 20:04
  • Execute: `SELECT * FROM pragma_table_info('history')` – forpas Mar 12 '21 at 20:07
  • There is something seriously wrong with this. Restart your PC/Server and try it again. – forpas Mar 12 '21 at 20:12
  • Restarted, also tried reading the db from a new docker container with just the latest sqlite3 installed, no luck .. I think it somehow got corrupted by the software I was using "DB Browser for SQLite". After running the server again, it created a new entry, 502, and everything seems fine again from that point on, although 500, 501 are still showing as duplicated in the select query. – Mazyod Mar 12 '21 at 20:25
  • 1
    Check this: https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database/57872238#57872238 – forpas Mar 12 '21 at 20:30
  • 1
    Amazing, it's fixed. Thanks for the help! – Mazyod Mar 12 '21 at 20:34

0 Answers0