From owner-freebsd-pkg@FreeBSD.ORG Sun Dec 21 20:39:48 2014 Return-Path: Delivered-To: freebsd-pkg@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [8.8.178.115]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by hub.freebsd.org (Postfix) with ESMTPS id A6092210 for ; Sun, 21 Dec 2014 20:39:48 +0000 (UTC) Received: from smtp.infracaninophile.co.uk (smtp6.infracaninophile.co.uk [IPv6:2001:8b0:151:1:3cd3:cd67:fafa:3d78]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "smtp.infracaninophile.co.uk", Issuer "ca.infracaninophile.co.uk" (not verified)) by mx1.freebsd.org (Postfix) with ESMTPS id 3131D2A76 for ; Sun, 21 Dec 2014 20:39:48 +0000 (UTC) Received: from maggot.black-earth.co.uk (maggot.black-earth.co.uk [81.2.117.101]) (authenticated bits=0) by smtp.infracaninophile.co.uk (8.15.1/8.15.1) with ESMTPSA id sBLKdaRh020687 (version=TLSv1.2 cipher=DHE-RSA-AES128-SHA bits=128 verify=NO) for ; Sun, 21 Dec 2014 20:39:37 GMT (envelope-from matthew@FreeBSD.org) Authentication-Results: smtp.infracaninophile.co.uk; dmarc=none header.from=FreeBSD.org DKIM-Filter: OpenDKIM Filter v2.9.2 smtp.infracaninophile.co.uk sBLKdaRh020687 Authentication-Results: smtp.infracaninophile.co.uk/sBLKdaRh020687; dkim=none reason="no signature"; dkim-adsp=none; dkim-atps=neutral Message-ID: <54973008.3050901@FreeBSD.org> Date: Sun, 21 Dec 2014 20:39:36 +0000 From: Matthew Seaman User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:31.0) Gecko/20100101 Thunderbird/31.3.0 MIME-Version: 1.0 To: freebsd-pkg@freebsd.org Subject: Re: sqlite error while executing DROP INDEX References: <20141221194818.788B9B51@hub.freebsd.org> In-Reply-To: <20141221194818.788B9B51@hub.freebsd.org> Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 7bit X-Virus-Scanned: clamav-milter 0.98.5 at lucid-nonsense.infracaninophile.co.uk X-Virus-Status: Clean X-Spam-Status: No, score=-2.9 required=5.0 tests=ALL_TRUSTED,AWL,BAYES_00 autolearn=ham autolearn_force=no version=3.4.0 X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) on lucid-nonsense.infracaninophile.co.uk X-BeenThere: freebsd-pkg@freebsd.org X-Mailman-Version: 2.1.18-1 Precedence: list List-Id: Binary package management and package tools discussion List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 21 Dec 2014 20:39:48 -0000 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 > 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