Mysql – Can’t restore MySQL DB because of a function

functionsmariadbMySQLmysqldump

I'm trying to restore a database dump that I took via mysqldump onto a new server. Here's how I made the dump:

 mysqldump --routines --triggers database_name | pbzip2 > database_name.sql.bz2

This works fine & I transfer the dump over to the new server to restore it. I should mention the database also contains two functions:

mysql> show function status;
+------------------+--------------+----------+--------------------+---------------------+---------------------+---------------+---------+
| Db               | Name         | Type     | Definer            | Modified            | Created             | Security_type | Comment |
+------------------+--------------+----------+--------------------+---------------------+---------------------+---------------+---------+
| database_name    | function_1   | FUNCTION | analytic@localhost | 2016-05-13 00:56:26 | 2016-05-13 00:56:26 | DEFINER       |         | 
| database_name    | function_2   | FUNCTION | analytic@localhost | 2017-07-18 20:47:59 | 2017-07-18 20:47:59 | DEFINER       |         | 
+------------------+--------------+----------+--------------------+---------------------+---------------------+---------------+---------+
2 rows in set (0.00 sec)

I created the user and gave the same grants for the analytic user on the new DB server that existed on the old server, then I proceed to restore the database:

GRANT EXECUTE ON FUNCTION `database_name`.`function_1` TO 'analytic'@'localhost';
GRANT EXECUTE ON FUNCTION `database_name`.`function_2` TO 'analytic'@'localhost';

However, restoring the database results in this error every time:

ERROR 1370 (42000) at line 16767: execute command denied to user 'analytic'@'localhost' for routine 'database_name.function_2'

I'm assuming it's because it's dropping the table during the restore, which also drops the grant for the user? I assume this because the grant I create for this user is no longer there after I attempt the restore.

I even went so far as to grant all privileges to this user on that DB, but I got the same error. What is the right way to restore this database successfully?

The dump came from a very old server running MySQL 5.0.95, and I am restoring it to a server running MariaDB 10.1.14.

Best Answer

Such DB migration is not obvious and you are not the first one to have trouble with functions and definers after importing DB as things have to be created in the right order.

I'm not sure to understand how you can grant right on a function to an user when this function failed to import so function wasn't created yet.

But instead of restarting restoration from the beginning, split your export in several files. fix the problem (create user analytic, create functions manually, if there is failure, don't use definer, you will fix the issue later after import completed) then continue execute export file from when it failed.

Related Question