sqlite error while executing DROP INDEX

[Matthew Seaman]

On 22/12/2014 04:26, Roger Marquis wrote:
> Matthew Seaman wrote:
>> Something like:
>>   SELECT name, version, package_id FROM deps GROUP BY name, version,
>>   package_id HAVING count(*) > 1 ;
> 
> That returns:
> 
>   pkgconf|0.8.7_2|7714
> 
> Though the server's daily 'pkg info' gave no indication this was multiply
> installed.  Question now is how to remove the package or at least the
> package's (index?) records using 'pkg shell'?

Unlike the old pkg_tools, in principle it is not possible to have
'multiply installed' packages with pkg(8)[*].  In exactly the same way
that it is impossible to have the sort of duplicated dependency you're
seeing here.

You could fix up the apparent problem by deleting all but one of the
duplicated rows in the deps table, but that doesn't explain how the
problem happened in the first place and it gives no guarantee that you
haven't got similar problems elsewhere.

First, check exactly what the duplicated lines contain.  There's an
'origin' field in the deps table, which could be enlightening, and there
could be more than two duplicated rows:

   SELECT * FROM DEPS WHERE name='pkgconf' AND version='0.8.7_2'
       AND package_id='7714';

Given two rows that are exactly the same then:

(untested...)

Hmmm... LIMIT 1 on a DELETE statement requires sqlite be compiled with
the -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT define set, which appears not to
be the case as far as I can see by inspecting the pkg(8) sources.  So
you might have to install sqlite3 from ports with the appropriate flags
and then:

   # sqlite3 /var/db/pkg/local.sqlite

   BEGIN;
   DELETE FROM deps WHERE name='pkgconf' AND version='0.8.7_2'
       AND package_id='7714' LIMIT 1;
   SELECT * FROM DEPS WHERE name='pkgconf' AND version='0.8.7_2'
       AND package_id='7714';

Then if that achieved the desired effect:

   COMMIT;

or else

   ROLLBACK;

	Cheers,

	Matthew

[*] which was more about duplicated records of a package being installed
than the same package being installed twice.

-- 
Dr Matthew J Seaman MA, D.Phil.

PGP: http://www.infracaninophile.co.uk/pgpkey
JID: matthew at infracaninophile.co.uk

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