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

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

[Ingo Ruhnke]

[-- Attachment #1: Type: text/plain, Size: 1236 bytes --]

I am using guix on Ubuntu 19.04, due to some unrelated BTRFS problems I
needed to free some space and did a quick:

    $ rm -rv /gnu/store/*libreoff*

This expectedly confuses guix a little and leads to error messages such as
this which make it impossible to reinstall any packages:

    $ guix install libreoffice
    guix install: warning: Consider running 'guix pull' followed by
    'guix package -u' to get up-to-date packages and security updates.

    guix install: error: opening file
`/gnu/store/kmyp9i01aci0azxwcll41kmn347616q7-libreoffice-orcus.patch.drv':
No such file or directory

However trying to clean up the issue with "guix gc --verify" unexpectedly
fails as well, this time with a failed SQLite constraint, this seems like a
bug:

    $ guix gc --verify
    reading the store...
    checking path existence...
    path
`/gnu/store/96ry03xc73c67xs7aj2pnird7icnk578-libreoffice-6.1.5.2.drv'
disappeared, but it still has valid referrers!
    path
`/gnu/store/a1hnx6vgwlnqf4fshfln9ljnp9d8y9pl-libreoffice-6.1.5.2.drv'
disappeared, removing from database...
    guix gc: error: executing SQLite statement: FOREIGN KEY constraint
failed

    $ guix --version
    guix (GNU Guix) 84d7784afe00dfc23c7feeb7ede6bce73406760b

[-- Attachment #2: Type: text/html, Size: 1525 bytes --]

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

[Ricardo Wurmus]

Ingo Ruhnke <grumbel@gmail.com> writes:

> I am using guix on Ubuntu 19.04, due to some unrelated BTRFS problems I
> needed to free some space and did a quick:
>
>     $ rm -rv /gnu/store/*libreoff*

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

You should use “guix gc” instead to remove unused items from the store.

-- 
Ricardo

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

[Ingo Ruhnke]

[-- Attachment #1: Type: text/plain, Size: 1143 bytes --]

On Wed, Jul 17, 2019 at 10:02 PM Ricardo Wurmus <rekado@elephly.net> 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
);

[-- Attachment #2: Type: text/html, Size: 1679 bytes --]

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

[Chris Marusich]

[-- Attachment #1: Type: text/plain, Size: 2050 bytes --]

Ingo Ruhnke <grumbel@gmail.com> writes:

> On Wed, Jul 17, 2019 at 10:02 PM Ricardo Wurmus <rekado@elephly.net> 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

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 832 bytes --]