Re: [sqlite] damaged database recovery
it wasn't possible to drop the index in question what happened when you tried ? Were you using your own code or the SQLite shell tool ? sqlite shell tool. Same complaint, database corrupted. My guess is that you actually have file-level corruption which just happened to corrupt data in an index page. Could have just as easily been a table page and you would have had more trouble recovering your data. Of course that's possible, but .dump produced what superficially appeared to be a perfectly consistent text file. However, since we're dealing with an impossible error, it's hard to say definitively. A good approach for your situation might have been to use the SQLite shell tool to .dump your database to a text file, then to use .read to create a new database from those commands. But it may or may not have worked from your particular corrupt database. That's exactly what I did do. .read initially failed with a complaint about the non-unique indexes (although once again, no indication which index). A process of elimination identified the index, which eventually allowed me to remove the duplicates, so .read could succeed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] damaged database recovery
The likely cause of corruption is that this is probably a database being accessed on a networked disk. -- sqlite select * from preference_table where preferenceset='foo'; sqlite drop index preferenceindex; (11) database corruption at line 52020 of [2677848087] (11) statement aborts at 24: [drop index preferenceindex;] (11) database corruption at line 46321 of [2677848087] (11) database corruption at line 46359 of [2677848087] Error: database disk image is malformed sqlite pragma quick_check; *** in database main *** On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max of 25141) On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745) On tree page... sqlite reindex; (11) database corruption at line 52020 of [2677848087] (11) statement aborts at 3: [reindex;] (11) database corruption at line 46321 of [2677848087] (11) database corruption at line 46359 of [2677848087] Error: database disk image is malformed sqlite pragma integrety_check; sqlite pragma quick_check; *** in database main *** On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max of 25141) On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745) On tree page... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] damaged database recovery
I have a case of a damaged database, where the only damage appears to be that somehow the index uniqueness constraint is violated. As long as the operations don't touch the index, the db operates without complaint. I was eventually able to construct a copy with good indexes, but 1) the generic error 11 database corrupt could have been more specific. It would have been handy to know that the complaint was about duplicate indexes, and which index, or even which table was involved. 2) it wasn't possible to drop the index in question. Or even the whole table containing the index. If I could have dropped the offending index, I could have removed the duplicates and recreated the index without requiring major surgery. 3) maybe I missed something - there was an easier way? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] damaged database recovery
On 15 Jan 2015, at 8:24pm, Dave Dyer ddyer-sql...@real-me.net wrote: 1) the generic error 11 database corrupt could have been more specific. It would have been handy to know that the complaint was about duplicate indexes, and which index, or even which table was involved. 2) it wasn't possible to drop the index in question. Or even the whole table containing the index. If I could have dropped the offending index, I could have removed the duplicates and recreated the index without requiring major surgery. You might have checked the extended error code ... https://sqlite.org/rescode.html#extrc though I don't know what it would have told you about your particular database. When you write that it wasn't possible to drop the index in question what happened when you tried ? Were you using your own code or the SQLite shell tool ? My guess is that you actually have file-level corruption which just happened to corrupt data in an index page. Could have just as easily been a table page and you would have had more trouble recovering your data. A good approach for your situation might have been to use the SQLite shell tool to .dump your database to a text file, then to use .read to create a new database from those commands. But it may or may not have worked from your particular corrupt database. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] damaged database recovery
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/15/2015 12:52 PM, Dave Dyer wrote: Of course that's possible, but .dump produced what superficially appeared to be a perfectly consistent text file. Note that .dump writes the output and then on encountering problems attempts the table again, but instead starting from the end in reverse order. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlS4Rk8ACgkQmOOfHg372QRlLACg2Qxbn/WFJYkIUq5g/k6hiOGT XxEAnA5UV6S6OQRBpMrqS1y2f3Gzx8IZ =dzLI -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] damaged database recovery
On 1/15/15, Dave Dyer ddyer-sql...@real-me.net wrote: The likely cause of corruption is that this is probably a database being accessed on a networked disk. -- sqlite select * from preference_table where preferenceset='foo'; sqlite drop index preferenceindex; (11) database corruption at line 52020 of [2677848087] (11) statement aborts at 24: [drop index preferenceindex;] (11) database corruption at line 46321 of [2677848087] (11) database corruption at line 46359 of [2677848087] Error: database disk image is malformed sqlite pragma quick_check; *** in database main *** On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max of 25141) On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745) More than just index corruption Try doing: sqlite3 old-database .dump | sqlite3 fixed-database Then verify that fixed-database still contains all of your data. On tree page... sqlite reindex; (11) database corruption at line 52020 of [2677848087] (11) statement aborts at 3: [reindex;] (11) database corruption at line 46321 of [2677848087] (11) database corruption at line 46359 of [2677848087] Error: database disk image is malformed sqlite pragma integrety_check; sqlite pragma quick_check; *** in database main *** On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max of 25141) On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745) On tree page... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] damaged database recovery
Try doing: sqlite3 old-database .dump | sqlite3 fixed-database Then verify that fixed-database still contains all of your data. This doesn't work on these databases, even undamaged ones. I think it's a buffer size problem with sqlite3. The databases contain some rather long text strings. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] damaged database recovery
On 1/15/15, Dave Dyer ddyer-sql...@real-me.net wrote: it wasn't possible to drop the index in question what happened when you tried ? Were you using your own code or the SQLite shell tool ? sqlite shell tool. Same complaint, database corrupted. First type: .log stdout That will get you additional diagnostic information. Also try: PRAGMA quick_check; and PRAGMA integrity_check; for additional information about the problem. If damage is restricted to indexes, it can be repaired using REINDEX;. More concerning is how the database got corrupted in the first place. That isn't suppose to ever happen. See https://www.sqlite.org/howtocorrupt.html for possible clues. My guess is that you actually have file-level corruption which just happened to corrupt data in an index page. Could have just as easily been a table page and you would have had more trouble recovering your data. Of course that's possible, but .dump produced what superficially appeared to be a perfectly consistent text file. However, since we're dealing with an impossible error, it's hard to say definitively. A good approach for your situation might have been to use the SQLite shell tool to .dump your database to a text file, then to use .read to create a new database from those commands. But it may or may not have worked from your particular corrupt database. That's exactly what I did do. .read initially failed with a complaint about the non-unique indexes (although once again, no indication which index). A process of elimination identified the index, which eventually allowed me to remove the duplicates, so .read could succeed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
