Recover/repair webappsstore.sqlite

[tuaris]

My webappsstore.sqlite is damaged due to some filesystem issues. This is causing rendering and functional issues on websites that rely on local storage. This is indicated by the error that shows up in the console NS_ERROR_FILE_CORRUPTED. The size of the storage is currently just below 500 MB and it seems that it is partially accessible. Interestingly, the part that's usable has data I care about most.

How can I repair this file without loosing that information? I've already tried using the tools available with SQLlite with no success. It just spits out the same file with the damaged data.

[morat]

It's not always possible to recover a corrupt sqlite database.

Recover a corrupt sqlite database
http://stackoverflow.com/questions/18259692

Command line shell
http://www.sqlite.org/cli.html
http://www.sqlite.org/cli.html#recover_data_from_a_corrupted_database
http://www.sqlite.org/download.html

I believe the current sqlite version is 3.33.0 for the release build.

Reference
http://searchfox.org/mozilla-release/source/third_party/sqlite3/README.MOZILLA
http://searchfox.org/mozilla-release/search?q=define+SQLITE_VERSION

[paul_wilson]

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.

I hope this information will be helpful!
Paul_Wilson

[tuaris]

I hope this information will be helpful!

Reference
http://searchfox.org/mozilla-release/source/third_party/sqlite3/README.MOZILLA
http://searchfox.org/mozilla-release/search?q=define+SQLITE_VERSION

Thank you both that was very helpful especially the part about removing the ROLLBACK statement. That explains why my previous attempts at using the SQLite shell produced nothing. These are the commands I ran:

Code: Select all

sqlite> .output dump.sql
sqlite> .dump


The resulted in a roughly 150 MB .sql dump file. The interesting part is that SQLite seems to have continued dumping past the corrupted data?

Code: Select all

INSERT INTO webappsstore2 VALUES('',....REDACTED
INSERT INTO webappsstore2 VALUES('',....REDACTED
INSERT INTO webappsstore2 VALUES('',....REDACTED
INSERT INTO webappsstore2 VALUES(NULL,NULL,NULL,NULL,NULL);
INSERT INTO webappsstore2 VALUES(NULL,NULL,NULL,NULL,NULL);
INSERT INTO webappsstore2 VALUES(NULL,NULL,NULL,NULL,NULL);
INSERT INTO webappsstore2 VALUES(NULL,NULL,NULL,NULL,NULL);
INSERT INTO webappsstore2 VALUES('',....REDACTED
/****** CORRUPTION ERROR *******/
INSERT INTO webappsstore2 VALUES('',....REDACTED
INSERT INTO webappsstore2 VALUES('',....REDACTED


Sadly, this table webappsstore2 does not have an ID that I can use to filter the query, but it seems that would have been unnecessary in this case?

After making the required adjustments to the .sql dump, I renamed the damaged file and re-imported it into a new database. The resulting database is about a quarter of the size of the old one (150 MB vs 500 MB).

Code: Select all

> sqlite3.exe webappsstore.sqlite
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .read dump.sql
sqlite> .quit


I opened Firefox and some of the data I care about was not there (I was able to recover most of the other data using the above method). I don't understand what is happening? with the "broken" database the data is readable to Firefox, but doesn't seem to be readable to SQLite?

Here's the output of PRAGMA integrity_check

Code: Select all

sqlite> PRAGMA integrity_check
   ...> ;
*** in database main ***
On tree page 12231 cell 6: invalid page number -2078538980
Page 11836: btreeInitPage() returns error code 11
Page 12719: btreeInitPage() returns error code 11
Page 12718: btreeInitPage() returns error code 11
On tree page 12512 cell 10: overflow list length is 6 but should be 14
Page 5838: btreeInitPage() returns error code 11
Page 11837: btreeInitPage() returns error code 11
On tree page 12068 cell 9: invalid page number 1751279982
On tree page 11335 cell 0: invalid page number 27394
Page 11164: btreeInitPage() returns error code 11
On tree page 10425 cell 17: invalid page number 218103808
On tree page 9374 cell 3: 2nd reference to page 1282
Page 8881: btreeInitPage() returns error code 11
Page 9581: btreeInitPage() returns error code 11
Page 9582: btreeInitPage() returns error code 11
On tree page 8700 cell 9: invalid page number 1751279982
Page 8016: btreeInitPage() returns error code 11
On tree page 7733 cell 2: invalid page number -805011285
On tree page 7709 cell 1: invalid page number 69635
On tree page 5466 cell 1: overflow list length is 22 but should be 26
On tree page 5011 cell 0: 2nd reference to page 1282
On tree page 5889 cell 0: 2nd reference to page 1282
On tree page 5277 cell 40: invalid page number -805044140
On tree page 5843 cell 7: 2nd reference to page 5378
On tree page 4998 cell 3: 2nd reference to page 5634
On tree page 3200 cell 2: 2nd reference to page 12032
Page 5224: btreeInitPage() returns error code 11
Page 5218: btreeInitPage() returns error code 11
On tree page 5207 cell 2: invalid page number 61186
On tree page 3504 cell 0: overflow list length is 1 but should be 7
Page 5191: btreeInitPage() returns error code 11
On tree page 5189 cell 2: Rowid 0 out of order
On tree page 5189 cell 1: Rowid 0 out of order
On tree page 5189 cell 0: Rowid 0 out of order
Fragmentation of 23959 bytes reported as 0 on page 5189
On tree page 12489 cell 1559: Rowid 163272 out of order
On tree page 12489 cell 503: 2nd reference to page 1282
Page 1283 is never used
Page 1284 is never used
Page 1285 is never used
Page 1286 is never used
Page 4382 is never used
Page 5190 is never used
Page 5193 is never used
Page 5194 is never used
Page 5195 is never used
Page 5196 is never used
Page 5197 is never used
Page 5198 is never used
Page 5216 is never used
Page 5217 is never used
Page 5219 is never used
Page 5220 is never used
Page 5221 is never used
Page 5222 is never used
Page 5223 is never used
Page 5226 is never used
Page 5227 is never used
Page 5319 is never used
Page 5321 is never used
Page 5417 is never used
Page 5477 is never used
Page 5493 is never used
Page 5503 is never used
Page 5719 is never used
Page 5744 is never used
Page 5747 is never used
Page 5786 is never used
Page 5814 is never used
Page 5831 is never used
Page 5833 is never used
Page 5842 is never used
Page 6168 is never used
Page 6169 is never used
Page 6170 is never used
Page 6177 is never used
Page 6178 is never used
Page 6284 is never used
Page 6527 is never used
Page 7026 is never used
Page 7190 is never used
Page 7391 is never used
Page 7392 is never used
Page 7393 is never used
Page 7396 is never used
Page 7633 is never used
Page 7636 is never used
Page 7640 is never used
Page 7641 is never used
Page 7642 is never used
Page 7671 is never used
Page 7674 is never used
Page 7681 is never used
Page 7713 is never used
Page 7716 is never used
Page 7744 is never used
Page 8012 is never used
Page 8014 is never used
Page 8015 is never used
Page 8029 is never used