My places.sqlite file fails selftest -- how do I fix it?
My places.sqlite file keeps getting marked as corrupt and replaced with a fresh one.
So, I took a look at it with sqlite3.
I need help with this error:
sqlite> .selftest Missing SELFTEST table - default checks only 1: Expected: [ok] 1: Got: [*** in database main *** On tree page 2 cell 53: Rowid 102326 out of order Page 98 is never used|row 10332 missing from index moz_places_visitcount|row 10333 missing from index moz_places_visitcount|row 10334 missing from index moz_places_visitcount|row 10335 missing from index moz_places_visitcount|row 10336 missing from index moz_places_visitcount|row 10337 missing from index moz_places_visitcount|row 10338 missing from index moz_places_visitcount|row 10339 missing from index moz_places_visitcount|row 10340 missing from index moz_places_visitcount|row 10341 missing from index moz_places_visitcount|row 10885 missing from index moz_places_visitcount|row 11001 missing from index moz_places_lastvisitdateindex|row 11001 missing from index moz_places_frecencyindex|row 11001 missing from index moz_places_visitcount|row 11055 missing from index moz_places_guid_uniqueindex|row 11057 missing from index moz_places_guid_uniqueindex|row 11060 missing from index moz_places_guid_uniqueindex|row 11061 missing from index moz_places_guid_uniqueindex|row 11063 missing from index moz_places_guid_uniqueindex|row 11064 missing from index moz_places_guid_uniqueindex|row 11065 missing from index moz_places_guid_uniqueindex|row 11067 missing from index moz_places_url_hashindex|row 11068 missing from index moz_places_guid_uniqueindex|row 11070 missing from index moz_places_url_hashindex|row 11070 missing from index moz_places_guid_uniqueindex|row 11071 missing from index moz_places_guid_uniqueindex|row 11074 missing from index moz_places_guid_uniqueindex|row 11076 missing from index moz_places_guid_uniqueindex|row 11077 missing from index moz_places_guid_uniqueindex|wrong # of entries in index moz_places_url_hashindex|wrong # of entries in index moz_places_guid_uniqueindex|wrong # of entries in index moz_places_lastvisitdateindex|wrong # of entries in index moz_places_frecencyindex|wrong # of entries in index moz_places_visitcount|wrong # of entries in index moz_places_hostindex|wrong # of entries in index moz_places_faviconindex|row 46 missing from index moz_historyvisits_placedateindex|row 47 missing from index moz_historyvisits_placedateindex|row 48 missing from index moz_historyvisits_placedateindex|row 49 missing from index moz_historyvisits_placedateindex|row 50 missing from index moz_historyvisits_placedateindex|row 51 missing from index moz_historyvisits_placedateindex|row 52 missing from index moz_historyvisits_placedateindex|row 53 missing from index moz_historyvisits_placedateindex|row 54 missing from index moz_historyvisits_placedateindex|row 55 missing from index moz_historyvisits_placedateindex|row 56 missing from index moz_historyvisits_placedateindex|row 57 missing from index moz_historyvisits_placedateindex|row 58 missing from index moz_historyvisits_placedateindex|row 59 missing from index moz_historyvisits_placedateindex|row 60 missing from index moz_historyvisits_placedateindex|wrong # of entries in index moz_historyvisits_placedateindex] 1 errors out of 1 tests sqlite>
Wšykne wótegrona (11)
Argh. Is there no "Edit" button? No "preview"? No way to see if it gets messed up before posting?
I see "preview" for this reply. Maybe I can fix that formatting here? ... Yea, this is a whole lot better.
sqlite> .selftest
Missing SELFTEST table - default checks only
1: Expected: [ok]
1: Got: [*** in database main ***
On tree page 2 cell 53: Rowid 102326 out of order
Page 98 is never used|row 10332 missing from index moz_places_visitcount|row 10333 missing from index moz_places_visitcount|row 10334 missing from index moz_places_visitcount|row 10335 missing from index moz_places_visitcount|row 10336 missing from index moz_places_visitcount|row 10337 missing from index moz_places_visitcount|row 10338 missing from index moz_places_visitcount|row 10339 missing from index moz_places_visitcount|row 10340 missing from index moz_places_visitcount|row 10341 missing from index moz_places_visitcount|row 10885 missing from index moz_places_visitcount|row 11001 missing from index moz_places_lastvisitdateindex|row 11001 missing from index moz_places_frecencyindex|row 11001 missing from index moz_places_visitcount|row 11055 missing from index moz_places_guid_uniqueindex|row 11057 missing from index moz_places_guid_uniqueindex|row 11060 missing from index moz_places_guid_uniqueindex|row 11061 missing from index moz_places_guid_uniqueindex|row 11063 missing from index moz_places_guid_uniqueindex|row 11064 missing from index moz_places_guid_uniqueindex|row 11065 missing from index moz_places_guid_uniqueindex|row 11067 missing from index moz_places_url_hashindex|row 11068 missing from index moz_places_guid_uniqueindex|row 11070 missing from index moz_places_url_hashindex|row 11070 missing from index moz_places_guid_uniqueindex|row 11071 missing from index moz_places_guid_uniqueindex|row 11074 missing from index moz_places_guid_uniqueindex|row 11076 missing from index moz_places_guid_uniqueindex|row 11077 missing from index moz_places_guid_uniqueindex|wrong # of entries in index moz_places_url_hashindex|wrong # of entries in index moz_places_guid_uniqueindex|wrong # of entries in index moz_places_lastvisitdateindex|wrong # of entries in index moz_places_frecencyindex|wrong # of entries in index moz_places_visitcount|wrong # of entries in index moz_places_hostindex|wrong # of entries in index moz_places_faviconindex|row 46 missing from index moz_historyvisits_placedateindex|row 47 missing from index moz_historyvisits_placedateindex|row 48 missing from index moz_historyvisits_placedateindex|row 49 missing from index moz_historyvisits_placedateindex|row 50 missing from index moz_historyvisits_placedateindex|row 51 missing from index moz_historyvisits_placedateindex|row 52 missing from index moz_historyvisits_placedateindex|row 53 missing from index moz_historyvisits_placedateindex|row 54 missing from index moz_historyvisits_placedateindex|row 55 missing from index moz_historyvisits_placedateindex|row 56 missing from index moz_historyvisits_placedateindex|row 57 missing from index moz_historyvisits_placedateindex|row 58 missing from index moz_historyvisits_placedateindex|row 59 missing from index moz_historyvisits_placedateindex|row 60 missing from index moz_historyvisits_placedateindex|wrong # of entries in index moz_historyvisits_placedateindex]
1 errors out of 1 tests
sqlite>
Hi
As a first step, I recomend that you update your copy of Firefox so that you have the latest security and stability fixes that may help resolve this issue.
If this doe snot resolve this problem, please come back here and we can look into it again for you.
If you use Sync then best is to disconnect Sync temporarily during troubleshooting.
You can check for problems with the places.sqlite database (bookmarks and history) in the Firefox profile folder.
- use "Verify Integrity" on the "Help -> Troubleshooting Information" (about:support) page
in case errors are reported close and restart Firefox and retry
See also:
- http://kb.mozillazine.org/Locked_or_damaged_places.sqlite
- https://support.mozilla.org/en-US/kb/Bookmarks+not+saved#w_fix-the-bookmarks-file
If "Verify Integrity" on the "Help -> Troubleshooting Information" (about:support) page cannot repair places.sqlite then remove all places.sqlite files in the Firefox profile folder. Firefox will rebuild places.sqlite and restore the bookmarks from a recent JSON backup in the bookmarkbackups folder.
- keep a backup copy of places.sqlite in case a new places.sqlite database has to be created
- http://kb.mozillazine.org/Unable_to_process_the_backup_file_-_Firefox
You can use the button on the "Help -> Troubleshooting Information" (about:support) page to go to the current Firefox profile folder or use the about:profiles page.
- Help -> Troubleshooting Information -> Profile Directory:
Windows: Show Folder; Linux: Open Directory; Mac: Show in Finder - http://kb.mozillazine.org/Profile_folder_-_Firefox
See also:
Lovely.
Places Database
Integrity
> Integrity check
- The database is corrupt
> Reindex
- Unable to reindex database
> Integrity check
- The database is corrupt
- Unable to fix corruption, database will be replaced on next startup
So I found advice on stack overflow for a generic database rebuilt, involving a .dump to a text file, and then a full rebuild of a new copy.
Said text file comes out pretty large :-) Oddly, it is smaller than the database, even with all that SQL instruction text in there.
But the "PRAGMA" instructions on that mozilla specific page? They return nothing. No errors, no messages, nothing.
Hmm, this might be something to do with it:
michael$ sqlite3 new.sqlite < dump_places.sql
Error: near line 10998: UNIQUE constraint failed: moz_places.id
Error: near line 10999: UNIQUE constraint failed: moz_places.id
Error: near line 11000: UNIQUE constraint failed: moz_places.id
Error: near line 11001: UNIQUE constraint failed: moz_places.id
Error: near line 11002: UNIQUE constraint failed: moz_places.id
Error: near line 11003: UNIQUE constraint failed: moz_places.id
Error: near line 11004: UNIQUE constraint failed: moz_places.id
Error: near line 11005: UNIQUE constraint failed: moz_places.id
Error: near line 11006: UNIQUE constraint failed: moz_places.id
Error: near line 11007: UNIQUE constraint failed: moz_places.id
Error: near line 11008: UNIQUE constraint failed: moz_places.id
Error: near line 11009: UNIQUE constraint failed: moz_places.id
Error: near line 404421: near "*": syntax error
michael$
Wót Keybounce
Alright, something is wrong here.
After removing the 10 or so URL's that had duplicate ID's, and removing the extra output at the end of the file (geez, remember how I said those PRAGMA commands made no output? :-), the database seems to pass validation, but Firefox calls it corrupt on every launch.
sqlite> .indexes
moz_annos_placeattributeindex moz_places_frecencyindex
moz_bookmarks_guid_uniqueindex moz_places_guid_uniqueindex
moz_bookmarks_itemindex moz_places_hostindex
moz_bookmarks_itemlastmodifiedindex moz_places_lastvisitdateindex
moz_bookmarks_parentindex moz_places_url_hashindex
moz_historyvisits_dateindex moz_places_visitcount
moz_historyvisits_fromindex sqlite_autoindex_moz_anno_attributes_1
moz_historyvisits_placedateindex sqlite_autoindex_moz_favicons_1
moz_items_annos_itemattributeindex sqlite_autoindex_moz_hosts_1
moz_keywords_placepostdata_uniqueindex sqlite_autoindex_moz_inputhistory_1
moz_places_faviconindex sqlite_autoindex_moz_keywords_1
sqlite> .selftest
Missing SELFTEST table - default checks only
0 errors out of 1 tests
sqlite> ^D
The indexes are there. It passes the database checks.
But Firefox wants to call it corrupt 100% of the time now. I can't even use the rebuild tool on the about:support page, because that requires me to be able to start up with that old database in place.
Any luck with this article>
After the rebuild? I got:
michael$ sqlite3 places.sqlite.corrupt
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> PRAGMA integrity_check;
ok
sqlite>
Let me go through the steps on the original (the one with the duplicated primary keys ... how did those get into the database in the first place?)
So, notes as I do this.
First, why do ".backup" and ".clone" give such different outputs? And why does .clone actually complain about table names like this?
sqlite> .backup new-back.sqlite
sqlite> .clone new-clone.sqlite
moz_places... done
moz_historyvisits... done
moz_inputhistory... done
moz_hosts... done
moz_bookmarks... done
moz_keywords... done
sqlite_sequence... Error: object name reserved for internal use: sqlite_sequence
SQL: [CREATE TABLE sqlite_sequence(name,seq)]
done
moz_favicons... done
moz_anno_attributes... done
moz_annos... done
moz_items_annos... done
sqlite_stat1... Error: object name reserved for internal use: sqlite_stat1
SQL: [CREATE TABLE sqlite_stat1(tbl,idx,stat)]
Error 1: no such table: sqlite_stat1 on [SELECT * FROM "sqlite_stat1"]
done
sqlite_autoindex_moz_inputhistory_1... done
sqlite_autoindex_moz_hosts_1... done
sqlite_autoindex_moz_keywords_1... done
sqlite_autoindex_moz_favicons_1... done
sqlite_autoindex_moz_anno_attributes_1... done
moz_places_faviconindex... done
moz_places_hostindex... done
moz_places_visitcount... done
moz_places_frecencyindex... done
moz_places_lastvisitdateindex... done
moz_historyvisits_placedateindex... done
moz_historyvisits_fromindex... done
moz_historyvisits_dateindex... done
moz_bookmarks_itemindex... done
moz_bookmarks_parentindex... done
moz_bookmarks_itemlastmodifiedindex... done
moz_places_guid_uniqueindex... done
moz_bookmarks_guid_uniqueindex... done
moz_annos_placeattributeindex... done
moz_items_annos_itemattributeindex... done
moz_keywords_placepostdata_uniqueindex... done
moz_places_url_hashindex... done
sqlite>
The output from .backup is smaller than the original, but bigger than the .clone; the .clone is the same size as .dump followed by a new database. (Another stack overflow answer indicated that .backup can succeed where .dump fails -- something about custom collations and other oddballs).
... have to close firefox before putting this new one in place, so posting this now, and ...
Well, it isn't immediately complaining.
Integrity check says:
> Integrity check
+ The database is sane
> Coherence check
+ The database is coherent
> Orphans expiration
+ Database cleaned up
> Vacuum
Initial database size is 71680 KiB
+ The database has been vacuumed
Final database size is 71680 KiB
> Statistics
Database size is 71680 KiB
user_version is 35
page_size is 32768
cache_size is -2048
journal_mode is wal
synchronous is 1
History can store a maximum of 146801 unique pages
Table moz_places has 138033 records
Table moz_historyvisits has 238517 records
Table moz_inputhistory has 318 records
Table moz_hosts has 5245 records
Table moz_bookmarks has 14628 records
Table moz_keywords has 6 records
Table sqlite_sequence has 2 records
Table moz_favicons has 4258 records
Table moz_anno_attributes has 14 records
Table moz_annos has 302 records
Table moz_items_annos has 2240 records
Table sqlite_stat1 has 15 records
Index sqlite_autoindex_moz_inputhistory_1
Index sqlite_autoindex_moz_hosts_1
Index sqlite_autoindex_moz_keywords_1
Index sqlite_autoindex_moz_favicons_1
Index sqlite_autoindex_moz_anno_attributes_1
Index moz_places_faviconindex
Index moz_places_hostindex
Index moz_places_visitcount
Index moz_places_frecencyindex
Index moz_places_lastvisitdateindex
Index moz_historyvisits_placedateindex
Index moz_historyvisits_fromindex
Index moz_historyvisits_dateindex
Index moz_bookmarks_itemindex
Index moz_bookmarks_parentindex
Index moz_bookmarks_itemlastmodifiedindex
Index moz_places_guid_uniqueindex
Index moz_bookmarks_guid_uniqueindex
Index moz_annos_placeattributeindex
Index moz_items_annos_itemattributeindex
Index moz_keywords_placepostdata_uniqueindex
Index moz_places_url_hashindex
So, I think this, at least, is good.
Now, to rebuild this profile to solve that other problem with local storage.
Hold on -- cache size is negative???