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

grant all on *.* to 'username'@'%' identified by 'password' with grant option;

example:

grant all on *.* to 'web2vi'@'%' identified by 'password' with grant option;

Answer 2

Go into the edit routine section and and at the bottom, change Security Type from Definer to Invoker.

Answer 3

The user who originally created the SQL view or procedure has been deleted. If you recreate that user, it should address your error.

Answer 4

Solution is just a single line query as below :

grant all on *.* to 'ROOT'@'%' identified by 'PASSWORD' with grant option;

Replace ROOT with your mysql user name. Replace PASSWORD with your mysql password.

Answer 5

The database user also seems to be case-sensitive, so while I had a root'@'% user I didn't have a ROOT'@'% user. I changed the user to be uppercase via workbench and the problem was resolved!

Answer 6

Try to set your procedure as SECURITY INVOKER

Mysql default sets procedures security as "DEFINER" (CREATOR OF).. you must set the security to the "invoker".