After updating to the latest Firefox ESR 68.8, for some reason my Firefox stopped autofilling the addressbar.
Our first stop lies in Help → Troubleshooting information → Places Database (which will take you to about:support).
Click verify integrity, it will probably detect an issue, and then it claims it will replace it on the next startup.
> Task: checkIntegrity
– Unable to fix corruption, places.sqlite will be replaced on next startup
But oddly enough, nothing of the sort happened! Not to worry, right at the top there’s the
profile directory, which indicates where the
places.sqlite file can be found. Rename it to something like
.old, or just outright delete it if you don’t care, and success! Everything will be working again.
> Task: checkIntegrity
+ The places.sqlite database is sane
+ The favicons.sqlite database is sane
Unfortunately you’ll have to repopulate the autofill if you take this approach.
But I didn’t want to admit defeat. Surely it can’t be too hard to salvage our old database. As an aside, but not strictly relevant to what I did here, I think it’s always useful to have sqlitebrowser installed. That way you can browse the data and run SQL commands on it. For example, if you open
places.sqlite and run
PRAGMA integrity_check on it… Oh dear, it says the same thing as Firefox:
database disk image is malformed.
But we have a trick up our sleeve: command line SQLite. My initial plan was to play around with export and import to determine where the error was located since SQLite didn’t want to say, but that seemed a bit tedious. Essentially to try it table by table, and once I’d identified the table, basically to keep cutting it in half. But as luck would have it, SQLite 2.29 and higher has a built-in error correction feature. Docs here.
sqlite3 places.sqlite ".recover" | sqlite3 places_fixed.sqlite Error: near line 378367: NOT NULL constraint failed: moz_origins.host
All that was left was to give it a try and all was well with the world again — or at least with Firefox. The SQLite error recovery command found the error and fixed it up all by itself. So now I still have all of my location history. Useful? Maybe not in this particular case, but good to know if anything like this should happen in the future on some more important data.