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

Why am I getting that error? How do I fix it?

I spent a hour before found a decision for a problem like this. But, in my case, I ran this:

mysql> UPDATE `users` SET `somefield` = 1 WHERE `user_id` = 2;
ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist

If you really want to find the problem, just run this commands one by one:

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
SHOW TRIGGERS;
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

...and, after each of them, look for the field 'definer'.

In my case it was bearded old trigger, that somebody of developers forgot to delete.

Answer 2

You can try this:

$ mysql -u root -p 
> grant all privileges on *.* to `root`@`%` identified by 'password'; 
> flush privileges;

Answer 3

If this is a stored procedure, you can do:

UPDATE `mysql`.`proc` SET definer = 'YournewDefiner' WHERE definer='OldDefinerShownBefore'

But this is not advised.

For me, better solution is to create the definer:

create user 'myuser' identified by 'mypass';
grant all on `mytable`.* to 'myuser' identified by 'mypass';

Answer 4

in my case I had a trigger on that table that I could not update data getting the same error.

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

the solution was to delete the triggers on that table and recreate them again, this fixed the issue, since the the trigger was made with another user from another server, and the user name changed on the new server after changing hosting company . that's my 2 cents

Answer 5

I had the same problem with root user ans it worked for me when I replaced

root@%

by

root@localhost

So, if the user 'web2vi' is allowed to connect from 'localhost', you can try:

web2vi@localhost

I'm connected remotely to the database.

Answer 6

i came here for the same problem, i couldn't find anywhere in my code where a certain user was making the action. apparently it was from a trigger that was using a user which was long deleted (db was restored from an older version) so in case you are puzzled as i were, take a look at your db events/triggers/routines. hope this will help someone.