Tom Sartain | Blog

I’d noticed that my Firefox bookmarking was acting wonky. When I tried to bookmark a page, the form would pre-populate with an existing bookmark and upon saving, would save a new bookmark with the old bookmark’s information. Not quite what it should have been doing.

Using the built-in bookmark manager was useless since there seemed to be an underlying problem with the SQLite database Firefox uses, so I tried opening up the database using the excellent SQLite Manager Add on for Firefox. By running the PRAGMA integrity_check command I saw that the database disk image is malformed. Uh oh.

It had to be rebuilt.

First, I had to make sure I had a SQLite Command Line Client because I wouldn’t really be able to work on Firefox’s databases from inside Firefox.

Then I navigated over to where my places.sqlite database file inside my Firefox profile (which, for me was ~/Library/Application Support/Firefox/Profiles/{profile_name} )

I ‘connected’ to my SQLite database

sqlite3 places.sqlite

I reran the integrity check to see if I could garner more information

PRAGMA integrity_check;

Which told me some more information that I didn’t know about

On page 11 at right child: 2nd reference to page 1609
rowid 912 missing from index moz_bookmarks_itemlastmodifiedindex
SQL error: database disk image is malformed

I’d read that doing a straightforward dump/reload of the data should fix this sort of problem, so exited out of the SQLite shell and did a dump from SQLite

sqlite3 places.sqlite .dump > places.sql

Followed by load into a new SQLite database

sqlite3 places.new.sqlite < places.sql

But then I got a bunch of primary key errors

SQL error near line 872: PRIMARY KEY must be unique
SQL error near line 877: PRIMARY KEY must be unique
SQL error near line 883: PRIMARY KEY must be unique
SQL error near line 884: PRIMARY KEY must be unique
SQL error near line 885: PRIMARY KEY must be unique
SQL error near line 888: PRIMARY KEY must be unique
SQL error near line 889: PRIMARY KEY must be unique
SQL error near line 890: PRIMARY KEY must be unique

I popped open places.sql and checked out the lines in question. I saw what appeared to be the primary key which had stopped logically increasing and was sort of random

INSERT INTO "moz_bookmarks" VALUES(915,1,16546,2,110,'Bookmark Page Name #1',NULL,NULL,1256413281706712,1256413281711799);
INSERT INTO "moz_bookmarks" VALUES(29,1,33509,2,135,'Bookmark Page Name #2',NULL,NULL,1258662433785621,NULL);
INSERT INTO "moz_bookmarks" VALUES(28,1,31347,27,0,'Bookmark Page Name #3',NULL,NULL,1258472420957472,NULL);
INSERT INTO "moz_bookmarks" VALUES(27,2,NULL,2,134,'Bookmark Page Name #4',NULL,'',1258472420951405,NULL);

After a cursory glance through the rest of the file, I figured that I could change those primary keys to whatever I wanted as long as they were unique. A quick run through with that, running the same code as before

sqlite3 places.new.sqlite < places.sql

And success! I renamed my new places.new.sqlite to replace my old places.sqlite, restarted Firefox and everything worked out hunky dory.

A few notes:

• Make sure Firefox is completely closed before attempting to do anything with the databases from the command line. You will get errors about the database being locked
• After restarting Firefox, I did notice issues with my AwesomeBar not quite remembering my usual auto-complete and I also had to click through all of the items in my toolbar in order to get the favicons showing again, but so far nothing major