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?
Best Answer
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
Copy and run the ALTER statements
How to change the definer for stored procedures
Example:
Be careful, because this will change all the definers for all databases.
2. Create the missing user
From http://www.lynnnayko.com/2010/07/mysql-user-specified-as-definer-root.html
This worked like a charm - you only have to change
someuser
to the name of the missing user. On a local dev server, you might typically just useroot
.Also consider whether you actually need to grant the user
ALL
permissions or whether they could do with less.