On 2014 Jun 23, at 21:49, Igor Tandetnik <i...@tandetnik.org> wrote:
> One possibility: the query with WHERE clause attempts to use some index, > while the query without doesn't. > > Try this query: > > SELECT * FROM itemTable WHERE +key = 'profileName' ; > > Note the + sign - this suppresses the use of index. Very good, Igor! Indeed, adding “+” to the query makes it work, no database corruption. > The data corruption happens to affect the area of the file where this index > is stored. Ah, so now we still need to find the root cause. Fortunately, I was able to dig up an older database file which contains exactly the same data, but works properly. Performing a binary diff on the Good vs. Bad file found only four bytes different. The first three differences are in the Header: the file change counter, the version_valid_for, and the SQLITE_VERSION_NUMBER. As expected, changing these did not help. (The Good file is 3.7.6, the Bad file is 3.7.13, and I am using the sqlite command-line tool version 3.7.13.) The culprit is byte 2048, the first byte in the 3rd page, assuming the 100-byte Header is part of the first page. In the Good file, it is 0x0A and in the Bad file, it’s 0x0D. Kind of weird that this would be part of an index - looks like a Mac vs. Unix line ending clash. But that’s the easy part. I wonder how long I’d have to study the sqlite file format document to decode the purpose of Byte 2048. For practical purposes, I could maybe just fix the bad byte and move on, but this database is a resource in an app I develop, and I sure would like to know how this happened. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
