Re: [sqlite] Strange Corruption
No, this is definitely not the reason in my case as I can reproduce this issue on every 3.7.2/3.7.3 machine I've tested after copying the database file (and only the database file) to these machines. Am 15.11.2010 15:41, schrieb Kirk Clemons: Not sure if it helps but I would see this quite frequently when an old journal file would be left behind in the same directory as the backup database. This could be why making a change to the database such as vacuum would prevent the corruption. ___ 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] Strange Corruption
.dump (3.6.23.1) and afterwards sqlite3 /tmp/new.db /tmp/dump.sql with 3.7.2 worked and fixed the .backup problem (as expected). As I've already downgraded sqlite3 in our new firmware and patched the live-systems that were running with the new firmware I'll only have one machine to check whether the error comes back or not... So maybe I'll not be able to give feedback for a few weeks (as I can not enforce errors). However: if the error will not come back and could have to do with an already existing error in the database it would be quite interesting to know why integrity_check doesn't find the error before making a backup. (What means that a bug exists in any case: either in PRAGMA integrity_check or in the backup function). --- Pirmin Walthert Am 16.11.2010 14:22, schrieb Black, Michael (IS): Sorry, I meant .dump Given what you're describing I think it's worth finding out if you've found some bug in 3.7.2. The docs say 3.7.2 fixed a long-standing corruption bug. I don't know if that's related to this or not but sounds suspiciously close. So... #1 .dump the database #2 .import in into 3.7.2 #3 Run for a few days and see if you still get your backup problem. If still corrupt try 3.7.3 If it works then it sounds like the database was corrupt already and 3.7.2 just hits it. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Tue 11/16/2010 7:09 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] EXTERNAL:Re: Strange Corruption Well indeed it wasn't 3.7.X that created the database originally. But it was always 3.7.2 that made the INSERTS/UPDATES that lead to the state in which the database couldn't be backed up anymore. So what do you mean in fact: 3.7.X maybe can't handle database structures created with older versions?! Even after doing a vacuum which fixed the bug I had the same errors again on the machines with 3.7.2 after a few days (after other INSERTS/UPDATES). About the thing I should test: There is no command called .export it seems?! But I think that I don't even have to test the thing you propose, as it will work almost for sure = like already stated several times one little tiny tiny tiny change already fixes the error. As an .export/.import will change some bits for sure this will already change the situation! Am 16.11.2010 13:53, schrieb Black, Michael (IS): I thought of another test you should try. Do an .export of your original database using 3.6.23.1 and .import it (constructing a new database). Then try your backup. If that works then you're just seeing corruption in the original database that 3.6.23.1 handles (since it created it). If it doesn't work import into 3.7.3 and test backup again. If it doesn't work then try cutting the SQL in half until it does work. Maybe you'll finally get a small enough size you can post. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Tue 11/16/2010 6:27 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Strange Corruption No, this is definitely not the reason in my case as I can reproduce this issue on every 3.7.2/3.7.3 machine I've tested after copying the database file (and only the database file) to these machines. ___ 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 ___ 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] Strange Corruption
No, this is definitely not the reason in my case as I can reproduce this issue on every 3.7.2/3.7.3 machine I've tested after copying the database file (and only the database file) to these machines. Am 15.11.2010 15:41, schrieb Kirk Clemons: Not sure if it helps but I would see this quite frequently when an old journal file would be left behind in the same directory as the backup database. This could be why making a change to the database such as vacuum would prevent the corruption. ___ 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] EXTERNAL:Re: Strange Corruption
Well indeed it wasn't 3.7.X that created the database originally. But it was always 3.7.2 that made the INSERTS/UPDATES that lead to the state in which the database couldn't be backed up anymore. So what do you mean in fact: 3.7.X maybe can't handle database structures created with older versions?! Even after doing a vacuum which fixed the bug I had the same errors again on the machines with 3.7.2 after a few days (after other INSERTS/UPDATES). About the thing I should test: There is no command called .export it seems?! But I think that I don't even have to test the thing you propose, as it will work almost for sure = like already stated several times one little tiny tiny tiny change already fixes the error. As an .export/.import will change some bits for sure this will already change the situation! Am 16.11.2010 13:53, schrieb Black, Michael (IS): I thought of another test you should try. Do an .export of your original database using 3.6.23.1 and .import it (constructing a new database). Then try your backup. If that works then you're just seeing corruption in the original database that 3.6.23.1 handles (since it created it). If it doesn't work import into 3.7.3 and test backup again. If it doesn't work then try cutting the SQL in half until it does work. Maybe you'll finally get a small enough size you can post. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Tue 11/16/2010 6:27 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Strange Corruption No, this is definitely not the reason in my case as I can reproduce this issue on every 3.7.2/3.7.3 machine I've tested after copying the database file (and only the database file) to these machines. ___ 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] Strange Corruption
.dump (3.6.23.1) and afterwards sqlite3 /tmp/new.db /tmp/dump.sql with 3.7.2 worked and fixed the .backup problem (as expected). As I've already downgraded sqlite3 in our new firmware and patched the live-systems that were running with the new firmware I'll only have one machine to check whether the error comes back or not... So maybe I'll not be able to give feedback for a few weeks (as I can not enforce errors). However: if the error will not come back and could have to do with an already existing error in the database it would be quite interesting to know why integrity_check doesn't find the error before making a backup. (What means that a bug exists in any case: either in PRAGMA integrity_check or in the backup function). --- Pirmin Walthert Am 16.11.2010 14:22, schrieb Black, Michael (IS): Sorry, I meant .dump Given what you're describing I think it's worth finding out if you've found some bug in 3.7.2. The docs say 3.7.2 fixed a long-standing corruption bug. I don't know if that's related to this or not but sounds suspiciously close. So... #1 .dump the database #2 .import in into 3.7.2 #3 Run for a few days and see if you still get your backup problem. If still corrupt try 3.7.3 If it works then it sounds like the database was corrupt already and 3.7.2 just hits it. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From:sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Tue 11/16/2010 7:09 AM To:sqlite-users@sqlite.org Subject: Re: [sqlite] EXTERNAL:Re: Strange Corruption Well indeed it wasn't 3.7.X that created the database originally. But it was always 3.7.2 that made the INSERTS/UPDATES that lead to the state in which the database couldn't be backed up anymore. So what do you mean in fact: 3.7.X maybe can't handle database structures created with older versions?! Even after doing a vacuum which fixed the bug I had the same errors again on the machines with 3.7.2 after a few days (after other INSERTS/UPDATES). About the thing I should test: There is no command called .export it seems?! But I think that I don't even have to test the thing you propose, as it will work almost for sure = like already stated several times one little tiny tiny tiny change already fixes the error. As an .export/.import will change some bits for sure this will already change the situation! Am 16.11.2010 13:53, schrieb Black, Michael (IS): I thought of another test you should try. Do an .export of your original database using 3.6.23.1 and .import it (constructing a new database). Then try your backup. If that works then you're just seeing corruption in the original database that 3.6.23.1 handles (since it created it). If it doesn't work import into 3.7.3 and test backup again. If it doesn't work then try cutting the SQL in half until it does work. Maybe you'll finally get a small enough size you can post. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From:sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Tue 11/16/2010 6:27 AM To:sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Strange Corruption No, this is definitely not the reason in my case as I can reproduce this issue on every 3.7.2/3.7.3 machine I've tested after copying the database file (and only the database file) to these machines. ___ 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 ___ 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] Strange corruptions
I finally downgraded to sqlite 3.6.23.1. On the same system with the same database-file backups seem to work again what lets me conclude the following: - the problem is not that the source file is corrupted and PRAGMA integrity_check simply gives a wrong result (ok instead of showing errors) - the error seems to be in the backup routine itself (and only happens if the source file has a certain state) Why? - The error is always reproducible with the same file as long as one doesn't change the content (100% of cases) - The error happens on different machines with different distributions - The error also happens after copying the file (with cp = same checksum!) and making the backup from the copy - The error doesn't happen, if the backup is made from the same file with sqlite 3.6.23 or 3.6.23.1 (didn't check other versions) - I had database states on five different systems that led to this error after a random uptime since we upgraded to sqlite 3.7.2. The content of the files and the write sequences always differed, only the structure was the same However the interesting thing is: - vacuuming the source file normally helps - deleting a record normally helps (no matter what record from what table) - on every machine the database file was working for a while after the upgrade to 3.7.2 and after a few changes (inserts, updates and deletes) it became unbackupable Pirmin Am 12.11.2010 20:30, schrieb Pirmin Walthert: Am 12.11.2010 14:40, schrieb Pirmin Walthert: Am 12.11.2010 14:19, schrieb Black, Michael (IS): Do a sum on the files to make sure they are identical. #1 Show all the files in the directorty #2 How are you copying? Basically...show us ALL the commands and files you are using... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Fri 11/12/2010 6:42 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Strange corruptions Am 12.11.2010 13:06, schrieb Simon Slavin: On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote: Some months ago we changed to uclibc-git (nptl support), kernel 2.6.32.X, busybox 1.16 and at the moment sqlite 3.7.2. Are you accessing your databases straight from a hard disk or across a network mount ? Please tell us the filing system (either hard disk FS or network FS) you're using. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Both (the one with the source and the one with the dst database) are local (ext3 loopback fs). I doubt that it has to do with the FS because if do the following, the same thing happens: - copy the corrupted DB to /tmp (tmpfs) - checking the db with sqlite3 /tmp/baddb PRAGMA integrity_check; = this still shows me ok - making a backup of /tmp/baddb to /tmp/backupdb (or whatever) - checking the destionation db now gives me the same errors again Pirmin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list~ # md5sum /mnt/sipbad.db here you have a sequence that works and one that doesn't work with exactly the same file. once without vacuum, once with vacuum a343370269988b912d1efdf02ffcbcd1 /mnt/sipbad.db ~ # cp /mnt/sipbad.db /tmp/copy.db ~ # md5sum /tmp/copy.db a343370269988b912d1efdf02ffcbcd1 /tmp/copy.db ~ # sqlite3 /tmp/copy.db PRAGMA integrity_check; ok ~ # sqlite3 /tmp/copy.db .backup main /tmp/backup.db ~ # sqlite3 /tmp/backup.db PRAGMA integrity_check; *** in database main *** On page 26 at right child: invalid page number 954 On tree page 21 cell 0: invalid page number 956 ~ # ls -l /tmp/copy.db -rw-r--r--1 root root984064 Nov 12 14:28 /tmp/copy.db ~ # ls -l /tmp/backup.db -rw-r--r--1 root root984064 Nov 12 14:29 /tmp/backup.db ~ # sqlite3 /tmp/copy.db PRAGMA integrity_check; ok ~ # md5sum /tmp/copy.db a343370269988b912d1efdf02ffcbcd1 /tmp/copy.db ~ # md5sum /tmp/backup.db 1b0c6d02b5851e707267903da39a2d0c /tmp/backup.db ~ # sqlite3 /tmp/copy.db vacuum ~ # md5sum /tmp/copy.db 716555badc876d4e4ae452c741c41bfd /tmp/copy.db ~ # sqlite3 /tmp/copy.db PRAGMA integrity_check; ok ~ # sqlite3 /tmp/copy.db .backup main /tmp/backup.db ~ # sqlite3 /tmp/backup.db PRAGMA integrity_check; ok ~ # md5sum /tmp/backup.db 9e65a7c2683083a5d36f3f58af587f1d /tmp/backup.db oh well. You also want an output of all files in the directory ;) well I don't know how this could help, but here you have it ;) ~ # ls -l /tmp/ -rw-r--r--1 root root 2925 Nov 12 09:27 Master.csv -rw-r--r--1 root root968704 Nov 12 14:33 backup.db -rw-r--r--1
Re: [sqlite] Strange corruptions
1) The same happens with version 3.7.3 (checked again half an hour ago) 2) I can get a dump from both databases, but the two differ: --- out1.txt +++ out2.txt @@ -1109,10 +1109,6 @@ INSERT INTO dialplans VALUES(27787,'044540dp4',0,0,0,''); INSERT INTO dialplans VALUES(27788,'044540dp5',0,0,0,''); INSERT INTO dialplans VALUES(27789,'044540dp6',0,0,0,''); -INSERT INTO dialplans VALUES(27790,'044540dp7',0,0,0,''); -INSERT INTO dialplans VALUES(27791,'044540dp8',0,0,0,''); -INSERT INTO dialplans VALUES(27792,'044540dp9',0,0,0,''); -INSERT INTO dialplans VALUES(27793,'044540dp10',0,0,0,''); CREATE TABLE load_balancer ( id integer NOT NULL primary key autoincrement, server_ids varchar(30) NOT NULL default '', However the dump of the good database doesn't differ from the two dumps made on the 3.6.23.1 system! So like I said: the .backup command (and API) in the new version obviously produces the error! (By the way: even if the PRAGMA checks changed since 3.6.23.1 and 3.6.23.1 should (according to your theory) maybe have shown the same error there would still have been an issue because then PRAGMA integrity_check would have analyzed the src db as being ok although it wouldn't have been ok in this case (because if a db is ok, sqlite should be able to make a backup without producing errors in the backup db...)) 3) I don't think that it will be possible to shrink the database: I can't share the whole database as there are sensitive informations stored inside and changing anything in the database seems to fix the error! = Normally deleting one single row, not depending on from what table, fixes the error... So it will be hard to delete the sensitive information while keeping the error. --- Pirmin Walthert Am 15.11.2010 14:12, schrieb Black, Michael (IS): Can you get a .dump from the bad database file? And does that match the dump from the good one? And...did you try 3.7.3 on the entire sequence? I seem to remember there were some additional checks put in since 3.6.23.1 so the old integrity_check perhaps SHOULD be failing but doesn't. And...can you shrink down your database to something you can share so others can reproduce your problem? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Mon 11/15/2010 2:00 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Strange corruptions I finally downgraded to sqlite 3.6.23.1. On the same system with the same database-file backups seem to work again what lets me conclude the following: - the problem is not that the source file is corrupted and PRAGMA integrity_check simply gives a wrong result (ok instead of showing errors) - the error seems to be in the backup routine itself (and only happens if the source file has a certain state) Why? - The error is always reproducible with the same file as long as one doesn't change the content (100% of cases) - The error happens on different machines with different distributions - The error also happens after copying the file (with cp = same checksum!) and making the backup from the copy - The error doesn't happen, if the backup is made from the same file with sqlite 3.6.23 or 3.6.23.1 (didn't check other versions) - I had database states on five different systems that led to this error after a random uptime since we upgraded to sqlite 3.7.2. The content of the files and the write sequences always differed, only the structure was the same However the interesting thing is: - vacuuming the source file normally helps - deleting a record normally helps (no matter what record from what table) - on every machine the database file was working for a while after the upgrade to 3.7.2 and after a few changes (inserts, updates and deletes) it became unbackupable Pirmin Am 12.11.2010 20:30, schrieb Pirmin Walthert: Am 12.11.2010 14:40, schrieb Pirmin Walthert: Am 12.11.2010 14:19, schrieb Black, Michael (IS): Do a sum on the files to make sure they are identical. #1 Show all the files in the directorty #2 How are you copying? Basically...show us ALL the commands and files you are using... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Fri 11/12/2010 6:42 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Strange corruptions Am 12.11.2010 13:06, schrieb Simon Slavin: On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote: Some months ago we changed to uclibc-git (nptl support), kernel 2.6.32.X, busybox 1.16 and at the moment sqlite 3.7.2. Are you accessing your databases straight from a hard disk or across a network mount ? Please tell us the filing system
[sqlite] Strange corruptions
Hello I'm working on a CPE project where we use sqlite for configuration storage. We develop our own firmware which is based on uclibc and busybox. We never had troubles with the sqlite-databases during the first 1.5 years (kernel 2.6.27 series, uclibc 0.9.30, busybox 1.14.1, sqlite up to 3.6.23). Some months ago we changed to uclibc-git (nptl support), kernel 2.6.32.X, busybox 1.16 and at the moment sqlite 3.7.2. Each 30 minutes a backup of the config-database is made if there was a change. This is done using the SQLite Backup API. With the new version we have quite often (on different machines) the same strange behaviour: - sqlite3 /databasePath PRAGMA integrity_check; will give ok - a backup is made without an error showing up (directly through our own binary using the backup api or by using the .backup function of the sqlite3 command line tool. We always delete the destination file before doing the backup - checking the new database with PRAGMA integrity_check will give an error like: On page 26 at right child: invalid page number 954 On tree page 21 cell 0: invalid page number 956 I played a bit with a database that seems to be ok where the backup is always corrupted and can reproduce rather strange things: - vaccum the source database before doing the backup fixes the error - deleting a row from a table (no matter what table!) in the source database also fixes the error - vaccum the corrupted destination database also fixes the error Does anybody have some ideas what could be wrong here? Should I downgrade to a 3.6.X version? Maybe you will blame the git version of uclibc... I've to mention here that we run quite a lot of programs (some of them quite thread-oriented) on this platform without any troubles (samba, php, openvpn, asterisk and many others) and the uclibc-developers are discussing about making a 0.9.32 release based on this code during the next weeks. Best regards, Pirmin Walthert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange corruptions
Am 12.11.2010 13:06, schrieb Simon Slavin: On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote: Some months ago we changed to uclibc-git (nptl support), kernel 2.6.32.X, busybox 1.16 and at the moment sqlite 3.7.2. Are you accessing your databases straight from a hard disk or across a network mount ? Please tell us the filing system (either hard disk FS or network FS) you're using. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Both (the one with the source and the one with the dst database) are local (ext3 loopback fs). I doubt that it has to do with the FS because if do the following, the same thing happens: - copy the corrupted DB to /tmp (tmpfs) - checking the db with sqlite3 /tmp/baddb PRAGMA integrity_check; = this still shows me ok - making a backup of /tmp/baddb to /tmp/backupdb (or whatever) - checking the destionation db now gives me the same errors again Pirmin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange corruptions
Am 12.11.2010 14:19, schrieb Black, Michael (IS): Do a sum on the files to make sure they are identical. #1 Show all the files in the directorty #2 How are you copying? Basically...show us ALL the commands and files you are using... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Fri 11/12/2010 6:42 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Strange corruptions Am 12.11.2010 13:06, schrieb Simon Slavin: On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote: Some months ago we changed to uclibc-git (nptl support), kernel 2.6.32.X, busybox 1.16 and at the moment sqlite 3.7.2. Are you accessing your databases straight from a hard disk or across a network mount ? Please tell us the filing system (either hard disk FS or network FS) you're using. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Both (the one with the source and the one with the dst database) are local (ext3 loopback fs). I doubt that it has to do with the FS because if do the following, the same thing happens: - copy the corrupted DB to /tmp (tmpfs) - checking the db with sqlite3 /tmp/baddb PRAGMA integrity_check; = this still shows me ok - making a backup of /tmp/baddb to /tmp/backupdb (or whatever) - checking the destionation db now gives me the same errors again Pirmin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list~ # md5sum /mnt/sipbad.db here you have a sequence that works and one that doesn't work with exactly the same file. once without vacuum, once with vacuum a343370269988b912d1efdf02ffcbcd1 /mnt/sipbad.db ~ # cp /mnt/sipbad.db /tmp/copy.db ~ # md5sum /tmp/copy.db a343370269988b912d1efdf02ffcbcd1 /tmp/copy.db ~ # sqlite3 /tmp/copy.db PRAGMA integrity_check; ok ~ # sqlite3 /tmp/copy.db .backup main /tmp/backup.db ~ # sqlite3 /tmp/backup.db PRAGMA integrity_check; *** in database main *** On page 26 at right child: invalid page number 954 On tree page 21 cell 0: invalid page number 956 ~ # ls -l /tmp/copy.db -rw-r--r--1 root root984064 Nov 12 14:28 /tmp/copy.db ~ # ls -l /tmp/backup.db -rw-r--r--1 root root984064 Nov 12 14:29 /tmp/backup.db ~ # sqlite3 /tmp/copy.db PRAGMA integrity_check; ok ~ # md5sum /tmp/copy.db a343370269988b912d1efdf02ffcbcd1 /tmp/copy.db ~ # md5sum /tmp/backup.db 1b0c6d02b5851e707267903da39a2d0c /tmp/backup.db ~ # sqlite3 /tmp/copy.db vacuum ~ # md5sum /tmp/copy.db 716555badc876d4e4ae452c741c41bfd /tmp/copy.db ~ # sqlite3 /tmp/copy.db PRAGMA integrity_check; ok ~ # sqlite3 /tmp/copy.db .backup main /tmp/backup.db ~ # sqlite3 /tmp/backup.db PRAGMA integrity_check; ok ~ # md5sum /tmp/backup.db 9e65a7c2683083a5d36f3f58af587f1d /tmp/backup.db oh well. You also want an output of all files in the directory ;) well I don't know how this could help, but here you have it ;) ~ # ls -l /tmp/ -rw-r--r--1 root root 2925 Nov 12 09:27 Master.csv -rw-r--r--1 root root968704 Nov 12 14:33 backup.db -rw-r--r--1 root root968704 Nov 12 14:31 copy.db -rw-r--r--1 root root 5174 Nov 12 14:13 dbCheck -rw-r--r--1 root root59 Nov 5 11:35 defRoute -rwxr-xr-x1 root root 7436 Nov 5 11:35 netScript.sh -rwxr-xr-x1 root root 252 Nov 12 14:35 tc.sh -rw-r--r--1 root root509952 Nov 12 14:36 temp.db -rw-r--r--1 root root 455 Nov 5 11:35 udhcpc.lease -rw-r--r--1 root root 0 Nov 12 11:35 udhcpc.log ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange corruptions
Am 12.11.2010 14:40, schrieb Pirmin Walthert: Am 12.11.2010 14:19, schrieb Black, Michael (IS): Do a sum on the files to make sure they are identical. #1 Show all the files in the directorty #2 How are you copying? Basically...show us ALL the commands and files you are using... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Fri 11/12/2010 6:42 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Strange corruptions Am 12.11.2010 13:06, schrieb Simon Slavin: On 12 Nov 2010, at 7:55am, Pirmin Walthert wrote: Some months ago we changed to uclibc-git (nptl support), kernel 2.6.32.X, busybox1.16 and at the moment sqlite 3.7.2. Are you accessing your databases straight from a hard disk or across a network mount ? Please tell us the filing system (either hard disk FS or network FS) you're using. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Both (the one with the source and the one with the dst database) are local (ext3 loopback fs). I doubt that it has to do with the FS because if do the following, the same thing happens: - copy the corrupted DB to /tmp (tmpfs) - checking the db with sqlite3 /tmp/baddb PRAGMA integrity_check; = this still shows me ok - making a backup of /tmp/baddb to /tmp/backupdb (or whatever) - checking the destionation db now gives me the same errors again Pirmin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list~ # md5sum /mnt/sipbad.db here you have a sequence that works and one that doesn't work with exactly the same file. once without vacuum, once with vacuum a343370269988b912d1efdf02ffcbcd1 /mnt/sipbad.db ~ # cp /mnt/sipbad.db /tmp/copy.db ~ # md5sum /tmp/copy.db a343370269988b912d1efdf02ffcbcd1 /tmp/copy.db ~ # sqlite3 /tmp/copy.db PRAGMA integrity_check; ok ~ # sqlite3 /tmp/copy.db .backup main /tmp/backup.db ~ # sqlite3 /tmp/backup.db PRAGMA integrity_check; *** in database main *** On page 26 at right child: invalid page number 954 On tree page 21 cell 0: invalid page number 956 ~ # ls -l /tmp/copy.db -rw-r--r--1 root root984064 Nov 12 14:28 /tmp/copy.db ~ # ls -l /tmp/backup.db -rw-r--r--1 root root984064 Nov 12 14:29 /tmp/backup.db ~ # sqlite3 /tmp/copy.db PRAGMA integrity_check; ok ~ # md5sum /tmp/copy.db a343370269988b912d1efdf02ffcbcd1 /tmp/copy.db ~ # md5sum /tmp/backup.db 1b0c6d02b5851e707267903da39a2d0c /tmp/backup.db ~ # sqlite3 /tmp/copy.db vacuum ~ # md5sum /tmp/copy.db 716555badc876d4e4ae452c741c41bfd /tmp/copy.db ~ # sqlite3 /tmp/copy.db PRAGMA integrity_check; ok ~ # sqlite3 /tmp/copy.db .backup main /tmp/backup.db ~ # sqlite3 /tmp/backup.db PRAGMA integrity_check; ok ~ # md5sum /tmp/backup.db 9e65a7c2683083a5d36f3f58af587f1d /tmp/backup.db oh well. You also want an output of all files in the directory ;) well I don't know how this could help, but here you have it ;) ~ # ls -l /tmp/ -rw-r--r--1 root root 2925 Nov 12 09:27 Master.csv -rw-r--r--1 root root968704 Nov 12 14:33 backup.db -rw-r--r--1 root root968704 Nov 12 14:31 copy.db -rw-r--r--1 root root 5174 Nov 12 14:13 dbCheck -rw-r--r--1 root root59 Nov 5 11:35 defRoute -rwxr-xr-x1 root root 7436 Nov 5 11:35 netScript.sh -rwxr-xr-x1 root root 252 Nov 12 14:35 tc.sh -rw-r--r--1 root root509952 Nov 12 14:36 temp.db -rw-r--r--1 root root 455 Nov 5 11:35 udhcpc.lease -rw-r--r--1 root root 0 Nov 12 11:35 udhcpc.log ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users More info: I copied the file to my notebook (Ubuntu 10.10, sqlite 3.7.2, 64bit, while the other system is a 32bit system). Here exactly the same thing happens like on the uclibc/busybox system. (integrity_check on the src db shows ok, the .backup command gives no error but the integrity_check on the backup-file shows errors). No I copied exactly the same file to a CPE that still runs the old version (sqlite 3.6.23). Here everything works as expected: ~ # md5sum /tmp/bad.db a343370269988b912d1efdf02ffcbcd1 /tmp/bad.db ~ # sqlite3 /tmp/bad.db PRAGMA integrity_check; ok ~ # sqlite3 /tmp/bad.db .backup /tmp/bad2.db ~ # md5sum /tmp/bad2.db a37095fe6a27e21c836c1342ec28bbe2 /tmp/bad2.db ~ # sqlite3 /tmp/bad2.db PRAGMA integrity_check; ok Pirmin