Zabbix Server 4.4.8 to 5.0 Database Migration Failure

[GeoffE]

Host OS: Ubuntu 18.04 LTS
MySQL Version: 5.7.30

When attempting to upgrade the server from 4.4.8 to 5.0, we get the following error in the Zabbix Server logs:

 3738:20200629:173032.939 Starting Zabbix Server. Zabbix 5.0.1 (revision c2a0b03480).
3738:20200629:173032.939 ****** Enabled features ******
3738:20200629:173032.939 SNMP monitoring: YES
3738:20200629:173032.939 IPMI monitoring: YES
3738:20200629:173032.939 Web monitoring: YES
3738:20200629:173032.939 VMware monitoring: YES
3738:20200629:173032.939 SMTP authentication: YES
3738:20200629:173032.939 ODBC: YES
3738:20200629:173032.939 SSH support: YES
3738:20200629:173032.939 IPv6 support: YES
3738:20200629:173032.939 TLS support: YES
3738:20200629:173032.939 ******************************
3738:20200629:173032.939 using configuration file: /etc/zabbix/zabbix_server.conf
3738:20200629:173032.947 current database version (mandatory/optional): 04050004/04050004
3738:20200629:173032.947 required mandatory version: 05000000
3738:20200629:173032.947 starting automatic database upgrade
3738:20200629:173033.077 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`#sql-74b_c4e7b`, CONSTRAINT `c_items_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE) [alter table `items` add constraint c_items_1 foreign key (`hostid`) references `hosts` (`hostid`) on delete cascade]
3738:20200629:173033.077 database upgrade failed

I'm no MySQL expert, so I really don't want to go poking too hard at the database in an attempt to fix this. As best as I can tell, this is telling me that we can't alter the "items" table to add a foreign key constraint because an item is missing... but I don't know how to fix that.

Any ideas/suggestions? Additional information that would help with troubleshooting?

[GeoffE]

With the assistance of our DBA, we were able to work around this by issuing the following command:

SET global foreign_key_checks = 0;

This is obviously not an ideal solution, but it worked for us, and we were able to complete the upgrade (and then set the key checks back to 1 afterwards).

[tim.mooney]

Since you have a DBA you can get assistance from I recommend you follow-up what you've already done with these steps:

- use the create.sql.gz from the exact same 5.0.x version of Zabbix that you're running now (whatever version it is, 5.0.1, 5.0.2, etc.) to create a new, empty zabbix database on a different host. This can be a VM or test system, but you want it (if possible) running the same version of the MySQL server software as what you're using for your actual Zabbix database.

Once you have the tables created (but with no data in that database), do one of the following two things:

- use the 'mysqldiff' tool from Oracle to compare the fresh, empty schema you created on the test system to your production schema.

OR

- you can use 'mysqldump' to dump just the schema, without the table contents. You probably want to use something like:

mysqldump --opt --single-transaction \
--comments --skip-dump-date \
--routines \
--triggers \
--no-data \
--user=zabbix \
-p \
zabbix

(note that you don't really need --routines or --triggers, but I included them as more of a general example). If you dump the schema from the fresh DB and compare it to the schema from the upgraded production DB (using something like GNU diff or some other text comparison tool you like), your DBA should be able to help you identify places where your production schema has diverged from what it should be. This is an artifact of the database upgrade procedures that have been run as you've done previous Zabbix upgrades. Once your DBA knows what the schema differences are, he or she can probably help you get them all cleaned up, so your upgraded production database has exactly the schema that it should.

[dimir]

Looks like you have item in the items table that is referencing non-existent host. This should't have happened unless some 3rd party intrusion with data modification. Try this SQL:

select key_,hostid from items where items.hostid not in (select hostid from hosts);

[levon74]

Looks like you have item in the items table that is referencing non-existent host. This should't have happened unless some 3rd party intrusion with data modification. Try this SQL:

select key_,hostid from items where items.hostid not in (select hostid from hosts);

Hi dimir,

I know it's a little bit old thread, but I'm having exactly the issue when upgrading my Zabbix 3.2 DB to version 5.4
The result of the abovementioned query for me:

mysql> select key_,hostid from items where items.hostid not in (select hostid from hosts);
+-----------------------+--------+
| key_ | hostid |
+-----------------------+--------+
| net.if.in[{#IFNAME}] | 10251 |
| net.if.out[{#IFNAME}] | 10251 |
| net.if.in[{#IFNAME}] | 10252 |
| net.if.out[{#IFNAME}] | 10252 |
| net.if.in[{#IFNAME}] | 10195 |
| net.if.out[{#IFNAME}] | 10195 |
| net.if.in[{#IFNAME}] | 10314 |
| net.if.out[{#IFNAME}] | 10314 |
| net.if.in[{#IFNAME}] | 10315 |
| net.if.out[{#IFNAME}] | 10315 |
| net.if.in[{#IFNAME}] | 10316 |
| net.if.out[{#IFNAME}] | 10316 |
| net.if.in[{#IFNAME}] | 10384 |
| net.if.out[{#IFNAME}] | 10384 |
| net.if.in[{#IFNAME}] | 10385 |
| net.if.out[{#IFNAME}] | 10385 |
| net.if.in[{#IFNAME}] | 10386 |
| net.if.out[{#IFNAME}] | 10386 |
| net.if.in[{#IFNAME}] | 10409 |
| net.if.out[{#IFNAME}] | 10409 |
| net.if.in[{#IFNAME}] | 10410 |
| net.if.out[{#IFNAME}] | 10410 |
| net.if.in[{#IFNAME}] | 10421 |
| net.if.out[{#IFNAME}] | 10421 |
| net.if.in[{#IFNAME}] | 10434 |
| net.if.out[{#IFNAME}] | 10434 |
+-----------------------+--------+
26 rows in set (0.05 sec)

What's the simplest remedy for this, finding those hosts in GUI and removing them?
From what you are saying though it feels like there are items referencing the hosts whereas no hosts exist. How do I find the items then...?

Added: Interestingly, if I upgrade 3.2 DB to 4.0, I don't get the "foreign key constraint" error. But I reckon that if I update 4.0 to 5.4 I'll get it again.

Added: Checked with Zabbix API, I'm really missing the hostids that returned dimir's query...

[levon74]

Sorry guys, my non-DBA brain said to just remove those items returned by the query, which I did and finished the DB upgrade.
Now fighting against "unsupported collation and charset"

Added: All is fixed, ignore me guys

[flavio.bk]

Looks like you have item in the items table that is referencing non-existent host. This should't have happened unless some 3rd party intrusion with data modification. Try this SQL:

select key_,hostid from items where items.hostid not in (select hostid from hosts);

Thanks for this help. It was big important to continue my update process.
After select, I deleted the rows.

[dimir]

In Zabbix 5.0 the constraint with "on delete cascade" was added to items table in relation with hosts, so since then this situation is not possible.

The cure SQL (to delete such "stale" items) is:

delete from items where items.hostid not in (select hostid from hosts);