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 gt  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

User · Answer

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/

User · Answer

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 gt  begin immediate   lt press CTRL Z gt  shell  cp some db some db backup shell  exit sqlite gt  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

User · Answer

while using Google App Engine  i had this problem  For some reason i did following since then Google App Engine was never starting     echo     gt   tmp appengine apprtc root   db   To fix it i required to do manually     sqlite3 datastore db sqlite gt  begin immediate   lt press CTRL Z gt    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

To avoid getting  database or disk is full  in the first place  try this if you have lots of RAM   sqlite gt  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 gt  pragma temp store   1  sqlite gt  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

[file] SQLite3 database or disk is full / the database disk image is malformed

Examples related to file

Examples related to sqlite

Examples related to filesystems

Examples related to corruption