MySQL error 1449: The user specified as a definer

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

when mysql.proc is empty, but system always notice "user@192.168.%" for table_name no exist,you just root in mysql command line and type:

CHECK TABLE `database`.`table_name` QUICK FAST MEDIUM CHANGED;
flush privileges;

over!

Answer 2

My 5 cents.

I had same error while I tried to select from a view.

However problem appears to be that this view, selected from another view that was restored from backup from different server.

and in fact, YES, user was invalid, but was not obvious where to from the first look.

Answer 3

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.

Answer 4

Your view, "view_quotes" may have been copied from a different database where "web2vi" is a valid user into a database where "web2vi" is not a valid user.
Either add the "web2vi" user to the database or alter the view (normally removing the DEFINER='web2vi'@'%' part and executing the script will do the trick)

Answer 5

For future googlers: I got a similar message trying to update a table in a database that contained no views. After some digging, it turned out I had imported triggers on that table, and those were the things defined by the non-existant user. Dropping the triggers solved the problem.

Answer 6

Fixed by running this following comments.

grant all on *.* to 'web2vi'@'%' identified by 'root' with grant option;
FLUSH PRIVILEGES;

if you are getting some_other instead of web2vi then you have to change the name accordingly.