Mysql Error 1449: the User Specified as a Definer Does Not Exist
When I Run the Following Query I 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...
When I run the following query I 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 `tbl_supplier_list` `a`
LEFT JOIN `view_quotes` `b`
ON (`b`.`quote_id` = `a`.`quote_id`)
LIMIT 0, 30
The error message is:
#1449 - The user specified as a definer ('web2vi'@'%') does not exist
Why am I getting that error? How do I fix it?
41 Answers
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.
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:
How to change the definer for views
Run this SQL to generate the necessary ALTER statements
SELECT CONCAT("ALTER DEFINER=
youruser@hostVIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='your-database-name';Copy and run the ALTER statements
How to change the definer for stored procedures
Example:
UPDATE `mysql`.`proc` p SET definer = 'user@%' WHERE definer='root@%'
Be careful, because this will change all the definers for all databases.
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
