Migratred my MySQL install to InnoDB Cluster. Now Zabbix will not start.

[elyograg]

I got my database migrated to InnoDB Cluster. The DB version is 8.0.33-1ubuntu22.04 installed from the official mysql APT repository.

Attached is what Zabbix logs when I try to start it. The external plugin that has been added is group_replication ... the central capability that makes InnoDB Cluster possible.

How do I need to adjust the zabbix database so it works again? Zabbix version is 6.4.2-1+ubuntu22.04.

[elyograg]

Unfortunately I can't get mysql to tell me what the problem is with the table structure. I am taking a wild guess that it might be the foreign key constraints, but I do not know enough about foreign keys to even make a guess about what might be wrong. And it might be something else entirely. Here is the definition of one of the tables it complains about:

| trigger_discovery | CREATE TABLE `trigger_discovery` (
`triggerid` bigint unsigned NOT NULL,
`parent_triggerid` bigint unsigned NOT NULL,
`lastcheck` int NOT NULL DEFAULT '0',
`ts_delete` int NOT NULL DEFAULT '0',
PRIMARY KEY (`triggerid`),
KEY `trigger_discovery_1` (`parent_triggerid`),
CONSTRAINT `c_trigger_discovery_1` FOREIGN KEY (`triggerid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE,
CONSTRAINT `c_trigger_discovery_2` FOREIGN KEY (`parent_triggerid`) REFERENCES `triggers` (`triggerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |

[elyograg]

Poking around, I found this as one of the limitations for group_replication:

Foreign Keys with Cascading Constraints

One of the foreign keys on that table definition above does do a delete cascade.

I am fairly sure that quite a lot of databases rely heavily on foreign keys that cascade deletes. It's a convenient and battle-tested way of controlling the overall size of databases that get huge.

There are a number of options that can replace this functionality, but I am worried that if I try to implement any of them that I will run into problems when a zabbix upgrade tries to modify the database structure.

[elyograg]

Clarification: The service not starting was actually caused by something else. The remaining issue does not keep it from starting, just keeps it from updating its database properly.

[wangjun]

Hi,elyograg.

Did you succeed？How do you create the innodb cluster？I have such a problem. 'Table conditions has a foreign key with 'CASCADE', 'SET NULL' or 'SET DEFAULT' clause. This is not compatible with Group Replication.' Zabbix version is 6.0.The DB version is 8.0.33.

[elyograg]

wangjun My solution was to put zabbix on a dedicated VM using libvirtd with its own mysql install. The zabbix database is simply not compatible with Innodb cluster. because it has foreign keys with cascade.