I had been trying to install a package (pwgen, simple an no dependencies.
This didn't work because PKG first updated itself to version 1.4.7, which on this very old box somehow triggered a compatilibity issue because pkg fucked up there and now wanted to use XZ version 5 which wasn't supported.
Then it was stuck in half-upgrade because it didn't do it's own update using pkg-static
i then tried to use pkg-static to fix it but that fucked up the sqlite database
Here you can see all those shit errors:
But, using pkg-static actually got us the shell:
pkg-static version | grep pkg shows us that the upgrade at least worked (otherwise, there'd be no XZ error, right?)
Due to some (very matching) list post I tried to run this command that randomly renames stuff in your database. Funny enough only later in the post is the recommendation, but not explanation, to use transactional statements for this, to allow for rollback and retries. As usual, you're not given that info because why would you not want to break your database.
So, here's the shitty statement you don't need, for sake of completeness.
Those renamed packages actually show up like this:
But what really works was shown in this thread a few days later:
Step 1: Identify the ports that pkg itself incorrectly converted to the SQLite db.
In my case this pointed at the 4.5.0 version of docbook-xml. This makes sense, since most or all docbook ports have been merged...
I didn't worry very much about losing it, even more so since the files wouldn't be touched just by updating
So by the next step was.
Step 2: Drop the dependencies to the merged / broken port.
The original article even shows how to run this when pkg shell wouldn't work anymore:
I was then able to re-install lzma using pkg install -f and then things were much better.
Of course, please remember that you now didn't really fix a potential conflict on the filesystem side. you just removed it's notion from the database.
I think the cleanest fix will need to hammer-remove the merged ports, and reinstall all their dependent ports and, well.
Whatever. Actually, no. They'll need to scrap this old dbmail server, that's it.
pkg is just going to fuck it up to the point of no repair.
it's not your fault. it's not your old broken packages. it's not their circular dependencies. it's PKGNG storing invalid input it tries to process but fucking can't.