hydra-init.service fails with: function gen_random_uuid() does not exist

[knedlsepp]

Describe the bug I did upgrade my CI box from NixOS-21.11 to NixOS 22.05 beta with a hydra service based on the package = pkgs.hydra-unstable; and get the following error:

May 26 21:17:01 myhydrabox hydra-init[3334551]: upgrading Hydra schema from version 76 to 77
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: create table TaskRetries (
May 26 21:17:01 myhydrabox hydra-init[3334551]:     id            serial primary key not null,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     channel       text not null,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     pluginname    text not null,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     payload       text not null,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     attempts      integer not null,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     retry_at      integer not null
May 26 21:17:01 myhydrabox hydra-init[3334551]: )
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: create index IndexTaskRetriesOrdered on TaskRetries(retry_at asc)
May 26 21:17:01 myhydrabox hydra-init[3334551]: upgrading Hydra schema from version 77 to 78
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: DROP TABLE SystemTypes
May 26 21:17:01 myhydrabox hydra-init[3334551]: upgrading Hydra schema from version 78 to 79
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: create table RunCommandLogs (
May 26 21:17:01 myhydrabox hydra-init[3334551]:     id            serial primary key not null,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     job_matcher   text not null,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     build_id      integer not null,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     
May 26 21:17:01 myhydrabox hydra-init[3334551]:     
May 26 21:17:01 myhydrabox hydra-init[3334551]:     
May 26 21:17:01 myhydrabox hydra-init[3334551]:     command         text not null,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     start_time      integer,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     end_time        integer,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     error_number    integer,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     exit_code       integer,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     signal          integer,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     core_dumped     boolean,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     foreign key (build_id) references Builds(id) on delete cascade,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     
May 26 21:17:01 myhydrabox hydra-init[3334551]:     constraint RunCommandLogs_not_started_no_exit_time_no_code check (
May 26 21:17:01 myhydrabox hydra-init[3334551]:         
May 26 21:17:01 myhydrabox hydra-init[3334551]:         
May 26 21:17:01 myhydrabox hydra-init[3334551]:         (start_time is not null) or (
May 26 21:17:01 myhydrabox hydra-init[3334551]:             end_time is null
May 26 21:17:01 myhydrabox hydra-init[3334551]:             and error_number is null
May 26 21:17:01 myhydrabox hydra-init[3334551]:             and exit_code is null
May 26 21:17:01 myhydrabox hydra-init[3334551]:             and signal is null
May 26 21:17:01 myhydrabox hydra-init[3334551]:             and core_dumped is null
May 26 21:17:01 myhydrabox hydra-init[3334551]:         )
May 26 21:17:01 myhydrabox hydra-init[3334551]:     ),
May 26 21:17:01 myhydrabox hydra-init[3334551]:     constraint RunCommandLogs_end_time_has_start_time check (
May 26 21:17:01 myhydrabox hydra-init[3334551]:         
May 26 21:17:01 myhydrabox hydra-init[3334551]:         (end_time is null) or (start_time is not null)
May 26 21:17:01 myhydrabox hydra-init[3334551]:     )
May 26 21:17:01 myhydrabox hydra-init[3334551]:     
May 26 21:17:01 myhydrabox hydra-init[3334551]:     
May 26 21:17:01 myhydrabox hydra-init[3334551]:     
May 26 21:17:01 myhydrabox hydra-init[3334551]:     
May 26 21:17:01 myhydrabox hydra-init[3334551]: )
May 26 21:17:01 myhydrabox hydra-init[3334551]: upgrading Hydra schema from version 79 to 80
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: drop index IndexBuildsOnJobsetIsCurrent
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: drop index IndexBuildsOnJobIsCurrent
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: drop index IndexBuildsOnJobset
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: drop index IndexBuildsOnProject
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: drop index IndexBuildsOnJobFinishedId
May 26 21:17:01 myhydrabox hydra-init[3334551]: executing SQL statement: alter table Builds
May 26 21:17:01 myhydrabox hydra-init[3334551]:     drop column project,
May 26 21:17:01 myhydrabox hydra-init[3334551]:     drop column jobset
May 26 21:17:02 myhydrabox hydra-init[3334551]: upgrading Hydra schema from version 80 to 81
May 26 21:17:02 myhydrabox hydra-init[3334551]: executing SQL statement: alter table runcommandlogs add column uuid uuid
May 26 21:17:02 myhydrabox hydra-init[3334551]: executing SQL statement: update runcommandlogs set uuid = gen_random_uuid() where uuid is null
May 26 21:17:02 myhydrabox hydra-init[3334551]: schema upgrade failed: main::run_(): DBI Exception: DBD::Pg::db do failed: ERROR:  function gen_random_uuid() does not exist
May 26 21:17:02 myhydrabox hydra-init[3334551]: LINE 1: update runcommandlogs set uuid = gen_random_uuid() where uui...
May 26 21:17:02 myhydrabox hydra-init[3334551]:                                          ^
May 26 21:17:02 myhydrabox hydra-init[3334551]: HINT:  No function matches the given name and argument types. You might need to add explicit type casts. at /nix/store/wfys8j4qmr20kbvkl5sk59bjymfca12n-hydra-2022-05-03/bin/.hydra-init-wrapped line 70

Hydra Server:

• OS and version: NixOS 22.05.20220527.e7dbbc7
• Version of Hydra: pkgs.hydra-unstable
• Version of Nix Hydra is built against: Default
• Version of the Nix daemon : Default

[knedlsepp]

To imperatively get me out of this I did now run

echo "create extension if not exists pgcrypto" | runuser -u postgres -- psql hydra

It seems to me that maybe we need something similar to https://github.com/NixOS/hydra/commit/8a0a5ec3a3200d4f4d4d38f87d0afdb49f092b39

[basile-henry]

I have hit the same issue with NixOS 22.05 (not beta)

[basile-henry]

And I can confirm that the imperative method suggested worked for me as well, thanks :smile:

[lheckemann]

The alternative is to use a recent postgresql (as in the nixos test for hydra), which contains this function without requiring an extension:

{ pkgs, ... }: {
  services.postgresql.package = pkgs.postgresql_14;
}

[knedlsepp]

The alternative is to use a recent postgresql (as in the nixos test for hydra)

@lheckemann Will postgres automatically transform its state such that it's compatible with the new version on service restart?

[lheckemann]

See https://nixos.org/manual/nixos/stable/index.html#module-services-postgres-upgrading