Date: Sun, 21 Dec 2014 20:56:20 +0000 From: Matthew Seaman <m.seaman@infracaninophile.co.uk> To: freebsd-pkg@freebsd.org Subject: Re: sqlite error while executing DROP INDEX Message-ID: <549733F4.1090507@infracaninophile.co.uk> In-Reply-To: <54973008.3050901@FreeBSD.org> References: <mailman.88.1418990402.75002.freebsd-pkg@freebsd.org> <20141221194818.788B9B51@hub.freebsd.org> <54973008.3050901@FreeBSD.org>
next in thread | previous in thread | raw e-mail | index | archive | help
This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--jGQMi2aHpqIElehsBohKreTECpulV1aWT
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
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 obvio=
us
>> workaround.
>>
>> # pkg <anything>
>> pkg: sqlite error while executing DROP INDEX deps_unique;CREATE UNIQ=
UE
>> 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?
>=20
> 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.
>=20
> What you can do: to see packages with non-unique names:
>=20
> % 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 =
;
>=20
> Now, pkg(8) does this next bit automatically anyhow:
>=20
> # pkg shell
> SQLite version 3.8.7 2014-10-17 11:24:17
> Enter ".help" for usage hints.
> sqlite> UPDATE packages SET name=3D name || "~pkg-renamed~" ||
> hex(randomblob(2))
> WHERE name IN (
> SELECT name FROM packages GROUP BY name HAVING count(name) > 1
> );
> ...> ...> ...> sqlite>
>=20
> 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 fai=
l
> to make the name column unique.
>=20
> In which case, just revert the change and try again. To revert the
> change easily, use transactions, and rollback instead of committing.
>=20
> 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
--jGQMi2aHpqIElehsBohKreTECpulV1aWT
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.22 (Darwin)
Comment: GPGTools - https://gpgtools.org
iQIcBAEBCgAGBQJUlzP7AAoJEABRPxDgqeTnLdIP/iU9hybTH25O1Ld8PT7GPW83
zKvHiO/CL/gRKqI4zVKHvX6NT+FHyBK/IVtr4+XCxgSCyYKRns9PStYyVo1dfUeY
iZDaD4yTVxcr9h5LckEj2d4M8sAoc2XsHF1parDo/vI/RKYTe8mIbI6jr9mw4xhA
7ToHyu6Md6gpo0Q/fh8d76ca4oMnfGiFMv072MYreyl5u3XPy9fzA/MWmh/y3v+X
KfwHiJGYJ3sK839WvGAZVEFq/xYZQPdzgeFZ4+mhpOktsJMtCOZw2WHpYjCsm/Qh
cg9/4qBRgmMK5ygeFpI/aeBBV5muOAn6nK8Y98nevhteKkfT36DnoMibe2ngPSl1
P0IHN1hgzxF30iO3ebD5VWT8OZJAOAx5POCFWvcfyCo2+8TAjD3xs6I4jMLHhAgM
2KEhc/JaC1DWpTEY/aLkwwqpwp9TgPIuKtkFu5/Aflwc6nkC7CW15s9o+95CVL2y
ZAOB0ghnGG03EkwkqNjaLM0vcc9rxsafgP0g2OfcyZ5H8JTO7uxFe+xi2eSu7If4
DsFEsel5iToVfIlonTZ2LZFAWIpOLiuzwrHWCaNqSq7OvP0zEj45QPu4M1FRXcNY
so2Jc/nuKX/pB4rEbThaHU+IGj9N0PAxuQjvL0tvlEvaqb7Uqp/8BkzqDdrLE3aB
X/s6U8L3JfR1gaWobFGJ
=7f+t
-----END PGP SIGNATURE-----
--jGQMi2aHpqIElehsBohKreTECpulV1aWT--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?549733F4.1090507>