On Wed, Mar 4, 2015 at 1:11 AM, Alexandr N?mec <a.nemec at atlas.cz> wrote: > we have a product that uses SQLite. Because it was running very stable for > years, we are still using SQLite 3.7.17. Now we've seen on one of our > installations that the database has been corrupted, we saw that there was a > power failure around the time of the corruption. We tested power failures by > ourselves in the past, the database had always survived. We are also aware > of this https://www.sqlite.org/atomiccommit.html#sect_9_0 > <https://www.sqlite.org/atomiccommit.html#sect_9_0> > > Nevertheless, I have the corrupted database and I could make it available if > someone likes to have a look at it. Maybe it would be possible to inspect > the kind of the corruption to see whether it might be an SQLite problem or > if one of the "Things that can go wrong" (see the aforementioned link) > kicked in...
In the time I've been involved with high-volume SQLite clients (Google Gears, then Chrome), what I've found is that the corruption invariably (*) ends up being a case where distinct pages were not written atomically, but where each page in isolation is completely valid when you inspect the data. For instance, you update a row and the corresponding page for the table btree is updated but the page for the index btree is not. Usually this correlated with power-failure events, but since corruptions of that sort aren't detected until SQLite actually compares the pages, the cause and detect can be separated by months. After analyzing many dozens of these over time, I mostly stopped tracking down and reviewing people's files, because it really wasn't telling me anything actionable. SQLite wasn't breaking the files, and AFAICT SQLite's system to guarantee atomicity works, so if some combination of the operating system and hardware are doing causing the problem you just need to figure out ways to recognize and deal with it at a higher layer. -scott (*) I have also seen cases where someone intentionally messed with disk I/O and screwed SQLite up. I don't really count those, because there's not much you can do to protect yourself against clever but mis-guided programmers.
