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

2021-11-20 00:00:00 permissions mysql

当我运行以下查询时，出现错误:

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

错误信息是:

#1449 - The user specified as a definer ('web2vi'@'%') does not exist

为什么我会收到那个错误?我该如何解决?

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

推荐答案

这通常发生在将视图/触发器/过程从一个数据库或服务器导出到另一个数据库或服务器时，因为创建该对象的用户不再存在.

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.

您有两个选择:

这在最初导入数据库对象时可能是最简单的，方法是从转储中删除任何 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:

  1. 运行此 SQL 以生成必要的 ALTER 语句

  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';

  • 复制并运行 ALTER 语句

  • 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.

    如果您在使用 MySQL 数据库时发现以下错误:

    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;

    来自 http://www.lynnnayko.com/2010/07/mysql-user-specified-as-definer-root.html

    这很有效 - 您只需将 someuser 更改为丢失用户的名称.在本地开发服务器上，您通常可能只使用 root.

    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.

    还要考虑您是否真的需要授予用户 ALL 权限，或者他们是否可以使用更少的权限.

    Also consider whether you actually need to grant the user ALL permissions or whether they could do with less.

    • 相关文章