MariaDB problem "You do not have the SUPER privilege and binary logging is enabled"

 

Řešil jsem problém se Zabbixem a MariaDB, která je zároveň Master pro replikaci na Slave. Během upgrade z  6.0.10 na 6.0.11 se projevil problem s opravněními v DB. Zabbix server nenastartoval :-( Projevilo se to i u 6.0.12

1646147:20221212:123444.717 Starting Zabbix Server. Zabbix 6.0.12 (revision 126aa2f53e9).
1646147:20221212:123444.717 ****** Enabled features ******
1646147:20221212:123444.717 SNMP monitoring:           YES
1646147:20221212:123444.717 IPMI monitoring:           YES
1646147:20221212:123444.717 Web monitoring:            YES
1646147:20221212:123444.717 VMware monitoring:         YES
1646147:20221212:123444.717 SMTP authentication:       YES
1646147:20221212:123444.717 ODBC:                      YES
1646147:20221212:123444.717 SSH support:               YES
1646147:20221212:123444.717 IPv6 support:              YES
1646147:20221212:123444.717 TLS support:               YES
1646147:20221212:123444.717 ******************************
1646147:20221212:123444.717 using configuration file: /etc/zabbix/zabbix_server.conf
1646147:20221212:123444.724 current database version (mandatory/optional): 06000000/06000010
1646147:20221212:123444.724 required mandatory version: 06000000
1646147:20221212:123444.724 optional patches were found
1646147:20221212:123444.724 starting automatic database upgrade
1646147:20221212:123444.725 [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)]
 

Pomohlo nastavit u běžící MariaDB direktivu log_bin_trust_function_creators.

set global log_bin_trust_function_creators=1;

Po restartu MariaDB se logicky opet nastavilo defaultní OFF, ale Zabbix server již šel restartovat a nabíhal korektně.

mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators |  OFF  |
+---------------------------------+-------+

Trvale nastavení konfigurace MariaDB asi není naškodu.

--log_bin_trust_function_creators[={OFF|ON}]