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

From: Ingo Ruhnke <[email-masked]>
Date: Wed, 17 Jul 2019 23:26:24 +0200

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
);