Schema Migration from v1.10.2 to v1.11.0 fails

[agougo]

Expected Behavior

Migrate Icinga2 Director from v1.10.2 to v1.11.0 - Migrate PostgreSQL schema (Apply 4 pending changes)

Current Behavior

Error:

Migration 186 failed (SQLSTATE[42883]: Undefined function: 7 ERROR: function gen_random_uuid() does not exist LINE 1: ...DATE director_datafield SET uuid = decode(replace(gen_random... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.) while running UPDATE director_datafield SET uuid = decode(replace(gen_random_uuid()::text, '-', ''), 'hex') WHERE uuid IS NULL

Possible Solution

No idea

Steps to Reproduce (for bugs)

/ Update incubator to version 0.20.0 / Update Icinga2 Director from v.1.9.1 to v1.10.2 / Apply schema migration from the GUI / Update Icinga2 Director from v1.10.2 to v1.11.0 / Apply schema migration from the GUI

Your Environment

• Director version (System - About): 1.10.2
• Icinga Web 2 version and modules (System - About): 2.12
• Icinga 2 version (icinga2 --version): 2.14.0
• Operating System and version: Oracle Linux 8.5
• Webserver, PHP versions: Apache/2.4.37 (Oracle Linux) - PHP 8.0.27
• (PostgreSQL) 10.23

[Thomas-Gelf]

You're probably missing the pgcrypto extension, as shown in our installation documentation. Please "create" the extension and trigger the migration once again.

[agougo]

We already checked that and it is not the case:

[root@s-07-linux ~]# su - postgres -c "psql -U postgres -d postgres -c \"CREATE EXTENSION pgcrypto;\"" ERROR: extension "pgcrypto" already exists

[sol1-matt]

We've had the same error message and our pgcrypto already exists as well

We starting on director 1.9.0 which was installed via git.

We then tried upgrading to director 1.11.1 using package management which completed without errors.

We get the same error message when trying to run the post upgrade schema update.

[sol1-matt]

note we are using postgresql Our Solution: This problem was because uuid-ossp was available but not installed.

After installing uuid-ossp we then needed to run CREATE EXTENSION pgcrypto; again as well as it wasn't showing up in \dx.

One final note is that the migrations were now in a invalid state as they had partially run and so would not run again. ALTER TABLE director_datafield ADD COLUMN uuid bytea UNIQUE CHECK(LENGTH(uuid) = 16); wouldn't run as the column already existed from the first failed run.

This is resolved by manually running the remaining commands in the migration script .

@Thomas-Gelf Invalid migration states could be avoided in postgresql by using transactions, BEGIN; .... COMMIT; rolls back on errors, for each upgrade file.