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
I reran the integrity check to see if I could garner more information
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