sqlite error while executing DROP INDEX

[Matthew Seaman]

On 2014/12/21 20:39, Matthew Seaman wrote:
> 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.

Errr.... actually, this isn't the solution to exactly the problem you
described.  But it's close -- you're failing to create a unique index on
the deps table, not the packages table.  As it's a unique index over
multiple columns, you'll need to adapt the code a bit.  Something like:

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

However, just renaming stuff in the deps table won't fix the problem.
You need to work out what the duplicated rows are, find out what
packages reference those rows and force them all to refer to one of
those row in the deps table, then delete the other duplicate rows.

The fact that you have duplicates at all in that table means that
something went pretty badly wrong with pkg(8) at an earlier date, so
there could be all sorts of weirdnesses in there.  Failing heroic DBA
action to fix up the schema, you might find it quicker and easier to
blow away your package database, start again with an empty package DB
and reinstall all your packages...

It depends on just how much stuff you've got on that system.

	Cheers,

	Matthew

	Cheers,

	Matthew



-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 882 bytes
Desc: OpenPGP digital signature
URL: <http://lists.freebsd.org/pipermail/freebsd-pkg/attachments/20141221/43cfe3e8/attachment.sig>