18

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?

2
  • 2
    For future reference, the FEBE (Firefox Environment Backup Extension) may be helpful in the future. Copies the entire profile, and packages it up as a single backup. I know it doesn't answer your question, but it may be helpful to know in the future. bit.ly/aumThw
    – Urda
    Feb 22, 2010 at 15:03
  • Edited to help Googlers find this question.
    – bwDraco
    Dec 10, 2014 at 20:37

5 Answers 5

27

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.1 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:

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.


1 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.

8
  • 4
    This is pure awesomeness. Helped me recover almost all the history from a corrupt places.sqlite. Thanks a lot!! Jan 10, 2014 at 23:27
  • It did help, with two modifications: 1) add a ";" in the user_version line; 2) for some reason, my "corrupt" file had a schema version that was "one less" than expected. After your method didn't work initially, I tried importing the dump into the 10MB new database and failed because the old table had one column less. A look at the source code link made me understand what was going on. Awesome post!!! Dec 9, 2014 at 23:37
  • @TilmanHausherr: Addressed. To avoid the column change issue, be sure to follow the steps in this answer as soon as you notice corruption and before updating Firefox, so that the database schema isn't changed. You could also try setting an older schema version—Firefox will update it to the new version when you restore the database.
    – bwDraco
    Dec 9, 2014 at 23:38
  • Setting the previous schema version is what I had done when writing my first comment, i.e. I was already successful :-) Yeah, I suspect that I hadn't noticed the corruption immediately, I usually notice it only when entering characters that should make an "old URL" appear and nothing happens. Dec 9, 2014 at 23:49
  • Excellent work! Glad you updated it, which put it back in the active questions where I spotted it.
    – fixer1234
    Mar 23, 2015 at 6:47
4

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
4
  • 1
    Thank you. The SO post wasn't helpful since it didn't work, but the solution referenced in the link did work d:\sqlite3.exe d:\idimager.cat.db .dump | d:\sqlite3.exe d:\newdb.cat.db. All favicons are now gone, but I they're rebuilding as I visit the sites. Thanks again!
    – Bobby
    Feb 23, 2010 at 8:14
  • stackoverflow.com/questions/2255305/… link in above question, was voluntarily removed by it's author. The answer below may be of help.
    – user66001
    Feb 18, 2013 at 19:41
  • @user66001: Yes, the OP deleted their question. I copied over the relevant command.
    – sleske
    Sep 25, 2017 at 7:30
  • This did not work for me, and I ended up with a places.sqlite.corrupt file. I posted another answer with a solution that worked for me.
    – Daniel
    Jun 6, 2018 at 17:36
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.

3

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.

2
  • Thanks Daniel, always helpful to see the actual command procedure
    – not2qubit
    Sep 26, 2018 at 17:07
  • This worked flawlessly, thanks!
    – nhaesler
    Jun 14, 2021 at 8:57
0

Another source of the "corrupt database" issue can happen when upgrading from a very old version of Firefox. None of the database maintenance suggestions in other answers will help in this case.

I recently gave in and decided to upgrade my long-standing version 56 Firefox to version 90 (last version where Proton can be disabled). When my existing profile was opened with version 90, it always renamed my existing SQLite databases as .corrupt. I found this question while looking for an explanation, and I found the answer in Database.cpp:

// Firefox 60 uses schema version 43.
if (currentSchemaVersion < 43) {
    // These are versions older than Firefox 60 ESR that are not supported
    // anymore.  In this case it's safer to just replace the database.
    return NS_ERROR_FILE_CORRUPTED;
}

This means that any profiles older than Firefox 60 will never be usable in newer versions of Firefox and will always be marked as corrupt.

The easiest way to deal with this and keep your existing profile when upgrading to the current version of Firefox is to use an earlier release as a stepping stone. It will upgrade your databases to a newer version which is supported by the current Firefox.

To do it I used the following steps:

  1. Get the backup you should have of your Firefox profile from before you installed the current version. If you don't have one, you can try making a copy of your existing one, renaming the .corrupt databases back to their normal names.

  2. Download and extract an older version of Firefox that will support your profile version. I used the previous ESR, version 68.0.9. That's the 64-bit version but other platforms and languages are available. The exe setup file can be extracted using 7zip.

  3. The extracted folder will have a core folder containing the Firefox application files. Put the copy of your profile (the folder named like abcd1234.default) in this folder.

  4. To disable Firefox updates so they doesn't cause problems, create a folder named distribution inside core, then create a text file named policies.json inside that. Put the following into the JSON file and save it:

    { "policies": { "DisableAppUpdate": true } }

  5. Open a command prompt in core and run: firefox.exe -no-remote -profile YOUR_PROFILE_NAME. This will run a standalone version of Firefox using your profile, which will upgrade the databases.

  6. Once the browser is open, verify your profile looks correct. Browser history and favicons shown in bookmarks are two good indicators. Close Firefox and check the profile folder that none of the databases were renamed .corrupt. (You can rerun the command as many times as you want to verify and test things.)

  7. Copy the profile folder you just updated back into your normal Firefox profiles folder (on Windows, %AppData%\Mozilla\Firefox\Profiles). I'd suggest renaming the existing folder you have in that location to keep a backup.

  8. Install the current version of Firefox again. This time it should be able to upgrade your profile since the databases have been brought up to a supported version.

As an aside, this same method is a great way to test new Firefox versions. I used this method occasionally over the last few years to see if the addons I use had WebExtension versions available, and to see how hard it would be to update from version 56 to the newest. Just be very careful to never run firefox.exe without using -no-remote or it will find your normal existing profile and update it (always make a backup beforehand!)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.