SQLite3 database or disk is full / the database disk image is malformed

Question

My database is about 25 MB, and I've verified that the username accessing it, as well as the file permissions haven't changed in months. I'm having a problem where queries are failing due to a "database or disk is full" and then sometimes "database disk image is malformed" issue.

Unless I'm reading this wrong, my disk isn't anywhere near full (this is an Ubuntu server, 9.10, if it makes any difference)

Filesystem           1K-blocks      Used Available Use% Mounted on /dev/sda1             19610300   2389596  16224560  13% / udev                     10240       128     10112   2% /dev none                    254136         0    254136   0% /dev/shm none                    254136        36    254100   1% /var/run none                    254136         0    254136   0% /var/lock none                    254136         0    254136   0% /lib/init/rw 

As a test I just did an action that added a new record, and it's fine. I'm trying to fiqure out if there's a specific set of actions that are failing. However, after the insert (and verifying that it's there) the number of bytes on disk for the database has not changed (neither up nor down).

Using the command line utility results in something like the following, which is failing spectacularly :)

SQLite version 3.6.12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma integrity_check; *** in database main *** On tree page 2 cell 0: 2nd reference to page 26416 On tree page 2 cell 1: 2nd reference to page 26417 On tree page 2 cell 2: 2nd reference to page 26434 On tree page 2 cell 3: 2nd reference to page 26449 On tree page 2 cell 4: 2nd reference to page 26464 On tree page 2 cell 5: 2nd reference to page 26358 On tree page 2 cell 6: 2nd reference to page 26494 On tree page 2 cell 7: Child page depth differs On tree page 2 cell 8: 2nd reference to page 26190 On tree page 2 cell 8: Child page depth differs  ... etc., etc. ... 

Any ideas on where I should be looking next? Is there a problem with the maximum number of rows in a table or something? I did some reading on SQLite3 max values, and nothing in my database is anything close to them as far as I can tell.

I then took a look at my daily backups, and I see that the database backup hasn't changed in file size for 3-4 days - very strange. I restored a backup copy of the database from before the time it was not changing in file size, and still getting strange issues.

I'm thinking I'm going to have to (1) restore from an older backup, and (2) re-run my Rails migrations to fix.

Answer 1

To repair a corrupt database you can use the sqlite3 commandline utility. Type in the following commands in a shell after setting the environment variables:

cd $DATABASE_LOCATION echo '.dump'|sqlite3 $DB_NAME|sqlite3 repaired_$DB_NAME mv $DB_NAME corrupt_$DB_NAME mv repaired_$DB_NAME $DB_NAME 

This code helped me recover a SQLite database I use as a persistent store for Core Data and which produced the following error upon save:

Could not save: NSError 259 in Domain NSCocoaErrorDomain { NSFilePath = mydata.db NSUnderlyingException = Fatal error. The database at mydata.db is corrupted. SQLite error code:11, 'database disk image is malformed' }

Answer 2

A few things to consider:

• SQLite3 DB files grow roughly in multiples of the DB page size and do not shrink unless you use VACUUM. If you delete some rows, the freed space is marked internally and reused in later inserts. Therefore an insert will often not cause a change in the size of the backing DB file.

• You should not use traditional backup tools for SQLite (or any other database, for that matter), since they do not take into account the DB state information that is critical to ensure an uncorrupted database. Especially, copying the DB files in the middle of an insert transaction is a recipe for disaster...

• SQLite3 has an API specifically for backing-up or copying databases that are in use.

• And yes, it does seem that your DB files are corrupted. It could be a hardware/filesystem error. Or perhaps you copied them while they were in use? Or maybe restored a backup that was not properly taken?