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

Question

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?

Answer 1

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

1. 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'; 

2. 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.

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 'someuser'@'%' IDENTIFIED BY 'complex-password'; FLUSH PRIVILEGES; 

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 use root.

Also consider whether you actually need to grant the user ALL permissions or whether they could do with less.