On 24 Oct 2017, at 4:03pm, Roberts, Barry (FINTL) <b.robe...@fugro.com> wrote:
> Our system would get a list of the rowid problems allowing it to log them and > inform the user. I am currently testing using the 1.0.105.1 driver, however > the ExecuteReader() call (above) throws an exception saying the database is > malformed. You originally wrote > In the System.Data.SQLite.dll 1.0.80.0 C# driver the following code would > return the reason, I would get a list of the rowid problems allowing me to > log them. The output of "PRAGMA integrity_check" just shows what SQLite found using a simple method of finding faults. SQLite does not exhaustively investigate every byte of the file trying to figure out what it might mean. The PRAGMA is just to give you an idea how much corruption there is: whether it looks like a crash occurred during the writing of one row [1] or whether a corrupt database has been in use for some time and or had multiple sectors overwritten. Please also note that by default "PRAGMA integrity_check" stops after the first 100 errors. If it finds 100 faults in one index it won’t show you any problems with another table. You are attempting to use standard SQLite API calls to investigate a corrupt database. There is nothing we can do to help with this. There is no requirement for consistency in handling corrupt databases in SQLite: if a SQLite call returns SQLITE_CORRUPT, or if "PRAGMA integrity_check" returns any faults at all, SQLite has done its job. It’s a "yes or no" thing. If you want software which can forensically investigate SQLite database corruption and tell you every little thing it finds, there are such programs on the market and the writer of at least one of them read this mailing list. But it can’t be done with just SQLite API calls. > If I replace the integrity_check with quick_check it works and returns ok, so > access to the file is ok, just some internal indexes are messed up. Your text makes it look like you think that that kind of corruption affects only existing rows. This is not the case. If you continue to write to a database which shows this problem, you can lose more of the existing rows and/or the new data you’re trying to write. The proper reaction to any such errors is to revert to an uncorrupted backup or, at minimum, to do VACUUM or REINDEX then check that the data is still consistent and plausible. Simon. [1] Under normal operation, if you did not intentionally turn off safety measures using PRAGMAs, even power failure should not cause corruption to a SQLite database once it has been reopened. However, many people use PRAGMAs to turn off safety measures hoping for greater speed. And a faulty storage module can also cause this kind of corruption. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
