Starbeamrainbowlabs

Stardust
Blog

Help! My SQLite database is malformed!

Recently I came across a rather worrying SQLite database error:

Error: database disk image is malformed

Hrm, that's odd. Upon double-checking, it looked like the database was functioning (mostly) fine. The above error popping up randomly was annoying though, so I resolved to do something about it. Firstly, I double-checked that said database was actually 'corrupt':

sudo sqlite3 path/to/database.sqlite 'PRAGMA integrity_check';

This outputted something like this:

*** in database main ***
Main freelist: 1 of 8 pages missing from overflow list starting at 36376
Page 23119: btreeInitPage() returns error code 11
On tree page 27327 cell 30: 2nd reference to page 27252

Uh oh. Time to do something about it then! Looking it up online, it turns out that the 'best' solution out there is to export to an .sql file and then reimport again into a fresh database. That's actually quite easy to do. Firstly, let's export the existing database to an .sql file. This is done via the following SQL commands (use sqlite3 path/to/database.db to bring up a shell)

.mode insert
.output /tmp/database_dump.sql
.dump
.exit

With the database exported, we can now re-import it into a fresh database. Bring up a new SQLite3 shell with sqlite3, and do the following:

.save /tmp/new_database.sqlite
.read /tmp/database_dump.sql
.exit

...that might take a while. Once done, swap our old corrupt database out for your shiny new one and you're done! I would recommend keeping the old one as a backup for a while just in case though (perhaps bzip2 path/to/old_database.sqlite?).

Also, if the database is on an embedded system, you may find that downloading it to your local computer for the repair process will make it considerably faster.

Found this useful? Still having issues? Comment below!

Sources

Tag Cloud

3d account algorithms android announcement architecture archives arduino artificial intelligence artix assembly async audio bash batch blog bookmarklet booting c sharp c++ challenge chrome os code codepen coding conundrums coding conundrums evolved command line compilers compiling compression css dailyprogrammer debugging demystification distributed computing documentation downtime electronics email embedded systems encryption es6 features event experiment external first impressions future game github github gist gitlab graphics hardware hardware meetup holiday holidays html html5 html5 canvas infrastructure interfaces internet io.js jabber jam javascript js bin labs learning library linux lora low level lua maintenance manjaro network networking nibriboard node.js operating systems performance photos php pixelbot portable privacy problem solving programming problems projects prolog protocol protocols pseudo 3d python reddit redis reference release releases resource review rust searching secrets security series list server software sorting source code control statistics storage svg technical terminal textures three thing game three.js tool tutorial tutorials twitter ubuntu university update updates upgrade version control virtual reality virtualisation visual web website windows windows 10 xmpp xslt

Archive

Art by Mythdael