Integrity Check Failure Handling

[techi eth]

Reference from below link gave me hint about integrity check failure case
recovery by Export/Import of database.

Please let me know is this is correct & way to handle integrity failure
check.

http://blog.niklasottosson.com/?p=852

http://community.spiceworks.com/how_to/show/1468-how-to-fix-corrupt-sqlite-database


In failure case integrity check return say “If any problems are found, then
strings are returned (as multiple rows with a single column per row)”

With this how can I found in which table, row got issue?

Cheers-

techi

[Simon Slavin]

Post by techi eth
Reference from below link gave me hint about integrity check failure case
recovery by Export/Import of database.
Please let me know is this is correct & way to handle integrity failure
check.

This is not the correct way to handle integrity failure check.

Post by techi eth
http://blog.niklasottosson.com/?p=852
http://community.spiceworks.com/how_to/show/1468-how-to-fix-corrupt-sqlite-database

This process, if it works, will present you with a /usable/ database: one which won't cause SQLite to crash. But the database

A) may have incorrect data in it if some corruption affected only the contents of fields
B) may have inconsistent data in it, e.g. an entry in your 'sales' table for a customer who is not in the 'customer' table
C) may be missing all data added after the point of corruption, or worse still just /some/ of the data added after the point of corruption.

It all depends on exactly which bytes of the file got corrupted.

Post by techi eth
In failure case integrity check return say “If any problems are found, then
strings are returned (as multiple rows with a single column per row)”
With this how can I found in which table, row got issue?

Could be many rows in many tables were corrupted. If SQLite knew exactly what had been corrupted it could just go and fix it without even needing your help.


This is not the correct way to handle integrity failure check. The correct way to handle integrity failure check it to figure out what caused it (probably a hardware or low-level programming issue), try to make sure it doesn't happen again, then to restore the database file(s) from the last good backup you took.

The method of rescue described on those pages /can/ be useful if you have a programmer who understands the data structure who has been told to spend hours desperately recovering all available data. It might be useful to reassemble new data files which could be printed out, then inspected for useful information. But I would not just recover datafiles that way and continue to use them in an operating system: you are running the risk of accumulating missing and incorrect data which will cause you problems later.

Simon.

[techi eth]

Many Thanks for comment.

I shall agree with your view.

Could be many rows in many tables were corrupted. If SQLite3 knew exactly

what had >been corrupted it could just go and fix it without even needing
your help.

Here i am thinking of getting details about table & infected
row,SQLite3 doesn't need to fix them.Calling Application will understand &
do the required needful to change that part of database.
It might be helpful to get return data structure with infected row
in corresponding table.

Post by techi eth
Reference from below link gave me hint about integrity check failure case
recovery by Export/Import of database.
Please let me know is this is correct & way to handle integrity failure
check.

This is not the correct way to handle integrity failure check.

Post by techi eth
http://blog.niklasottosson.com/?p=852

http://community.spiceworks.com/how_to/show/1468-how-to-fix-corrupt-sqlite-database
This process, if it works, will present you with a /usable/ database: one
which won't cause SQLite to crash. But the database
A) may have incorrect data in it if some corruption affected only the contents of fields
B) may have inconsistent data in it, e.g. an entry in your 'sales' table
for a customer who is not in the 'customer' table
C) may be missing all data added after the point of corruption, or worse
still just /some/ of the data added after the point of corruption.
It all depends on exactly which bytes of the file got corrupted.

Post by techi eth
In failure case integrity check return say “If any problems are found,

then

Post by techi eth
strings are returned (as multiple rows with a single column per row)”
With this how can I found in which table, row got issue?

Could be many rows in many tables were corrupted. If SQLite knew exactly
what had been corrupted it could just go and fix it without even needing
your help.
This is not the correct way to handle integrity failure check. The
correct way to handle integrity failure check it to figure out what caused
it (probably a hardware or low-level programming issue), try to make sure
it doesn't happen again, then to restore the database file(s) from the last
good backup you took.
The method of rescue described on those pages /can/ be useful if you have
a programmer who understands the data structure who has been told to spend
hours desperately recovering all available data. It might be useful to
reassemble new data files which could be printed out, then inspected for
useful information. But I would not just recover datafiles that way and
continue to use them in an operating system: you are running the risk of
accumulating missing and incorrect data which will cause you problems later.
Simon.
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by techi eth

Could be many rows in many tables were corrupted. If SQLite3 knew exactly
what had >been corrupted it could just go and fix it without even needing
your help.

Here i am thinking of getting details about table & infected
row,SQLite3 doesn't need to fix them.Calling Application will understand &
do the required needful to change that part of database.
It might be helpful to get return data structure with infected row
in corresponding table.

Sorry, but the idea that one piece of corruption corresponds to one row of one table doesn't work.

Sometimes it's obvious that a block of data in the database was corrupted but there's no way to tell what part of the block was changed. Sometimes the corruption is in the form of a database file cut off at a certain point: a file which was originally 123456 bytes long is suddenly 100000 bytes long. Sometimes schema details near the beginning of the file become corrupt and it's not possible to tell even which tables were in the database or what rows and columns were in each table. Sometimes the corruption occurs purely in indexes: values stored in tables are correct but SQLite malfunctions while trying to find particular values and ranges.

The full details of the SQLite file format are widely available ...

<http://www.sqlite.org/fileformat.html>

and you could write a very thorough program which might pick through a file and try to guess what data could be rescued, asking a user which possibilities are the most plausible at each stage. But it would be a great deal of work. You might be interested in the sqlite-analyzer programs you can find on the SQLite download page which do some of the work involved.

The basic message here is that if a database file has been corrupted it's no longer appropriate to use any data from it in a working system. The good news is that corruption of SQLite databases on working hardware is very rare. With literally billions (thanks DRH) of installations of SQLite it has been debugged very thoroughly and incidents of corrupted files are rare. The best ways to corrupt a SQLite database are listed here:

<http://www.sqlite.org/howtocorrupt.html>

Simon.