MySQL Database upgrade fails with [Z3005] and a missing DB trigger

[Adam Stadnick]

Hi all. I am attempting to upgrade from 6.4.12 to 7.4.3 (yes, I realize this was an ambitious thing to attempt).

 15348:20251003:120731.571 Starting Zabbix Server. Zabbix 7.4.3 (revision 9c308d6f49c).
 15348:20251003:120731.571 ****** Enabled features ******
 15348:20251003:120731.571 SNMP monitoring:           YES
 15348:20251003:120731.571 IPMI monitoring:           YES
 15348:20251003:120731.571 Web monitoring:            YES
 15348:20251003:120731.571 VMware monitoring:         YES
 15348:20251003:120731.571 SMTP authentication:       YES
 15348:20251003:120731.571 ODBC:                      YES
 15348:20251003:120731.571 SSH support:               YES
 15348:20251003:120731.571 IPv6 support:              YES
 15348:20251003:120731.571 TLS support:               YES
 15348:20251003:120731.571 ******************************
 15348:20251003:120731.571 using configuration file: /etc/zabbix/zabbix_server.conf
 15348:20251003:120731.606 current database version (mandatory/optional): 06050154/06050154
 15348:20251003:120731.606 required mandatory version: 07040000
 15348:20251003:120731.606 mandatory patches were found
 15348:20251003:120731.609 starting automatic database upgrade
 15348:20251003:120731.611 [Z3005] query failed: [1360] Trigger does not exist [drop trigger items_name_upper_insert]
 15348:20251003:120731.611 database upgrade failed on patch 06050155, exiting in 10 seconds

The relevant line is 15348:20251003:120731.611 [Z3005] query failed: [1360] Trigger does not exist [drop trigger items_name_upper_insert], but I included the overall output for the sake of completeness - no other errors are thrown.

Does anyone know how I can create this trigger? I found a very old copy of it but it's from 5.x - I tried to run it anyway, on the theory that the database upgrade will drop the trigger regardless, but it fails:

mysql> CREATE TRIGGER items_name_upper_insert BEFORE INSERT ON items FOR EACH ROW SET NEW.name_upper=UPPER(NEW.name);
ERROR 1054 (42S22): Unknown column 'name_upper' in 'NEW'

I'm not terribly comfortable trying to figure this out on my own and potentially messing up the database. log_bin_trust_function_creators is enabled and the database user account has full grants on the entire Zabbix database so I'm assuming this may just be a consequence of trying to jump too many versions at once. I do have backups so if there's no solution I will roll it all back and try to upgrade to 7.0 instead and then go from there.

[cyber]

There is no restrictions to upgrade from 6.4 to 7.4... You do not need to run each major version upgrade.

Have you ran some upgrades before this one ?

current database version (mandatory/optional): 06050154/06050154

As long as I see, this patch is from v7 upgrade (and of course included in upgrade to 7.4), but it was not there in 6.4... So how it appeared in your setup? 6050154 removed that name_upper field from items table... There are bunch of pathces related to that field.. https://git.zabbix.com/projects/ZBX/...elease%2F7.0#1 920

I am pretty sure you can find those trigger creation clauses from DB setup scripts for your old version... I am too lazy to download sources and look for them...

[Adam Stadnick]

I'm not sure what happened there. It may have been an artifact from a previous upgrade attempt. Since the system was so badly broken, I ended up restoring from backup, upgrading Zabbix to 6.4.21, upgrading the host to 22.04, and then trying to upgrade to 7.0 instead of 7.4. However, I have the same general issue:

8758:20251006:152034.320 completed 43% of database upgrade
8758:20251006:152034.404 completed 44% of database upgrade
8758:20251006:152037.603 completed 45% of database upgrade
8758:20251006:152037.872 completed 46% of database upgrade
8758:20251006:152038.865 completed 47% of database upgrade
8758:20251006:152038.866 [Z3005] query failed: [1360] Trigger does not exist [drop trigger items_name_upper_insert]

I downloaded the 6.4 and 7.0 source and found the trigger creation syntax in the schema.sql 6.4:

create trigger items_name_upper_insert
before insert on items for each row
set new.name_upper=upper(new.name)
$$

However, it looks like I'm missing a column, I get this when I try to run it:

ERROR 1054 (42S22): Unknown column 'name_upper' in 'NEW'

Any ideas? I am not terribly MySQL savvy and I don't want to run random snippets of code without understanding what I'm doing. I understand that I basically need to recreate that trigger for the automated database upgrade to complete successfully, but I'm wondering if there's a way to stop the upgrade from trying to drop the trigger since it doesn't exist.

[Adam Stadnick]

Okay, I think I got it. I just made two triggers that have no content (since the upgrade is trying to drop them, the contents can be null and that shouldn't matter). I had to make two as another one complained in the same way immediately, but running these let the automatic upgrade complete and the server is up and running 7.0 now.

create trigger items_name_upper_insert before insert on items for each row begin end;
create trigger items_name_upper_update before insert on items for each row begin end;

Now I just have to upgrade my proxies and we should be moving along. Thank you for the tip on the creation scripts, that got me moving in the right direction.

[arun001]

This happens when you jump multiple Zabbix versions and the schema you start from doesn’t match what the upgrade scripts expect—specifically, Zabbix 6.4 → 7.4 still contains references to the old items_name_upper_insert/items_name_upper_update triggers, but in 6.4 the name_upper column was already removed, so the upgrade script tries to drop a trigger that no longer exists, causing the [Z3005] / 1360 failure. The fix is simply to recreate the missing triggers temporarily so the upgrade can drop them cleanly. You don’t need full functionality—just stub triggers:

CREATE TRIGGER items_name_upper_insert BEFORE INSERT ON items FOR EACH ROW BEGIN END;
CREATE TRIGGER items_name_upper_update BEFORE UPDATE ON items FOR EACH ROW BEGIN END;

These compile (because they don’t reference name_upper), satisfy the patch check, and then the upgrade removes them and continues. If the upgrade still fails because the underlying table metadata is inconsistent—common on old InnoDB files or after partial upgrades—you can repair or extract the corrupted table definition using a third party recovery tool like Stellar Repair for MySQL, which can rebuild missing triggers or damaged InnoDB metadata before retrying the upgrade. But normally the two stub triggers above are all you need; once they exist, restart the server and the database upgrade completes cleanly.