SQLite Forum

Error: database disk image is malformed for every large database after a few SELECT instruction
Login

Error: database disk image is malformed for every large database after a few SELECT instruction

(1) By anonymous on 2021-02-03 10:43:55 [source]

System: Ubuntu 20.04 file system ext4: SQLite version 3.31.1 2020-01-27 19:55:54

database sizes: 280M, 700M, 1.2T Contents (1 table with 7 columns (text primary and 6 numbers)) indexes (all columns with numbers and 2 indexes with 2 columns)

The database has been created using a python script.

Problem: After few searches using SELECT I am consistently getting : Error: database disk image is malformed.

This problem appears on 4 different desktops with the same system but different databases

(2) By Gunter Hick (gunter_hick) on 2021-02-03 12:05:10 in reply to 1 [link] [source]

Have you checked https://sqlite.org/howtocorrupt.html for things you may be doing?

Have you tried to execute the SELECTs in the SQLite shell?

Most probably some other process is overwriting your file. Try lsof or equivalent to see who else may be acessing your file.

(3) By anonymous on 2021-02-03 12:38:50 in reply to 2 [link] [source]

Thank you for your quick answer.

Yes, I have checked the site and I do not see any relevance to me.

I only use python script to create the dictionary. SELECT is within SQlite shell.

I have checked lsof - nothing beside sqlite3 is accessing the file The timestamp on the file does not change. I understand that SELECT should not write to the database but somehow the file get corrupted after several searches without timestamp changed. When I copy the backup it starts again fine and it is getting worse and worse since my search are having more and more "disk image is malformed" problems. This makes the database practically unusable. The problem does not appear on the database ~10x smaller (~20GB) but maybe I have not performed enough searches since larger the database the fewer searches are needed to create this problem.

Best regards,

Witek

(5) By Stephan Beal (stephan) on 2021-02-03 12:51:56 in reply to 3 [link] [source]

The timestamp on the file does not change. I understand that SELECT should not write to the database but somehow the file get corrupted after several searches without timestamp changed.

Which suggests to me that your storage device is likely to blame. Only recently i had to dispose of a flaky SSD which would occasionally randomly corrupt files which were never written to by the OS.

This problem appears on 4 different desktops with the same system but different databases

It's not clear from that statement whether you're running the same external drive on multiple computers or whether they're accessing the db via a network, but my suspicion is the former. If that's the case, and multiple computers are failing in the same way with that same device, there's even more reason to suspect the storage device.

(6) By anonymous on 2021-02-03 16:08:31 in reply to 5 [link] [source]

No not the same drive and not the same databases, the drive for each database is always internal for each desktop (3x 2TB Disk model: INTEL SSDPEKNW020T8 and in one case Samsung SSD 970 EVO Plus 2TB). The problem happens on all of them so must be reletad or to operating system and file system or sqlite3. I suspect sqlite3 since it is quite consistent (backup is fine and only internal operations are corrupting the database - it can be indexing of the database which creates the problems - however I cannot see how the initial problem created by indexing can get worse by running SELECT SQLs)

(4) By Richard Hipp (drh) on 2021-02-03 12:49:42 in reply to 1 [link] [source]

Please post:

  1. The database schema

  2. The SQL that is giving you the error.

  3. The result of running "PRAGMA quick_check". If time allows, also the result of "PRAGMA integrity_check".

(7) By anonymous on 2021-02-03 16:09:17 in reply to 4 [link] [source]

Still running pragma. Will post when it is done.

(8) By anonymous on 2021-02-04 14:06:55 in reply to 4 [link] [source]

Schema:

CREATE TABLE combination7 (mer7 TEXT PRIMARY KEY, pop_ur50 DEFAULT 0, pop_dev_ur50 DEFAULT 0, pop_est DEFAULT 0, amylogen DEFAULT 0, pop_ur50_nr DEFAULT 0, pop_dev_ur50_nr DEFAULT 0);

CREATE INDEX id_pop on combination7(pop_ur50);

CREATE INDEX id_pop_dev on combination7(pop_dev_ur50);

CREATE INDEX id_pop_est on combination7(pop_est);

CREATE INDEX id_amylogen on combination7(amylogen);

CREATE INDEX id_pop_nr on combination7(pop_ur50_nr);

CREATE INDEX id_pop_dev_nr on combination7(pop_dev_ur50_nr);

CREATE INDEX id_pop_dev_amylog on combination7(pop_dev_ur50,amylogen);

CREATE INDEX id_pop_dev_nr_amylog on combination7(pop_dev_ur50_nr,amylogen);

/* No STAT tables available */

sqlite> select count(*) from combination7 where amylogen < 0.5;

Error: database disk image is malformed

sqlite> pragma quick_check;

ok

sqlite> pragma integrity_check;

(still running)

Best regards,

Witek

(9.1) By Richard Hipp (drh) on 2021-02-04 14:19:19 edited from 9.0 in reply to 8 [link] [source]

Deleted

(10) By Richard Hipp (drh) on 2021-02-04 14:30:35 in reply to 8 [link] [source]

The query should only be looking at the id_amylogen index. Nothing else in the database file should matter. And the "PRAGMA quick_check" shows that the index is well-formed.

Please verify the first sentence of the previous paragraph by sending us the output of:

EXPLAIN QUERY PLAN
select count(*) from combination7 where amylogen < 0.5;
EXPLAIN
select count(*) from combination7 where amylogen < 0.5;

What happens if you DROP all indexes other than id_amylogen? Do you still get the error then? Can you run VACUUM after DROPPING all indexes other than id_amylogen? If you are still getting the error after dropping unused indexes and running VACUUM, how big is the database file then? Is it small enough to send me a copy?

(11) By anonymous on 2021-02-04 15:10:53 in reply to 10 [link] [source]

Dear Richard,

Should I wait until pragma integrity_check finishes (it has been running for ~24h)? If not - How to gently brake the process without destroying the db further?

I estimate that the database without indexes will still be >100GB - if less, I can share it with you.

Best

Witek

(12) By Richard Hipp (drh) on 2021-02-04 15:18:45 in reply to 11 [link] [source]

Integrity_check is read-only. You can just interrupt it.

Can you get me a ssh login to one of your machines that contains a faulty database, so that I can log in and debug it there? You can send private email to me at drh at sqlite dot org.

(13) By anonymous on 2021-02-04 15:32:52 in reply to 12 [link] [source]

sqlite> pragma integrity_check;

^CError: interrupted

sqlite> EXPLAIN QUERY PLAN

...> select count(*) from combination7 where amylogen < 0.5;

QUERY PLAN

`--SEARCH TABLE combination7 USING COVERING INDEX id_amylogen (amylogen<?)

sqlite> EXPLAIN

...> select count(*) from combination7 where amylogen < 0.5;

addr opcode p1 p2 p3 p4 p5 comment


0 Init 0 13 0 00 Start at 13

1 Null 0 1 1 00 r[1..1]=NULL

2 OpenRead 1 35847181 0 k(2,,) 00 root=35847181 iDb=0; id_amylogen

3 Null 0 2 0 00 r[2]=NULL

4 SeekGT 1 9 2 1 00 key=r[2]

5 Real 0 2 0 0.5 00 r[2]=0.5

6 IdxGE 1 9 2 1 00 key=r[2]

7 AggStep 0 0 1 count(0) 00 accum=r[1] step(r[0])

8 Next 1 6 0 00

9 AggFinal 1 0 0 count(0) 00 accum=r[1] N=0

10 Copy 1 3 0 00 r[3]=r[1]

11 ResultRow 3 1 0 00 output=r[3]

12 Halt 0 0 0 00

13 Transaction 0 0 15 0 01 usesStmtJournal=0

14 Goto 0 1 0 00

Error still is here:

sqlite> select count(*) from combination7 where amylogen < 0.5;

Error: database disk image is malformed

I will setup a user and login tomorrow (can you use teamviewer?)

Thank you so much for your help.

Best

Witek

(14) By Richard Hipp (drh) on 2021-02-04 15:37:17 in reply to 13 [link] [source]

I will setup a user and login tomorrow (can you use teamviewer?)

Please coordinate remote login via private email to drh at sqlite dot org.