MySQL错误1449：指定为定义者的用户不存在

程序员文章站 2022-06-05 14:20:17

...

本文翻译自：MySQL error 1449: The user specified as a definer does not exist

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? 我如何解决它？

#1楼

参考：https://stackoom.com/question/gffc/MySQL错误-指定为定义者的用户不存在

#2楼

The user 'web2vi' does not exist on your mysql server. 您的mysql服务器上不存在用户'web2vi'。

See http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_no_such_user 请参阅http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_no_such_user

If that user does exist, check what servers it can access from, although I would have thought that would be a different error (EG you might have [email protected], but you are accessing the db as [email protected]% (At anything) 如果该用户确实存在，请检查它可以访问哪些服务器，虽然我认为这将是一个不同的错误（EG您可能有web2vi @ localhost，但您正在访问数据库为web2vi @％（在任何地方）

#3楼

From MySQL reference of CREATE VIEW : 从MySQL参考 CREATE VIEW ：

The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time. DEFINER和SQL SECURITY子句指定在视图调用时检查访问权限时要使用的安全上下文。

This user must exist and is always better to use 'localhost' as hostname. 此用户必须存在，并且总是更好地使用“localhost”作为主机名。 So I think that if you check that the user exists and change it to 'localhost' on create view you won't have this error. 因此，我认为如果您检查用户是否存在并在创建视图上将其更改为“localhost”，则不会出现此错误。

#4楼

进入编辑例程部分，在底部，将安全类型从Definer更改为Invoker。

#5楼

The user who originally created the SQL view or procedure has been deleted. 最初创建SQL视图或过程的用户已被删除。 If you recreate that user, it should address your error. 如果您重新创建该用户，则应该解决您的错误。

#6楼

I had your very same problem minutes ago, I ran into this issue after deleting an unused user from mysql.user table, but doing an alter view fixed it, here is a handy command that makes it very simple: 几分钟前我遇到了同样的问题，我在从mysql.user表中删除一个未使用的用户后遇到了这个问题，但是做了一个修改视图修复了它，这里有一个方便的命令，它非常简单：

SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ",
table_name," AS ", view_definition,";") FROM 
information_schema.views WHERE table_schema='databasename'

Mix this with the mysql command line (assuming *nix, not familiar with windows): 将其与mysql命令行混合使用（假设* nix，不熟悉windows）：

> echo above_query | mysql -uuser -p > alterView.sql
> mysql -uuser -ppass databasename < alterView.sql

Note: the command generates and extra SELECT CONCAT on the file, making mysql -uuser -ppass databasename < alterView.sql fail if you don't remove it. 注意：该命令会在文件上生成额外的SELECT CONCAT，如果不删除它，则会使mysql -uuser -ppass databasename < alterView.sql失败。

Source: https://dba.stackexchange.com/questions/4129/modify-definer-on-many-views 资料来源： https ： //dba.stackexchange.com/questions/4129/modify-definer-on-many-views