Mysql replication and service_state multiple unique indexes

[Yoda-BZH]

Describe the bug

When the database icingadb is replicated, when the table service_state is updated, the following error occurs:

Oct 26 17:08:46 servername mariadbd[154547]: 2022-10-26 17:08:46 187210 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO "service_state" ("check_source", "normalized_performance_data", "host_id", "last_update", "next_check", "last_state_change", "severity", "check_commandline", "in_downtime", "is_problem", "is_reachable", "long_output", "properties_checksum", "service_id", "last_comment_id", "scheduling_source", "hard_state", "is_acknowledged", "environment_id", "state_type", "id", "is_flapping", "next_update", "is_handled", "check_attempt", "latency", "performance_data", "previous_soft_state", "execution_time", "check_timeout", "output", "previous_hard_state", "soft_state", "acknowledgement_comment_id") VALUES ('my-hostname', [...]

To Reproduce

Provide a link to a live example, or an unambiguous set of steps to reproduce this bug. Include configuration, logs, etc. to reproduce, if relevant.

1. setup icingadb
2. add a mysql replication
3. check the logs

Expected behavior

No error should occur.

Your Environment

Include as many relevant details about the environment you experienced the problem in

• Icinga DB version: 1.0.0-1.bullseye
• IcingaDB redis: 7.0.5-1.bullseye
• Icinga 2 version: 2.13.5-1.bullseye
• Operating System and version: Debian 11
• Mariadb/Mysql version: 1:10.5.15-0+deb11u1

Additional context

Last week, the error occured 21160 times.

binlog_format is set to MIXED (default value)

I suppose the problem is at https://github.com/Icinga/icingadb/blob/master/schema/mysql/schema.sql#L494 . I don't know if removing the UNIQUE constraint fixes the problem nor has any other implication.

Thank you

[lippserd]

Hi,

Thanks for the report.

I don't know if removing the UNIQUE constraint fixes the problem nor has any other implication.

It would fix the problem. Though it might impact JOIN performance.

Since MySQL 8, row-based replication is the default, if I'm not mistaken. Have you considered changing it?

All the best,
Eric

[Yoda-BZH]

Hello,

I didn't considered changing it. I don't know if

• every database is compatible with binlog_format = ROW (icinga2, icingadirector, icingaweb2, icingadb, icinga_reporting, grafana),
• mariadb 10.5 (provided by debian) is compatible and will work well

[Al2Klimov]

I don't know if removing the UNIQUE constraint fixes the problem nor has any other implication.

It would fix the problem. Though it might impact JOIN performance.

Suggestion

I don't know all the Icinga DB Web queries, but I guess it doesn't need both TYPE_state#id and TYPE_state#TYPE_id to be unique for optimal join performance. Not to change any Web queries we could even make TYPE_state#TYPE_id the primary key and the id non-unique. The values are the same anyway.

Or! Web could make TYPE_id just an alias of id in its ORM. And we can deUNIQUEify TYPE_id. What do you think?

[Al2Klimov]

Web could make TYPE_id just an alias of id in its ORM. And we can deUNIQUEify TYPE_id. What do you think?

@nilmerg Is this reasonable?

[nilmerg]

I'd say yes. Though, an alias isn't even necessary.

[Al2Klimov]

Please explain. Would one re-write existing joins instead? Or aren't there such? Or (why) do the JOIN performance problems Eric mentioned above not exist?

[nilmerg]

The join to host_state and service_state are always made from host or service respectively. Using the primary key as foreign key in this case might sound weird, but works fine.