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 (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 root 984064 Nov 12 14:28 /tmp/copy.db >> ~ # ls -l /tmp/backup.db >> -rw-r--r-- 1 root root 984064 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 root 968704 Nov 12 14:33 backup.db >> -rw-r--r-- 1 root root 968704 Nov 12 14:31 copy.db >> -rw-r--r-- 1 root root 5174 Nov 12 14:13 dbCheck >> -rw-r--r-- 1 root root 59 Nov 5 11:35 defRoute >> -rwxr-xr-x 1 root root 7436 Nov 5 11:35 netScript.sh >> -rwxr-xr-x 1 root root 252 Nov 12 14:35 tc.sh >> -rw-r--r-- 1 root root 509952 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 > _______________________________________________ > 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
