schema mysql - missing primary key

[valderaplets]

• Debian 12
• mariadb-server 1:10.11.14-0+deb12u2

mysql -h mybdd -u notifications -p notifications < /usr/share/icinga-notifications/schema/mysql/schema.sql
Enter password: 

--------------
CREATE TABLE incident_contact (
    incident_id bigint NOT NULL,
    contact_id bigint,
    contactgroup_id bigint,
    schedule_id bigint,
    role enum('recipient', 'subscriber', 'manager'),

    CONSTRAINT uk_incident_contact_incident_id_contact_id UNIQUE (incident_id, contact_id),
    CONSTRAINT uk_incident_contact_incident_id_contactgroup_id UNIQUE (incident_id, contactgroup_id),
    CONSTRAINT uk_incident_contact_incident_id_schedule_id UNIQUE (incident_id, schedule_id),

    CONSTRAINT ck_incident_contact_has_exactly_one_recipient CHECK (if(contact_id IS NULL, 0, 1) + if(contactgroup_id IS NULL, 0, 1) + if(schedule_id IS NULL, 0, 1) = 1),
    CONSTRAINT ck_incident_contact_role_notnull CHECK (role IS NOT NULL),
    CONSTRAINT fk_incident_contact_incident FOREIGN KEY (incident_id) REFERENCES incident(id),
    CONSTRAINT fk_incident_contact_contact FOREIGN KEY (contact_id) REFERENCES contact(id),
    CONSTRAINT fk_incident_contact_contactgroup FOREIGN KEY (contactgroup_id) REFERENCES contactgroup(id),
    CONSTRAINT fk_incident_contact_schedule FOREIGN KEY (schedule_id) REFERENCES schedule(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
--------------

ERROR 1173 (42000) at line 373: This table type requires a primary key 

Regards,

[oxzi]

Thanks for creating this issue.

Unfortunately, I was unable to reproduce this with a MariaDB server version 10.11.14, as shown below. I have also tried this for the schema at the current HEAD of the main branch.

$ git checkout v0.1.1
[...]
$ docker run --name mariadb -e MARIADB_ROOT_PASSWORD=insecure -d mariadb:10.11.14
[...]

$ docker exec -it mariadb mariadb --password=insecure -V
mariadb  Ver 15.1 Distrib 10.11.14-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

$ docker exec -i mariadb mariadb --password=insecure <<< 'create database notifications;'
$ docker exec -i mariadb mariadb --password=insecure notifications < schema/mysql/schema.sql
$ echo $?
0

$ docker exec -i mariadb mariadb --password=insecure notifications <<< 'describe incident_contact;'
Field   Type    Null    Key     Default Extra
incident_id     bigint(20)      NO      MUL     NULL
contact_id      bigint(20)      YES     MUL     NULL
contactgroup_id bigint(20)      YES     MUL     NULL
schedule_id     bigint(20)      YES     MUL     NULL
role    enum('recipient','subscriber','manager')        YES             NULL

Please provide us with some additional information about your setup.

• Is this a clean MariaDB installation or do you have some potentially altered configuration?
• Which Icinga Notification schema did you want to import? Is this an installation from our packages?
• Anything else you can think of?

Please also paste the output of the following two SQL queries.

show variables like 'innodb_force_primary_key';
show global variables like 'innodb_force_primary_key';

If the value is ON, could you verify if this is based on some configuration change made on your side?

Edit: I have just started a debian:12 container, installed mariadb-server and ran these two queries, both resulting in OFF for me. The database package version is 1:10.11.14-0+deb12u2.

[valderaplets]

Yes the value is ON for the two commands.
I just install two databases before :

• icingadb
• icingaweb

Which Icinga Notification schema did you want to import? Is this an installation from our packages?

yes from icinga-bookworm 0.1.1-1+debian12
md5sum schema : 3aaf880b22db73a10b21362e4152e83f

I have another palteform icinga, old but same installation and i have OFF to iinnodb_force_primary_key.

Can I change the value without any problems?

[valderaplets]

Oh okay, Got it

I have a galera cluster.. and galera fix innodb_force_primary_key = 1

I don't think I can change that value without losing my high availability. I can create a local database on my server, but can I modify any parameters in the notifications or table database?

[oxzi]

Thanks for clarifying this.

I have revived my MariaDB Galera test setup (three nodes) and checked the innodb_force_primary_key variable still defaults to OFF on this setup.

and galera fix innodb_force_primary_key = 1

Could you please explain what you mean with the "galera fix" part? Did you set this by yourself or did you follow some instructions?

I don't think I can change that value without losing my high availability.

Why do you think so? I could think of reasons, but I failed to find any source verifying those claims. Furthermore, my just created testing setup worked without this option being enabled.

After searching the web for innodb_force_primary_key w/ Galera, I found some presentations, howtos or texts, setting this key to ONE/1, but without any real explanation why they do so - such as https://archive.fosdem.org/2023/schedule/event/deploying_galera/attachments/paper/5835/export/events/attachments/deploying_galera/paper/5835/Deploying_Galera_Cluster_in_the_real_world.pdf, slide 17.

[valderaplets]

yes, in most installation documents, they ask you to set this value to 1.

So I have two options:

• Set the value to 0 and see what happens.

or

• Install MariaDB on one of my Icinga web servers (I have a high-availability architecture) juste for this module.

I haven't had any issues with the MySQL schemas for icingadb and icinagaweb.

Would it be possible to evolve the schema for icinga-notification?

[oxzi]

I have created #342, adding a primary key to this table. Fortunately, every other table already comes with a primary key.

Edit: This might not work as initially though. Please refer to the PR.

[valderaplets]

I added the line and everything seems to be working fine! No errors during import and service restart.

[valderaplets]

Could there be a connection with these errors I am encountering?

incident : failed to update incident severity
icinga2: cannot process event

or

incident: failed to upsert incident recipient
incinga2: cannot process event

?

[oxzi]

Sorry, this patch cannot work, as I stated in the edit I made ~25 minutes after creating the comment and wrote over at the PR.

[valderaplets]

with the PR and a fresh database :

2025-10-22T13:56:48+0200 daspiamsi351web03 icinga-notifications[186019]: Failed to load config from database Error 1054 (42S22): Unknown column 'icinga2_base_url' in 'SELECT'
2025-10-22T13:56:48+0200 daspiamsi351web03 icinga-notifications[186019]: runtime-updates: Cannot execute query to fetch incremental config updates
2025-10-22T13:49:56+0200 daspiamsi351web03 systemd[1]: Failed to start icinga-notifications.service - Icinga Notifications.
2025-10-22T13:49:56+0200 daspiamsi351web03 icinga-notifications[183512]: Failed to load config from database Error 1054 (42S22): Unknown column 'icinga2_auth_user' in 'SELECT'
2025-10-22T13:49:56+0200 daspiamsi351web03 icinga-notifications[183512]: runtime-updates: Cannot execute query to fetch incremental config updates
2025-10-22T13:49:46+0200 daspiamsi351web03 systemd[1]: Failed to start icinga-notifications.service - Icinga Notifications.
2025-10-22T13:49:46+0200 daspiamsi351web03 icinga-notifications[183351]: Failed to load config from database Error 1054 (42S22): Unknown column 'icinga2_base_url' in 'SELECT'
2025-10-22T13:49:46+0200 daspiamsi351web03 icinga-notifications[183351]: runtime-updates: Cannot execute query to fetch incremental config updates
2025-10-22T13:49:40+0200 daspiamsi351web03 systemd[1]: Failed to start icinga-notifications.service - Icinga Notifications.
2025-10-22T13:49:40+0200 daspiamsi351web03 icinga-notifications[183269]: Failed to load config from database Error 1054 (42S22): Unknown column 'icinga2_common_name' in 'SELECT'
2025-10-22T13:49:40+0200 daspiamsi351web03 icinga-notifications[183269]: runtime-updates: Cannot execute query to fetch incremental config updates
2025-10-22T13:49:30+0200 daspiamsi351web03 systemd[1]: Failed to start icinga-notifications.service - Icinga Notifications.
2025-10-22T13:49:30+0200 daspiamsi351web03 icinga-notifications[183184]: Failed to load config from database Error 1054 (42S22): Unknown column 'icinga2_auth_pass' in 'SELECT' 

i have to wait a new version of icinga-notification ?

[oxzi]

We are aware of this issue and will most likely add an unnecessary primary key to the table. However, for the moment please consider setting innodb_force_primary_key to OFF.