ERROR: SQLite database is malformed – SOLVED

Every once in a while if SQLiteyou’re making heavy use of a SQLite database, as with any other DBMS, it will become corrupted. Usually this is due to bugs within the DBMS itself and sometimes hardware failure.

I had a SQLite database go corrupt when an application crashed. Normally I would have a backup to go to but in this case I didn’t because it was a new application that I was in the process of setting up.

Attempting to list tables in the database using sqlite3 resulted in the following message:

Error: near line 1: database disk image is malformed

In SQLite, the consistency checker is pragma integrity_check

This pragma does an integrity check of the entire database. The integrity_check pragma looks for out-of-order records, missing pages, malformed records, missing index entries, and UNIQUE and NOT NULL constraint errors. If the integrity_check pragma finds problems, strings are returned (as multiple rows with a single column per row) which describe the problems. Pragma integrity_check will return at most N errors before the analysis quits, with N defaulting to 100. If pragma integrity_check finds no errors, a single row with the value ‘ok’ is returned.

PRAGMA integrity_check does not find FOREIGN KEY errors. Use the PRAGMA foreign_key_check command for to find errors in FOREIGN KEY constraints.

sqlite> pragma integrity_check;
Error: database disk image is malformed

Yeah, it’s effectively a boolean response. OK if all is good else “database disk image is malformed”. Not very helpful IMO.

So what to do? Obviously, reverting to an earlier backup is preferred but if you remember, I didn’t have one and I didn’t want to redo all that work.

We need to export the schema and data and create a new database, import the schema and data. Simple:

sqlite> .mode insert
sqlite> .output mydb_export.sql
sqlite> .dump
sqlite> .exit

The .output sets the name of the file that the output of following commands to mydb_export.sql.
The .dump will export the database in SQL format.

Now, move the mydb.db file to mydb.db.original.

mv mydb.db mydb.db.original

We are almost done. Let’s create the database and load the exported schema & data:

sqlite3 mydb.db < mydb_export.sql

We have one last step. If the database had indexes, and all databases should have indexes, you will want to update the statistics for the indexes:

sqlite> analyze;
sqlite> .exit
Share Button

Optimizing the Firefox (SQLite databases)

Most people that want to speed up Firefox know about the various settings in about:config but many forget that Firefox uses SQLite databases that can become cluttered.

In your home directory ($HOME on *nix/Mac and %APPDIR% on Windows), run sqlite3 with vacuum, reindex and analyze on eche of the *.sqlite databases. Don’t have sqlite3? No problem, get it from the SQLite Download page or your distribution’s package manager (e.g. apt-get install sqlite3)

#!/bin/bash

cd ${HOME}/.mozilla/firefox/

for profile in *.default; do
cd ${profile}

for db in *.sqlite; do 
echo Processing $db
sqlite3 "${db}" < 

That wasn’t hard was it? 🙂

Share Button

Native support of SQLite in Sybase PowerBuilder and PowerDesigner?

What I would love to see is native support for SQLite. http://www.sqlite.org

It is the most installed embedded database on the planet hands down.  Don’t believe me?  You know that Firefox web browser, Thunderbird newsreader, most Adobe products, Miro, etc all have it embedded?  – reference http://www.sqlite.org/mostdeployed.html

We use it extensively at work as:

  1. staging for mass data imports/exports/conversions
  2. local application ‘cache’ for large data sets
  3. projects that don’t require all the features of Sybase ASE (or Oracle for that matter)

One of the best features is that the database itself in platform independent… copy the db on to AIX from your Windows box … then on to your old Amiga … then on to your windows mobile device.  Getting the point?  🙂

I really wish Sybase would make it so that the Sybase ASE databases were truly platform and character set/sort order independent… but that is in another dream 😉

Not a blurb in the PowerDesigner/PowerBuilder manuals or anything

Share Button

Google Video: Introduction to SQLite


We are currently using SQLite in production for some smaller projects.

Share Button

SQLiteman Ubuntu i386 package!

I downloaded the excellent SQLite administration tool, SQLiteman, today.  Take a look at the screenshots.  I’m very impressed with it but was disappointed that they don’t provide an Ubuntu binary package.  So… I built my own Ubuntu package using checkinstall:

sqliteman_1.0.1-1_i386.deb

When you install the Ubuntu package, it will show up in the Applications -> Office menu.

Share Button

FW: Scanning your iPhone Backup Files

Erica Sadun wrote an application, written in Perl, to convert your iPhone backup files into a SQLite3 database:


Here’s a nice way to recover notes from your iPhone without having to mail them to yourself–although it’s not for the faint of heart. Read more at The Unofficial Apple Weblog

It would be a simple matter to have the data imported in to your favorite DBMS if you prefer Sybase ASE, MS SQL Server, dBASE / xBase, etc.

Share Button