Zabbix 2.0 Foreign Keys not supported with MySQL partitioning

[ke_sheng_jie]

Are foreign keys supported in MySQL partitioning at all? I'm having the following problem when I run the patch.sql script to upgrade my database from 1.8.10.

$ time mysql -u root -p zabbix2 --verbose < patch.sql
Enter password:
--------------
ALTER TABLE acknowledges MODIFY acknowledgeid bigint unsigned NOT NULL,
MODIFY userid bigint unsigned NOT NULL,
MODIFY eventid bigint unsigned NOT NULL
--------------

--------------
DELETE FROM acknowledges WHERE NOT userid IN (SELECT userid FROM users)
--------------

--------------
DELETE FROM acknowledges WHERE NOT eventid IN (SELECT eventid FROM events)
--------------

--------------
ALTER TABLE acknowledges ADD CONSTRAINT c_acknowledges_1 FOREIGN KEY (userid) REFERENCES users (userid) ON DELETE CASCADE
--------------

ERROR 1506 (HY000) at line 6: Foreign key clause is not yet supported in conjunction with partitioning

real 0m12.286s
user 0m0.003s
sys 0m0.005s


It looked like there was another post started on this in Russian. (http://www.zabbix.com/forum/showthread.php?t=26321)

[fixinko]

Are foreign keys supported in MySQL partitioning at all?

http://dev.mysql.com/doc/refman/5.5/...mitations.html

Foreign keys not supported with MySQL Partitioning...

[nms_user]

So, can anybody give a hint/solution for partitioning and upgrading to Zabbix 2 for now?

As many big installations use partitioning because of performance, there must be a trick to solve this, or am I wrong?

[parcival]

Any news here ?
Need also a solution with this issues.

thx for zabbix.

[Mizukusai]

Hello, this is Captain Obvious.

The things are :

• Partitionning and foreign keys don't work in MySQL, currently.
  and
• Zabbix 2 uses foreign keys in MySQL

Solutions are :

• do not migrate to zabbix 2
  or
• Do not use MySQL. Try PGSQL or Oracle instead. There is no tool to migrate to another rdbms.
  or
• do not partition tables

[hwidjaja]

Just wondering, does anyone know the roadmap if Zabbix 2 would support MySQL partitioning in the future? by removing the foreign keys?

[Mizukusai]

Hi,

1st of all, know that I'm not from Zabbix SIA, and this is only my point of view. But, if nobody from Zabbix SIA answers, you have an awser nonetheless.

Removing foreign keys will force Zabbix to add features in the code to handles the "cascade" stuff. Zabbix choose to let the DB handle that stuff.

It's a pity that MySQL does not handle partitions AND foreign keys. but the limitation is on MySQL's side, not Zabbix's.

You should ask MySQL "when will you support foreign keys on partitionned tables ?"

I know, it does not help. Maybe MariaDB does better than MySQL.

[hwidjaja]

Thanks Mizukusai for your view.

I checked latest MySQL, it still doesn't support partitioning with Foreign Keys. I hope MariaDB will support it soon. Or, I'll move to PgSQL.

[Mizukusai]

Or, I'll move to PgSQL.

Good choice.
The question is "how to keep your history and data."
Config, hosts, and stuff can be exported/imported but what about data ?

[ke_sheng_jie]

Remove Partitioning

So, can anybody give a hint/solution for partitioning and upgrading to Zabbix 2 for now?

As many big installations use partitioning because of performance, there must be a trick to solve this, or am I wrong?

We just migrated back to a non-partitioned MySQL database. It was a pain, but we really wanted to get on 2.0.

[hwidjaja]

Good choice.
The question is "how to keep your history and data."
Config, hosts, and stuff can be exported/imported but what about data ?

Luckily, I just deployed Zabbix servers for monitoring around 1000 hosts. The housekeeper process is really impacting the performance.

It's still under trial so migrating history is not that important for me. I'll have a fresh new data. I have configured auto-registration rules and a few scripts calling Zabbix API to automate the configuration, so it will be quite okay to just migrate the DB.

Of course, it would be great if I could use partitioned tables with MySQL/MariaDB.

[Yello]

Hi,
What about some ideas...

Spider engine anyone? Not used it but I might have a play with it cos it looks like it could be useful for bigger zabbix deployments.


Regards,
David

[Mox]

I think the only way for MySQL and Zabbix 2.0 is just removing the foreign keys. Nothing bad will happen in this case because partitioning will take this functionality.

[Mox]

I think the only way for MySQL and Zabbix 2.0 is just removing the foreign keys. Nothing bad will happen in this case because partitioning will take this functionality.

Like

# diff -u /usr/local/share/zabbix2/server/upgrades/dbpatches/2.0/mysql/patch.sql.orig /usr/local/share/zabbix2/server/upgrades/dbpatches/2.0/mysql/patch.sql
--- /usr/local/share/zabbix2/server/upgrades/dbpatches/2.0/mysql/patch.sql.orig 2013-02-18 11:48:57.000000000 +0400
+++ /usr/local/share/zabbix2/server/upgrades/dbpatches/2.0/mysql/patch.sql      2013-02-20 11:03:59.000000000 +0400
@@ -3,8 +3,6 @@
                         MODIFY eventid bigint unsigned NOT NULL;
 DELETE FROM acknowledges WHERE NOT userid IN (SELECT userid FROM users);
 DELETE FROM acknowledges WHERE NOT eventid IN (SELECT eventid FROM events);
-ALTER TABLE acknowledges ADD CONSTRAINT c_acknowledges_1 FOREIGN KEY (userid) REFERENCES users (userid) ON DELETE CASCADE;
-ALTER TABLE acknowledges ADD CONSTRAINT c_acknowledges_2 FOREIGN KEY (eventid) REFERENCES events (eventid) ON DELETE CASCADE;
 ALTER TABLE actions
        MODIFY actionid bigint unsigned NOT NULL,
        MODIFY def_longdata text NOT NULL,
@@ -23,10 +21,6 @@
 DELETE FROM alerts WHERE NOT eventid IN (SELECT eventid FROM events);
 DELETE FROM alerts WHERE NOT userid IN (SELECT userid FROM users);
 DELETE FROM alerts WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
-ALTER TABLE alerts ADD CONSTRAINT c_alerts_1 FOREIGN KEY (actionid) REFERENCES actions (actionid) ON DELETE CASCADE;
-ALTER TABLE alerts ADD CONSTRAINT c_alerts_2 FOREIGN KEY (eventid) REFERENCES events (eventid) ON DELETE CASCADE;
-ALTER TABLE alerts ADD CONSTRAINT c_alerts_3 FOREIGN KEY (userid) REFERENCES users (userid) ON DELETE CASCADE;
-ALTER TABLE alerts ADD CONSTRAINT c_alerts_4 FOREIGN KEY (mediatypeid) REFERENCES media_type (mediatypeid) ON DELETE CASCADE;
 ALTER TABLE applications MODIFY applicationid bigint unsigned NOT NULL,
                         MODIFY hostid bigint unsigned NOT NULL,
                         MODIFY templateid bigint unsigned NULL;
@@ -44,11 +38,9 @@
        MODIFY oldvalue text NOT NULL,
        MODIFY newvalue text NOT NULL;
 DELETE FROM auditlog_details WHERE NOT auditid IN (SELECT auditid FROM auditlog);
-ALTER TABLE auditlog_details ADD CONSTRAINT c_auditlog_details_1 FOREIGN KEY (auditid) REFERENCES auditlog (auditid) ON DELETE CASCADE;
 ALTER TABLE auditlog MODIFY auditid bigint unsigned NOT NULL,
                     MODIFY userid bigint unsigned NOT NULL;
 DELETE FROM auditlog WHERE NOT userid IN (SELECT userid FROM users);
-ALTER TABLE auditlog ADD CONSTRAINT c_auditlog_1 FOREIGN KEY (userid) REFERENCES users (userid) ON DELETE CASCADE;
 DROP INDEX autoreg_host_1 ON autoreg_host;
 CREATE INDEX autoreg_host_1 ON autoreg_host (proxy_hostid,host);
 ALTER TABLE autoreg_host MODIFY autoreg_hostid bigint unsigned NOT NULL,
@@ -1623,7 +1615,6 @@
 ALTER TABLE service_alarms MODIFY servicealarmid bigint unsigned NOT NULL,
                           MODIFY serviceid bigint unsigned NOT NULL;
 DELETE FROM service_alarms WHERE NOT serviceid IN (SELECT serviceid FROM services);
-ALTER TABLE service_alarms ADD CONSTRAINT c_service_alarms_1 FOREIGN KEY (serviceid) REFERENCES services (serviceid) ON DELETE CASCADE;
 ALTER TABLE services_links MODIFY linkid bigint unsigned NOT NULL,
                           MODIFY serviceupid bigint unsigned NOT NULL,
                           MODIFY servicedownid bigint unsigned NOT NULL;

[tts00]

If i remove the FKs. What's the risk?