No auto upgrade db after migration 5.0.40→7.2.4

[iav]

I need to move zabbix server from v5.0.40 on CentOS 7 to AlmaLinux 9, zabbix v7.2.4.
Going by manual, I set empty new zabbix server, then stop zabbix-server service and did a mysql restore of database from old zabbix.
Then service zabbix-server exit ob start, and I see in /var/log/zabbix/zabbix_server.log^

  2091:20250228:033329.569 Starting Zabbix Server. Zabbix 7.2.4 (revision c34078a4563).
  2091:20250228:033329.569 ****** Enabled features ******
  2091:20250228:033329.569 SNMP monitoring:           YES
  2091:20250228:033329.569 IPMI monitoring:           YES
  2091:20250228:033329.569 Web monitoring:            YES
  2091:20250228:033329.569 VMware monitoring:         YES
  2091:20250228:033329.569 SMTP authentication:       YES
  2091:20250228:033329.569 ODBC:                      YES
  2091:20250228:033329.569 SSH support:               YES
  2091:20250228:033329.569 IPv6 support:              YES
  2091:20250228:033329.569 TLS support:               YES
  2091:20250228:033329.569 ******************************
  2091:20250228:033329.569 using configuration file: /etc/zabbix/zabbix_server.conf
  2091:20250228:033329.573 current database version (mandatory/optional): 05000000/05000007
  2091:20250228:033329.573 required mandatory version: 07020000
  2091:20250228:033329.573 mandatory patches were found
  2091:20250228:033329.573 [Z3005] query failed: [1054] Unknown column 'ha_failover_delay' in 'field list' [select unix_timestamp(),ha_failover_delay from config]
  2091:20250228:033329.573 cannot retrieve database time
  2091:20250228:033329.573 Zabbix Server stopped. Zabbix 7.2.4 (revision c34078a4563).

How to move old configuration to new system?
History data can be dropped.

[MRedbourne]

It's not quite that simple. The procedure you're describing would be relevant if you're merely migrating from "zabbix-old" (5.0) to "zabbix-new" (5.0). Eg: changing purely the underlying OS. What you're doing is way more complicated.

What you should do here is install Zabbix 5.0.40 on your "new" server and import the data per the procedure you did above. Zabbix has packages for 5.0 stream for RHEL9. What DB are you using though, that might make a big difference. Once you have that running, then you follow patching procedures for the entire OS, all packages including Zabbix and the DB. This should run the DB migrator (as schema changes will be needed). After that it should work.

[cyber]

There is no server binaries for v5 for rh9. And they are not really needed... You can install new version of Zabbix but you have to install old DB (and not run db creation scripts from v7.x). You need that DB exactly as it is in your v5. Then your new shiny v7 can do all the required upgrade steps and it should not discover any tables and fields, that do not exist in v5 and err out... v5 did not have HA, so you should not have anything like ha_failover_delay in your v5 DB...

[tim.mooney]

The way I read that failing SQL, it's failing *because* 'ha_failover_delay' doesn't exist but the updater expects it to exist by that point in the upgrade. My interpretation (as I outlined in my response below) is that an earlier update was supposed to ALTER config and add that column, but it failed (for some unknown reason) but the updater didn't catch that there was an error and it continued on assuming the column had been added. Then, one of the later updates tried to SELECT with that column and got the error that was logged.

I've seen several reports of the exact same issue on the forums in the past few months. Not sure what's causing the problem, but I think the root cause is with the earlier update schema update that was supposed to add the column.

At least, that's my theory so far.

[iav]

There is no server binaries for v5 for rh9. And they are not really needed... You can install new version of Zabbix but you have to install old DB (and not run db creation scripts from v7.x). You need that DB exactly as it is in your v5. Then your new shiny v7 can do all the required upgrade steps and it should not discover any tables and fields, that do not exist in v5 and err out... v5 did not have HA, so you should not have anything like ha_failover_delay in your v5 DB...

Sounds like all I do: install rh9, install new zabbix 7, restore database from zabbix 5, and it can't upgrade.
What exactly you mean "You need that DB exactly as it is in your v5."? I restore exactly my v5 database.
What I miss?

[tim.mooney]

Sounds like all I do: install rh9, install new zabbix 7, restore database from zabbix 5, and it can't upgrade.
What exactly you mean "You need that DB exactly as it is in your v5."? I restore exactly my v5 database.
What I miss?

I don't think you missed anything. I'm not sure what MRedbourne is talking about, as the procedure you used is exactly correct.

Cyber is saying your 'zabbix' database should be empty when you import your old backup. You shouldn't have created any new tables/views/index/etc. with the Zabbix 7.2.x schema files. If you used standard options (specifically --opt, which includes '--add-drop-table') with "mysqldump" on your old host then when you do the import it would first automatically DROP any existing object with the same name, like this:

DROP TABLE IF EXISTS `acknowledges`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `acknowledges` (
  `acknowledgeid` bigint(20) unsigned NOT NULL,
  `userid` bigint(20) unsigned NOT NULL,
  `eventid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT 0,
  `message` varchar(2048) NOT NULL DEFAULT '',
  `action` int(11) NOT NULL DEFAULT 0,
  `old_severity` int(11) NOT NULL DEFAULT 0,
  `new_severity` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`acknowledgeid`),
  KEY `acknowledges_1` (`userid`),
  KEY `acknowledges_2` (`eventid`),
  KEY `acknowledges_3` (`clock`),
  CONSTRAINT `c_acknowledges_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE,
  CONSTRAINT `c_acknowledges_2` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

This would only clean up objects that existed in the 5.0.x schema though, so if you try load a myssqldump of a 5.0.x schema into a database that has already been populated with the Zabbix 7.2.x schema, there may be extra schema objects that don't get cleaned up.

So why are you running into this problem? I'm not sure, but there have been a lot of other reports on these forums of people running into the exact same problem, related to the 'ha_failover_delay' column not getting added correctly as part of the upgrade procedure and then this later step failing because the entire column is missing.

Official high availability (HA) was added to Zabbix at 6.0.x, so the upgrade procedure should be applying an update to ALTER your config table to have a column named "ha_failover_delay". Mine looks like this (my site is still using 6.0.x, not 7.2.x):

  `ha_failover_delay` varchar(32) NOT NULL DEFAULT '1m',

I think (I'm pretty sure, but not positive) that the error you're receiving is happening because a later update is doing a SELECT involving that column and it's failing because the column doesn't exist. The earlier step that should have created it failed (for some reason) but the updater didn't notice the failure at that step and continued.

Since you're moving to a newer host as part of your migration, you can just DROP the zabbix database on the new AlmaLinux 9 host, recreate an empty database and get ready to try another re-import.

The difficult part is figuring out why it's happening. You could try setting the "DebugLevel=5" before starting zabbix_server on the AlmaLinux 9 host, when you re-try the import. I'm not certain if that will log all the database changes, but if it does it might give you an idea what's happening with the updater that is failing.

If increasing the log level for zabbix_server on the import doesn't help you identify what's going wrong, then another approach you could try is to load the "sql_error" log plugin for MySQL or MariaDB, and set it up to log any SQL errors. This is different from the "error_log" setting. Logging the SQL errors that the updater generates should help you identify why the update is failing.

[iav]

Thank you for detailed answer!

But Now I can't test it, because I already solve this problem other way:

1. upgrade my 5.0.40 to 5.0.46 (latest 5) — not sure it means.
2. set zabbix 6 to new server, and import old db there. Success! DB imported!
3. upgrade zabbix from 6 to 7.20 on new server. Success!

Now problem is solved.

[tim.mooney]

Thank you for detailed answer!

But Now I can't test it, because I already solve this problem other way:

1. upgrade my 5.0.40 to 5.0.46 (latest 5) — not sure it means.
2. set zabbix 6 to new server, and import old db there. Success! DB imported!
3. upgrade zabbix from 6 to 7.20 on new server. Success!

That's OK! The goal was to get you to the version of Zabbix your organization wants to run. In this case, 7.2.x.

You should not have to do the intermediate upgrades that you ended up doing. It's supported to go directly from Zabbix 5.0.x to 7.2..

But if doing the upgrade in steps allows you to get to the version you want to run, that's good enough.

I don't know why it worked when you went from 5.0.40 -> 5.0.46 -> 6.0.x -> 7.2.4 but the upgrade wasn't working for 5.0.40 -> 7.2.4. That should be the same no matter whether you used intermediate steps or not. I expected that you would have to make changes to your database at some point, not that intermediate versions would be enough.

My experience is that a Zabbix database that has been upgraded many times can accumulate some slight differences from a fresh database schema. It seems like the upgrade procedure isn't always perfect at detecting when adding or dropping some schema element (even an index) doesn't succeed.

Before I upgraded to Zabbix 5.0.x, I examined my (Mariadb/MySQL) database schema carefully. It had been upgraded many times, since my site started at 2.0.x. I used a procedure to compare my existing (heavily upgraded) database schema to what I would have if I freshly installed the exact same version, to see where things had diverged. Then I manually modified my heavily-upgrade schema to bring it back in line with what the schema should be. From there, my upgrades have always gone very smoothly.