Database upgrade failed after zabbix upgrade from 6.0.10 to 6.0.11

[mnastalek]

Hello,

Im using zabbix appliance on centos 8 from 5.4.

Today i launched update from 6.0.10 to 6.0.11 and zabbix_server cant start.
Errors:

1583964:20221130:060100.929 Zabbix Server stopped. Zabbix 6.0.10 (revision e35d7dceead).
3407926:20221130:060100.942 Starting Zabbix Server. Zabbix 6.0.11 (revision 17788cd2098).
3407926:20221130:060100.942 ****** Enabled features ******
3407926:20221130:060100.942 SNMP monitoring: YES
3407926:20221130:060100.942 IPMI monitoring: YES
3407926:20221130:060100.942 Web monitoring: YES
3407926:20221130:060100.942 VMware monitoring: YES
3407926:20221130:060100.942 SMTP authentication: YES
3407926:20221130:060100.942 ODBC: YES
3407926:20221130:060100.942 SSH support: YES
3407926:20221130:060100.942 IPv6 support: YES
3407926:20221130:060100.942 TLS support: YES
3407926:20221130:060100.942 ******************************
3407926:20221130:060100.942 using configuration file: /etc/zabbix/zabbix_server.conf
3407926:20221130:060100.952 current database version (mandatory/optional): 06000000/06000007
3407926:20221130:060100.952 required mandatory version: 06000000
3407926:20221130:060100.952 optional patches were found
3407926:20221130:060100.952 starting automatic database upgrade
3407926:20221130:060101.148 completed 10% of database upgrade
3407926:20221130:060101.237 completed 20% of database upgrade
3407926:20221130:060101.248 completed 30% of database upgrade
3407926:20221130:060101.250 [Z3005] query failed: [1419] You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) [create trigger hosts_name_upper_insert
before insert on hosts for each row
set new.name_upper=upper(new.name)]
3407926:20221130:060101.250 database upgrade failed
3407926:20221130:060101.256 database could be upgraded to use primary keys in history tables

So i used set global log_bin_trust_function_creators = 1; in mysql.

From now im reciving:
3408967:20221130:063849.331 Starting Zabbix Server. Zabbix 6.0.11 (revision 17788cd2098).
3408967:20221130:063849.331 ****** Enabled features ******
3408967:20221130:063849.331 SNMP monitoring: YES
3408967:20221130:063849.331 IPMI monitoring: YES
3408967:20221130:063849.331 Web monitoring: YES
3408967:20221130:063849.331 VMware monitoring: YES
3408967:20221130:063849.331 SMTP authentication: YES
3408967:20221130:063849.331 ODBC: YES
3408967:20221130:063849.331 SSH support: YES
3408967:20221130:063849.331 IPv6 support: YES
3408967:20221130:063849.331 TLS support: YES
3408967:20221130:063849.331 ******************************
3408967:20221130:063849.331 using configuration file: /etc/zabbix/zabbix_server.conf
3408967:20221130:063849.343 current database version (mandatory/optional): 06000000/06000010
3408967:20221130:063849.343 required mandatory version: 06000000
3408967:20221130:063849.343 optional patches were found
3408967:20221130:063849.343 starting automatic database upgrade
3408967:20221130:063849.345 [Z3005] query failed: [1142] TRIGGER command denied to user 'zabbix_srv'@'localhost' for table 'hosts' [create trigger hosts_name_upper_insert
before insert on hosts for each row
set new.name_upper=upper(new.name)]
3408967:20221130:063849.345 database upgrade failed
3408967:20221130:063849.351 database could be upgraded to use primary keys in history tables

Any hints whats going on?

[phfas]

It looks like this fixed it for me:

https://catonrug.blogspot.com/2022/07/zabbix-62-you-do-not-have-super.html

But not sure what's going on.

[Markku]

TRIGGER command denied to user 'zabbix_srv'@'localhost'

How was the database user configured? Look for "SHOW GRANTS" command.

Markku

[derli1988]

Here's how I fixed the issue

I've added these 3 lines into MySQL initiation file

disable_log_bin
log_bin_trust_function_creators=1
skip-log-bin

I then restart mysql, and then zabbix-proxy

[Andrea.M]

I've got the same issue today updating a proxy from 6.0.9 to 6.0.12

In my case adding this line

log_bin_trust_function_creators=1

was enough to solve the database upgrade problem

  2058:20230105:121803.491 using configuration file: /etc/zabbix/zabbix_proxy.conf
  2058:20230105:121803.500 Got signal [signal:15(SIGTERM),sender_pid:1,sender_uid:0,reason:0]. Exiting ...
  2058:20230105:121803.501 Zabbix Proxy stopped. Zabbix 6.0.12 (revision 126aa2f53e9).
  2210:20230105:123337.126 Starting Zabbix Proxy (active) [SCUZPROXY1]. Zabbix 6.0.12 (revision 126aa2f53e9).
  2210:20230105:123337.126 **** Enabled features ****
  2210:20230105:123337.126 SNMP monitoring:       YES
  2210:20230105:123337.126 IPMI monitoring:       YES
  2210:20230105:123337.126 Web monitoring:        YES
  2210:20230105:123337.126 VMware monitoring:     YES
  2210:20230105:123337.126 ODBC:                  YES
  2210:20230105:123337.126 SSH support:           YES
  2210:20230105:123337.126 IPv6 support:          YES
  2210:20230105:123337.126 TLS support:           YES
  2210:20230105:123337.126 **************************
  2210:20230105:123337.126 using configuration file: /etc/zabbix/zabbix_proxy.conf
  2210:20230105:123337.202 current database version (mandatory/optional): 06000000/06000010
  2210:20230105:123337.202 required mandatory version: 06000000
  2210:20230105:123337.202 optional patches were found
  2210:20230105:123337.202 starting automatic database upgrade
  2210:20230105:123337.300 completed 14% of database upgrade
  2210:20230105:123337.314 completed 28% of database upgrade
  2210:20230105:123337.408 completed 42% of database upgrade
  2210:20230105:123337.491 completed 57% of database upgrade
  2210:20230105:123337.500 completed 71% of database upgrade
  2210:20230105:123337.511 completed 85% of database upgrade
  2210:20230105:123337.525 completed 100% of database upgrade
  2210:20230105:123337.525 database upgrade fully completed
​