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,'044540XXXXdp4',0,0,0,'');
INSERT INTO "dialplans" VALUES(27788,'044540XXXXdp5',0,0,0,'');
INSERT INTO "dialplans" VALUES(27789,'044540XXXXdp6',0,0,0,'');
-INSERT INTO "dialplans" VALUES(27790,'044540XXXXdp7',0,0,0,'');
-INSERT INTO "dialplans" VALUES(27791,'044540XXXXdp8',0,0,0,'');
-INSERT INTO "dialplans" VALUES(27792,'044540XXXXdp9',0,0,0,'');
-INSERT INTO "dialplans" VALUES(27793,'044540XXXXdp10',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 (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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
