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:

host:/usr/ports/archivers/lzma# /usr/local/sbin/pkg-static check -B
pkg-static: sqlite error while executing DROP INDEX deps_unique;CREATE UNIQUE INDEX deps_unique ON deps(name, version, package_id); in file pkgdb.c:2262: UNIQUE constraint failed: deps.name, deps.version, deps.package_id
host:/usr/ports/archivers/lzma# pkg shelll
/libexec/ld-elf.so.1: /usr/local/lib/liblzma.so.5: version XZ_5.0 required by /usr/lib/libarchive.so.5 not defined

But, using pkg-static actually got us the shell:

# /usr/local/sbin/pkg-static shell 
SQLite version 3.8.7 2014-10-17 11:24:17

 

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.

sqlite> UPDATE packages SET name= name || "~pkg-renamed~" || hex(randomblob(2)) WHERE name IN ( SELECT name FROM packages GROUP BY name HAVING count(name) > 1 );

 

Those renamed packages actually show up like this:

pkg-static: docbook~pkg-renamed~F564-1.4: duplicate dependency listing: docbook-xml-4.4_1, ignoring
pkg-static: docbook~pkg-renamed~F564-1.4: duplicate dependency listing: docbook-xml-4.3, ignoring
pkg-static: docbook~pkg-renamed~F564-1.4: duplicate dependency listing: docbook-4.5_2, ignoring
pkg-static: docbook~pkg-renamed~F564-1.4: duplicate dependency listing: docbook-4.4_2, ignoring
pkg-static: docbook~pkg-renamed~F564-1.4: duplicate dependency listing: docbook-4.3, ignoring
pkg-static: docbook~pkg-renamed~F564-1.4: duplicate dependency listing: docbook-4.2, ignoring
pkg-static: docbook~pkg-renamed~F564-1.4: duplicate dependency listing: docbook-4.1_4, ignoring

 

 

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.

SELECT name, version, package_id FROM deps GROUP BY name, version,
  package_id HAVING count(*) > 1 ;

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.

DELETE FROM deps where 
origin='textproc/docbook-xml';

The original article even shows how to run this when pkg shell wouldn't work anymore:

sqlite3 /var/db/pkg/local.sqlite "delete from deps where origin='devel/pkg-config';"

https://www.mail-archive.com/freebsd-ports@freebsd.org/msg62890.html

 

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.