Replication failure MariaDB Icinga Master

Hello,

we’ve got a problem with our icinga master and the db. We use MariaDB (10.3.34-MariaDB-0+deb10u1-log) and we set up a master-slave in a two cluster.
About Icinga Web 2:
|Icinga Web 2 Version |2.10.0|
|PHP-Version|7.3.31-1~deb10u1|
|Git Commit Datum|2022-03-23|

The MariaDB replication failed almost every day.

2023-06-26T14:09:24 mysqld-relay-bin.000073 23484837 1032 Could not execute Delete_rows_v1 event on table icinga2.icinga_runtimevariables; Can’t find record in ‘icinga_runtimevariables’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.006862, end_log_pos 23484933

Everytime this error and its about the the table ‘icinga_runtimevariables’:

runtimevariable_id | instance_id | varname | varvalue | endpoint_object_id |
±-------------------±------------±-------------------------±---------±-------------------+
| 39810761 | 1 | total_services | 5901 | NULL |
| 39810762 | 1 | total_scheduled_services | 5901 | NULL |
| 39810763 | 1 | total_hosts | 318 | NULL |
| 39810764 | 1 | total_scheduled_hosts | 318 | NULL |
±-------------------±------------±-------------------------±---------±-------------------+

Our workaround is to use this command:
pt-slave-restart -v

If it doesn’t work, we copy the databse from one server to another.

We don’t see where exactly the problem is.
Can anyone help?

Thanks a lot.

Are there 2 Nodes in the master zone?

If yes, are both connecting to the MariaDB master?

If no, is ha enabled for the DB or where does HA_ERR_KEY_NOT_FOUND coming from?

Hi Dominik,

yes, there are two nodes in the master zone, master-master.
How can I check the ha status? I don’t know where HA_ERR_KEY_NOT_FOUND is coming from.

Run the following command on both master nodes:

grep enable_ha /etc/icinga2/features-enabled/ido-mysql.conf

Do both connect to the master:
grep host /etc/icinga2/features-enabled/ido-mysql.conf

Both are connected to the ip for “vIP for keepalived”.

What is keepalived?

If you don’t use MariaDB Master ↔ Master, you must make sure to never try to write to the other node (slave).

a virtual ip, loadbalancing

we use a master-master replication

Object ‘ido-mysql’ of type ‘IdoMysqlConnection’:
% declared in ‘/etc/icinga2/features-enabled/ido-mysql.conf’, lines 5:1-5:37

  • __name = “ido-mysql”
  • categories = [ “DbCatConfig”, “DbCatState”, “DbCatAcknowledgement”, “DbCatComment”, “DbCatDowntime”, “DbCatEventHandler”, “DbCatFlapping”, “DbCatNotification”, “DbCatProgramStatus”, “DbCatRetention”, “DbCatStateHistory” ]
  • cleanup
  • database = “icinga2”
    % = modified in ‘/etc/icinga2/features-enabled/ido-mysql.conf’, lines 9:3-9:22
  • enable_ha = true
  • enable_ssl = false
    % = modified in ‘/etc/icinga2/features-enabled/ido-mysql.conf’, lines 11:3-11:20
  • failover_timeout = 60

It looks like it’s from MariaDB: https://dba.stackexchange.com/questions/59935/mysql-row-based-replication-ha-err-key-not-found-which-record-is-it

Maybe you will have to follow the instructions in the link to find out, what’s really going on.