Hi,
A user of our application (CC-ed) reported a corruption in an SQLite DB on
FreeBSD (see below). There was no mmap used in this case, just a WAL DB with
PRAGMA SYNCHRONOUS=NORMAL.
>From my reading of the SQLite docs this should not result in a corrupt DB,
>worst case some COMMITs could be lost/missing.
The DB is recoverable with SQLite 3.9.2 on Linux.
In fact the DB itself is not corrupt, but when WAL recovery is run on startup
it corrupts the DB.
Would it be possible to provide a way (via a PRAGMA?) to detect corruption when
recovering the WAL and rather than corrupting the DB to allow the application
to ignore the corrupt WAL entries?
SQLite is 3.8.10 version: 2015-05-07 11:53:08
cf975957b9ae671f34bb65f049acf351e650d437, compile options: OMIT_LOAD_EXTENSION
SYSTEM_MALLOC THREADSAFE=0
$ ls -l temp.db*
-rw-r--r-- 1 nobody nobody 278528 Oct 13 15:31 temp.db
-rw-r--r-- 1 nobody nobody 32768 Oct 13 15:41 temp.db-shm
-rw-r--r-- 1 nobody nobody 65008 Oct 13 15:28 temp.db-wal
$ ./sqlite3 temp.db
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> .schema
Error: database disk image is malformed
sqlite> pragma integrity_check;
Error: database disk image is malformed
sqlite> .clone /tmp/recover2
hashfs... done
tmpfiles... done
tmpmeta... done
sqlite_autoindex_hashfs_1... done
sqlite_autoindex_tmpfiles_1... done
tmpfiles_ttl... done
sqlite_autoindex_tmpmeta_1... done
sqlite> .open /tmp/recover2
sqlite> pragma integrity_check
...> ;
ok
sqlite> .schema
CREATE TABLE hashfs (key TEXT NOT NULL PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE tmpfiles (tid INTEGER PRIMARY KEY, token TEXT (32) NULL UNIQUE,
volume_id INTEGER NOT NULL, name TEXT (1024) NOT NULL, size INTEGER NOT NULL
DEFAULT 0, t TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f')), flushed
INTEGER NOT NULL DEFAULT 0, content BLOB, uniqidx BLOB, ttl INTEGER NOT NULL
DEFAULT 0, avail BLOB);
CREATE TABLE tmpmeta (tid INTEGER NOT NULL REFERENCES tmpfiles(tid) ON DELETE
CASCADE ON UPDATE CASCADE, key TEXT (256) NOT NULL, value BLOB (1024) NOT NULL,
PRIMARY KEY (tid, key));
CREATE INDEX tmpfiles_ttl ON tmpfiles(ttl) WHERE ttl > 0;
Lets try without the WAL
$ tar xf temp.tar.gz && cd temp
$ rm temp.db-*
$ sqlite3 temp.db
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> pragma integrity_check;
ok
I have uploaded the corrupted DB and WAL files here in case someone wants to
take a look: http://vol-public.s3.indian.skylable.com:8008/corrupted/temp.tar.gz
There are other corrupted DBs too, for example this small one which is the
first one opened by our application (and thus no WAL file because it was
recovered already)
$ ls -l /usr/local/sxserver/lib/sxserver/storage/hashfs.db*
-rw-r--r-- 1 nobody nobody 20480 Nov 25 14:17
/usr/local/sxserver/lib/sxserver/storage/hashfs.db
[2015-11-25 13:55:22.226] sx.fcgi[630]: Notice | [qlog]: SQLite result 0x11b:
recovered 59 frames from WAL file
/usr/local/sxserver/lib/sxserver/storage/hashfs.db-wal
[2015-11-25 13:55:22.226] sx.fcgi[630]: ALERT | [qlog]: SQLite result 0xb:
database corruption at line 55740 of [cf975957b9]
[2015-11-25 13:55:22.226] sx.fcgi[630]: ALERT | [qlog]: SQLite result 0xb:
database disk image is malformed
-------- Forwarded Message --------
Subject: Re: [sx-users] Corruption storage
Date: Wed, 25 Nov 2015 17:21:31 +0300
From: Veniamin Gvozdikov <v...@freebsd.org>
Reply-To: sx-users <sx-users at lists.skylable.com>
To: sx-users <sx-users at lists.skylable.com>
2015-11-25 17:17 GMT+03:00 T?r?k Edwin <edwin at skylable.com>:
> On 11/25/2015 04:01 PM, Veniamin Gvozdikov wrote:
> > Hello,
> >
> > My server has lost power. I can't run sxserver and get some errors:
>
> What are your operating system, kernel and SX versions?
>
root at sx02:~ # uname -a
FreeBSD sx02 10.1-RELEASE-p15 FreeBSD 10.1-RELEASE-p15 #0: Tue Jul 21
18:00:00 UTC 2015
root at amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC
amd64
SX version is 1.2
Which filesystem do you use for SX node storage, and what are its mount
> options?
>
UFS+SU
root at sx02:~ # mount
/dev/ada0p2 on / (ufs, local, journaled soft-updates)
>
> Please read points 3 onward here and see if any applies to your OS/disks:
> https://www.sqlite.org/howtocorrupt.html
>
> Also some general information on hard disk caches and a script that can be
> used to test
> whether your I/O path honors fsync:
> http://www.postgresql.org/docs/9.4/static/wal-reliability.html
>
> >
> > Nov 25 13:58:38 sx02 sx.fcgi[837]: [qlog]: SQLite result 0xb: database
> > corruption at line 55740 of [cf975957b9]
> > Nov 25 13:58:38 sx02 sx.fcgi[837]: [qlog]: SQLite result 0xb: database
> disk
> > image is malformed
> > Nov 25 13:58:38 sx02 sx.fcgi[837]: [qprep_db]: Cannot prepare query
> "PRAGMA
> > synchronous = NORMAL": database disk image is malformed
> > Nov 25 13:58:38 sx02 sx.fcgi[837]: [main]: Failed to initialize the
> storage
> > interface
> > Nov 25 13:58:38 sx02 vg: /usr/local/etc/rc.d/sxserver: WARNING: failed to
> > start sxserver
> > Nov 25 13:58:57 sx02 sx.fcgi[873]: [qlog]: SQLite result 0xb: database
> > corruption at line 55740 of [cf975957b9]
> > Nov 25 13:58:57 sx02 sx.fcgi[873]: [qlog]: SQLite result 0xb: database
> disk
> > image is malformed
> > Nov 25 13:58:57 sx02 sx.fcgi[873]: [qprep_db]: Cannot prepare query
> "PRAGMA
> > synchronous = NORMAL": database disk image is malformed
> > Nov 25 13:58:57 sx02 sx.fcgi[873]: [main]: Failed to initialize the
> storage
> > interface
>
> Can you upload these databases somewhere, and send a link to it?
> The SQLite developers would also probably be interested in analyzing these
> corrupted databases, is it OK if I share it with them?
>
> --
> Edwin T?r?k | Co-founder and Lead Developer
>
> Skylable open-source object storage: reliable, fast, secure
> http://www.skylable.com
> _______________________________________________
> http://lists.skylable.com/listinfo/sx-users
