Follow-up to "database disk image is corrupted" problem

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Follow-up to "database disk image is corrupted" problem

Eric Scouten
Earlier today I posted a question about SQLite compiled into my
application generating a "database disk image is corrupted" on a
particular query, but the command-line application does not (even using
the same database file). I haven't seen that message posted yet, but I
have some additional information that may help in understanding the problem.

The query that's running is essentially this: (table names obfuscated
since this is from an unannounced product)

     SELECT foo.rowid FROM foo WHERE foo.x == ?;

When run from the command-line application (either version 3.1.3 or
3.2.8), this query runs without error.

sqlite> SELECT foo.rowid FROM foo WHERE foo.x == 25;
61    // expected result

sqlite> explain SELECT foo.rowid FROM foo WHERE foo.x == ?;
0|Goto|0|20|
1|Integer|0|0|
2|OpenRead|1|117|keyinfo(1,BINARY)
3|SetNumColumns|1|2|
4|Variable|1|0|
5|NotNull|-1|8|
6|Pop|1|0|
7|Goto|0|18|
8|MakeRecord|1|0|o
9|MemStore|0|0|
10|MoveGe|1|18|
11|MemLoad|0|0|
12|IdxGE|1|18|+
13|RowKey|1|0|
14|IdxIsNull|1|17|
15|IdxRowid|1|0|
16|Callback|1|0|
17|Next|1|11|
18|Close|1|0|
19|Halt|0|0|
20|Transaction|0|0|
21|VerifyCookie|0|181|
22|Goto|0|1|
23|Noop|0|0|


When run from inside our application (using version 3.2.7 -- sorry,
haven't upgraded yet), however, we get different VDBE instructions:

2005-12-22 15:30:33.478 Got error: database disk image is malformed
  Statement: SELECT foo.rowid FROM foo WHERE foo.x == ?;

addr | opcode        | p1 | p2  | p3                                    
--------------------------------------------------------------------------
0    | Goto          | 0  | 20  |                                        
1    | Integer       | 0  | 0   | # (index for value x on table foo)
2    | OpenRead      | 1  | 100 | keyinfo(1,BINARY)                      
3    | SetNumColumns | 1  | 2   |                                        
4    | Variable      | 1  | 0   |                                        
5    | NotNull       | -1 | 8   |                                        
6    | Pop           | 1  | 0   |                                        
7    | Goto          | 0  | 18  |                                        
8    | MakeRecord    | 1  | 0   | o                                      
9    | MemStore      | 0  | 0   |                                        
10   | MoveGe        | 1  | 18  |                                        
11   | MemLoad       | 0  | 0   |                                        
12   | IdxGE         | 1  | 18  | +                                      
13   | RowKey        | 1  | 0   |                                        
14   | IdxIsNull     | 1  | 17  |                                        
15   | IdxRowid      | 1  | 0   |                                        
16   | Callback      | 1  | 0   |                                        
17   | Next          | 1  | 11  |                                        
18   | Close         | 1  | 0   |                                        
19   | Halt          | 0  | 0   |                                        
20   | Transaction   | 0  | 0   |                                        
21   | VerifyCookie  | 0  | 199 |                                        
22   | Goto          | 0  | 1   |                                        
23   | Noop          | 0  | 0   |                                        


For the record, our application and the SQLite command-line application
have been pointed to exactly the same database file. No other changes
have been made to the file, so they should be expected to behave
identically. But they don't. At address 2, the P2 parameter (which
should be the root page # for the index of field "x" on table "foo") is
100 in the embedded case and 117 in the command-line case. When I
inspect the sqlite_master table, it is obvious that 117 is the correct
answer. Page 100 is the root page of an unrelated table (not an index).

Has anyone seen anything like this before, and if so, can you help us
understand what would cause SQLite to generate this incorrect VDBE?

-Eric

--
Eric Scouten | [hidden email] | Photography: www.ericscouten.com

Reply | Threaded
Open this post in threaded view
|

Re: Follow-up to "database disk image is corrupted" problem

Bogusław Brandys
Eric Scouten wrote:

> Earlier today I posted a question about SQLite compiled into my
> application generating a "database disk image is corrupted" on a
> particular query, but the command-line application does not (even using
> the same database file). I haven't seen that message posted yet, but I
> have some additional information that may help in understanding the
> problem.
>
> The query that's running is essentially this: (table names obfuscated
> since this is from an unannounced product)
>
>     SELECT foo.rowid FROM foo WHERE foo.x == ?;
>
> When run from the command-line application (either version 3.1.3 or
> 3.2.8), this query runs without error.
>
> sqlite> SELECT foo.rowid FROM foo WHERE foo.x == 25;
> 61    // expected result
>
> sqlite> explain SELECT foo.rowid FROM foo WHERE foo.x == ?;
> 0|Goto|0|20|
> 1|Integer|0|0|
> 2|OpenRead|1|117|keyinfo(1,BINARY)
> 3|SetNumColumns|1|2|
> 4|Variable|1|0|
> 5|NotNull|-1|8|
> 6|Pop|1|0|
> 7|Goto|0|18|
> 8|MakeRecord|1|0|o
> 9|MemStore|0|0|
> 10|MoveGe|1|18|
> 11|MemLoad|0|0|
> 12|IdxGE|1|18|+
> 13|RowKey|1|0|
> 14|IdxIsNull|1|17|
> 15|IdxRowid|1|0|
> 16|Callback|1|0|
> 17|Next|1|11|
> 18|Close|1|0|
> 19|Halt|0|0|
> 20|Transaction|0|0|
> 21|VerifyCookie|0|181|
> 22|Goto|0|1|
> 23|Noop|0|0|
>
>
> When run from inside our application (using version 3.2.7 -- sorry,
> haven't upgraded yet), however, we get different VDBE instructions:
>
> 2005-12-22 15:30:33.478 Got error: database disk image is malformed
>  Statement: SELECT foo.rowid FROM foo WHERE foo.x == ?;
>
> addr | opcode        | p1 | p2  | p3                                    
> --------------------------------------------------------------------------
> 0    | Goto          | 0  | 20  |                                        
> 1    | Integer       | 0  | 0   | # (index for value x on table foo)
> 2    | OpenRead      | 1  | 100 | keyinfo(1,BINARY)                      
> 3    | SetNumColumns | 1  | 2   |                                        
> 4    | Variable      | 1  | 0   |                                        
> 5    | NotNull       | -1 | 8   |                                        
> 6    | Pop           | 1  | 0   |                                        
> 7    | Goto          | 0  | 18  |                                        
> 8    | MakeRecord    | 1  | 0   | o                                      
> 9    | MemStore      | 0  | 0   |                                        
> 10   | MoveGe        | 1  | 18  |                                        
> 11   | MemLoad       | 0  | 0   |                                        
> 12   | IdxGE         | 1  | 18  | +                                      
> 13   | RowKey        | 1  | 0   |                                        
> 14   | IdxIsNull     | 1  | 17  |                                        
> 15   | IdxRowid      | 1  | 0   |                                        
> 16   | Callback      | 1  | 0   |                                        
> 17   | Next          | 1  | 11  |                                        
> 18   | Close         | 1  | 0   |                                        
> 19   | Halt          | 0  | 0   |                                        
> 20   | Transaction   | 0  | 0   |                                        
> 21   | VerifyCookie  | 0  | 199 |                                        
> 22   | Goto          | 0  | 1   |                                        
> 23   | Noop          | 0  | 0   |                                      
>
> For the record, our application and the SQLite command-line application
> have been pointed to exactly the same database file. No other changes
> have been made to the file, so they should be expected to behave
> identically. But they don't. At address 2, the P2 parameter (which
> should be the root page # for the index of field "x" on table "foo") is
> 100 in the embedded case and 117 in the command-line case. When I
> inspect the sqlite_master table, it is obvious that 117 is the correct
> answer. Page 100 is the root page of an unrelated table (not an index).
>
> Has anyone seen anything like this before, and if so, can you help us
> understand what would cause SQLite to generate this incorrect VDBE?
>
> -Eric
>

Don't know if it's related but I have (under 2.8.17 and 2.8.16 also)
simple update query
UPDATE PRACOWNICY SET IMIE='Jacek',NAZWISKO='Placek' WHERE NR_PRACOWNIKA=12

which runs fine on sqlite.exe but in my application it simply do nothing
  (return SQLITE_OK but do not update row)

More to say; this query runs correctly on both sqlite.exe and my
application:

UPDATE PRACOWNICY SET IMIE='Jacek' where NAZWISKO='Kowalski'


Regards
Boguslaw Brandys