Unable to initialize mysql database in 6.2 per documentation

[adminjerry]

So far, I have spent 4 hours on this to the point I have removed mysql and zabbix and performed a fresh install. It still does not work.
Per the documentation: Download page, Section 2 d. mysql> grant all privileges on zabbix.* to zabbix@localhost;
The result:
mysql> show grants for zabbix@localhost;
+-------------------------------------------------------------------------------------------+
| Grants for zabbix@localhost |
+-------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `zabbix`@`localhost` |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO `zabbix`@`localhost` |
| GRANT ALL PRIVILEGES ON `zabbix`.`db` TO `zabbix`@`localhost` |
+--------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Next step:
# zcat /usr/share/doc/zabbix-sql-scripts/mysql/server.sql.gz | mysql -u zabbix -p zabbix
Enter password:
ERROR 1419 (HY000) at line 2119: 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).

I have only been successful in setting the Super privilege once. Now, if I try to run the zcat again, I get:
ERROR 1050 (42S01) at line 1: Table 'role' already exists.
I get in this endless loop of dropping tables, creating tables, not having the correct permissions, etc.

Anyone else run into this? Can anyone please help?

[Markku]

What is your MySQL version?

Markku

[adminjerry]

Version is 8.0.26.

I actually have it working.
From the point described in my post, here is what I did:
1. Dropped the database.
2. mysql> update mysql.user SET Super_Priv='Y' where user='zabbix' AND host='localhost';
3. mysql> flush privileges;
4. mysql> create database zabbix character set utf8mb4 collate utf8mb4_bin;
5. mysql> exit
6. # zcat /usr/share/doc/zabbix-sql-scripts/mysql/server.sql.gz | mysql -u zabbix -p zabbix
At this point, the zcat command worked and the schema was loaded.
I now have 6.2 running.

[Markku]

Right. FWIW, the server.sql.gz script works fine with MariaDB 10.5.15 (from Debian 11) with the normal instructions (privileges) from zabbix.com download link.

Markku

[Atsushi]

In case of MySQL 8.0.26, it was possible to deal with it by setting the value of log_bin_trust_function_creators to 1 (= ON) as shown in the error message.

[markfree]

I've just had the same issue with MySQL 8.0.29.
For future reference, this is what I did.

When checking the database, I found "log_bin_trust_function_creators" was set to "Off".

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

Therefore, as suggested by Atsushi , we can simply set it to "On" (1).

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

The other option is to update the user to Super.
We can see the users Super privileges like this:

mysql> SELECT Host,User,Super_priv FROM mysql.user;
+-----------+------------------+------------+
| Host      | User             | Super_priv |
+-----------+------------------+------------+
| %         | zabbix1          |     N      |
| %         | zabbix2          |     N      |
| %         | zabbixf          |     N      |
| localhost | mysql.infoschema |     N      |
| localhost | mysql.session    |     Y      |
| localhost | mysql.sys        |     N      |
| localhost | root             |     Y      |
| localhost | zabbixdb         |     N      |
+-----------+------------------+------------+

To set a user as Super, just update the table as shown by adminjerry . ("zabbix1" user as an example)

UPDATE mysql.user SET Super_Priv='Y' WHERE user='zabbix1' AND host='%';

Finally, I started Zabbix server and it updated the DB correctly.
After that, I just went back to MySQL and removed the Super privilege from Zabbix user.

If you changed "log_bin_trust_function_creators", just set it to "Off" (0) again after updating.


When testing with MariaDB 10.5.15, that was not an issue at all.
It has Zabbix user as Super already.

MariaDB [(none)]> SELECT Host,User,Super_priv FROM mysql.user;
+-----------+-------------+------------+
| Host      | User        | Super_priv |
+-----------+-------------+------------+
| localhost | mariadb.sys |     N      |
| localhost | root        |     Y      |
| localhost | mysql       |     Y      |
| localhost | zabbix      |     Y      |
+-----------+-------------+------------+

[fbicknel]

log_bin_trust_function_creators = 1 worked for me; thanks.

[MUXA]

Hi!
It was a very helpful post.
After upgrading from Zabbix 6.2.4 to 6.2.6 I did have the same problem.
Thank you very much!

[frymor]

Thanks, this answer solved my problem as well.

[Markku]

When testing with MariaDB 10.5.15, that was not an issue at all.
It has Zabbix user as Super already.

MariaDB [(none)]> SELECT Host,User,Super_priv FROM mysql.user;
+-----------+-------------+------------+
| Host      | User        | Super_priv |
+-----------+-------------+------------+
| localhost | mariadb.sys | N          |
| localhost | root        | Y          |
| localhost | mysql       | Y          |
| localhost | zabbix      | Y          |
+-----------+-------------+------------+

That's not what I see here on MariaDB 10.5.15 (Debian 11):

MariaDB [(none)]> select Host,User,Super_priv from mysql.user;
+-----------+-------------+------------+
| Host      | User        | Super_priv |
+-----------+-------------+------------+
| localhost | mariadb.sys | N          |
| localhost | root        | Y          |
| localhost | mysql       | Y          |
| localhost | zabbix      | N          |
| localhost | zabbix62    | N          |
+-----------+-------------+------------+

(zabbix = for a Zabbix 5.0/6.0 setup, zabbix62 = for a Zabbix 6.2 setup)

I haven't manually adjusted the users in any way after installing Zabbix according to the official instructions.

(Added: This is a new Debian 11 + MariaDB 10.5.x database install, not upgraded from any previous series of MariaDB, in case it matters.)

Markku

[Markku]

Ok hold on. When checking the documentation of log_bin_trust_function_creators I realized the issue:

Whenever binary logging is enabled, the error occurs when importing the Zabbix 6.2 database schema (server.sql.gz) (or when upgrading to 6.2, apparently).

Binary logging is not enabled by default on MariaDB 10.5.15, at least when packaged by Debian 11. When I set log_bin to ON and tried creating a test database, the error occurred with MariaDB as well:

# zcat /usr/share/doc/zabbix-sql-scripts/mysql/server.sql.gz | mysql -uzabbixtest -p zabbixtest
Enter password:
ERROR 1419 (HY000) at line 2119: 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)

Based on the documentation of log_bin_trust_function_creators I believe it is enough to enable it while installing/upgrading and disable again after that. Or, enable SUPER privilege, if you trust the Zabbix developers, and again disable that after install/upgrade.

But, I haven't found official Zabbix documentation about this yet.

Markku

[Markku]

I added some comments on case ZBXNEXT-7554 that added the feature causing the errors.

Markku

[Markku]

Some of the documentation is currently being fixed, but these are the links (I somehow failed to find these earlier, sorry):

Database creation
Upgrade notes for 6.2.0

Markku

[markfree]

I don't see any procedure for disabling binary logging at the Upgrade Procedure documentation.
There's only a small note saying it's important the variable "log_bin_trust_function_creators" is set to 1.

The Database Creation documentation has a simple but better reference to the topic.

I also commented at ZBXNEXT-7554 suggesting to copy the Database Creation reference to the Upgrade Procedure documentation.

[Markku]

Yeah, suggesting disabling binary logging in Zabbix documentation is something that won't happen. Using it is purely a choice of the database administrator, maybe because of database replication (which is responsibility of the DBA, not Zabbix) for redundancy, or because of some other reason.

I believe for now it is best that the Zabbix documentation is improved to explain the situation better, and also to give the exact option(s) how to deal with the error message.

Markku

[Markku]

Just for the sake of "completing" the discussion, I see there is now a mention about setting the log_bin_trust_function_creators variable in the download page:

https://www.zabbix.com/download?zabbix=6.2&os_distribution=debian&os_version=11_bullseye&db=mysql&ws=apache

Also, the documentation linked above has been updated as well.

Markku

[acovington7920]

Thank you markfree! My 6.0 -> 6.2 update failed today with database errors and your mysql tips did the trick!

[vonlo]

We had he same error this morning when updating from 6.0.10 to 6.0.11 (AlmaLinux 8.7 and mysql Ver 8.0.30)