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.