[Solved-4 Solutions] MySQL error 1449: The user specified as a definer does not exist
Error Description:
When we run the following query, we get an error:
SELECT
`a`.`sl_id` AS `sl_id`,
`a`.`quote_id` AS `quote_id`,
`a`.`sl_date` AS `sl_date`,
`a`.`sl_type` AS `sl_type`,
`a`.`sl_status` AS `sl_status`,
`b`.`client_id` AS `client_id`,
`b`.`business` AS `business`,
`b`.`affaire_type` AS `affaire_type`,
`b`.`quotation_date` AS `quotation_date`,
`b`.`total_sale_price_with_tax` AS `total_sale_price_with_tax`,
`b`.`STATUS` AS `status`,
`b`.`customer_name` AS `customer_name`
FROM `wikitechy_list` `a`
LEFT JOIN `view_quotes` `b`
ON (`b`.`quote_id` = `a`.`quote_id`)
LIMIT 0, 30
click below button to copy the code. By - mysql tutorial - team
- The error message is:
#1449 - The user specified as a definer ('web2vi'@'%') does not exist
click below button to copy the code. By - mysql tutorial - team
Solution 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:
How to change the definer for views
- Run this SQL to generate the necessary ALTER statements
SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ",
table_name, " AS ", view_definition, ";")
FROM information_schema.views
WHERE table_schema='your-database-name';
click below button to copy the code. By - mysql tutorial - team
- Copy and run the ALTER statements
Solution 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`
click below button to copy the code. By - mysql tutorial - team
- Then you can solve it by using following :
GRANT ALL ON *.* TO 'someuser'@'%' IDENTIFIED BY 'complex-password';
FLUSH PRIVILEGES;
click below button to copy the code. By - mysql tutorial - team
Solution 3:
- The user who originally created the SQL view or procedure has been deleted. If you recreate that user, it should address your error.
Solution 4:
- If the user exists, then:
mysql> flush privileges;