Re: [sqlite] database disk image is malformed 3.7.x
I'm having the same problem with my application. Basically, it's a combination of jukebox/music management app I've been developing myself over the last few years. I had always used the dump commands to back up & restore the database, given that I develop on both linux and windows. When the backup commands were introduced, I switched them them. Things have been working fine. General procedure is: - backup database under windows, to my server - reboot to linux - restore from windows backup - work - backup database to my server - reboot to windows - restore - work and so on. Until a week or so ago, both linux and windows were running 3.6.14. Recent upgrade of my gentoo system, however, upgraded sqlite there to 3.7.2 Now what I get is the following: -backup database under windows to my server - reboot to linux - restore from windows backup when I try to update or do anything in the database, my app crashes with the malformed database error. If I nuke the linux database file, re-create from scratch instead of backup restore and load the virgin file from a dump, everything is ok. On 2011-02-09 13:25, Dan Kennedy wrote: > >> I didn't find a way yet to reproduce the issue with a "clean" database. >> Only way I can reproduce it is with some of the database, like the >> test.db3. So I'm running out of ideas. > > This is the theory. test.db3 is an auto-vacuum database. > > http://www.sqlite.org/src/info/89b8c9ac54 > > Dan. > > ___ > 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] database disk image is malformed 3.7.x
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: woensdag 9 februari 2011 19:26 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] database disk image is malformed 3.7.x > > > > I didn't find a way yet to reproduce the issue with a "clean" > database. > > Only way I can reproduce it is with some of the database, like the > > test.db3. So I'm running out of ideas. > > This is the theory. test.db3 is an auto-vacuum database. > >http://www.sqlite.org/src/info/89b8c9ac54 > > Dan. > You are correct, the test.db3 is the only database with the PRAGMA AUTO_VACUUM 1. Looking at the ticket, it looks likely that this issue is indeed triggered. Only thing I don't know, is how this PRAGMA changed. Can't remember that this is set in any way, maybe a viewer tool changed it... Thanks for helping! Dennis This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please consider the environment before printing this email message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
> I didn't find a way yet to reproduce the issue with a "clean" database. > Only way I can reproduce it is with some of the database, like the > test.db3. So I'm running out of ideas. This is the theory. test.db3 is an auto-vacuum database. http://www.sqlite.org/src/info/89b8c9ac54 Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
On 02/10/2011 12:10 AM, Dan Kennedy wrote: > On 02/09/2011 08:17 PM, Dennis Geldhof wrote: >> I checked some things for the attached database. It is created with >> sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite >> wrapper. The header displays (with the ./showdb) version 3.7.4, so it is >> opened and changed with a database viewer tool. Before executing the >> "query" the database integrity is OK in both 3.6.23.1 and 3.7.4, after >> executing it is OK for 3.6.23.1 but malformed for 3.7.4. The "query" is >> executed using the system.data.sqlite wrapper (sqlite v3.6.23.1). It >> does not matter if the database was encrypted while executing the >> "query", the result stays the same. > > Thanks for doing this. It sounds like it is worth trying to figure > out what makes this db special. > > Are you able to make the test.db3 database available for download or > mail it to me directly? This mailing list strips out attachments. You already did that... My mistake. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
On 02/09/2011 08:17 PM, Dennis Geldhof wrote: > I checked some things for the attached database. It is created with > sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite > wrapper. The header displays (with the ./showdb) version 3.7.4, so it is > opened and changed with a database viewer tool. Before executing the > "query" the database integrity is OK in both 3.6.23.1 and 3.7.4, after > executing it is OK for 3.6.23.1 but malformed for 3.7.4. The "query" is > executed using the system.data.sqlite wrapper (sqlite v3.6.23.1). It > does not matter if the database was encrypted while executing the > "query", the result stays the same. Thanks for doing this. It sounds like it is worth trying to figure out what makes this db special. Are you able to make the test.db3 database available for download or mail it to me directly? This mailing list strips out attachments. Dan. > > The "query" is used to change some table structures in the database and > executes the following actions on the database; > BEGIN TRANSACTION; > > ALTER TABLE [A] RENAME TO [TMP_A] > ALTER TABLE [B] RENAME TO [TMP_B] > > CREATE TABLE [A] (); -- where A is a new table, stripped columns > CREATE TABLE [B] (); -- where B is a new table, stripped columns > CREATE TABLE [C] (); -- stripped columns > > INSERT INTO [A] > SELECT > (SELECT [ColumnA] FROM [D] WHERE [ColumnB] = [TMP_A].[ColumnB]), > (SELECT [ColumnC] FROM [TMP_B] WHERE ([ColumnD] = > [TMP_A].[ColumnD]) AND (NOT [ColumnC] ISNULL) LIMIT 1), > -- stripped more columns > FROM [TMP_A]; > > -- stripped some more INSERT INTO [] SELECT > -- stripped some UPDATE > > DROP TABLE [TMP_A]; > DROP TABLE [TMP_B]; > DROP TABLE [D]; > DROP TABLE [E]; > DROP TABLE [F]; > > COMMIT TRANSACTION; > > > When omitting the DROP TABLE [TMP_B], the 3.7.4 does not detect > corruption directly, until some more updates are executed on the > database. > > When the database before the "query" is .dump-ed to file, and .read into > a new 3.6.23.1 database, the corruption does not become visible after > the "query". So it seems to be in the header of that database. > > I didn't find a way yet to reproduce the issue with a "clean" database. > Only way I can reproduce it is with some of the database, like the > test.db3. So I'm running out of ideas. > > Dennis > > This message contains confidential information and is intended only for the > individual named. If you are not the named addressee you should not > disseminate, distribute or copy this e-mail. Please notify the sender > immediately by e-mail if you have received this e-mail by mistake and delete > this e-mail from your system. E-mail transmission cannot be guaranteed to be > secure or error-free as information could be intercepted, corrupted, lost, > destroyed, arrive late or incomplete, or contain viruses. The sender > therefore does not accept liability for any errors or omissions in the > contents of this message, which arise as a result of e-mail transmission. If > verification is required please request a hard-copy version. > Please consider the environment before printing this email message > ___ > 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] database disk image is malformed 3.7.x
I checked some things for the attached database. It is created with sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite wrapper. The header displays (with the ./showdb) version 3.7.4, so it is opened and changed with a database viewer tool. Before executing the "query" the database integrity is OK in both 3.6.23.1 and 3.7.4, after executing it is OK for 3.6.23.1 but malformed for 3.7.4. The "query" is executed using the system.data.sqlite wrapper (sqlite v3.6.23.1). It does not matter if the database was encrypted while executing the "query", the result stays the same. The "query" is used to change some table structures in the database and executes the following actions on the database; BEGIN TRANSACTION; ALTER TABLE [A] RENAME TO [TMP_A] ALTER TABLE [B] RENAME TO [TMP_B] CREATE TABLE [A] (); -- where A is a new table, stripped columns CREATE TABLE [B] (); -- where B is a new table, stripped columns CREATE TABLE [C] (); -- stripped columns INSERT INTO [A] SELECT (SELECT [ColumnA] FROM [D] WHERE [ColumnB] = [TMP_A].[ColumnB]), (SELECT [ColumnC] FROM [TMP_B] WHERE ([ColumnD] = [TMP_A].[ColumnD]) AND (NOT [ColumnC] ISNULL) LIMIT 1), -- stripped more columns FROM [TMP_A]; -- stripped some more INSERT INTO [] SELECT -- stripped some UPDATE DROP TABLE [TMP_A]; DROP TABLE [TMP_B]; DROP TABLE [D]; DROP TABLE [E]; DROP TABLE [F]; COMMIT TRANSACTION; When omitting the DROP TABLE [TMP_B], the 3.7.4 does not detect corruption directly, until some more updates are executed on the database. When the database before the "query" is .dump-ed to file, and .read into a new 3.6.23.1 database, the corruption does not become visible after the "query". So it seems to be in the header of that database. I didn't find a way yet to reproduce the issue with a "clean" database. Only way I can reproduce it is with some of the database, like the test.db3. So I'm running out of ideas. Dennis This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please consider the environment before printing this email message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: woensdag 9 februari 2011 5:57 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] database disk image is malformed 3.7.x > > The only problem we know of was caused by 3.7.0, which was replaced > by 3.7.0.1 when the problem was discovered. Even with 3.7.0, you needed > the right sequence of writes from 3.7.0 and some earlier version. > > I don't know how the corruption you're seeing is caused. I would like > to though. OK. I'll spent time today to try to get a real reproducible example. But on the moment I have really no clue why some have the issue and some don't have the issue. > > Do you use auto-vacuum mode? Or incremental vacuum? > > Dan. I did not set any vacuum-modes (so they are still on the defaults). And I never VACUUM the database explicitly. Dennis This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please consider the environment before printing this email message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
On 02/08/2011 10:24 PM, Dennis Geldhof wrote: >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of Dan Kennedy >> Sent: dinsdag 8 februari 2011 14:51 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] database disk image is malformed 3.7.x >> > >> Then run the resulting executable with the path to a database >> file as the first argument and "dbheader" as the second. i.e. >> >> ./showdb test.db dbheader >> >> where "test.db" is the database file name. The program prints >> out a short report that, if the database was ever written by >> 3.7.0 or newer, includes the version number of the most recent >> version to do so. > > Hi Dan, > > The tool shows me the SQLite version of the test.db3 is indeed 3007004. > So the database created with version 3.6.23.1, is once modified with a > 3.7.4 and then modified with 3.6.23.1 again. > > So I guess we run into something similar to this ticket; > http://www.sqlite.org/src/info/51ae9cad317a1 . > > One strange thing though, I also have a database with version 3007002 > which does not experience the database corruption. Can it be that the > size just matches accidentally for that database? The only problem we know of was caused by 3.7.0, which was replaced by 3.7.0.1 when the problem was discovered. Even with 3.7.0, you needed the right sequence of writes from 3.7.0 and some earlier version. I don't know how the corruption you're seeing is caused. I would like to though. Do you use auto-vacuum mode? Or incremental vacuum? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: dinsdag 8 februari 2011 14:51 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] database disk image is malformed 3.7.x > > Then run the resulting executable with the path to a database > file as the first argument and "dbheader" as the second. i.e. > >./showdb test.db dbheader > > where "test.db" is the database file name. The program prints > out a short report that, if the database was ever written by > 3.7.0 or newer, includes the version number of the most recent > version to do so. Hi Dan, The tool shows me the SQLite version of the test.db3 is indeed 3007004. So the database created with version 3.6.23.1, is once modified with a 3.7.4 and then modified with 3.6.23.1 again. So I guess we run into something similar to this ticket; http://www.sqlite.org/src/info/51ae9cad317a1 . One strange thing though, I also have a database with version 3007002 which does not experience the database corruption. Can it be that the size just matches accidentally for that database? Thanks, Dennis This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please consider the environment before printing this email message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
On 02/08/2011 08:26 PM, Dennis Geldhof wrote: >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of Dan Kennedy >> Sent: dinsdag 8 februari 2011 12:33 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] database disk image is malformed 3.7.x >> > >> We're very interested in how this happened. Do you ever write to the >> db using the 3.7.4 tools? Or write to it with any other 3.7.X version? > > I have several machines over here (with developers) and the corruption > only occurs on some machines. Besides that I am not able to reproduce > this issue with a brand new database (maybe because my tools are at > version 3.7.4). I can only reproduce this issue on some of the database > that are in use more than 2 months. Looking at the description you gave > and the symptoms we have, maybe this is caused by issue; > http://www.sqlite.org/src/info/51ae9cad317a1 . Because the > System.Data.Sqlite wrapper is still at sqlite version 3.6.23.1 and the > tools update automatically we could have triggered that issue. > Is there a way to check if the database was ever opened with a sqlite > version newer than 3.6.23.1, so we can make sure it was ever edited by a > tool? Grab the C file from this link and compile it to a standalone executable. http://www.sqlite.org/src/raw/tool/showdb.c?name=471c0f8fa472e71bb7654500096a5bdb4ea1fb2a Then run the resulting executable with the path to a database file as the first argument and "dbheader" as the second. i.e. ./showdb test.db dbheader where "test.db" is the database file name. The program prints out a short report that, if the database was ever written by 3.7.0 or newer, includes the version number of the most recent version to do so. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: dinsdag 8 februari 2011 12:33 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] database disk image is malformed 3.7.x > > We're very interested in how this happened. Do you ever write to the > db using the 3.7.4 tools? Or write to it with any other 3.7.X version? I have several machines over here (with developers) and the corruption only occurs on some machines. Besides that I am not able to reproduce this issue with a brand new database (maybe because my tools are at version 3.7.4). I can only reproduce this issue on some of the database that are in use more than 2 months. Looking at the description you gave and the symptoms we have, maybe this is caused by issue; http://www.sqlite.org/src/info/51ae9cad317a1 . Because the System.Data.Sqlite wrapper is still at sqlite version 3.6.23.1 and the tools update automatically we could have triggered that issue. Is there a way to check if the database was ever opened with a sqlite version newer than 3.6.23.1, so we can make sure it was ever edited by a tool? > Vacuum it? We never vacuum the database. > I think the mailing list stripped your attachment. Can you put > somewhere > we can download it from? Or just mail it to me if you like. Thanks. Uploaded: http://jjb3.nl/sqlite/test.db3 (3KB) Thanks, Dennis This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please consider the environment before printing this email message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed 3.7.x
On 02/08/2011 06:00 PM, Dennis Geldhof wrote: > Hi all, > > > > I experienced some strange behavior between different versions of > sqlite. Our application uses the System.Data.Sqlite wrapper > (http://sqlite.phxsoftware.com/) which is on sqlite version 3.6.23.1, > but the tools we use to view the database are on sqlite version 3.7.4. > In the application no signs of database corruption are found, but the > tools cannot open the database (tools; http://osenxpsuite.net/?xp=3 / > http://www.sqliteexpert.com/). Version 3.7.0 of SQLite introduced a field in the database file header containing the logical size of the database. So that a database file can be arbitrarily extended (AKA preallocated) without corrupting it. Earlier versions of SQLite just used the size of the file as the size of the database. http://www.sqlite.org/releaselog/3_7_0.html The symptoms you are reporting come about if the header field indicates that the database image is *larger* than the file on disk. If this is the case, version 3.7.0 and newer assume that the file is corrupt and report the error you are seeing. Earlier versions never read the header field and never see a problem. Since the integrity check passes, it is likely that the database is fine except that the header field is set incorrectly. We're very interested in how this happened. Do you ever write to the db using the 3.7.4 tools? Or write to it with any other 3.7.X version? Vacuum it? > Attached you will find a database file which is not encrypted, and all > tables are dropped. The corruption is still in there and is used to > generate the output mentioned above. I think the mailing list stripped your attachment. Can you put somewhere we can download it from? Or just mail it to me if you like. Thanks. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database disk image is malformed 3.7.x
Hi all, I experienced some strange behavior between different versions of sqlite. Our application uses the System.Data.Sqlite wrapper (http://sqlite.phxsoftware.com/) which is on sqlite version 3.6.23.1, but the tools we use to view the database are on sqlite version 3.7.4. In the application no signs of database corruption are found, but the tools cannot open the database (tools; http://osenxpsuite.net/?xp=3 / http://www.sqliteexpert.com/). To verify the behavior, I downloaded different versions of the sqlite windows commandline tool from the sqlite website and ran the following commands; C:\tmp\malformed>3_6_23_sqlite3.exe test.db3 "pragma integrity_check;" ok C:\tmp\malformed>3_6_23_1_sqlite3.exe test.db3 "pragma integrity_check;" ok C:\tmp\malformed>3_7_0_sqlite3.exe test.db3 "pragma integrity_check;" Error: database disk image is malformed C:\tmp\malformed>3_7_4_sqlite3.exe test.db3 "pragma integrity_check;" Error: database disk image is malformed Another thing that might be interesting, is that when you run "VACUUM;" with a 3.6.x version on the database, the corruption seems to be solved for the 3.7.x versions. The corruption seems to occur after running a number of CREATE TABLE / INSERT / UPDATE / ALTER TABLE / DROP TABLE commands in one transaction. Normally the database is encrypted using the encryption provided by the System.Data.Sqlite wrapper, but the corruption also remains when decrypting the database. Attached you will find a database file which is not encrypted, and all tables are dropped. The corruption is still in there and is used to generate the output mentioned above. Can someone help me out with this issue? Regards, Dennis Geldhof This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please consider the environment before printing this email message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
