Subject: bug#36706: "guix gc --verify" fails with "FOREIGN KEY constraint failed"

From: Chris Marusich <[email-masked]>
Date: Sun, 21 Jul 2019 16:37:01 -0700

Ingo Ruhnke <[email-masked]> writes:

> On Wed, Jul 17, 2019 at 10:02 PM Ricardo Wurmus <[email-masked]> wrote:
>
>> This is bad and you cannot recover from it.  The store should *never* be
>> edited manually as it will become inconsistent with the database (which
>> I assume you have not edited).
>>
>
> I recovered it just fine with a quick&dirty:
>
> $ sqlite3 /var/guix/db.sqlite
> delete from Refs where reference in (select id from ValidPaths where path
> glob "*libreof*");
> delete from Refs where referrer in (select id from ValidPaths where path
> glob "*libreof*");
> delete from DerivationOutputs where path glob "*libreof*";
> delete from ValidPaths where path glob "*libreof*";
>
> Which I assume is what `guix gc --verify=repair` was trying to do, but it's
> not cleaning up Refs table and thus failing at the FOREIGN KEY constraint.
>
> $ sqlite3 /var/guix/db/db.sqlite
> [...]
> sqlite> .schema
> [...]
> CREATE TABLE Refs (
>     referrer  integer not null,
>     reference integer not null,
>     primary key (referrer, reference),
>     foreign key (referrer) references ValidPaths(id) on delete cascade,
>     foreign key (reference) references ValidPaths(id) on delete restrict
> <-------- this one here
> );

That might work, or it might not.  Since manual manipulation of the
store and the database is not supported, and your Guix installation
might now be in an unknown, invalid state.  At the very least, you
should probably run "guix gc --verify=contents,repair".  The safest (but
admittedly heavy-handed) thing to do is to reinstall Guix completely.

The guix-daemon is carefully designed to maintain several critical
invariants regarding the state of the store and its database.  If by
manually modifying the store or the database you have accidentally
invalidated one of those invariants (or if you have made a change that
is not detected now but which later on might invalidate one of those
invariants), there is no guarantee you can easily recover.

-- 
Chris