Re: [sqlite] Malformed database disk image, only when running VACUUM

[Richard Hipp]

On Fri, Aug 13, 2010 at 10:34 PM, Colin Wetherbee wrote:

> Hello.
>
> I believe the sqlite shell client is incorrectly telling me I have a
> malformed
> database image when I run VACUUM.  As far as I can tell, the database is,
> in
> fact, uncorrupted, but the message is a bit scary.  I'm using SQLite 3.7.0
> (as
> packaged by the Debian maintainers), and the database, itself, was also
> created with 3.7.0.
>
> $ sqlite3 hf.sqlite3
> SQLite version 3.7.0
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> pragma integrity_check;
> ok
> sqlite> vacuum;
> Error: database disk image is malformed
> sqlite> pragma integrity_check;
> ok
>

Thanks for sending me your database file.

The header of the database you sent me shows that you created the database
using SQLite version 3.7.0 but that you made 406 subsequent updates to the
database file using a prior version of SQLite.  By updating the database
using a prior version of SQLite you probably hit this bug:

  http://www.sqlite.org/src/info/51ae9cad317a1

That bug was fixed in 3.7.0.1, and in fact was the whole reason for the
3.7.0.1 emergency release.

I recommend that you upgrade to version 3.7.0.1.  Failing that, please do
not use both 3.7.0 and prior versions of SQLite on the same database file
because if you do 3.7.0 will give you corruption reports, or if you try to
update the database using 3.7.0 after the database was previously written
with a prior version of SQLite you might truly corrupt the database file.


>
> The error message appears every time I've tried to run VACUUM, but I'm able
> to
> continue executing queries in the same session afterward, and they always
> succeed.  Running VACUUM is the only time I've seen this error message.
>
> Why might this happen, and is there any way to narrow the problem, if any,
> down a bit?  I'm a bit worried that something might be breaking under the
> hood and that it will rear its head later on in the form of something
> catastrophic and horrible. :)
>
> My schema has three tables, and my application accesses it with PRAGMA
> foreign_keys = ON.  The only "abnormal" things about the schema are two
> CHECK
> constraints (on the same column) and one two-column unique index.  One of
> the
> tables has about 200,000 rows, but the other two only have about 90.
>
> I would be happy to share my database and/or schema with anybody who is
> interested.
>
> Thanks for your help.
>
> (Also, please CC me directly on any responses.)
>
> Colin
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Strange error

[Richard Hipp]

On Sat, Aug 14, 2010 at 12:23 PM, Marco Bambini  wrote:

> I have a strange error with sqlite 3.6.23.1, does anyone have an
> explanation?
>
> CREATE TABLE 'tblMoneyFlow'
> (
> 'id' INTEGER DEFAULT '0' NOT NULL PRIMARY KEY  AUTOINCREMENT  UNIQUE,
> 'RekeningNrEigenaar' INTEGER DEFAULT '0' NOT NULL REFERENCES
> 'tblBankAccount' ('idAccountNr'),
> 'Valuta' VARCHAR(4),
> 'DatumTransactie' DATE NOT NULL DEFAULT '01-01-1970',
> 'direction' DEFAULT 'D',
> 'bedrag' FLOAT NOT NULL DEFAULT '0',
> 'RekeningNrBestemming' INTEGER DEFAULT '0' NOT NULL REFERENCES
> 'tblBankAccount' ('idAccountNr'),
> 'Begunstigde' VARCHAR(32),
> 'DatumBoeking' DATE NOT NULL DEFAULT '01-01-1970',
> 'TransactieCode' INTEGER NOT NULL DEFAULT '0' REFERENCES
> 'tblMoneyFlowAction' ('id'),
> 'TransactieOmschrijving' VARCHAR(25)
> );
>
> INSERT INTO 'tblMoneyFlow' DEFAULT VALUES;
>
> error is "no such table: main.tblMoneyFlow"
>

When I copy/paste your SQL into sqlite3 3.6.23, it works fine for me.  No
errors.  Same for 3.7.0, 3.7.1, 3.6.22, 3.6.21, etc.


>
> Thanks a lot.
> P.S. I suspect it should have something to do with the references
> constraints
> --
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Strange error

[Marco Bambini]

I have a strange error with sqlite 3.6.23.1, does anyone have an explanation?

CREATE TABLE 'tblMoneyFlow'
(
'id' INTEGER DEFAULT '0' NOT NULL PRIMARY KEY  AUTOINCREMENT  UNIQUE,
'RekeningNrEigenaar' INTEGER DEFAULT '0' NOT NULL REFERENCES 'tblBankAccount' 
('idAccountNr'),
'Valuta' VARCHAR(4),
'DatumTransactie' DATE NOT NULL DEFAULT '01-01-1970',
'direction' DEFAULT 'D',
'bedrag' FLOAT NOT NULL DEFAULT '0',
'RekeningNrBestemming' INTEGER DEFAULT '0' NOT NULL REFERENCES 'tblBankAccount' 
('idAccountNr'),
'Begunstigde' VARCHAR(32),
'DatumBoeking' DATE NOT NULL DEFAULT '01-01-1970',
'TransactieCode' INTEGER NOT NULL DEFAULT '0' REFERENCES 'tblMoneyFlowAction' 
('id'),
'TransactieOmschrijving' VARCHAR(25)
);

INSERT INTO 'tblMoneyFlow' DEFAULT VALUES;

error is "no such table: main.tblMoneyFlow"

Thanks a lot.
P.S. I suspect it should have something to do with the references constraints
--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/






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

Re: [sqlite] Malformed database disk image, only when running VACUUM

[Simon Slavin]

On 14 Aug 2010, at 3:34am, Colin Wetherbee wrote:

> The error message appears every time I've tried to run VACUUM, but I'm able to
> continue executing queries in the same session afterward, and they always
> succeed.  Running VACUUM is the only time I've seen this error message.
> 
> Why might this happen, and is there any way to narrow the problem, if any,
> down a bit?  I'm a bit worried that something might be breaking under the
> hood and that it will rear its head later on in the form of something
> catastrophic and horrible. :)

Take your database file, pre-VACUUM, and use the command-line tool to .dump it, 
and .read the dump back in.  If you get the same error message on the resulting 
copy, you've definitely found a bug in SQLite.  I don't know where the bug is 
most likely to lie, but knowing the result of this will definitely establish 
there's a bug somewhere, and that the 'database disk image is malformed' 
message is misguiding.

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

Re: [sqlite] Database.

[Simon Slavin]

On 13 Aug 2010, at 7:45pm, Kirk Clemons wrote:

> Is there a way to parse my database by row id? In other words incase there is 
> an error I don't want SQLite stop parsing. I have bailing turned off

What is 'bailing' ?

> but it still does not continue on through the database when doing a 'select 
> *' on each row.

The row ids can be addressed as the column "_rowid_" even if you didn't define 
such a column yourself.  So you can do things like

SELECT * FROM myTable WHERE _rowid_ = 100;
SELECT * FROM myTable WHERE _rowid_ = 101;
SELECT * FROM myTable WHERE _rowid_ = 102;

But your question suggests you're dealing with a corrupt database.  The SQLite 
functions rely on the database being uncorrupted for pretty-much everything: 
any corruption in your database can cause any of the functions to give wrong 
results.  The only safe attitude to have is that as soon as you know your 
database is corrupt you trash it and restore a backup.  If you want the experts 
(the people who know /everything/ about SQLite) to piece though it and rescue 
everything possible, contact hwaci

http://www.hwaci.com/sw/sqlite/prosupport.html#rescue

and pay what they ask.  For a simple job with no huge time-pressure, it 
shouldn't be too expensive.

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

[sqlite] Malformed database disk image, only when running VACUUM

[Colin Wetherbee]

Hello.

I believe the sqlite shell client is incorrectly telling me I have a malformed
database image when I run VACUUM.  As far as I can tell, the database is, in
fact, uncorrupted, but the message is a bit scary.  I'm using SQLite 3.7.0 (as
packaged by the Debian maintainers), and the database, itself, was also
created with 3.7.0.

$ sqlite3 hf.sqlite3
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma integrity_check;
ok
sqlite> vacuum;
Error: database disk image is malformed
sqlite> pragma integrity_check;
ok

The error message appears every time I've tried to run VACUUM, but I'm able to
continue executing queries in the same session afterward, and they always
succeed.  Running VACUUM is the only time I've seen this error message.

Why might this happen, and is there any way to narrow the problem, if any,
down a bit?  I'm a bit worried that something might be breaking under the
hood and that it will rear its head later on in the form of something
catastrophic and horrible. :)

My schema has three tables, and my application accesses it with PRAGMA
foreign_keys = ON.  The only "abnormal" things about the schema are two CHECK
constraints (on the same column) and one two-column unique index.  One of the
tables has about 200,000 rows, but the other two only have about 90.

I would be happy to share my database and/or schema with anybody who is
interested.

Thanks for your help.

(Also, please CC me directly on any responses.)

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

[sqlite] Database.

[Kirk Clemons]

Is there a way to parse my database by row id? In other words incase there is 
an error I don't want SQLite stop parsing. I have bailing turned off but it 
still does not continue on through the database when doing a 'select *' on each 
row.

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