Adding WHERE to query --> database disk image is malformed

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Adding WHERE to query --> database disk image is malformed

Jerry Krinock
How can it be that adding a WHERE clause to a successful query causes ‘database disk image is malformed’?

My database has one table named `itemTable`.  This table has two columns, `key` which is type text and `value` which is type blob.  There are two rows of data.  Their `value` blobs are actually strings encoded as UTF16 little endian.

The issue is demonstrated in the following transcript, using the sqlite command line tool in Mac OS X.

Air2: jk$ sqlite3 Test.sql
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT key from itemTable ;
profileName
extoreName
sqlite> SELECT key FROM `itemTable` WHERE `key` = 'profileName' ;
Error: database disk image is malformed
sqlite> SELECT * FROM `itemTable` WHERE `key` = 'nonexistent' ;
Error: database disk image is malformed

// Same succeed,fail result if I change query to "SELECT *" instead of "SELECT key".

sqlite> SELECT * FROM `itemTable` ;
profileName|Fooobar
extoreName|Baaaaaahhhhh
sqlite> SELECT * FROM `itemTable` WHERE `key` = 'profileName' ;
Error: database disk image is malformed

One thing I find rather surprising is that sqlite seems to know that the 14-byte and 24-byte blobs are UTF16-LE encoded strings, and prints them as “Fooobar” and “Baaaaaahhhhh”.

Is my database OK or malformed?

The same thing happens when I execute the failing query with the sqlite3 C Library, using years-old tested code.

Thank you!

Jerry Krinock

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Igor Tandetnik-2
On 6/23/2014 11:16 PM, Jerry Krinock wrote:
> How can it be that adding a WHERE clause to a successful query causes ‘database disk image is malformed’?

One possibility: the query with WHERE clause attempts to use some index,
while the query without doesn't. The data corruption happens to affect
the area of the file where this index is stored.

Try this query:

SELECT * FROM itemTable WHERE +key = 'profileName' ;

Note the + sign - this suppresses the use of index.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Jerry Krinock

On 2014 Jun 23, at 21:49, Igor Tandetnik <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Jerry Krinock
In reply to this post by Igor Tandetnik-2
Oh, I just realized that, although Igor’s suggestion “fixed” the problem, this database definitely does not contain any indexes.  The schema dump is simply this…

CREATE TABLE ItemTable (
  "key" text UNIQUE,
  value blob NOT NULL
);


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Keith Medcalf

Unique is implemented (as it must be) via a unique index.

CREATE TABLE ItemTable (
  "key" text UNIQUE,
  value blob NOT NULL
);

is equivalent to

CREATE TABLE ItemTable (
  "key" text,
  value blob NOT NULL
);
CREATE UNIQUE INDEX sqlite_autoindex_ItemTable_1 on ItemTable (key);

You should be able to rebuild the corrupted index with:

REINDEX ItemTable;

>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Jerry Krinock
>Sent: Monday, 23 June, 2014 23:33
>To: SQLite SQLite Users
>Subject: Re: [sqlite] Adding WHERE to query --> database disk image is
>malformed
>
>Oh, I just realized that, although Igor's suggestion "fixed" the problem,
>this database definitely does not contain any indexes.  The schema dump
>is simply this...
>
>CREATE TABLE ItemTable (
>  "key" text UNIQUE,
>  value blob NOT NULL
>);
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Clemens Ladisch
In reply to this post by Jerry Krinock
Jerry Krinock wrote:
> On 2014 Jun 23, at 21:49, Igor Tandetnik <[hidden email]> wrote:
>>  The data corruption happens to affect the area of the file where
>> this index is stored.
>
> The culprit is byte 2048, the first byte in the 3rd page.  In the Good
> file, it is 0x0A and in the Bad file, it’s 0x0D.

<http://www.sqlite.org/fileformat2.html#cell_payload> says:
| B-tree Page Header Format: Offset: 0, Size: 1, Description:
| A flag indicating the b-tree page type [...]  A value of 10 means the
| page is a leaf index b-tree page. A value of 13 means the page is
| a leaf table b-tree page.

So the bad index claims to be a table.

> looks like a Mac vs. Unix line ending clash.

Is there any other 0x0A byte in the good file?


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Eduardo Morras-2
In reply to this post by Jerry Krinock
On Mon, 23 Jun 2014 20:16:44 -0700
Jerry Krinock <[hidden email]> wrote:

> How can it be that adding a WHERE clause to a successful query causes
> ‘database disk image is malformed’?
>
> My database has one table named `itemTable`.  This table has two
> columns, `key` which is type text and `value` which is type blob.
> There are two rows of data.  Their `value` blobs are actually strings
> encoded as UTF16 little endian.
>
> The issue is demonstrated in the following transcript, using the
> sqlite command line tool in Mac OS X.
>
> Air2: jk$ sqlite3 Test.sql
> SQLite version 3.7.13 2012-07-17 17:46:21
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT key from itemTable ;
> profileName
> extoreName
> sqlite> SELECT key FROM `itemTable` WHERE `key` = 'profileName' ;
> Error: database disk image is malformed
> sqlite> SELECT * FROM `itemTable` WHERE `key` = 'nonexistent' ;
> Error: database disk image is malformed
>
> // Same succeed,fail result if I change query to "SELECT *" instead
> of "SELECT key".
>
> sqlite> SELECT * FROM `itemTable` ;
> profileName|Fooobar
> extoreName|Baaaaaahhhhh
> sqlite> SELECT * FROM `itemTable` WHERE `key` = 'profileName' ;
> Error: database disk image is malformed
>
> One thing I find rather surprising is that sqlite seems to know that
> the 14-byte and 24-byte blobs are UTF16-LE encoded strings, and
> prints them as “Fooobar” and “Baaaaaahhhhh”.
>
> Is my database OK or malformed?

Did you create the db schema with newer version of sqlite3? Perhaps you did that and create a partial index, not supported on older sqlite3 versions.

Please, check index schemas for a where clause in them.

>
> The same thing happens when I execute the failing query with the
> sqlite3 C Library, using years-old tested code.
>
> Thank you!
>
> Jerry Krinock

---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Jerry Krinock
In reply to this post by Keith Medcalf
(I think it’s cleaner to reply to three replies in one message; here goes…)

On 2014 Jun 23, at 22:43, Keith Medcalf <[hidden email]> wrote:

> Unique is implemented (as it must be) via a unique index.

Very good, Keith.  So my database *does* have an index.

> You should be able to rebuild the corrupted index with:
> REINDEX ItemTable;

Nope.  If I try that on the “bad” file, I get Error: Database disk image is malformed.  If I manually change byte 2048 to 0x0A first, then the command succeeds.  Looking at the resulting database, it seemed to change the order of the columns listed in the last page, which I assume is not significant.

On 2014 Jun 24, at 00:06, Clemens Ladisch <[hidden email]> wrote:

> <http://www.sqlite.org/fileformat2.html#cell_payload> says …

Thank you, Clemens.  0x0A=index.  0x0D=table.  I suppose that is a sensible re-use of “carrige return” and “line feed”.

> So the bad index claims to be a table.


> Is there any other 0x0A byte in the good file?

No, only that one.

On 2014 Jun 24, at 02:10, Eduardo Morras <[hidden email]> wrote:

> Did you create the db schema with newer version of sqlite3? Perhaps you did that and create a partial index, not supported on older sqlite3 versions.

I see that the Partial Index feature was added in August 2013, in sqlite 3.8.  I don’t think I have anything greater than 3.7.x on this Mac, but I’m looking into it.

* * *

According to the git history for my project, I committed the corrupt file about 10 days ago.  Still trying to figure out how that happened.  Thank you guys for all of the clues.

Jerry

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Richard Hipp-3
On Tue, Jun 24, 2014 at 4:29 PM, Jerry Krinock <[hidden email]> wrote:

>
> Thank you, Clemens.  0x0A=index.  0x0D=table.  I suppose that is a
> sensible re-use of “carrige return” and “line feed”.
>

Indexes and tables can have several different initial bytes, depending on
circumstances.   Any relationship to ASCII control characters is entirely a
coincidence.  In fact, you might be the first person to notice this
coincidence in the 10+ years that the SQLite3 file format has been defined.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

David Empson
In reply to this post by Jerry Krinock

On 25/06/2014, at 8:29 am, Jerry Krinock <[hidden email]> wrote:
> On 2014 Jun 24, at 00:06, Clemens Ladisch <[hidden email]> wrote:
>> Is there any other 0x0A byte in the good file?
>
> No, only that one.

That leads to a plausible theory: had you done anything with the "good" database along the lines of storing it in a version control system, or including it with source files in some other kind of bulk processing, or uploaded/downloaded it via FTP?

The most likely explanation is that it got processed by something which thought it should be treated as ASCII text and was doing a spurious LF-to-CR translation. If there was only one 0x0A byte in the "good" file, then that is the only one which would have been modified.

> According to the git history for my project, I committed the corrupt file about 10 days ago.  Still trying to figure out how that happened.  Thank you guys for all of the clues.

--
David Empson
[hidden email]
Snail mail: P.O. Box 27-103, Wellington 6141, New Zealand

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

phaworth
In reply to this post by Jerry Krinock
>
>
> SELECT * FROM itemTable WHERE +key = 'profileName' ;
>
> Note the + sign - this suppresses the use of index.
> --
> Igor Tandetnik
>

That's really interesting - I've never seen that use of the "+" sign
mentioned in the docs.

Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Igor Tandetnik-2
On 6/24/2014 6:04 PM, Peter Haworth wrote:
>> SELECT * FROM itemTable WHERE +key = 'profileName' ;
>>
>> Note the + sign - this suppresses the use of index.
>
> That's really interesting - I've never seen that use of the "+" sign
> mentioned in the docs.

http://www.sqlite.org/optoverview.html
To be usable by an index a term must be of one of the following forms: ...

Adding a unary + changes the term so it's no longer one of those forms.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

Jerry Krinock
In reply to this post by David Empson

On 2014 Jun 24, at 14:46, David Empson <[hidden email]> wrote:

> The most likely explanation is that it got processed by something which thought it should be treated as ASCII text and was doing a spurious LF-to-CR translation. If there was only one 0x0A byte in the "good" file, then that is the only one which would have been modified.

Yes, that makes sense, David.  I don’t think that happened.  But I’m still thinking :)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding WHERE to query --> database disk image is malformed

James K. Lowden-2
On Tue, 24 Jun 2014 22:06:15 -0700
Jerry Krinock <[hidden email]> wrote:

> > The most likely explanation is that it got processed by something
> > which thought it should be treated as ASCII text and was doing a
> > spurious LF-to-CR translation. If there was only one 0x0A byte in
> > the "good" file, then that is the only one which would have been
> > modified.
>
> Yes, that makes sense, David.  I don?t think that happened.  But I?m
> still thinking :)

Like perhaps maybe the file's git properties use native line endings,
and you're programming on a Mac?  

It seems unlikely, though, that the entire database would contain only
a single 10.  

--jkl

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users