You do not have the SUPER privilege on Zabbix

The best moment of the day for me is to share the knowledge with others those will face the same problem like me. Today I decided to update my zabbix server and at the end of the progress my zabbix-service stopped working. Investigation details are given below and I hope that someone can access this article when they face the same problem.

[root@appliance ~]# tail -f /var/log/zabbix/zabbix_server.log
 11857:20221209:194033.621 ******************************
 11857:20221209:194033.621 using configuration file: /etc/zabbix/zabbix_server.conf
 11857:20221209:194033.643 current database version (mandatory/optional): 06000000/06000010
 11857:20221209:194033.643 required mandatory version: 06000000
 11857:20221209:194033.643 optional patches were found
 11857:20221209:194033.643 starting automatic database upgrade
 11857:20221209:194033.645 [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)]
 11857:20221209:194033.645 database upgrade failed
 11862:20221209:194045.870 Starting Zabbix Server. Zabbix 6.0.12 (revision 126aa2f53e9).

Actually, the answer is already given inside the error output. It says “our zabbix user doesn’t have super privilege to complete the database upgrade process” and we need to take a look whether if it is true. Let’s login mysql using root account.

mysql -u root -p

if you successfully login the mysql then enter the following command to see which user has super privilegeon zabbix database. As you see in the picture, I have two users for zabbix and they don’t have super privilege permission. I check my zabbix_server.conf file and I saw that I used zabbix_srv account for database and I will give super privilege permisson to zabbix_srv account.

SELECT Host,USER,Super_priv FROM mysql.user;

Let’s give the upgrade process what it wants.

UPDATE mysql.user SET Super_Priv='Y' WHERE USER='zabbix_srv' AND host='localhost';

As you see in the last shared picture zabbix_srv account has super privilege permission. Let’s check the log file again and see what is happening.

[root@appliance ~]# tail -f /var/log/zabbix/zabbix_server.log
27980:20221209:203151.523 **
27980:20221209:203151.523 using configuration file: /etc/zabbix/zabbix_server.conf
27980:20221209:203151.550 current database version (mandatory/optional): 06000000/06000010
27980:20221209:203151.551 required mandatory version: 06000000
27980:20221209:203151.551 optional patches were found
27980:20221209:203151.551 starting automatic database upgrade
27980:20221209:203151.552 [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)]
27980:20221209:203151.552 database upgrade failed

As you see error code is changed from 1149 to 1419. I made some research on google and l discovered that Zabbix already publish an information about log_bin_trust_function_creators in upgrade notes of 6.0.11 version. The referance article is here you can get more information over there. Let’s log in to mysql again and run the following command.

SET GLOBAL log_bin_trust_function_creators = 1;

Let’s return back to log file and check what is happening.

 12406:20221209:200530.647 starting automatic database upgrade
 12406:20221209:200530.649 [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)]
 12406:20221209:200530.650 database upgrade failed

Wow something is happening and error code has been changed and seems my poor zabbix_srv account needs all privilieges. Let’s set it and and check the log file again.

grant all privileges on zabbix.* to 'zabbix_srv'@'localhost';

[root@appliance ~]# tail -f /var/log/zabbix/zabbix_server.log
 28208:20221209:204214.771 ******************************
 28208:20221209:204214.771 using configuration file: /etc/zabbix/zabbix_server.conf
 28208:20221209:204214.793 current database version (mandatory/optional): 06000000/06000010
 28208:20221209:204214.793 required mandatory version: 06000000
 28208:20221209:204214.793 optional patches were found
 28208:20221209:204214.793 starting automatic database upgrade
 28208:20221209:204214.809 completed 14% of database upgrade
 28208:20221209:204214.827 completed 28% of database upgrade
 28208:20221209:204215.129 completed 42% of database upgrade
 28208:20221209:204215.883 completed 57% of database upgrade
 28208:20221209:204219.570 slow query: 3.686632 sec, "update items set name_upper=upper(name)"
 28208:20221209:204219.574 completed 71% of database upgrade
 28208:20221209:204219.730 completed 85% of database upgrade
 28208:20221209:204219.752 completed 100% of database upgrade
 28208:20221209:204219.752 database upgrade fully completed
 28217:20221209:204219.761 starting HA manager
 28217:20221209:204219.769 HA manager started in active mode
 28208:20221209:204219.771 server #0 started [main process]
 28218:20221209:204219.772 server #1 started [service manager #1]
 28219:20221209:204219.773 server #2 started [configuration syncer #1]

As you see database upgrade progress is done and zabbix-server started working.

Thanks for your time reading.

Regards,
Hasan