IcingaDB Health IcingaHealth

ShowMeYourSkil · July 21, 2025, 5:21pm

Hello everyone,

I updated my Icinga2 environment today. Since then I have the problem that the IcingaDB no longer works.

Status: Icinga DB is outdated, please upgrade to version 1.4.0 or later.

However, the output on the icingadb command tells me that I am already on the current version. I have also installed the latest SQL schema update.

 systemctl status icingadb
× icingadb.service - Icinga DB
     Loaded: loaded (/lib/systemd/system/icingadb.service; enabled; preset: enabled)
     Active: failed (Result: exit-code) since Mon 2025-07-21 15:26:48 CEST; 3h 53min ago
   Duration: 5ms
    Process: 214553 ExecStart=/usr/sbin/icingadb --config /etc/icingadb/config.yml (code=exited, status=1/FAILURE)
   Main PID: 214553 (code=exited, status=1/FAILURE)
        CPU: 11ms
Jul 21 15:26:48 monitoring systemd[1]: Starting icingadb.service - Icinga DB...
Jul 21 15:26:48 monitoring icingadb[214553]: Starting Icinga DB daemon (v1.4.0)
Jul 21 15:26:48 monitoring systemd[1]: Started icingadb.service - Icinga DB.
Jul 21 15:26:48 monitoring icingadb[214553]: Connecting to database at 'mysql://icingadb@IPv4:3306/icingadb'
Jul 21 15:26:48 monitoring icingadb[214553]: unexpected database schema version: v6 (expected v7), please make sure you have applied all database migrations after upgrading Icinga DB
Jul 21 15:26:48 monitoring systemd[1]: icingadb.service: Main process exited, code=exited, status=1/FAILURE
Jul 21 15:26:48 monitoring systemd[1]: icingadb.service: Failed with result 'exit-code'.

2025-07-21T19:14:17.130+0200 INFO icingadb Starting Icinga DB daemon (v1.4.0)

2025-07-21T19:14:17.130+0200 INFO icingadb Connecting to database at 'mysql://icingadb@172.16.100.80:3306/icingadb'

2025-07-21T19:14:17.134+0200 FATAL icingadb unexpected database schema version: v6 (expected v7), please make sure you have applied all database migrations after upgrading Icinga DB

Do we have a known bug here or do any of you have an idea before I restore my snapshot

Icinga DB Web version (System - About): 1.2.2
Icinga Web 2 version (System - About): 2.12.5
Web browser: Microsoft Edge, Google Chrome
Icinga 2 version (icinga2 --version): r2.15.0-1
Icinga DB version (icingadb --version): v1.4.0
PHP version used (php --version): 8.2.29
Server operating system and version: Debian 12 (Bookworm)

moreamazingnick · July 21, 2025, 6:16pm

David Franzen:

2025-07-21T19:14:17.134+0200 FATAL icingadb unexpected database schema version: v6 (expected v7), please make sure you have applied all database migrations after upgrading Icinga DB

this says that you did not apply all the migrations:

you have schema 6:

github.com/Icinga/icingadb

schema/mysql/upgrades/1.2.1.sql

ab9aba83c


      
          ALTER TABLE eventcommand_argument MODIFY COLUMN argument_key varchar(255) NOT NULL;
          ALTER TABLE eventcommand_argument MODIFY COLUMN argument_key_override varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
          ALTER TABLE notificationcommand_argument MODIFY COLUMN argument_key varchar(255) NOT NULL;
          ALTER TABLE notificationcommand_argument MODIFY COLUMN argument_key_override varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
          
          ALTER TABLE checkcommand_envvar MODIFY COLUMN envvar_key varchar(255) NOT NULL;
          ALTER TABLE eventcommand_envvar MODIFY COLUMN envvar_key varchar(255) NOT NULL;
          ALTER TABLE notificationcommand_envvar MODIFY COLUMN envvar_key varchar(255) NOT NULL;
          
          INSERT INTO icingadb_schema (version, timestamp)
            VALUES (6, UNIX_TIMESTAMP() * 1000);

and the file 1.4.0 has schema 7:

github.com/Icinga/icingadb

schema/mysql/upgrades/1.4.0.sql

ab9aba83c


      
            display_name text NOT NULL,
          
            CONSTRAINT pk_dependency_edge PRIMARY KEY (id),
          
            UNIQUE INDEX idx_dependency_edge_from_node_to_node_id (from_node_id, to_node_id)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
          
          ALTER TABLE icingadb_instance ADD COLUMN icingadb_version varchar(255) NOT NULL DEFAULT 'unknown' AFTER icinga2_performance_data_enabled;
          ALTER TABLE icingadb_instance MODIFY COLUMN icingadb_version varchar(255) NOT NULL;
          
          INSERT INTO icingadb_schema (version, timestamp)
            VALUES (7, UNIX_TIMESTAMP() * 1000);

ShowMeYourSkil · July 21, 2025, 8:08pm

Hi,

I updated schema 1.4.0.sql using the command mysql -u icingadb -p icingadb <1.4.0.sql. As I see it, I have imported the expected schema after all. I ran the command again to be sure and I get the following message.

Enter password:
--------------
ALTER TABLE host ADD COLUMN total_children int unsigned DEFAULT NULL AFTER check_retry_interval
--------------

ERROR 1060 (42S21) at line 1: Duplicate column name 'total_children'

moreamazingnick · July 22, 2025, 2:03am

These mysql statements are not idempotent so you can’t apply them twice.

What is the content of the table “icingadb_schema”?

ShowMeYourSkil · July 22, 2025, 5:44am

Hi, I hope this helps you. I am currently checking the database files as the schema has definitely been updated.

MariaDB [icingadb]> SELECT * FROM icingadb_schema;
+----+---------+---------------+
| id | version | timestamp     |
+----+---------+---------------+
|  1 |       3 | 1672298914000 |
|  2 |       4 | 1694762696000 |
|  3 |       5 | 1713527524000 |
|  4 |       7 | 1744916671000 |
|  5 |       6 | 1745304889000 |
|  6 |       6 | 1753104098000 |
+----+---------+---------------+
6 rows in set (0.002 sec)

MariaDB [icingadb]> DESCRIBE icingadb_schema;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id        | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| version   | smallint(5) unsigned | NO   |     | NULL    |                |
| timestamp | bigint(20) unsigned  | NO   |     | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.002 sec)

MariaDB [icingadb]> SHOW TABLES LIKE 'icingadb_schema';
+--------------------------------------+
| Tables_in_icingadb (icingadb_schema) |
+--------------------------------------+
| icingadb_schema                      |
+--------------------------------------+
1 row in set (0.001 sec)

MariaDB [icingadb]> SHOW COLUMNS FROM icingadb_schema;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id        | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| version   | smallint(5) unsigned | NO   |     | NULL    |                |
| timestamp | bigint(20) unsigned  | NO   |     | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)


MariaDB [icingadb]> SHOW CREATE TABLE icingadb_schema\G
*************************** 1. row ***************************
       Table: icingadb_schema
Create Table: CREATE TABLE `icingadb_schema` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `version` smallint(5) unsigned NOT NULL,
  `timestamp` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.000 sec)

moreamazingnick · July 22, 2025, 7:13am

I think you imported the migrations in the wrong order.

update icingadb_schema set version = 6 where id = 4;
update icingadb_schema set version = 7 where id = 5;
delete from icingadb_schema where id = 6;

restart icingadb

apenning · July 22, 2025, 7:14am

Why do you suspect that the scheme upgrade was not correctly performed?

David Franzen:

MariaDB [icingadb]> SELECT * FROM icingadb_schema;
+----+---------+---------------+
| id | version | timestamp     |
+----+---------+---------------+
|  1 |       3 | 1672298914000 |
|  2 |       4 | 1694762696000 |
|  3 |       5 | 1713527524000 |
|  4 |       7 | 1744916671000 |
|  5 |       6 | 1745304889000 |
|  6 |       6 | 1753104098000 |
+----+---------+---------------+
6 rows in set (0.002 sec)

Looking at your icingadb_schema table, you have first updated from MySQL schema version 5 to 7 (Icinga DB version 1.2.0 and 1.4.0, respectively) and have skipped schema version 6 (Icinga DB 1.2.1). Then, you have applied schema version 6 twice. However, there are some time gaps between updates:

schema version 5 on Fri Apr 19 11:52:04 AM UTC 2024
schema version 7 on Thu Apr 17 07:04:31 PM UTC 2025
schema version 6 on Tue Apr 22 06:54:49 AM UTC 2025
schema version 6 on Mon Jul 21 01:21:38 PM UTC 2025

Since the current Icinga DB check logic is quite simple, it selects the schema version with the greatest id, which would be 6 in your case. You can try to quick fix this by updating the version value of the column where id is 6.

Afterwards, please try to restart Icinga DB and check if it logs any errors. If not, you are lucky. Otherwise, either try to address them accordingly or just import your latest backup.

In general, upgrading is an iterative approach where one upgrade needs to be performed after the other, as described at the beginning of our upgrading documentation.

For future updates, please take a look at the changelog or latest release information. If manual intervention becomes necessary, it is stated there.

apenning · July 22, 2025, 11:01am

Wait a moment. Icinga DB version 1.4.0 introduced the schema version 7, but was released on Jun 18. Either you have applied a development schema version or you have manually altered the icingadb_schema table and it is now a bit messed up.

I would repeat my previous suggestion to roll back to a backup.

If you have none, consider how important the Icinga DB history is for you. If it does not matter, just purge the database and start with a clean schema import. Otherwise, backup the history tables (and everything else), purge the database, import the empty schema and then just reimport the history tables from your just created backup.

ShowMeYourSkil · August 8, 2025, 10:42am

Hi,

Your solution worked, and ICINGADB has restarted. However, I have another problem related to IPL. I have already updated IPL to the latest version. Any ideas?

moreamazingnick · August 8, 2025, 2:20pm

you should recreate the icingadb database because some migration steps are not done correctly.

alternatively you can check each migration manually and check if the changes have been applied.
if there is an add column your column should exist in the database’s table

apenning · August 18, 2025, 6:26am

The Icinga DB Web error complains about the missing is_sticky_acknowledgement column in the service_state table. It was introduced in version 1.4.0.

As both @moreamazingnick and myself already wrote, starting from a blank new schema would be the easiest way to go unless you have an untempered database backup you can restore and apply the missing schema upgrades upon. Based on my earlier response, this backup should have been created before Thu Apr 17 07:04:31 PM UTC 2025.