SQLite3 database or disk is full / the database di

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 avoid getting "database or disk is full" in the first place, try this if you have lots of RAM:

sqlite> pragma temp_store = 2;

That tells SQLite to put temp files in memory. (The "database or disk is full" message does not mean either that the database is full or that the disk is full! It means the temp directory is full.) I have 256G of RAM but only 2G of /tmp, so this works great for me. The more RAM you have, the bigger db files you can work with.

If you haven't got a lot of ram, try this:

sqlite> pragma temp_store = 1;
sqlite> pragma temp_store_directory = '/directory/with/lots/of/space';

temp_store_directory is deprecated (which is silly, since temp_store is not deprecated and requires temp_store_directory), so be wary of using this in code.

Answer 2

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 3

while using Google App Engine, i had this problem. For some reason i did following since then Google App Engine was never starting.

$ echo '' > /tmp/appengine.apprtc.root/*.db

To fix it i required to do manually:

$ sqlite3 datastore.db
sqlite> begin immediate;
<press CTRL+Z>
$ cp datastore.db logs.db

And then run the Google App Engine with flag:

$ dev_appserver.py --clear_datastore --clear_search_index

after that it finally worked.

Answer 4

I have seen this happen when the database gets corrupted, have you tried cloning it into a new one ?

Safley copy a s SQLite db

Safely copy a SQLite database

It's trivially easy to copy a SQLite database. It's less trivial to do this in a way that won't corrupt it. Here's how:

shell$ sqlite3 some.db
sqlite> begin immediate;
<press CTRL+Z>
shell$ cp some.db some.db.backup
shell$ exit
sqlite> rollback;

This will give you a nice clean backup that's sure to be in a proper state, since writing to the database half-way through your copying process is impossible.

Answer 5

I use the following script for repairing malformed sqlite files:

#!/bin/bash

cat <( sqlite3 "$1" .dump | grep "^ROLLBACK" -v ) <( echo "COMMIT;" ) | sqlite3 "fix_$1"

Most of the time when a sqlite database is malformed it is still possible to make a dump. This dump is basically a lot of SQL statements that rebuild the database.

Some rows might be missing from the dump (probably becasue they are corrupted). If this is the case the INSERT statements of the missing rows will be replaced with some comments and the script will end with a ROLLBACK TRANSACTION.

So what we do here is we make the dump (malformed rows are excluded) and we replace the ROLLBACK with a COMMIT so that the entire dump script will be committed in stead of rolled back.

This method saved my life a couple of 100 times already \o/

Answer 6

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?