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
