So I’m switching between libressl, libressl-devel and openssl on my FreeBSD Raspberry Pi to see if any of them works (without segfaulting) when used from Python (spoiler: they don’t). And suddenly! Deinstalling openssl results in this:

pkg: sqlite error while executing DELETE FROM packages WHERE id = ?1; in file pkgdb.c:2305: database disk image is malformed

But I can query /var/db/pkg/local.sqlite using the SQLite shell! WTF. Maybe some index got corrupted or something because of the awful microSD storage of the Pi.

So, recovery procedure I figured out: move the database to a different place, recreate a new one (run, like, pkg upgrade), use a clever grep trick to separate the data from the schema (on the old DB, obviously), do some manual editing on the resulting data-only file (the trick doesn’t catch pkg_search because it’s a virtual table created using an INSERT or something), pipe the resulting SQL into the newly created database, use the shell to delete everything related to the offending package.

No, pkgng doesn’t like it when you just dump the DB into a new file, it will try to recreate tables as if you were upgrading. For some reason.