MySQL error 1449: The user specified as a definer does not exist
Thursday, June 16, 2016
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.
#1449 - The user specified as a definer ('olduser'@'%') does not exist
Run this SQL to generate the necessary ALTER statements:
The user specified as a definer ('someuser'@'%') does not exist`.
Then you can solve it by using following :
#1449 - The user specified as a definer ('olduser'@'%') does not exist
You have two options:
1. Change the DEFINER
This is possibly easiest to do when initially importing your database objects, by removing any DEFINER statements from the dump. Changing the definer later is a more little tricky:Run this SQL to generate the necessary ALTER statements:
SELECT CONCAT("ALTER DEFINER=`olduser`@`host` VIEW ",
table_name, " AS ", view_definition, ";")
FROM information_schema.views
WHERE table_schema='your-database-name';
To change the definer for stored procedures:UPDATE `mysql`.`proc` p SET definer = 'youruser@%' WHERE definer='olduser@%';
Be careful, because this will change all the definers for all databases.
2. Create the missing user
If you've found following error while using MySQL database:The user specified as a definer ('someuser'@'%') does not exist`.
Then you can solve it by using following :
GRANT ALL ON *.* TO 'olduser'@'%' IDENTIFIED BY '12345678';
FLUSH PRIVILEGES;
Post a Comment