ERROR 1418 (HY000) When I Do a Database Restore on RHEL 7

ERROR 1418 (HY000) When I Do a Database Restore on RHEL 7

ERROR 1418 (HY000) When I Do a Database Restore on RHEL 7 - Hello everyone, in this discussion I will bring you one problem that I encountered when restoring the database, namely ERROR 1418 (HY000). You may encounter this problem in some of the databases that you execute.

To solve this problem, it's quite easy, you just follow this tutorial that I made. Keep in mind if your server is production you must know for this step requires a restart of the mysql server itself.

The output error when I restore the database is like this:

ERROR 1418 (HY000) at line 11272: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

ERROR 1418 (HY000)

Issue

ERROR 1418 (HY000)

Cause

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled.

Resolution

(you *might* want to use the less safe log_bin_trust_function_creators variable)

You can solve this in 2 ways :

Setup without downtime but temporary.

# mysql -u root -p
mysql> SHOW VARIABLES LIKE '%log_bin_trust%';
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
mysql> SHOW VARIABLES LIKE '%log_bin_trust%';
mysql> exit;

Setup log_bin_trust_function_creators = 1

Setup with downtime but forever.

# vi /etc/my.cnf

# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
#default-authentication-plugin=mysql_native_password
log_bin_trust_function_creators = 1

Save & Quit

# systemctl restart mysqld

Trying

# mysql -u root -p Bangkit < Bangkit.sql

Retry to restore database

So it worked, didn't it? That's how to solve ERROR 1418 (HY000) when you restore the database to your mysql server. Hopefully this article can be useful for all of you.

Thank you.