After months, I managed to track this down. I'd like to extend a BIG thanks to Richard Hip and Dan Kennedy for their help in helping me instrument and understand the SQLITE internal data structures better, as well as giving me a way to programmatically do this, as well as to teach me about the showdb tool. It has been invaluable to help me understand and categorize the corruption which in turn helped me to know what common pattern to look out for.
Turns out, in some shutdown scenarios we would call what ends up being this: closesocket(_socket); // posix socket SSL_shutdown(_ssl); // openssl (_ssl was initialized using the _socket above) If in between those two commands, we opened a new SQLITE connection on another thread, SQLITE may get the same file handle value as what the old socket value used to be. SSL_shutdown then sends out a sequence resembling the following, to what used to be the socket: 150301002071476f3be1f3fa76f22b9addbe0f520ebbe007fcc1d6536c19ec9d69c5334799 However, since the old socket handle value is now being used as a file handle value, the sequence ends up in the database file instead. So this was a special case of re-using the File handle as per the corruption guide. One just has to be in the mindset that on unix based platforms, a socket is a file handle. (Not instinctive if you're coming from a Windows background). Thanks again guys! I'll leave the following search terms here as well in case someone searches for this in the future: 1503010020 15030100 352518400 (decimal version) OpenSSL - Deon -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Deon Brewis Sent: Tuesday, February 6, 2018 8:57 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] Header corruption I’m trying to track down SQLITE corruptions that seems to corrupt our databases in a consistent way. (Running on 3.20.1). This isn’t related to a crash or hardware failure. The app is running and reading and writing to the database fine, and then suddenly we start getting a SQLITE_NOTADB errors. The first error we got was on trying to execute a ‘BEGIN TRANSACTION’, and everything thereafter started failing. When our database headers are fine, I see this: 00000000h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format 3. 00000010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .....@ ...›..4B On corruption #1, I see this: 00000000h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ; .... .‘!…D.,í¾!ú 00000010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ; žtçêš..,™°·;Æð5û On corruption #2, I see this: 00000000h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ; .... ..˱ÿœ.ÐÖ»" 00000010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ; .dÓôÝ8ûߣàG¸×ö!¼ This bit pattern of “15 03 01 00” for the first 4 characters where it should be “SQLi” is a bit too much of a coincidence to ignore or write off as disk error. The rest of the header and file after the first 25-ish bytes or so, seem fine. It’s just these first 25 bytes that gets corrupted. If I restore Offsets 0 to Offsets 27 out of a “working” database, I get a usable file. Under what circumstances would SQLITE even attempt to re-write the header string? - Deon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://nam03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7C%7Cfdf8fd84436048062e6c08d56d82ae47%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636535330385784749&sdata=qjk7S7H6FW%2FaZIBD2XKPULL4v1wH7p2UN4GaKkx1O7I%3D&reserved=0 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
