The title ["string or blob too big" issue on a corrupted db] may not clear
for you.
Let me describe my situation.
I have a problematic database.
It has two of tables which have a huge entry approximately 1.45GB.
They are sqlite_sequence and log table.
Here are output from sqlite3 shell.
------------------------------------------------------------------
sqlite> .schema sqlite_sequence
CREATE TABLE sqlite_sequence(name,seq);
sqlite> select * from sqlite_sequence;
Error: string or blob too big
------------------------------------------------------------------
------------------------------------------------------------------
sqlite> .schema log
CREATE TABLE log (_id INTEGER PRIMARY KEY AUTOINCREMENT,event_user_id
INT,event_type INT,event_time_ms INT,key TEXT,pkg TEXT,nid INT,tag
TEXT,when_ms INT,defaults INT,flags INT,priority INT,category
TEXT,action_count INT,posttime_ms INT,airtime_ms
INT,first_expansion_time_ms INT,expansion_airtime_ms INT,expansion_count
INT);
sqlite> select * from log where _id=2932;
Error: string or blob too big
------------------------------------------------------------------
The entry with _id 2932 in the log table has a huge record.
For sqlite_sequence table, the hex dump of the problematic entry is like
below.
The rootpage of sqlite_sequence resides in page 5.
00004E00: 00 00 00 00 00 00 00 00 00 00 00 00 00 85 D6 DD
................
00004E10: 8B 28 01 03 13 02 6C 6F 67 0B 74 00 00 00 00 00
.(....log.t.....
00004E20: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
................
00004E30: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
................
00004E40: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
................
00004E50: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
................
Offset 4E0D: 85 D6 DD 8B 28 - payload-size (1524057512 : 489 local,
1524057023 overflow)
Offset 4E12: 01 - rowid (1)
Offset 4E13: 03 - record-header-size: 3
Offset 4E14: 13 typecode[0]: 19 - text(3)
Offset 4E15: 02 typecode[1]: 2 - int16
Offset 4E16: 6c 6f 67 data[0]: 'log'
Offset 4E19: 0b 74 data[1]: 2932
Offset 4E1B: ... 481 bytes of content ...
............
Offset 4FFC: 00 05 b0 fa overflow-page: 372986
I can see that the total payload-size of the record should be 9:
1 byte for rowid
3 bytes for record-header
3 bytes for field "name"
2 bytes for field "seq"
But the in-file payload-size is 1524057512 (All other bytes are zero!).
So I think the db file must be corrupted.
I think this kind of issue should not happen because there is a defense
code in OP_MakeRecord.
in sqlite3VdbeExec() (OP_MakeRecord)
if( nByte+nZero>db->aLimit[SQLITE_LIMIT_LENGTH] ){
goto too_big;
}
--> nZero can not be very large !!
Now, here are my questions.
1. How could this problem happen? any guess?
2. Possible defense countermeasure.
*** Additional information ****
1. The problematic db passed pragma integrity_check.
2. sqlite version: 3.8.10.2
3. system : android M
4. pragma journal_mode : delete
5. pragma synchronous : 2 (FULL)
6. output of sqlite_analyzer -
/** Disk-Space Utilization Report For notification_log.db
Page size in bytes................................ 4096
Pages in the whole file (measured)................ 745888
Pages in the whole file (calculated).............. 745887
Pages that store data............................. 744977 99.88%
Pages on the freelist (per header)................ 0 0.0%
Pages on the freelist (calculated)................ 1 0.0%
Pages of auto-vacuum overhead..................... 910 0.12%
Number of tables in the database.................. 4
Number of indices................................. 0
Number of defined indices......................... 0
Number of implied indices......................... 0
Size of the file in bytes......................... 3055157248
Bytes of user payload stored...................... 3048408029 99.78%
*** Page counts for all tables with their indices
*****************************
LOG............................................... 372526 49.9%
SQLITE_SEQUENCE................................... 372449 49.9%
ANDROID_METADATA.................................. 1 0.0%
SQLITE_MASTER..................................... 1 0.0%
*** Page counts for all tables and indices separately
*************************
LOG............................................... 372526 49.9%
SQLITE_SEQUENCE................................... 372449 49.9%
ANDROID_METADATA.................................. 1 0.0%
SQLITE_MASTER..................................... 1 0.0%
*** All tables
****************************************************************
Percentage of total database...................... 99.88%
Number of entries................................. 2933
Bytes of storage consumed......................... 3051425792
Bytes of payload.................................. 3048408540 99.901%
Average payload per entry......................... 1039348.29
Average unused bytes per entry.................... 7.20
Average fanout.................................... 77.00
Maximum payload per entry......................... 1524057619
Entries that use overflow......................... 2 0.068%
Index pages used.................................. 1
Primary pages used................................ 80
Overflow pages used............................... 744896
Total pages used.................................. 744977
Unused bytes on index pages....................... 3479 84.9%
Unused bytes on primary pages..................... 17361 5.3%
Unused bytes on overflow pages.................... 279 0.0%
Unused bytes on all pages......................... 21119 0.0%
*** Table ANDROID_METADATA
****************************************************
Percentage of total database...................... 0.0%
Number of entries................................. 2
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 12 0.29%
B-tree depth...................................... 1
Average payload per entry......................... 6.00
Average unused bytes per entry.................... 2034.00
Maximum payload per entry......................... 7
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 4068 99.32%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4068 99.32%
*** Table LOG
*****************************************************************
Percentage of total database...................... 49.9%
Number of entries................................. 2927
Bytes of storage consumed......................... 1525866496
Bytes of payload.................................. 1524350505 99.901%
B-tree depth...................................... 2
Average payload per entry......................... 520789.38
Average unused bytes per entry.................... 3.35
Average fanout.................................... 77.00
Non-sequential pages.............................. 1 0.0%
Maximum payload per entry......................... 1524057619
Entries that use overflow......................... 1 0.034%
Index pages used.................................. 1
Primary pages used................................ 77
Overflow pages used............................... 372448
Total pages used.................................. 372526
Unused bytes on index pages....................... 3479 84.9%
Unused bytes on primary pages..................... 6242 2.0%
Unused bytes on overflow pages.................... 86 0.0%
Unused bytes on all pages......................... 9807 0.0%
*** Table SQLITE_MASTER
*******************************************************
Percentage of total database...................... 0.0%
Number of entries................................. 3
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 511 12.5%
B-tree depth...................................... 1
Average payload per entry......................... 170.33
Average unused bytes per entry.................... 1154.67
Maximum payload per entry......................... 344
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 3464 84.6%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 3464 84.6%
*** Table SQLITE_SEQUENCE
*****************************************************
Percentage of total database...................... 49.9%
Number of entries................................. 1
Bytes of storage consumed......................... 1525551104
Bytes of payload.................................. 1524057512 99.902%
B-tree depth...................................... 1
Average payload per entry......................... 1524057512.00
Average unused bytes per entry.................... 3780.00
Non-sequential pages.............................. 0 0.0%
Maximum payload per entry......................... 1524057512
Entries that use overflow......................... 1 100.0%
Primary pages used................................ 1
Overflow pages used............................... 372448
Total pages used.................................. 372449
Unused bytes on primary pages..................... 3587 87.6%
Unused bytes on overflow pages.................... 193 0.0%
Unused bytes on all pages......................... 3780 0.0%
Thanks in advance
Young Joonas Lee
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
