zabbix 5.2 to 6.0 database not match in the UI ONLY!!

[Yannick Croteau]

Hello,

I'm trying to update my Zabbix 5.2 to 6.0. After a lot of walkaround to fix many issues. Now my BD look to be good, but in the UI, this error remain :



But if I check the zabbix-server.log

I can see

-------------------------

38074:20220311:202116.636 Starting Zabbix Server. Zabbix 6.0.1 (revision a80cb13868).
38074:20220311:202116.650 ****** Enabled features ******
38074:20220311:202116.659 SNMP monitoring: YES
38074:20220311:202116.673 IPMI monitoring: YES
38074:20220311:202116.687 Web monitoring: YES
38074:20220311:202116.694 VMware monitoring: YES
38074:20220311:202116.702 SMTP authentication: YES
38074:20220311:202116.709 ODBC: YES
38074:20220311:202116.716 SSH support: YES
38074:20220311:202116.723 IPv6 support: YES
38074:20220311:202116.730 TLS support: YES
38074:20220311:202116.738 ******************************
38074:20220311:202116.745 using configuration file: /etc/zabbix/zabbix_server.conf
38074:20220311:202116.821 current database version (mandatory/optional): 05030069/05030069
38074:20220311:202116.829 required mandatory version: 06000000
38074:20220311:202116.844 starting automatic database upgrade
38074:20220311:202116.858 completed 0% of database upgrade
38074:20220311:202116.989 completed 1% of database upgrade
38074:20220311:202117.160 completed 2% of database upgrade
38074:20220311:202117.270 completed 3% of database upgrade
38074:20220311:202117.384 completed 4% of database upgrade
38074:20220311:202117.479 completed 5% of database upgrade
38074:20220311:202117.556 completed 6% of database upgrade
38074:20220311:202117.600 completed 7% of database upgrade
38074:20220311:202117.721 completed 8% of database upgrade
38074:20220311:202117.808 completed 9% of database upgrade
38074:20220311:202117.860 completed 10% of database upgrade
38074:20220311:202117.925 completed 11% of database upgrade
38074:20220311:202117.957 completed 12% of database upgrade
38074:20220311:202117.999 completed 13% of database upgrade
38074:20220311:202118.021 completed 14% of database upgrade
38074:20220311:202118.037 completed 15% of database upgrade
38074:20220311:202118.251 completed 16% of database upgrade
38074:20220311:202118.342 completed 17% of database upgrade
38074:20220311:202118.386 completed 18% of database upgrade
38074:20220311:202118.702 completed 19% of database upgrade
38074:20220311:202118.758 completed 20% of database upgrade
38074:20220311:202118.773 completed 21% of database upgrade
38074:20220311:202118.784 completed 22% of database upgrade
38074:20220311:202118.828 completed 23% of database upgrade
....

38074:20220311:202126.613 completed 82% of database upgrade
38074:20220311:202126.621 completed 83% of database upgrade
38074:20220311:202126.856 completed 84% of database upgrade
38074:20220311:202127.730 completed 85% of database upgrade
38074:20220311:202131.085 slow query: 3.185636 sec, "create index alerts_8 on alerts (acknowledgeid)"
38074:20220311:202131.094 completed 86% of database upgrade
38074:20220311:202131.280 completed 87% of database upgrade
38074:20220311:202131.344 completed 88% of database upgrade
38074:20220311:202131.409 completed 89% of database upgrade
38074:20220311:202131.491 completed 90% of database upgrade
38074:20220311:202131.574 completed 91% of database upgrade
38074:20220311:202131.634 completed 92% of database upgrade
38074:20220311:202131.716 completed 93% of database upgrade
38074:20220311:202131.773 completed 94% of database upgrade
38074:20220311:202131.824 completed 95% of database upgrade
38074:20220311:202131.967 completed 96% of database upgrade
38074:20220311:202132.083 completed 97% of database upgrade
38074:20220311:202132.098 completed 98% of database upgrade
38074:20220311:202132.105 completed 99% of database upgrade
38074:20220311:202132.113 completed 100% of database upgrade
38074:20220311:202132.120 database upgrade fully completed
38074:20220311:202132.154 database could be upgraded to use primary keys in history tables
38095:20220311:202132.179 starting HA manager
38095:20220311:202132.200 HA manager started in active mode
38074:20220311:202132.210 server #0 started [main process]
38096:20220311:202132.218 server #1 started [service manager #1]
38097:20220311:202132.229 server #2 started [configuration syncer #1]
38098:20220311:202132.466 server #3 started [alert manager #1]
38100:20220311:202132.483 server #5 started [alerter #2]
38102:20220311:202132.496 server #7 started [preprocessing manager #1]
38099:20220311:202132.506 server #4 started [alerter #1]
38106:20220311:202132.523 server #11 started [lld manager #1]
38101:20220311:202132.531 server #6 started [alerter #3]
38108:20220311:202132.538 server #13 started [lld worker #2]
38109:20220311:202132.549 server #14 started [housekeeper #1]
38107:20220311:202132.559 server #12 started [lld worker #1]
38114:20220311:202132.569 server #19 started [history syncer #2]

-----------------------

if I check the dbversion table after DB update:

--------------------
mysql> select * from dbversion;
+-------------+-----------+----------+
| dbversionid | mandatory | optional |
+-------------+-----------+----------+
| 1 | 6000000 | 6000000 |
+-------------+-----------+----------+
1 row in set (0.00 sec)
-------------------

So nothing look "really" wrong...

I'm under :
- Ubuntu 20.04 for the server and the front-end
- BD was AWS RDS Aurora MySQL 5.7 and I pass to AWS RDS MySQL 8.0 Community for Zabbix 6.0. (my Zabbix 5.2 works under my MySQL 8.0 setup)
- Use nginx as front-end services.

The agent look to works great also in the background.

I had to do the
Zabbix upgrade 5.0 LTS to 5.4: Database upgrade error - ZABBIX Forums
workaround first to make the DB update...

---- otherwise I had -----

37943:20220311:201855.666 using configuration file: /etc/zabbix/zabbix_server.conf
37943:20220311:201855.752 current database version (mandatory/optional): 05030069/05030069
37943:20220311:201855.761 required mandatory version: 06000000
37943:20220311:201855.768 starting automatic database upgrade
37943:20220311:201855.777 [Z3005] query failed: [1832] Cannot change column 'scriptid': used in a foreign key constraint 'c_opcommand_2' [alter table opcommand modify `scriptid` bigint uns
igned not null]
37943:20220311:201855.784 database upgrade failed

-------------------------------
error

Thank you!

Someone have a clue about my problem?

Yannick

[tim.mooney]

Based on the information you've provided, it seems part of the work you did to prepare for Zabbix 6.0 was to copy your database to a new database server, running MySQL 8.0.

My guess is that you forgot to change the PHP configuration so that your Zabbix front-end is connecting to the new database. The Zabbix front-end does not use the settings from zabbix_server.conf (in large installations, the front-end may be on a completely different server from the zabbix-server, so the front-end needs its own separate configuration for how to access the database). If the old database is still up and reachable, the front-end may be accessing that.

Examine your 'zabbix.conf.php' file or other PHP config, and make sure that the parameters are correct for your MySQL 8.0 database, rather than the older 5.7 one.

[Yannick Croteau]

That was the problem.

I had to change $DB['SERVER'] and $DB['PASSWORD'] PHP VAR.
I didn't notice the direct connection to the DB through PHP.

Thank you very much.

Yannick

[tim.mooney]

You're welcome!

And don't feel bad, you're not the first person on these forums to have that exact problem, and I'm certain you won't be the last. :-)