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.
[[email protected] ~]# 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:  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.
[[email protected] ~]# 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:  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:  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';
[[email protected] ~]# 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.
16 comments on “You do not have the SUPER privilege on Zabbix”
Thanks man, very useful!
I’m happy to hear that you found it useful.
Thanks a lot. It helped me.
Happy to hear that. :)
Puno ti hvala ;)
Nema na cemu. :)
You are welcome.
worked for me. thank you sir.
I’m happy to hear that.
Best dude ever. Luv ya <3
Love you too. <3
I appreciate your time and effort.
You are welcome.
Thank you very much, exactly this article helped me. Thank you for your work!
I’m happy this article helped you.