sqlite error while executing DROP INDEX

[Matthew Seaman]

On 2014/12/21 19:42, Roger Marquis wrote:
> Of all the pkgng bugs we've finally hit one that doesn't have an obvious
> workaround.
> 
>   # pkg <anything>
>   pkg: 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
> 
> Any sqlite gurus out there know how to fix this?

This is trying to create a unique index on the name column of the
packages table.  The unique key was finally changed to pkg name just
recently: previously it was a combination of name and version.  This
entailed a bit of work in the ports tree making sure all the different
ports have unique package names -- which is all fine and dandy, except
that there will be people with older packages installed which *don't*
have unique names.

What you can do:  to see packages with non-unique names:

% pkg shell
SQLite version 3.8.7 2014-10-17 11:24:17
Enter ".help" for usage hints.
sqlite> select name from packages group by name having count(name) > 1 ;

Now, pkg(8) does this next bit automatically anyhow:

# pkg shell
SQLite version 3.8.7 2014-10-17 11:24:17
Enter ".help" for usage hints.
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
    );
   ...>    ...>    ...> sqlite>

That appends '~pkg-renamed~' and a string of 4 random hex digits to the
name of anything if it is non-unique.  Now, 4 random hex digits gives a
1 in 65536 chance that you'll get the same 4 random digits again, so
it's entirely possible but pretty long odds that this will actually fail
to make the name column unique.

In which case, just revert the change and try again.  To revert the
change easily, use transactions, and rollback instead of committing.

Oh, and if you're worried about dealing with all those randomly named
packages, you can use the portname 'posrts-mgmt/pkg' instead for most
purposes.

	Cheers,

	Matthew