Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

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

share|improve this question
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 '10 at 15:03

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
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 '10 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 '13 at 19:41

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; a rather outdated ER diagram can be found here. 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 3: schema version 6
    • Firefox 3.5: schema version 8
    • Firefox 3.6: schema version 10
    • Firefox 4-7: schema version 11
    • Firefox 8-10: schema version 12
    • Firefox 11: schema version 16
    • Firefox 12: schema version 17
    • Firefox 13: schema version 19
    • Firefox 14-21: schema version 21
    • Firefox 22-23: schema version 22
    • Firefox 24 and later: schema version 23

PRAGMA page_size=4096;
PRAGMA user_version=<version>
  • 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:


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.

share|improve this answer
    
This is pure awesomeness. Helped me recover almost all the history from a corrupt places.sqlite. Thanks a lot!! –  Ashutosh Jindal Jan 10 at 23:27

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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