[MySQL Error] The user specified as a definer does not exist

Contents

1. 1. Background
2. 2. Error Info
3. 3. Solutions
   1. 3.1. Solution 1: Change the DEFINER
   2. 3.2. Solution 2: Create the missing user
4. 4. Reasons
5. 5. References

Background

When I execute a SQL script of the dump of a database structure and data, occurs an error “The user specified as a definer 'xxx'@'%' does not exist”.

Error Info

SQL Error (1449)：The user specified as a definer ('xxx'@'%') does not exist

Solutions

This commonly occurs when exporting views/triggers/procedures from one database or server to another as the user that created that object no longer exists.

For example, the following is a trigger create statement:

CREATE 
DEFINER=`not_exist_user` 
TRIGGER your_trigger BEFORE INSERT ON your_table
FOR EACH ROW SET new.create_time=NOW();

Solution 1: Change the DEFINER

This is possibly easiest to do when initially importing your database objects, by removing any DEFINER statements DEFINER=some_user from the dump.

Changing the definer later is a more little tricky. You can search solutions for “How to change the definer for views/triggers/procedures”.

Solution 2: Create the missing user

If you’ve found following error while using MySQL database:

The user specified as a definer ('some_user'@'%') does not exist`

Then you can solve it by using following :

CREATE USER 'some_user'@'%' IDENTIFIED BY 'complex-password';
GRANT ALL ON *.* TO 'some_user'@'%' IDENTIFIED BY 'complex-password'; 
/* or GRANT ALL ON *.* TO 'some_user'@'%'; */
FLUSH PRIVILEGES;

Reasons

My exported trigger has a definer user that does not exist.

When you insert data into the table used in the trigger, MySQL will occur the error “The user specified as a definer xxx does not exist”.

References

[1] MySQL error 1449: The user specified as a definer does not exist