81

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.

Tobu
  • 24,771
  • 4
  • 91
  • 98
jefflunt
  • 33,527
  • 7
  • 88
  • 126
  • Have you tried using the sqlite3 command line utility to rule out any issue with your application code? – thkala Mar 11 '11 at 14:38
  • Can you be slightly more specific (e.g. which command you would run from the CLU)? – jefflunt Mar 11 '11 at 14:53
  • Well, the crisis is nearly over. I was able to export the corrupted database into a fresh one, which is now passing its integrity check. I confirmed that all the critical data is intact (i.e. 'users' table, among others). I've also confirmed that (quite luckily) the only thing that was unrecoverable was actually some trivial site usage data (pageview stats, etc.) that I can certainly live without. There is some data that didn't survive that is important, but I have a backup of that, and as such I can re-import it via an INSERT. So, nothing critical lost; only a little more work ahead of me. – jefflunt Mar 11 '11 at 18:00

10 Answers10

79

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' }

Pegolon
  • 3,468
  • 3
  • 21
  • 13
  • 2
    Thank you a million times over - I spent a couple hours creating a Core Data database which was having this error code 11 issue (in iOS 4.3 but not 4.2 for some reason) and wouldn't load, but dumping into a newly created database like this fixed the issue. Yes! – Cory Imdieke Jun 09 '11 at 22:10
  • This was exactly what I needed. Doing dev work on a remote target thats running OE. I attempted to copy the DB not knowing it was being written to, and was getting errors that was throwing off the DB. This fixed the problem right-skippy. – Justin Carroll Jan 28 '13 at 18:14
  • Shout out to Pegolon ... just saved a customer's bacon with this recipe. Thanks much! – MichelV69 Jun 14 '13 at 12:33
  • 2
    I had a similar issue, and this pointed me to the right direction, but just doing the dump wasn't enough... it was producing a new sqlite file with 0 bytes. I had to dump the sql file into a text file first, and then remove some lines with "error" written in them that were obviously from the sqlite system, and then created a sqlite file using that text file's sql. That worked for me. – Z S Aug 20 '13 at 01:26
  • 2
    You could also just use .clone newdb.sqlite – David Benko Feb 11 '16 at 20:49
  • How would I do this in Xamarin? – callisto Apr 29 '16 at 09:52
  • 3
    Here it generates a 0 bytes output file. Dumping to .sql return a "ROLLBACK" with "due to errors" comment as the last line. – robertspierre Sep 06 '22 at 09:34
34

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/

Elmer
  • 9,147
  • 2
  • 48
  • 38
34

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?

thkala
  • 84,049
  • 23
  • 157
  • 201
  • 3
    Given the symptoms I'm seeing, it looks like #3 (backing up a copy of a database in use). The paging size/DB size makes perfect sense the way you describe it. So, I'm in the process of trying to repair it, or restore from a daily backup copy that is passing it's integrity check (I've got one, thankfully), and then changing my backup scheme. – jefflunt Mar 11 '11 at 16:52
  • 7
    For the benefit of the community, I wound up repairing/recovering the DB using the method outlined here: http://community.spiceworks.com/how_to/show/1468 I also had daily backups, and so this potential 'Oh sh*t' moment was turned into a relatively minor interruption. – jefflunt Mar 11 '11 at 18:04
  • 1
    For those using *nix systems, I found a similar set of instructions similar to what @normalocity found at spiceworks: http://askubuntu.com/questions/30185/banshee-encountered-a-fatal-error-sqlite-error-11-database-disk-image-is-malfo – daxiang28 Jan 07 '13 at 18:08
14

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.

Phil Goetz
  • 549
  • 4
  • 14
  • 3
    I had plenty of disk space in /tmp that didn't appear to be used. I was creating a temporary table that was causing the "database or disk is full" error to be thrown. Setting `temp_store` to use memory solved the problem. I had 3GB of ram and 1.6GB of /tmp. – N Klosterman Dec 11 '14 at 02:25
12

Cloning the current database from the sqlite3 commandline worked for me. 

.open /path/to/database/corrupted_database.sqlite3
.clone /path/to/database/new_database.sqlite3

In the Django setting file change the database name

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.sqlite3',
    'NAME': os.path.join(BASE_DIR, 'new_database.sqlite3'),
}}
Ahmad Taj
  • 147
  • 1
  • 6
9

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.

zx8754
  • 52,746
  • 12
  • 114
  • 209
Nix
  • 57,072
  • 29
  • 149
  • 198
  • This would make sense, since we recently implemented daily backups of the DB (been running for about 30 days), and we're doing it the way that's specified in the above link. I've also got a "sanity check" function in my app that alerts me when things go wrong, and alarms started to go off, and I suddenly have rows with bad ID fields, and other strangeness. – jefflunt Mar 11 '11 at 14:48
  • I tried the above, but the resulting DB fails an integrity check nonetheless. I'm looking at other ways of doing this however. Thanks for the lead - it's been very helpful. Either way it looks like the method being employed for backing up the database has caused the corruption. We'll be changing that so as not to have this problem in the future. – jefflunt Mar 11 '11 at 16:53
  • So i am assuming you went back and tested the original copy and it worked? I would post a question to severoverflow and ask the approved way to perform routine backups of sqlite... – Nix Mar 11 '11 at 20:55
  • I repaired the DB, recovering as much data as possible using this: http://community.spiceworks.com/how_to/show/1468 Then I found a way that looks right. I'll post the method on ServerFault and see if the folks there agree that it's a good way. – jefflunt Mar 11 '11 at 21:14
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.

2

During app development I found that the messages come from the frequent and massive INSERT and UPDATE operations. Make sure to INSERT and UPDATE multiple rows or data in one single operation.

var updateStatementString : String! = ""

for item in cardids {
    let newstring = "UPDATE "+TABLE_NAME+" SET pendingImages = '\(pendingImage)\' WHERE cardId = '\(item)\';"
    updateStatementString.append(newstring)
}

print(updateStatementString)

let results = dbManager.sharedInstance.update(updateStatementString: updateStatementString)

return Int64(results)
Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
Jorge Cardenas
  • 49
  • 1
  • 2
1

I had "database disk image is malformed" trying to read DB with object definitions created with later lib version and not supported by the current one.

I suppose there can be many examples of this, in my case it was GENERATED ALWAYS AS column - it was causing "database disk image is malformed" error with lib version 3.24.0. Updating it to 3.37.0 which supports this feature - has fixed this issue.

sarh
  • 6,371
  • 4
  • 25
  • 29
0

Seems like you are accessing SQLITE file from the system where the file is locked and the application using that file is not allowing the file to read. If you try the file to copy to another location, it will become malformed and will not be able to use.

Bashir Ahmed
  • 1