Hi, We have an application that in a Linux VM that's running into SQLite DB corruption (after weeks and months of running, 4 such instances yet in different VMs).
We would appreciate some help in debugging this further to identify source of corruption! Symptom is btree page corruption, e.g. > $ sqlite3 stats.sqlite "pragma integrity_check;" > *** in database main *** > Page 3818: btreeInitPage() returns error code 11 > Page 46: btreeInitPage() returns error code 11 > Error: database disk image is malformed (Same error is raised for SELECT queries too.) There were no power-off or reboots in near time vicinity when the corruption was detected. We have poured over this document https://sqlite.org/howtocorrupt.html many times to check if any of the conditions could apply, but so far no leads yet. We have also been unable to reproduce the corruption by stressing application's SQLite DB read/write code paths for a week. I'm attaching showdb output for the DB header and 2 corrupt pages if it's of any hint. --- A bit more application setup context/information: - Linux kernel 4.4.41 - glibc 2.22 - Ext4 file system, mounted as (rw,relatime,data=ordered). - Writer C++ process: sqlite-3.17 - Creates a set of "time series" tables, each table has 2 numeric columns (timestamp, int) during initialization. - Every 1 minute, 2 threads will do total 15 writes. (using "INSERT OR REPLACE ... (timestamp, int)" SQL into 15 tables). - SQLite DB opened with SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE flags, initialized with "PRAGMA journal_mode=wal;", threading mode is Serialized for the libsqlite build, uses default VFS ("unix"). All other config params are default (e.g. autovacuum is disabled etc.). - A separate thread runs "PRAGMA quick_check;" periodically every 5 minutes, in its own separate DB connection. - Reader process: sqlite-3.11 + Python 2.7.11 - Periodically reads time series tables for a given timestamp range (usually latest 5 minutes) using SELECT queries (no INSERT/UPDATE/ DELETE from this process). - Uses same same "PRAGMA journal_mode=wal", uses the sqlite3 DBAPI module from Python standard library. Apart from above 2, no other processes are accessing the SQLite DB file. We have updated both the reader and writer to use latest SQLite 3.21, but without understanding the cause of corruption, we are unable to say if this update to latest 3.21 would indeed prevent further occurrences. Thanks, Nikhil
Pagesize: 4096 Available pages: 1..16865 000: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3. 010: 10 00 02 02 00 40 20 20 00 00 00 06 00 00 41 e1 .....@ ......A. 020: 00 00 00 00 00 00 00 00 00 00 00 56 00 00 00 04 ...........V.... 030: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 ................ 040: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ 050: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 06 ................ 060: 00 2e 09 28 00 ...(. Decoded: 010: 10 00 4096 Database page size 012: 02 2 File format write version 013: 02 2 File format read version 014: 00 0 Reserved space at end of page 018: 00 00 00 06 6 File change counter 01c: 00 00 41 e1 16865 Size of database in pages 020: 00 00 00 00 0 Page number of first freelist page 024: 00 00 00 00 0 Number of freelist pages 028: 00 00 00 56 86 Schema cookie 02c: 00 00 00 04 4 Schema format version 030: 00 00 00 00 0 Default page cache size 034: 00 00 00 00 0 Largest auto-vac root page 038: 00 00 00 01 1 Text encoding 03c: 00 00 00 00 0 User version 040: 00 00 00 00 0 Incremental-vacuum mode 044: 00 00 00 00 0 Application ID 048: 00 00 00 00 0 meta[8] 04c: 00 00 00 00 0 meta[9] 050: 00 00 00 00 0 meta[10] 054: 00 00 00 00 0 meta[11] 058: 00 00 00 00 0 meta[12] 05c: 00 00 00 06 6 Change counter for version number 060: 00 2e 09 28 3017000 SQLite version number
46: corrupt node [sm_stats_metadata], child 2 of page 2 3818: root corrupt node [sm_stat_disk_latency_rate_dm-9]
Pagesize: 4096 Available pages: 1..16865 Header on btree page 46: 000: 00 0 unknown 001: 00 00 0 Offset to first freeblock 003: 00 0b 11 Number of cells on this page 005: 01 25 293 Offset to cell content area 007: 00 0 Fragmented byte count key: lx=left-child n=payload-size r=rowid ea4: cell[0] lx: -2108128674 n: 5 d43: cell[1] lx: -2107800993 n: 5 bdd: cell[2] lx: -2107473312 n: 5 ab0: cell[3] lx: -2111208863 n: 5 97f: cell[4] lx: -2110946718 n: 5 813: cell[5] lx: -2107080093 n: 5 6bc: cell[6] lx: -2108456348 n: 5 573: cell[7] lx: -2109373851 n: 5 400: cell[8] lx: -2106621338 n: 5 286: cell[9] lx: -2106162585 n: 5 125: cell[10] lx: -2107800984 n: 5
Pagesize: 4096 Available pages: 1..16865 Header on btree page 46: 000: 00 0 unknown 001: 00 00 0 Offset to first freeblock 003: 00 0b 11 Number of cells on this page 005: 01 25 293 Offset to cell content area 007: 00 0 Fragmented byte count key: lx=left-child n=payload-size r=rowid ea4: cell[0] lx: -2108128674 n: 5 d43: cell[1] lx: -2107800993 n: 5 bdd: cell[2] lx: -2107473312 n: 5 ab0: cell[3] lx: -2111208863 n: 5 97f: cell[4] lx: -2110946718 n: 5 813: cell[5] lx: -2107080093 n: 5 6bc: cell[6] lx: -2108456348 n: 5 573: cell[7] lx: -2109373851 n: 5 400: cell[8] lx: -2106621338 n: 5 286: cell[9] lx: -2106162585 n: 5 125: cell[10] lx: -2107800984 n: 5 Page map: (H=header P=cell-index 1=page-1-header .=free-space) 000: HHHHHHHHPPPPPPPPPPPPPPPPPPPPPP.................................. 040: ................................................................ 080: ................................................................ 0c0: ................................................................ 100: .....................................[10******]................. 140: ................................................................ 180: ................................................................ 1c0: ................................................................ 200: ................................................................ 240: ................................................................ 280: ......[9*******]................................................ 2c0: ................................................................ 300: ................................................................ 340: ................................................................ 380: ................................................................ 3c0: ................................................................ 400: [8*******]...................................................... 440: ................................................................ 480: ................................................................ 4c0: ................................................................ 500: ................................................................ 540: ...................................................[7*******]... 580: ................................................................ 5c0: ................................................................ 600: ................................................................ 640: ................................................................ 680: ............................................................[6** 6c0: *****].......................................................... 700: ................................................................ 740: ................................................................ 780: ................................................................ 7c0: ................................................................ 800: ...................[5*******]................................... 840: ................................................................ 880: ................................................................ 8c0: ................................................................ 900: ................................................................ 940: ...............................................................[ 980: 4*******]....................................................... 9c0: ................................................................ a00: ................................................................ a40: ................................................................ a80: ................................................[3*******]...... ac0: ................................................................ b00: ................................................................ b40: ................................................................ b80: ................................................................ bc0: .............................[2*******]......................... c00: ................................................................ c40: ................................................................ c80: ................................................................ cc0: ................................................................ d00: ................................................................ d40: ...[1*******]................................................... d80: ................................................................ dc0: ................................................................ e00: ................................................................ e40: ................................................................ e80: ....................................[0*******].................. ec0: ................................................................ f00: ................................................................ f40: ................................................................ f80: ................................................................ fc0: ................................................................
Pagesize: 4096 Available pages: 1..16865 Header on btree page 3818: 000: 00 0 unknown 001: 00 00 0 Offset to first freeblock 003: 00 01 1 Number of cells on this page 005: 0f ef 4079 Offset to cell content area 007: 00 0 Fragmented byte count key: lx=left-child n=payload-size r=rowid fef: cell[0] lx: 193318784 n: 12384 ov: 0
Pagesize: 4096 Available pages: 1..16865 Header on btree page 3818: 000: 00 0 unknown 001: 00 00 0 Offset to first freeblock 003: 00 01 1 Number of cells on this page 005: 0f ef 4079 Offset to cell content area 007: 00 0 Fragmented byte count key: lx=left-child n=payload-size r=rowid fef: cell[0] lx: 193318784 n: 12384 ov: 774778414 Page map: (H=header P=cell-index 1=page-1-header .=free-space) 000: HHHHHHHHPP...................................................... 040: ................................................................ 080: ................................................................ 0c0: ................................................................ 100: ................................................................ 140: ................................................................ 180: ................................................................ 1c0: ................................................................ 200: ................................................................ 240: ................................................................ 280: ................................................................ 2c0: ................................................................ 300: ................................................................ 340: ................................................................ 380: ................................................................ 3c0: ................................................................ 400: ................................................................ 440: ................................................................ 480: ................................................................ 4c0: ................................................................ 500: ................................................................ 540: ................................................................ 580: ................................................................ 5c0: ................................................................ 600: ................................................................ 640: ................................................................ 680: ................................................................ 6c0: ................................................................ 700: ................................................................ 740: ................................................................ 780: ................................................................ 7c0: ................................................................ 800: ................................................................ 840: ................................................................ 880: ................................................................ 8c0: ................................................................ 900: ................................................................ 940: ................................................................ 980: ................................................................ 9c0: ................................................................ a00: ................................................................ a40: ................................................................ a80: ................................................................ ac0: ................................................................ b00: ................................................................ b40: ................................................................ b80: ................................................................ bc0: ................................................................ c00: ................................................................ c40: ................................................................ c80: ................................................................ cc0: ................................................................ d00: ................................................................ d40: ................................................................ d80: ................................................................ dc0: ................................................................ e00: ................................................................ e40: ................................................................ e80: ................................................................ ec0: ................................................................ f00: ................................................................ f40: ................................................................ f80: ................................................................ fc0: ...............................................[0***************
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
