Saturday, 8 August 2015

Error : “The user specified as a definer does not exist” when importing mysqldump

                      Change the Definer of Mysql Stored Procedures

Sometimes when we try to import mysqldump into our local machine through command line terminal,we end up getting following error

mysqldump: Got error: 1449: The user specified as a definer ('user@%') does not exist when using LOCK TABLES

This error comes because the database we are trying to import contains procedures and functions that are defined by the user 'user'.

You can monitor your procedure characteristics like database name,type,creator,creation time and character set information by using this command:

         SHOW procedure status;

after executing this command, you can see some procedures creator name is ('user@%').

So you need to change it to 'root@%' or 'root'@'localhost',you should update definer for procedures using this command in Mysql terminal,

         UPDATE 'mysql'.'proc' p SET definer='root@%' WHERE definer='user@%';

keep in mind one thing this command will update definer for all procedures.

Other way to achieve this is create user name 'user' in your database and grant all permission to it.

        GRANT all on *.* to 'user@%' identified by 'password' with grant option;

You can also view grants on any user using this command,

        SHOW GRANTS for 'user@%';

I hope this solution helps you.Enjoy coding :)

No comments:

Post a Comment