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:

• https://developer.mozilla.org/en-US/Mozilla/Tech/Places/places.sqlite_Database_Troubleshooting

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$

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>

• https://developer.mozilla.org/en-US/Mozilla/Tech/Places/places.sqlite_Database_Troubleshooting

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???