MySQL 错误 #1133 - 在用户表中找不到任何匹配的行

MySQL Error #1133 - Can't find any matching row in the user table

提问by Tumharyyaaden

Unable to set password for a user using 3.5.2.2 - phpMyAdminfor 5.5.27 - MySQL. When trying to set the password while logged onto phpMyAdminas the user, it pops up the following error:

无法为使用3.5.2.2-phpMyAdminfor 5.5.27-MySQL的用户设置密码。以phpMyAdmin用户身份登录时尝试设置密码时，会弹出以下错误：

#1133 - Can't find any matching row in the user table

When logged on as root, following password set successfully message pops up.

以 root 身份登录时，会弹出以下密码设置成功消息。

SET PASSWORD FOR 'user'@'%' = PASSWORD( '***' )

In either case, password does not set and stays as it currently is, blank.

在任何一种情况下，密码都不会设置并保持当前状态，空白。

采纳答案by Tumharyyaaden

It turns out, the error is very vague indeed!

事实证明，错误确实很模糊！

1) Password was setting while logged on as root, as it was updating the user/password field in the users table under MySql.

1) 以 root 身份登录时设置密码，因为它正在更新 MySql 下用户表中的用户/密码字段。

2) When logged on as user, password was in fact not changing and even though there was one specified in the users table in MySql, config.inc.php file allowed authentication without password.

2）当以用户身份登录时，密码实际上并没有改变，即使在MySql的users表中指定了一个，config.inc.php文件允许无需密码进行身份验证。

Solution:

解决方案：

Changefollowing value to falsein the config.inc.php.

false在config.inc.php 中更改以下值。

$cfg['Servers'][$i]['AllowNoPassword'] = true;

So that it reads

所以它读

$cfg['Servers'][$i]['AllowNoPassword'] = false;

Changeuser's hostfrom Anyor %to localhostin MySql users table. This could easily be achieved via phpMyAdminconsole.

在 MySql 用户表中将用户的主机更改为Any或更改%为localhost。这可以通过phpMyAdmin控制台轻松实现。

These two changes allowed me to authenticate as user with it's password and disallowed authentication without password.

这两个更改允许我使用密码进行用户身份验证，并禁止没有密码的身份验证。

It also allowed user to change its password while logged on as user.

它还允许用户在以用户身份登录时更改其密码。

Seems all permissions and the rest was fixed with these two changes.

似乎所有权限，其余的都通过这两个更改进行了修复。

回答by pdg137

I encountered this error using MySQL in a different context (not within phpMyAdmin). GRANT and SET PASSWORD commands failed on a particular existing user, who was listed in the mysql.user table. In my case, it was fixed by running

我在不同的上下文中（不在 phpMyAdmin 中）使用 MySQL 遇到了这个错误。对 mysql.user 表中列出的特定现有用户的 GRANT 和 SET PASSWORD 命令失败。就我而言，它是通过运行修复的

FLUSH PRIVILEGES;

The documentationfor this command says

此命令的文档说

Reloads the privileges from the grant tables in the mysql database.

The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.

从 mysql 数据库中的授权表重新加载权限。

作为 GRANT 和 CREATE USER 语句的结果，服务器将信息缓存在内存中。这块内存不会被对应的REVOKE和DROP USER语句释放，所以对于一个执行很多导致缓存的语句实例的服务器，内存使用量会增加。可以使用 FLUSH PRIVILEGES 释放此缓存内存。

Apparently the user table cache had reached an inconsistent state, causing this weird error message. More information is available here.

显然用户表缓存达到了不一致的状态，导致了这个奇怪的错误信息。可在此处获得更多信息。

回答by Stephane

This error can occur if trying to grant privileges for a non existing user.

如果尝试为不存在的用户授予权限，则可能会发生此错误。

It is not clear to me what MySQL considers a non existing user. But I suspect MySQL considers a user to exist if it can be found by a name (column User) and a host (column Host) in the usertable.

我不清楚 MySQL 认为什么是非现有用户。但是我怀疑如果可以通过表中的名称（列User）和主机（列Host）找到用户，MySQL 会认为该用户存在user。

If trying to grant privileges to a user that can be found with his name (column User) but not by his name and host (columns Userand Host), and not provide a password, then the error occurs.

如果尝试向可以通过其姓名（列User）找到但不能通过其姓名和主机（列User和Host）找到的用户授予权限，并且未提供密码，则会发生错误。

For example, the following statement triggers the error:

例如，以下语句会触发错误：

grant all privileges on mydb.* to myuser@'xxx.xxx.xxx.xxx';

This is because, no password being specified, MySQL cannot create a new user, and thus tries to find an existing user. But no user with the name myuserand the host xxx.xxx.xxx.xxxcan be found in the usertable.

这是因为，没有指定密码，MySQL 无法创建新用户，因此会尝试查找现有用户。但是在表中找不到具有名称myuser和主机的xxx.xxx.xxx.xxx用户user。

Whereas providing a password, allows the statement to be executed successfully:

而提供密码，则允许语句成功执行：

grant all privileges on mydb.* to myuser@'xxx.xxx.xxx.xxx' identified by 'mypassword';

Make sure to reuse the same password of that user you consider exists, if that new "MySQL user" is the same "application user".

如果新的“MySQL 用户”与“应用程序用户”相同，请确保重复使用您认为存在的该用户的相同密码。

Complete the operation by flushing the privileges:

通过刷新权限完成操作：

flush privileges;

回答by e.thompsy

I encountered this issue, but in my case the password for the 'phpmyadmin' user did not match the contents of /etc/phpmyadmin/config-db.php

我遇到了这个问题，但就我而言，'phpmyadmin' 用户的密码与 /etc/phpmyadmin/config-db.php

Once I updated the password for the 'phpmyadmin' user the error went away.

一旦我更新了“phpmyadmin”用户的密码，错误就消失了。

These are the steps I took:

这些是我采取的步骤：

1. Log in to mysql as root: mysql -uroot -pYOUR_ROOT_PASS
2. Change to the 'mysql' db: use mysql;
3. Update the password for the 'phpmyadmin' user: UPDATE mysql.user SET Password=PASSWORD('YOUR_PASS_HERE') WHERE User='phpmyadmin' AND Host='localhost';
4. Flush privileges: FLUSH PRIVILEGES;

1. 以 root 身份登录 mysql： mysql -uroot -pYOUR_ROOT_PASS
2. 更改为“mysql”数据库： use mysql;
3. 更新“phpmyadmin”用户的密码： UPDATE mysql.user SET Password=PASSWORD('YOUR_PASS_HERE') WHERE User='phpmyadmin' AND Host='localhost';
4. 刷权限： FLUSH PRIVILEGES;

DONE!! It worked for me.

完毕！！它对我有用。

回答by cmueller

grant all on newdb.* to newuser@localhost identified by 'password';

将 newdb.* 上的所有内容授予由“密码”标识的 newuser@localhost；

回答by user2686848

If you're using PHPMyAdmin you have to be logged in as root to be able to change root password. in user put root than leave password blank than change your password.

如果您使用 PHPMyAdmin，您必须以 root 身份登录才能更改 root 密码。在用户中输入 root 而不是将密码留空而不是更改密码。

回答by Kayvan Tehrani

In my case I had just renamed the Mysql user which was going to change his password on a gui based db tool (DbVisualizer). The terminal in which I tried to 'SET PASSWORD' did not work(MySQL Error #1133).

就我而言，我刚刚重命名了 Mysql 用户，该用户将在基于 gui 的数据库工具（DbVisualizer）上更改他的密码。我尝试“设置密码”的终端不起作用（MySQL 错误 #1133）。

However this answerworked for me, even after changing the password the 'SET PASSWORD' command did not work yet.

然而，这个答案对我有用，即使在更改密码后“SET PASSWORD”命令也不起作用。

After closing the terminal and opening new one the command worked very well.

关闭终端并打开新终端后，该命令运行良好。

回答by user3575680

I think the answer is here now : https://bugs.mysql.com/bug.php?id=83822

我想答案就在这里：https: //bugs.mysql.com/bug.php?id=83822

So, you should write :

所以，你应该写：

GRANT ALL PRIVILEGES ON mydb.* to myuser@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'mypassword';

将 mydb.* 上的所有权限授予 myuser@'xxx.xxx.xxx.xxx' 由 'mypassword' 标识；

And i think that could be work :

我认为这可能是工作：

SET PASSWORD FOR myuser@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'old_password' = PASSWORD('new_password');

设置密码为 myuser@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'old_password' = PASSWORD('new_password');