How do I repair a corrupted Firefox places.sqlite database?

I had some problems with my RAM (bluescreen several times, Windows XP) and now are my Firefox databases damaged. Firefox is working, but my history is gone and it's reporting several inconsistencies and errors when executing pragma integrity_check on places.sqlite:

database disk image is malformed

Now the question, how do I repair SQLite-Databases?

Solution 1:

Note

Because Firefox must be closed to perform this procedure, be sure to open this page in another web browser or print it out before proceeding.

After hours of work trying to recover the Places database, even reading the Firefox source code, I've managed to succeed. Here's how I did it:

Download the latest version of the SQLite shell and extract it into your profile folder. On Windows Vista and Windows 7, it is in the C:\Users\<username>\AppData\Roaming\Mozilla\Firefox\Profiles\<code>.default folder.
Close Firefox if it is running.
The Places database is in the places.sqlite file. If the file was replaced due to corruption, use the places.sqlite.corrupt file for recovery. Create a backup copy of the file, named places.sqlite.bak or places.sqlite.corrupt.bak.
Use the SQLite shell to open the database file (sqlite3 places.sqlite or sqlite3 places.sqlite.corrupt), then enter:

.output dump.sql    -- sends output to file dump.sql
.dump               -- dumps database to file

Because the database is corrupt, the resulting database dump is not complete, and not all of the recoverable data have been retrieved. To determine where the error occurred, search for the word ERROR (all caps) in an SQL comment inside the dump file dump.sql (I used Notepad++ to do this), and read the SQL INSERT command above it to determine the table in question. In my case, the damaged table is moz_places. (A description of the tables found in the Places database can be found here, which includes an outdated ER diagram.) I'll explain how to recover additional data from this table only; the following procedure is probably not applicable for the other tables, so skip these sub-steps if a table other than moz_places is involved.)
- Each row in the moz_places table has an ID. The rows are dumped from the table following the order of this ID.¹ The ID is the first value following the opening parenthesis in the INSERT statement. The area where the database is damaged is likely to be a small block of rows in this table; the idea here is to skip this damaged area and recover as much data as possible. The start area of such a block is represented in the dump as the row before the ERROR comment appears. Using the ID for this row, we can determine where the database is damaged. We do so by using SELECT statements with the ID as a condition; this process takes some trial and error. For example, if the last ID before the error was 49999, and the error follows, the damaged block starts at ID 50000. Use statements like:
```
-- suppress unnecessary output
-- the following command is for Windows systems
-- for Linux and other Unix and Unix-like systems, use .output /dev/null
.output NUL

SELECT id FROM moz_places WHERE id >= 50100;
```
- Adjust the value following the id >= and repeat the above SELECT command until you find the smallest value that does not cause SQLite to output an error. This is the ID that refers to the row starting from which we can recover additional data. Let's assume this ID is 50200. To dump this data, enter:
```
.output dump2.sql
.mode insert
SELECT * FROM moz_places WHERE id >= 50200;

-- restore normal output behavior
.output stdout
.mode list
```
- Note that the INSERT statements in the dump2.sql file begins with INSERT INTO table VALUES, so use the find and replace feature in your text editor to replace all instances of this string with INSERT INTO moz_places VALUES.
- Copy the entire contents of the dump2.sql file and paste it into the dump.sql file where the ERROR comment appears.
Replace the ROLLBACK; -- due to errors at the end of the file with COMMIT;.
Add the following code to the top of the dump.sql file. Replace <version> with the correct value, which is required for Firefox to determine the database schema version based on the version of Firefox, as follows (this can be found in the Firefox source file toolkit/components/places/Database.cpp):
- Firefox 52: schema version 35
- Firefox 53: schema version 36
- Firefox 57: schema version 39
- Firefox 58: schema version 41
- Firefox 60: schema version 43
- Firefox 61: schema version 47
- Firefox 62: schema version 52
- Firefox 69: schema version 53

PRAGMA user_version=<version>;
PRAGMA journal_mode = truncate;
PRAGMA page_size = 32768;
VACUUM;
PRAGMA journal_mode = wal;

Exit the SQLite shell, delete places.sqlite, then start the SQLite shell creating a empty places.sqlite database using sqlite3 places.sqlite. Type .read dump.sql to load the SQL dump into the database.
Start Firefox and confirm that your history and location bar are functioning as intended. Once you have confirmed that everything is OK, remove the database dump files and SQLite shell executable from the profile folder.

More relevant information can be found on the following pages:

http://kb.mozillazine.org/Locked_or_damaged_places.sqlite
http://www.sqlite.org/pragma.html

A simplified procedure is described in this MDN article but I have not tested it. Nonetheless, I've incorporated updated PRAGMA commands from that article.

¹ SQL does not normally guarantee that database output will be given in any order unless you use the ORDER BY clause. However, ORDER BY will likely fail to produce any output on a corrupted database (as SQLite will need to read the entire table before it can produce any output). As far as I know, Firefox always writes moz_places table entries with sequential IDs, so we can assume that all output is ordered by ID.

Solution 2:

Well, depending on how damaged it is, repair might not be possible. Your best bet is probably to try and dump the db using sqlite, then see what you can salvage.

If that fails, you'll probably have to restore from backup.

To dump and recreate a database, use the command .dump:

sqlite places.sqlite .dump | sqlite places-new.sqlite

Solution 3:

As always with performing a repair like this, I recommend that you first make at least one backup copy of your places.sqlite file located in your profile directory. Having a backup allows you to try various different things to repair such problems while knowing that if the attempted repair makes things worse, you can always make another copy of the backup on which to try again.

Depending on what is corrupted and how badly it is corrupted, it may be possible to fix the problems with the extension Places Maintenance. I have ended up with a corrupted places.sqlite file on a few occasions. Places Maintenance has been able to fix the problem each time by running various of the checks/fixes which it provides as operations in its options dialog. The various different checks and/or reporting should take only a few moments to minutes.

If this does not work, then going the route of manually fixing it in a manner similar to what DragonLord describes above may be what is needed.

Solution 4:

This process described on MDN helped me resolve an issue where new pages I visited were not recorded in browser history. I did not have a places.sqlite.corrupt (or places.sqlite-corrupt) file, but checking the integrity of my places.sqlite file revealed the database disk image is malformed error.

Quit Firefox and make a backup of your Firefox profile before you go any further here.

$ cd /Users/<username>/Library/Application\ Support/Firefox/Profiles/<profile_dir>/
$ cp places.sqlite places.sqlite.bak  # for safety

$ sqlite3 places.sqlite
sqlite> PRAGMA integrity_check;
*** in database main ***
On tree page 2 cell 131: Rowid 20884 out of order
...
Error: database disk image is malformed
sqlite> .clone places-clone.sqlite
moz_places... done
moz_historyvisits... done
... more output like above plus a few errors (which I ignored) like
sqlite_sequence... Error: object name reserved for internal use: sqlite_sequence
SQL: [CREATE TABLE sqlite_sequence(name,seq)]
done
...
sqlite> PRAGMA user_version;
43  <----- TAKE NOTE OF THIS VALUE it may be different for you
sqlite> .exit

$ sqlite3 places-clone.sqlite
sqlite> PRAGMA integrity_check;
ok
sqlite> PRAGMA user_version = 43;  -- use the number you got from PRAGMA user_version; above
sqlite> PRAGMA journal_mode = truncate;
truncate
sqlite> PRAGMA page_size = 32768;
sqlite> VACUUM;
sqlite> PRAGMA journal_mode = wal;
wal
sqlite> .exit

$ mv places-clone.sqlite places.sqlite

Start Firefox. History should be working again.

I'm on a Mac with Firefox 60.0.1. You may need to adjust the commands for your platform.