8

I have been given a directory of directories each containing a collection of .sqlite and .sqback files that I must parse.

The problem is that I believe some of these files to be corrupt when I receive them because I get the error: ERR: [SQLITE_CORRUPT] The database disk image is malformed (database disk image is malformed) on my console when I try to process them. This only happens with some of the files. I have isolated a few and tried running my program on fresh copies of these bad files individually and they cause errors. Most of the files are fine though :)

I realized that there is a chance that I may indeed be given corrupt files to begin with so I would like a way to determine, prior to trying to parse them, which files are good and which are not.

I am writing in Java. I am only interested in the sqlite and sqback validation as I know my parser works. I am reusing it from a previous project.

Hint? Suggestions? Answers?

Many Thanks for the knowledge transfer.

miss.serena
  • 1,170
  • 1
  • 11
  • 28
  • I may or may not have just answered my own question: http://www.sqlite.org/faq.html#q21 – miss.serena Sep 14 '12 at 05:37
  • unfortunately that didn't actually help me the way I was hoping. I am not sure I know how to use it. Would still love to hear your thoughts! – miss.serena Sep 14 '12 at 06:29
  • Yes, sorry for not being more explicate. I'm not quite sure how to use the PRAGMA quick_check. Specifically, how to use it in a manner that I can obtain a value that allows me to determine if the db is corrupt or not. Once i get a value, what do i compare it againt? Thank you. – miss.serena Sep 14 '12 at 16:20

2 Answers2

5

Run PRAGMA quick_check like a normal SQL query. The result is the same as a one-column table containing strings; for an intact database, you get a single row saying "ok", for a corrupt one, a bunch of error messages.

sqlite> pragma quick_check;
ok

after changing some bytes in the file:

sqlite> pragma quick_check;
*** in database main ***
Page 64: btreeInitPage() returns error code 11
On tree page 40 cell 23: Child page depth differs
On tree page 40 cell 24: Child page depth differs

There is no guarantee that errors will be found by this.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thank you so much, CL. That works like a charm. I just integrated that into my java code and i am good to go!!!! – miss.serena Sep 17 '12 at 19:27
  • **Note [this answer](http://stackoverflow.com/questions/8973527/better-sqlite-corruption-detection) before you go ahead using this** - there is indeed no guarantee errors will/will not be found. – i336_ Mar 26 '16 at 02:42
0

I would and do use

pragma integrity_check

This pragma does an integrity check of the entire database. The integrity_check pragma looks for out-of-order records, missing pages, malformed records, missing index entries, and UNIQUE, CHECK, and NOT NULL constraint errors. If the integrity_check pragma finds problems, strings are returned (as multiple rows with a single column per row) which describe the problems. Pragma integrity_check will return at most N errors before the analysis quits, with N defaulting to 100. If pragma integrity_check finds no errors, a single row with the value 'ok' is returned. (ref)

Why is this better than pragma quick_check?

The pragma is like integrity_check except that it does not verify UNIQUE constraints and does not verify that index content matches table content. (ref)

In other words, pragma integrity_check is more thorough.

Drakes
  • 23,254
  • 3
  • 51
  • 94