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

[James K. Lowden]

On Tue, 24 Jun 2014 22:06:15 -0700
Jerry Krinock  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jerry Krinock]

On 2014 Jun 24, at 14:46, David Empson  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter Haworth]

>
>
> 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 
Home of lcStackBrowser  and
SQLiteAdmin 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[David Empson]

On 25/06/2014, at 8:29 am, Jerry Krinock  wrote:
> On 2014 Jun 24, at 00:06, Clemens Ladisch  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
demp...@emptech.co.nz
Snail mail: P.O. Box 27-103, Wellington 6141, New Zealand

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On Tue, Jun 24, 2014 at 4:29 PM, Jerry Krinock  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jerry Krinock]

(I think it’s cleaner to reply to three replies in one message; here goes…)

On 2014 Jun 23, at 22:43, Keith Medcalf  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  wrote:

>  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  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Eduardo Morras]

On Mon, 23 Jun 2014 20:16:44 -0700
Jerry Krinock  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|Baah
> 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 “Baah”.
> 
> 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 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

Jerry Krinock wrote:
> On 2014 Jun 23, at 21:49, Igor Tandetnik  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.

 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] 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
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jerry Krinock]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jerry Krinock]

On 2014 Jun 23, at 21:49, Igor Tandetnik  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

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

[Igor Tandetnik]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users