Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body
On 25 Nov 2010, at 5:25pm, Tito Ciuro wrote: > 1) SELECT on sqlite_master. All SQLite3 databases contain it, so it would > fail right away is it wasn't a valid database. > 2) Issue a "PRAGMA integrity_check" statement, if (1) succeeds. > > However, I'm wondering whether (1) is redundant. If the integrity check > detects right away that the file is not a SQLite3 database, then we're good. > But if the integrity check reads the whole file before failing, perhaps > running (1) would not be such a bad after all. > > Also, if there is a need to know whether a database is corrupted, an > optimization seems to be to issue "PRAGMA integrity_check (1)" statement, > which would bail out at the first sign of trouble. As stated in the docs: > http://www.sqlite.org/pragma.html I believe that if Nick's going to do (2) anyway, (1) is redundant. In my own case I couldn't do (2) because some of the databases I was dealing with would have made it take too long. And my hardware were extremely trustworthy anyway: I was really just checking that the files were the right files, and someone hadn't (for example) renamed a journal file as the database file WHICH IS SOMETHING SOMEONE ACTUALLY DID TO ME ONCE ARGH ! And I agree Nick only needs to see the first error from 'PRAGMA integrity_check' so he can use 1 as a parameter. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body
Hi Nick, On Nov 25, 2010, at 2:09 PM, Nick Shaw wrote: > As Igor points out, that may not fail if the corruption is in a table > you don't query. > > In this case, the databases I'm working with are either small enough for > the speed of an integrity check not to be an issue, or the integrity of > the database is more important than a delayed DB open (which happens in > a seprate thread to my apps' main GUI, so the user won't notice an issue > anyway). > > Thanks for all the help peeps, > Nick. Agreed. However, the original email looks like it's a two-part question: opening non-SQLite files and detecting corrupted databases. Looks like the solution would be to integrate a method combining both Igor and Simon's solution: 1) SELECT on sqlite_master. All SQLite3 databases contain it, so it would fail right away is it wasn't a valid database. 2) Issue a "PRAGMA integrity_check" statement, if (1) succeeds. However, I'm wondering whether (1) is redundant. If the integrity check detects right away that the file is not a SQLite3 database, then we're good. But if the integrity check reads the whole file before failing, perhaps running (1) would not be such a bad after all. Also, if there is a need to know whether a database is corrupted, an optimization seems to be to issue "PRAGMA integrity_check (1)" statement, which would bail out at the first sign of trouble. As stated in the docs: http://www.sqlite.org/pragma.html > PRAGMA integrity_check; > PRAGMA integrity_check(integer) > > This pragma does an integrity check of the entire database. It looks for > out-of-order records, missing pages, malformed records, and corrupt indices. > If any problems are found, then strings are returned (as multiple rows with a > single column per row) which describe the problems. At most integer errors > will be reported before the analysis quits. The default value for integer is > 100. If no errors are found, a single row with the value "ok" is returned. Cheers, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body
Tito Ciuro wrote: > On 25 Nov 2010, at 12:51, Igor Tandetnikwrote: > > Run "PRAGMA integrity_check" right after opening. > > That could be a potentially slow operation if the database is valid and contains lots of records. > Wouldn't be better to issue a SELECT statement and let SQLite come back with a SQLITE_NOTADB error right away? As Igor points out, that may not fail if the corruption is in a table you don't query. In this case, the databases I'm working with are either small enough for the speed of an integrity check not to be an issue, or the integrity of the database is more important than a delayed DB open (which happens in a seprate thread to my apps' main GUI, so the user won't notice an issue anyway). Thanks for all the help peeps, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users