Database upgrades are not atomic with schema number bump

[nico202]

Report

I'm on NixOS unstable. After nixos-rebuild switch'ing to the latest unstable, (upgrade from the unstable of last week), nix does not work anymore, neither as root nor as user.
The error message:

nix-shell -p # same as sudo nixos-rebuild switch
error: cannot open connection to remote store 'daemon': executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')        
(use '--show-trace' to show detailed location information)

The same error if run with sudo or after sudo -s.

Details

nix-info as user (those don's seems to be channels, see the output of nix-channel --list below):

• system: "x86_64-linux"
• host os: Linux 4.14.24, NixOS, 18.03pre130932.cc4677c36ee (Impala)
• multi-user?: no
• sandbox: yes
• version: nix-env (Nix) 2.0pre5968_a6c0b773
• channels(IITaudio): "nixos-18.03pre129076.831ef4756e3, telegram-cli-2016-03-23, machine-0.9.0, gitlab-runner-1.10.4, california-0.4.0, entr-3.6, tmuxp-1.2.2, keybase-20170209.17b641d, kbfs-20170209.d1db463, non-2016-12-07, go-sct-20160529-1d6b5e0, freecad-0.16, antimony-0.9.2, librecad-2.1.3, lemonbar-xft-unstable-2016-02-17, pencil-2.0.21, python3.5-qnotero-1.0.0, ruby-2.3.3, gimp-2.8.20, chromium-56.0.2924.87, nix-repl-1.11.7-2016-02-28, gparted-0.26.1, screen-4.5.1, vmpk-0.5.1, a2jmidid-8, platformio, teensyduino-1.6.12, teensy-loader-cli-2.1, blender-2.78c, unetbootin-613, mediainfo-0.7.91, xournal-0.4.8, zip-3.0, graphviz-2.40.1, ffmpeg-3.2.4, vlc-2.2.4, okular-16.12.2, pdfmod-0.9.1, green-pdfviewer-nightly-2014-04-22, android-sdk-25.2.3, android-studio-2.2.3.0, zathura-with-plugins-0.3.7, openjdk-8u121b13, android-ndk-r10e, virtualbox-5.1.14, R-3.3.2-wrapper, aspell-dict-en-2016.06.26-0"
• channels(gitlab-runner): "nixpkgs-18.03pre117013.aebdc892d6"
• channels(root): "nixos-18.03pre130932.cc4677c36ee"
• nixpkgs: /nix/var/nix/profiles/per-user/root/channels/nixos/nixpkgs

nix-info as root:
error: executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')

• system: 0
• host os: Linux 4.14.24, NixOS, 18.03pre130932.cc4677c36ee (Impala)
• multi-user?: no
• sandbox: yes
  error: executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')
• version: 0
  error: executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')

Both as user and root:
nix-channel --list
nixos https://nixos.org/channels/nixos-unstable

cat /etc/nix/nix.conf

# WARNING: this file is generated from the nix.* options in                                                                                                                                      
# your NixOS configuration, typically
# /etc/nixos/configuration.nix.  Do not edit it!
build-users-group = nixbld
max-jobs = 4
cores = 1
sandbox = true
extra-sandbox-paths =  
substituters = https://cache.nixos.org/
trusted-substituters = 
trusted-public-keys = cache.nixos.org-1:6NCHdD59X431o0gWypbMrAURkbJ16ZPMQFGspcDShjY=
auto-optimise-store = false
require-sigs = true

trusted-users = root
allowed-users = *
builders =

nix-store --verify --check-contents --readonly-mode

root:

error: executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')

user:

error: cannot open connection to remote store 'daemon': executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')

[infinisil]

Ping @edolstra, this seems very bad

[shlevy]

@NixOS/nix-core FYI

[nico202]

I'm waiting to re-install nixos from a live usb to help this get fixed, so please let me know of any missing information you need, thanks :)

[shlevy]

@nico202 What version of nix did you have before?

[nico202]

@shlevy ehm, no idea how to find out. I've always been on latest unstable, and run sudo nix-collect-garbage -d before upgrading. This is what I have in /nix/store now:

ls /nix/store|grep nix-1.|grep -v .drv|grep -v man

0l1dl400k2a5b2r1r6jci113gwyrgqyh-nix-1.11.16                                                                                                                                                     
27llrm6fwf5yl30907zczdg6cn3jvw55-nix-1.11.15
85g53pzfnq6vsn233i2jzjf0ixmh0qd8-nix-1.11.8
866pyym5zgvx78kr55pwas16xp1m0sjp-nix-1.11.11
ggj0xvnkx0r22x21z4jnz16vq6k76wx0-nix-1.11.16
jpiy120h7ik4brra9r8k26jngj3vshll-nix-1.11.16
lpy12ab10d7gxmcvfx8dg5ccj0dcb8vl-nix-1.11.15
m0ikyjfgansgrabsrgjf4kivnmi8ws6g-nix-1.12pre5413_b4b1f452
nqkha8l57hxpgzkcpdgxyxsgr389cy3z-nix-1.11.15
nsrymmfgs6isjbf9wv105dp9sf3qyqvb-nix-1.11.16
pjk50qzcs5n3x43q8hb4556g4xm22rr3-nix-1.11.7
r91f56wfami48l9zqyk16n0xmsaw0a8n-nix-1.11.15

[shlevy]

@nico202 Can you check /run/booted-system?

[shlevy]

Ah, if you gc'd before upgrading then you should still have the generation right before the upgrade too.

[shlevy]

Ah I bet I know what it is. The database migrations aren't atomic. Let me get you a command to verify...

[shlevy]

Can you do:

echo .schema ValidPaths | sqlite3 /nix/var/nix/db/db.sqlite
cat /nix/var/nix/db/schema
?

[shlevy]

I think we need to store the schema version in the DB to avoid this.

[nico202]

I'd like to, but I don't have sqlite3 installed .-. I have another nixos machine, is there a simple way to copy all the dependencies and install it manually from there?

[shlevy]

@nico202 Not sure what you mean by copy all the dependencies, but you can just copy db.sqlite

[shlevy]

@nico202 Note that I think this will be resolvable without re-installing. Just have to verify my guess.

[nico202]

@shlevy ahah yeah I was thinking the other way round (moving sqlite3 to this machine XD) I'll copy the file and try.
In the meanwhile, cat /nix/var/nix/db/schema = 7

[nico202]

CREATE TABLE ValidPaths (
    id               integer primary key autoincrement not null,
    path             text unique not null,
    hash             text not null,
    registrationTime integer not null,
    deriver          text,
    narSize          integer
, ultimate integer, sigs text, ca text);
CREATE TRIGGER DeleteSelfRefs before delete on ValidPaths
  begin
    delete from Refs where referrer = old.id and reference = old.id;
  end;

[shlevy]

OK, great. So what happened here is that you got a schema upgrade saved in the DB but the schema version file was not updated (due to process getting interrupted, reboot, whatever). The quick workaround is echo -n 10 > /nix/var/nix/db/schema, I'm working on a proper code fix to avoid this in the future.

[nico202]

Wow, that was easy, thanks!

[nico202]

Do you want to keep this open until you push a fix or can I close it?

[shlevy]

I'll keep it open if you don't mind.

[nico202]

Sure no problem, thanks a lot :)

[fuzzy-id]

This bug just hit me after an NixOS upgrade 17.09 -> 18.03. I updated the channel nix-channel --remove and nix-channel --add, did a nixos-rebuild switch --upgrade and booted into the new system. It seems to run fine, except:

[root@host01:~]# nixos-rebuild switch 
error: executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')
building Nix...
error: cannot open connection to remote store 'daemon': executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')
(use '--show-trace' to show detailed location information)
error: cannot open connection to remote store 'daemon': executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')
(use '--show-trace' to show detailed location information)
error: cannot open connection to remote store 'daemon': executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')
warning: don't know how to get latest Nix
building the system configuration...
error: cannot open connection to remote store 'daemon': executing SQLite statement 'alter table ValidPaths add column ultimate integer': SQL logic error (in '/nix/var/nix/db/db.sqlite')
(use '--show-trace' to show detailed location information)

Here the stuff asked from above:

# echo .schema ValidPaths | sqlite3 /tmp/db.sqlite               
CREATE TABLE ValidPaths (
    id               integer primary key autoincrement not null,
    path             text unique not null,
    hash             text not null,
    registrationTime integer not null,
    deriver          text,
    narSize          integer,
    ultimate         integer, -- null implies "false"
    sigs             text, -- space-separated
    ca               text -- if not null, an assertion that the path is content-addressed; see ValidPathInfo
);
CREATE TRIGGER DeleteSelfRefs before delete on ValidPaths
  begin
    delete from Refs where referrer = old.id and reference = old.id;
  end;
# cat /nix/var/nix/db/schema 
7

NOTE: Schema looks different than the one above and also different to another NixOS 18.03 I own. But the setting /nix/var/nix/db/schema to 10 saved the day for me.

[shlevy]

See #1956

[stale]

I marked this as stale due to inactivity. → More info

[fricklerhandwerk]

Related:

• Error: schema is corrupt #7064
• Improve durability of schema version file writes #7065