Zabbixのバージョンを上げたら起動しなくなった

Zabbixを6.0.11にバージョンアップしたら、Zabbix Severが起動しなくなったので対処をしました。

メモを残しておきます。

■環境
CentOS Stream 8
nginx 1.14.1
PHP 8.1.13
MySQL 8.0.26
Zabbix 6.0.10 → 6.0.11

zabbix_server.logを見ると

以下のログが繰り返し出力されていました。
データベースのアップグレードができず、Zabbix Serverが起動できませんでした。

1216925:20221129:211149.887 Starting Zabbix Server. Zabbix 6.0.11 (revision 17788cd2098).
1216925:20221129:211149.887 ****** Enabled features ******
1216925:20221129:211149.887 SNMP monitoring:           YES
1216925:20221129:211149.887 IPMI monitoring:           YES
1216925:20221129:211149.887 Web monitoring:            YES
1216925:20221129:211149.887 VMware monitoring:         YES
1216925:20221129:211149.887 SMTP authentication:       YES
1216925:20221129:211149.887 ODBC:                      YES
1216925:20221129:211149.888 SSH support:               YES
1216925:20221129:211149.888 IPv6 support:              YES
1216925:20221129:211149.888 TLS support:               YES
1216925:20221129:211149.888 ******************************
1216925:20221129:211149.888 using configuration file: /etc/zabbix/zabbix_server.conf
1216925:20221129:211149.935 current database version (mandatory/optional): 06000000/06000010
1216925:20221129:211149.935 required mandatory version: 06000000
1216925:20221129:211149.935 optional patches were found
1216925:20221129:211149.935 starting automatic database upgrade
1216925:20221129:211149.939 [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)]
1216925:20221129:211149.939 database upgrade failed
1216925:20221129:211149.947 database could be upgraded to use primary keys in history tables

MySQLのlog_bin_trust_function_creatorsという値を変えれば何とかなりそう。 

log_bin_trust_function_creatorsを設定

log_bin_trust_function_creatorsをONに指定します。

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 547737
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql>
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye
#

再度zabbix_server.logを見る

log_bin_trust_function_creatorsを設定後、データベースのアップグレードが始まりました。

1217691:20221129:212346.386 Starting Zabbix Server. Zabbix 6.0.11 (revision 17788cd2098).
1217691:20221129:212346.386 ****** Enabled features ******
1217691:20221129:212346.386 SNMP monitoring:           YES
1217691:20221129:212346.386 IPMI monitoring:           YES
1217691:20221129:212346.386 Web monitoring:            YES
1217691:20221129:212346.386 VMware monitoring:         YES
1217691:20221129:212346.386 SMTP authentication:       YES
1217691:20221129:212346.386 ODBC:                      YES
1217691:20221129:212346.386 SSH support:               YES
1217691:20221129:212346.386 IPv6 support:              YES
1217691:20221129:212346.386 TLS support:               YES
1217691:20221129:212346.386 ******************************
1217691:20221129:212346.387 using configuration file: /etc/zabbix/zabbix_server.conf
1217691:20221129:212346.435 current database version (mandatory/optional): 06000000/06000010
1217691:20221129:212346.435 required mandatory version: 06000000
1217691:20221129:212346.435 optional patches were found
1217691:20221129:212346.435 starting automatic database upgrade
1217691:20221129:212346.901 completed 14% of database upgrade
1217691:20221129:212347.593 completed 28% of database upgrade
1217691:20221129:212350.813 slow query: 3.217367 sec, "alter table `items` add `name_upper` varchar(255) default '' not null"
1217691:20221129:212351.036 completed 42% of database upgrade
1217691:20221129:212353.185 completed 57% of database upgrade
1217691:20221129:212354.352 completed 71% of database upgrade
1217691:20221129:212355.161 completed 85% of database upgrade
1217691:20221129:212355.748 completed 100% of database upgrade
1217691:20221129:212355.748 database upgrade fully completed

データベースのアップグレード後、Zabbix Servierが起動するようになりました。

結局、log_bin_trust_function_creatorsが何なのかはよくわかっていません。