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
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. :)
Thanx!
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
Very helpful.
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.
Muito obrigado por compartilhar, exatamente meu problema. Zabbix no ar.
Thanks a Lot Hasan. Your explanation solved my problem
This was exactly what I needed and got us back up and running. Thanks!
tnx a lot
Awesome, sorted my issue. Thanks heaps!
Thanks, it helped
this happen to me when I upgrade from 6.2 to 6.4. Thanks it helps.
Thanks it help me when upgrade from 6.2.3 to 6.2.9
Thanks for the input. Solved a problem for me that prevented me from updating Zabbix from version 5.4 to 6.0
Thanks Man! you help me on deployment process!
Thanks a lot, you helped me so much
Thanks for taking the time to save us all
This was a great post, and thanks for sharing
Thank you and it is very useful.