2

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.

  • if the file is editable, do change the definer to root and then check. – Nawaz Sohail Jul 19 '17 at 13:17
1

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.

  • I think what's happening is the import drops the function, then recreates it. Seems that dropping it also drops the user privilege, but it's still there when the import fails because it's recreated, but the import fails because the user was dropped. It's like an impossible scenario to get around. For now, I've removed the function & it's accompanying view, and will try to restore like that. We determined they were unneeded anyway, go figure. – churnd Jul 19 '17 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.