Hi guys,
Ran into an interesting situation recently where a database was transferred
via FTP and the client somehow managed to truncate the file. As a result
the last page was only 337 bytes rather than the expected 1024.
Surprisingly running a SELECT on the affected table works without sqlite
returning any error!
However several of the returned rows are completely blank, despite 7 out of
8 columns having a NOT NULL constraint. Anyway we came up with a simple
reproducer:
sqlite3 test.db "CREATE TABLE test_table (A INT4 NOT NULL, B INT4 NOT
NULL);"
for N in `seq 1 10`
do
sqlite3 test.db "INSERT INTO test_table (A, B) VALUES ($N, $N);"
done
dd if=test.db of=cropped.db bs=2020 count=1
sqlite3 cropped.db "SELECT * FROM test_table;"
Which produces results:
|
|
|
|
0|0
6|6
7|7
8|8
9|9
10|10
test.db is two pages long, so the truncation here is only 28 bytes. I
realise that some types of corruption are too small to notice without some
kind of checksum/error checking code, which feels a bit heavyweight for the
general case. But here it seems like there are some pretty significant red
flags:
1. the database contains an incomplete page
2. a NOT NULL column contains a NULL cell
"PRAGMA integrity_check" does flag problems with cropped.db, but because of
these two conditions I wonder if this is something sqlite could catch in
normal operation and return SQLITE_CORRUPT? Or are there reasons/history
which would render this conclusion inaccurate?
I notice that if I modify the database and another page gets added, sqlite
*does* start returning SQLITE_CORRUPT so I wonder if there's something
special about the last page?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
