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

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

提问by Tech MLG

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?

为什么我会收到那个错误？我如何解决它？

回答by Chococroc

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

1. 更改定义器

This is possibly easiest to do when initially importing your database objects, by removing any DEFINERstatements from the dump.

在最初导入数据库对象时，通过DEFINER从转储中删除任何语句，这可能是最容易做到的。

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

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

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

2. 复制并运行 ALTER 语句

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

2.创建丢失的用户

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;

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

The user specified as a definer ('someuser'@'%') does not exist`

然后您可以使用以下方法解决它：

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

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

This worked like a charm - you only have to change someuserto the name of the missing user. On a local dev server, you might typically just use root.

这就像一个魅力 - 您只需更改someuser为丢失用户的名称。在本地开发服务器上，您通常可能只使用root.

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

还要考虑您是否真的需要授予用户ALL权限，或者他们是否可以少做一些。

回答by Dave Z Dopson

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

最初创建 SQL 视图或过程的用户已被删除。如果您重新创建该用户，它应该可以解决您的错误。

回答by artamonovdev

I got the same error after updating mysql.

更新mysql后我遇到了同样的错误。

The error has been fixed after this command:

此命令后错误已修复：

mysql_upgrade -u root

mysql_upgrade should be executed each time you upgrade MySQL. It checks all tables in all databases for incompatibilities with the current version of MySQL Server. If a table is found to have a possible incompatibility, it is checked. If any problems are found, the table is repaired. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

每次升级 MySQL 时都应该执行 mysql_upgrade。它检查所有数据库中的所有表是否与当前版本的 MySQL 服务器不兼容。如果发现某个表可能存在不兼容性，则会对其进行检查。如果发现任何问题，则修复该表。mysql_upgrade 还会升级系统表，以便您可以利用可能已添加的新权限或功能。

回答by BroknDodge

If the user exists, then:

如果用户存在，则：

mysql> flush privileges;

回答by Kevin

Create the deleted user like this :

像这样创建已删除的用户：

mysql> create user 'web2vi';

or

或者

mysql> create user 'web2vi'@'%';

回答by hussainfrotan

Follow these steps:

按着这些次序：

1. Go to PHPMyAdmin
2. Select Your Database
3. Select your table
4. On the top menu Click on 'Triggers'
5. Click on 'Edit' to edit trigger
6. Change definer from [user@localhost] to root@localhost

1. 转到 PHPMyAdmin
2. 选择您的数据库
3. 选择您的餐桌
4. 在顶部菜单上单击“触发器”
5. 单击“编辑”以编辑触发器
6. 将定义器从 [user@localhost] 更改为 root@localhost

Hope it helps

希望能帮助到你

回答by Muhammad Azeem

Solution is just a single line query as below :

解决方案只是一个单行查询，如下所示：

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

Replace ROOTwith your mysql user name. Replace PASSWORDwith your mysql password.

替换ROOT为您的 mysql 用户名。替换PASSWORD为您的 mysql 密码。

回答by Selvamani

Fixed by running this following comments.

通过运行以下评论来修复。

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

if you are getting some_otherinstead of web2vithen you have to change the name accordingly.

如果你得到some_other而不是web2vi那么你必须相应地更改名称。

回答by Chris Ptheitroadier

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.

对于未来的谷歌员工：我收到一条类似的消息，试图更新不包含视图的数据库中的表。经过一番挖掘，结果我在那个表上导入了触发器，这些是由不存在的用户定义的。删除触发器解决了这个问题。

回答by cosmorogers

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 web2vi@localhost, but you are accessing the db as web2vi@% (At anything)

如果该用户确实存在，请检查它可以从哪些服务器访问，尽管我认为这会是一个不同的错误（例如，您可能有 web2vi@localhost，但您正在以 web2vi@% 的身份访问数据库（在任何情况下）