Icingadb apt upgrade schema error

ment4field commented 1 year ago

Describe the bug

Upgrading from v.1.1.0 to 1.1.1 leads to the following error in the webinterface:

It seems that Icinga DB is not running. Make sure Icinga DB is running and writing into the database.

icingadb service status:

× icingadb.service - Icinga DB
     Loaded: loaded (/lib/systemd/system/icingadb.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Mon 2023-08-14 15:29:11 CEST; 10min ago
    Process: 4893 ExecStart=/usr/sbin/icingadb --config /etc/icingadb/config.yml (code=exited, status=1/FAILURE)
   Main PID: 4893 (code=exited, status=1/FAILURE)
        CPU: 32ms

Aug 14 15:29:11 Ubuntu-Icinga2-V1 systemd[1]: Starting Icinga DB...
Aug 14 15:29:11 Ubuntu-Icinga2-V1 icingadb[4893]: Starting Icinga DB
Aug 14 15:29:11 Ubuntu-Icinga2-V1 icingadb[4893]: Connecting to database at 'localhost:0'
Aug 14 15:29:11 Ubuntu-Icinga2-V1 systemd[1]: Started Icinga DB.
Aug 14 15:29:11 Ubuntu-Icinga2-V1 icingadb[4893]: unexpected database schema version: v3 (expected v4), please make sure you h>
Aug 14 15:29:11 Ubuntu-Icinga2-V1 systemd[1]: icingadb.service: Main process exited, code=exited, status=1/FAILURE
Aug 14 15:29:11 Ubuntu-Icinga2-V1 systemd[1]: icingadb.service: Failed with result 'exit-code'.

To Reproduce

install icinga2 using the webguide 4 days ago (mariadb).
Import schema.sql in mariadb as per guide
check icingadb package version (1.1.0)
apt update
apt upgrade

Expected behavior

Running database without error or information on package upgrade in terminal/webinterface to the database schema upgrade guide or automatic schema upgrade

Your Environment

Icinga DB version: 1.1.1-1+ubuntu22.04 (upgraded from 1.1.0)
Icinga 2 version: 2.14.0-1+ubuntu22.04
Operating System and version: ubuntu22.04

julianbrost commented 1 year ago

The full message (your systemctl status output only shows a truncated line) is:

unexpected database schema version: v3 (expected v4), please make sure you have applied all database migrations after upgrading Icinga DB

Also, the release announcement and the upgrading documentation mention that a schema upgrade has to be applied.

Artim96 commented 3 months ago

Also, the release announcement and the upgrading documentation mention that a schema upgrade has to be applied.

How do you do apply it? I have the databases in PostgreSQL, just applying it with psql -d icingadb -a -f /usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql results in

UPDATE icingadb_schema SET timestamp = UNIX_TIMESTAMP(timestamp / 1000) * 1000 WHERE timestamp > 20000000000000000;
psql:/usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql:1: ERROR: function unix_timestamp(bigint) does not exist
LINE 1: UPDATE icingadb_schema SET timestamp = UNIX_TIMESTAMP(timest...
                                               ^
HINT: No function matches the specified name and argument types. You may need to add explicit type conversions.
ALTER TABLE history ADD INDEX idx_history_event_time_event_type (event_time, event_type) COMMENT 'History filtered/ordered by event_time/event_type';
psql:/usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql:3: ERROR: Syntax error at “COMMENT”
LINE 1: ...ry_event_time_event_type (event_time, event_type) COMMENT 'H...
                                                             ^
ALTER TABLE history DROP INDEX idx_history_event_time;
psql:/usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql:4: ERROR: Syntax error for “idx_history_event_time”
LINE 1: ALTER TABLE history DROP INDEX idx_history_event_time;
                                       ^
ALTER TABLE host_state MODIFY COLUMN check_attempt int unsigned NOT NULL;
psql:/usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql:6: ERROR: Syntax error for “MODIFY”
LINE 1: ALTER TABLE host_state MODIFY COLUMN check_attempt int unsig...
                               ^
ALTER TABLE service_state MODIFY COLUMN check_attempt int unsigned NOT NULL;
psql:/usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql:8: ERROR: Syntax error for “MODIFY”
LINE 1: ALTER TABLE service_state MODIFY COLUMN check_attempt int un...
                                  ^
ALTER TABLE state_history MODIFY COLUMN check_attempt tinyint unsigned NOT NULL COMMENT 'optional schema upgrade not applied yet, see https://icinga.com/docs/icinga-db/latest/doc/04-Upgrading/#upgrading-to-icinga-db-v112';
psql:/usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql:10: ERROR: Syntax error for “MODIFY”
LINE 1: ALTER TABLE state_history MODIFY COLUMN check_attempt tinyin...
                                  ^
INSERT INTO icingadb_schema (version, timestamp)
  VALUES (5, UNIX_TIMESTAMP() * 1000);
psql:/usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql:13: ERROR: Function unix_timestamp() does not exist
LINE 2: VALUES (5, UNIX_TIMESTAMP() * 1000);
                     ^
HINT: No function matches the specified name and argument types. You may need to add explicit type conversions.

Applying the optional update also isn't the solution, as it answers with this error:

ALTER TABLE state_history MODIFY COLUMN check_attempt int unsigned NOT NULL;
psql:/usr/share/icingadb/schema/mysql/upgrades/optional/1.2.0-history.sql:1: FEHLER: Syntaxfehler bei „MODIFY“
ZEILE 1: ALTER TABLE state_history MODIFY COLUMN check_attempt int un...

oxzi commented 3 months ago

How do you do apply it? I have the databases in PostgreSQL, just applying it with psql -d icingadb -a -f /usr/share/icingadb/schema/mysql/upgrades/1.2.0.sql results in

Please take a look at the path of the schema upgrade. It seems like you want to apply the mysql schema upgrade for your PsotgreSQL database.

For future problems, please create a new issue and don't reuse an old one.

Artim96 commented 3 months ago

Sorry about that, it's just too warm to think...

Icinga / icingadb