Zabbix Errors: MYSQL_OPT_RECONNECT ERROR 1396 SUPER privilege

Post Views: 11

Error1

WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
 17435:20231014:161840.921 cannot use database "zabbix": its "users" table is empty (is this the Zabbix proxy database?)

Solution

The error message WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version. 17435:20231014:161840.921 cannot use database "zabbix": its "users" table is empty (is this the Zabbix proxy database?) indicates that:

The MYSQL_OPT_RECONNECT option is deprecated and will be removed in a future version of Zabbix.
The Zabbix database that you are trying to connect to does not have any users in the users table.
This may be because you are trying to connect to the Zabbix proxy database, which does not have any users by default.

To resolve this error, you can do one of the following:

Upgrade to a newer version of Zabbix that does not use the MYSQL_OPT_RECONNECT option.
Create a user in the Zabbix database.
Connect to the Zabbix server database instead of the Zabbix proxy database.

Here is a step-by-step guide on how to create a user in the Zabbix database:

Open the MySQL client.
Connect to the Zabbix database.
Run the following command to create a new user:

CREATE USER 'zabbix'@'localhost' IDENTIFIED BY 'password';

Replace password with a strong password.

Grant the user the necessary permissions. For example, to grant the user the ability to read and write data to the Zabbix database, you can run the following command:

GRANT ALL ON zabbix.* TO 'zabbix'@'localhost';

Flush the privileges.

FLUSH PRIVILEGES;

Once you have created a user in the Zabbix database, you can connect to the database using that user.

Error 2

mysql> create user zabbix@localhost identified by 'DevOpsSchool$123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'zabbix'@'localhost'

Solution 2

There are a few possible reasons why you are getting the error message ERROR 1396 (HY000): Operation CREATE USER failed for 'zabbix'@'localhost':

You may not have the necessary permissions to create a new user in the MySQL database.
The user name or password may be invalid.
The user may already exist.

To resolve this error, you can do one of the following:

Make sure that you have the necessary permissions to create a new user in the MySQL database.
Check the user name and password to make sure that they are valid.
If the user already exists, you can try to reset the password for the user.

Here is a step-by-step guide on how to reset the password for a MySQL user:

Open the MySQL client.
Connect to the MySQL database.
Run the following command to reset the password for the zabbix user:

ALTER USER 'zabbix'@'localhost' IDENTIFIED BY 'DevOpsSchool$123';

Flush the privileges.

FLUSH PRIVILEGES;

Once you have reset the password for the zabbix user, you will be able to connect to the MySQL database using that user.

Here are some additional tips for troubleshooting this error:

Make sure that the MySQL service is running.
Check the MySQL error log for any additional information about the error.
Try restarting the MySQL service.
If you are still having problems, you can contact your MySQL administrator for assistance.

Error 3

root@ip-172-31-33-47:/var/log/zabbix# zcat /usr/share/zabbix-sql-scripts/mysql/server.sql.gz | mysql -uzabbix -p zabbix
Enter password:
ERROR 1419 (HY000) at line 2114: 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)

Solution 3

The error message ERROR 1419 (HY000) at line 2114: 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) indicates that the zabbix user does not have the SUPER privilege and binary logging is enabled. This is required for the user to be able to create functions and triggers.

To resolve this error, you can do one of the following:

Grant the SUPER privilege to the zabbix user.
Disable binary logging.
Set the log_bin_trust_function_creators variable to 1.
To grant the SUPER privilege to the zabbix user, you can run the following command:

mysql> GRANT SUPER ON *.* TO 'zabbix'@'localhost' WITH GRANT OPTION;
To disable binary logging, you can edit the my.cnf file and set the log_bin variable to 0.

To set the log_bin_trust_function_creators variable to 1, you can edit the my.cnf file and add the following line:

log_bin_trust_function_creators = 1
Once you have made the necessary changes, you can restart the MySQL service and try to import the Zabbix SQL scripts again.

Author
Recent Posts

rajeshkumar