操作 MySQL 用户表时出错：故障排除 ERROR 1396

原创谭磊Terry 恩墨学院 2022-10-30

1256

几周前，我们在托管服务客户端的复制环境中遇到了一个问题：

LAST_ERROR_MESSAGE：Worker 2在主 binlog.0012345，end_log_pos 98765 处执行事务“UUID：GTID”失败；查询时出现错误“ ‘test_user’@'10.10.10.10”的操作 CREATE USER 失败。默认数据库：‘mysql’。查询：'CREATE USER ‘test_user’@‘10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘************’

经过初步调查，我们注意到副本中的用户不存在！MySQL 疯了吗？但是后来客户提到他们在能够成功执行查询之前在主数据库中出现以下错误：

root@localhost [mysql]> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY '************' WITH MAX_USER_CONNECTIONS 10;
ERROR 1396 (HY000): Operation CREATE USER failed for 'test_user'@'10.10.10.10
复制

但是我们知道第一个命令无法复制（因为失败的命令永远不会记录到 binlog 中），所以问题是，发生了什么，为什么 MySQL 在创建不存在的用户时遇到问题？

如果您进行快速研究，您会发现一些帖子提供了一些选项：

执行 FLUSH PRIVILEGES。
DROP USER 然后 FLUSH PRIVILEGES。
许多人提到这是一个报告的错误。
公平地说，如果您尝试上述选项，您最终会解决问题。尽管如此，我还是想更深入地了解为什么会发生这种情况以及修复它的正确方法应该是什么（并了解为什么会修复它）；所以我决定做一些测试并为将来遇到此类问题的人创建一个帖子，希望您也能理解它。

让我们从一个提醒开始：你不应该手动操作mysql 授权表，如果你这样做，你应该确保你知道你在做什么。因此，首先，这里总结一下 MySQL 的权限是如何工作的。

MySQL权限总结

在启动时，MySQL 读取授权表并将它们加载到内存中，因此当它需要检查用户是否被允许读取或连接到那里时，MySQL 可以更快地查找权限（从内存中读取）。

MySQL 使用授权表跟踪权限，因此当我们在内部发出 CREATE USER 时，它会转换为

插入 mysql.user
插入 mysql.db

如果我们 GRANT 或 REVOKE，它将转化为

插入 mysql.table_priv
在 mysql.db 中更新
在 mysql.table_priv 中删除。
等等。请注意，这不是关于所发生情况的确切分步说明；这只是 MySQL 需要执行的一些内部事情的一个例子。

最后，在每个Account Management Statement之后，MySQL 将新权限读入内存，并应用更改。

有了这个，MySQL 让我们可以很容易地操作权限；如果您需要删除用户，而不是手动删除每个授权表中的每一行，您可以执行 DROP USER，仅此而已。

尽管这种方式更简单，但有时用户决定采用“硬方式”并手动操作权限：而不是 DROP USER，而是从授权表中执行 DELETE。但是，如前所述，MySQL 不会知道这些更改，因为授权表已经被读取，并且权限在内存中。

让我们解决上面提到的问题

第一步：在主服务器中创建用户：

root@localhost [(none)]> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY '<redacted>';
Query OK, 0 rows affected (0.00 sec)
复制

第二步：经过一些测试，他们想删除用户并重新创建它，但他们手动删除了用户：

root@localhost [(none)]> use mysql
Database changed

root@localhost [mysql]> delete from user where user='test_user';
Query OK, 1 row affected (0.00 sec)

root@localhost [mysql]> delete from db where user='test_user';
Query OK, 1 row affected (0.00 sec)
复制

到目前为止，如上所述，MySQL 不知道用户test_user已被删除（MySQL 没有将授权表重新读入内存，因为它们没有执行 FLUSH PRIVILEGES）；这就是为什么当他们再次尝试创建用户时，它失败了。

root@localhost [mysql]> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY '<redacted>' WITH MAX_USER_CONNECTIONS 10;
ERROR 1396 (HY000): Operation CREATE USER failed for 'test_user'@'10.10.10.10'
复制

由于他们找不到用户，他们做了我们都会做的事情：再试一次，希望这次命令能神奇地工作。

root@localhost [mysql]> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY '<redacted>';
Query OK, 0 rows affected (0.01 sec)
复制

它奏效了，创建了用户，世界上的一切又恢复了。为什么它起作用了？因为使用第一个 CREATE USER（即使命令失败），MySQL 重新加载了授予权限。

这是第一个 CREATE 发生的情况：

1.MySQL 检查它的内存表并发现用户已经存在。
2.拒绝新用户，因为它不能复制用户。
3.MySQL 重新加载内存表（无论是否成功都会发生这种情况）。

因此，即使命令失败，MySQL 也会重新加载内存表并且用户test_user不存在（因为之前手动删除了用户）；这就是第二次 CREATE 成功的原因。

第二个 CREATE USER 成功执行，它进入 binlog 和副本。

现在，从副本的角度来看，到目前为止它已经复制了 DELETE 语句和 ONE CREATE USER，然后回到一切开始的地方，副本中的错误：

LAST_ERROR_MESSAGE: Worker 2 在 master binlog.0012345, end_log_pos 98765 处执行事务 ‘UUID:GTID’ 失败；查询时出现错误“‘test_user’@'10.10.10.10 的操作 CREATE USER 失败”。默认数据库：‘mysql’。查询：'CREATE USER ‘test_user’@‘10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘************’

有了上面的解释，我认为这里发生的事情更容易理解：这个 CREATE 语句是“第一个”，因为主节点中的第一个失败，所以一个没有到达副本，所以要“修复”这个我们只开始了复制，就成功了，这次 CREATE 工作了，因为它已经重新加载了内存表。

在完成之前，我想测试更多的东西，以了解为什么有时一个简单的 FLUSH PRIVILEGES 有效，以及为什么有时需要完全 DROP USER；简短的回答：这取决于您手动操作授权表的“好坏”程度。

当 FLUSH PRIVILEGES 成功时

mysql> CREATE USER test123@10.10.10.10;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT user,host FROM mysql.user WHERE user='test123';
+------------------+-------------+
| user             | host        |
+------------------+-------------+
| test123          | 10.10.10.10 |
+------------------+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM mysql.user WHERE user='test123';
Query OK, 1 row affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER test123@10.10.10.10;
Query OK, 0 rows affected (0.01 sec)
复制

由于create user只插入到mysql.user中，是该用户存在的唯一表，所以flushprivileges命令成功。

当 FLUSH PRIVILEGES 不够时

授权表有很多，因此在手动删除用户时，很容易错过其中一个，并导致 FLUSH PRIVILEGES 无法解决的错误。

mysql> CREATE USER test123@10.10.10.10;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON test_db.* TO test123@10.10.10.10;
Query OK, 0 rows affected (0.00 sec)
复制

GRANT 命令也在 mysql.db 表中插入一条记录

mysql> SELECT user,host FROM mysql.user WHERE user='test123';
+---------+-------------+
| user    | host        |
+---------+-------------+
| test123 | 10.10.10.10 |
+---------+-------------+
1 row in set (0.00 sec)

mysql> SELECT user,host,db FROM mysql.db WHERE user='test123';
+---------+-------------+---------+
| user    | host        | db      |
+---------+-------------+---------+
| test123 | 10.10.10.10 | test_db |
+---------+-------------+---------+
1 row in set (0.00 sec)
复制

然后，如果我手动删除，但仅从 mysql.user 表中删除，则 mysql.db 中的记录将一直存在，直到它被清除。

mysql> DELETE FROM mysql.user WHERE user='test123';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT user,host FROM mysql.user WHERE user='test123';
Empty set (0.00 sec)

mysql> SELECT user,host,db FROM mysql.db WHERE user='test123';
+---------+-------------+---------+
| user    | host        | db      |
+---------+-------------+---------+
| test123 | 10.10.10.10 | test_db |
+---------+-------------+---------+
1 row in set (0.00 sec)
复制

所以即使我尝试使用 FLUSH 命令，我也无法创建用户（由于用户存在于 mysql.db 中）

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
 
mysql> CREATE USER test123@10.10.10.10;
ERROR 1396 (HY000): Operation CREATE USER failed for 'test123'@'10.10.10.10'
 
mysql> CREATE USER test123@10.10.10.10;
ERROR 1396 (HY000): Operation CREATE USER failed for 'test123'@'10.10.10.10'
 
mysql> CREATE USER test123@10.10.10.10;
ERROR 1396 (HY000): Operation CREATE USER failed for 'test123'@'10.10.10.10'
复制

执行此操作的正确方法是执行 DROP 命令并让 MySQL 清理所需的任何表（您可以在下面看到 mysql.db 表也已清理）。

mysql> DROP USER test123@10.10.10.10;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user,host,db FROM mysql.db WHERE user='test123';
Empty set (0.00 sec)
复制

现在 CREATE 起作用了：

mysql> CREATE USER test123@10.10.10.10;
Query OK, 0 rows affected (0.00 sec)
复制

“如果不存在”条款呢？

使用与复制相同的场景；首先，在主节点中创建用户。

mysql> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY  '<redacted>';
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT user,host FROM mysql.user WHERE user='test_user';
+-----------+-------------+
| user      | host        |
+-----------+-------------+
| test_user | 10.10.10.10 |
+-----------+-------------+
1 row in set (0.00 sec)
复制

在此之后，从 mysql.user 表中手动删除用户。

mysql> DELETE FROM mysql.user WHERE user='test_user';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT user,host FROM mysql.user WHERE user='test_user';
Empty set (0.00 sec)
复制

我们知道 CREATE USER 命令会失败并重新加载内存表；让我们看看如果我们添加 IF NOT EXISTS 子句会发生什么：

mysql> CREATE USER IF NOT EXISTS 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY '<redacted>';
Query OK, 0 rows affected, 1 warning (0.00 sec)
复制

没有错误，但有一个警告：

mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------------------+
| Level | Code | Message                                                    |
+-------+------+------------------------------------------------------------+
| Note  | 3163 | Authorization ID 'test_user'@'10.10.10.10' already exists. |
+-------+------+------------------------------------------------------------+
1 row in set (0.00 sec)
复制

并且用户不存在，正如预期的那样（它被手动删除，但没有 FLUSH PRIVILEGES 所以 MySQL 不知道）。

mysql> SELECT user,host FROM mysql.user WHERE user='test_user';
Empty set (0.00 sec)
复制

但是，这次命令记录在 binlog 中：

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 2395 LIMIT 2\G
*************************** 1. row ***************************
  Log_name: mysql-bin.000001
       Pos: 2395
Event_type: Gtid
 Server_id: 1
End_log_pos: 2474
      Info: SET @@SESSION.GTID_NEXT= '83988545-3051-11ed-b2af-0a3d309b4fdf:8'
*************************** 2. row ***************************
  Log_name: mysql-bin.000001
       Pos: 2474
Event_type: Query
 Server_id: 1
End_log_pos: 2712
      Info: use `mysql`; CREATE USER IF NOT EXISTS 'test_user'@'10.10.10.10' IDENTIFIED WITH 'mysql_native_password' AS '<redacted>' /* xid=68 */
2 rows in set (0.00 sec)
复制

并且复制也没有问题，以下是中继日志事件：

mysql> SHOW RELAYLOG EVENTS IN 'relay-log-server.000002' FROM 2611\G
*************************** 1. row ***************************
  Log_name: relay-log-server.000002
       Pos: 2611
Event_type: Gtid
 Server_id: 1
End_log_pos: 2474
      Info: SET @@SESSION.GTID_NEXT= '83988545-3051-11ed-b2af-0a3d309b4fdf:8'
*************************** 2. row ***************************
  Log_name: relay-log-server.000002
       Pos: 2690
Event_type: Query
 Server_id: 1
End_log_pos: 2712
       Info: use `mysql`; CREATE USER IF NOT EXISTS 'test_user'@'10.10.10.10' IDENTIFIED WITH 'mysql_native_password' AS '<redacted>' /* xid=68 */
复制

最后要记住的是，这次Account Management Statement在执行后没有重新加载授权表（我们可以知道这是因为第二次执行没有创建用户，就像在没有子句的情况下执行 CREATE USER 时发生的那样，请参阅下面的警告）：

mysql> CREATE USER IF NOT EXISTS 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY '<redacted>';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE USER IF NOT EXISTS 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY '<redacted>';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE USER IF NOT EXISTS 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY '<redacted>';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT user,host FROM mysql.user WHERE user='test_user';
Empty set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER IF NOT EXISTS 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY '<redacted>';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user,host FROM mysql.user WHERE user='test_user';
+-----------+-------------+
| user      | host        |
+-----------+-------------+
| test_user | 10.10.10.10 |
+-----------+-------------+
1 row in set (0.00 sec)
复制

因此，IF NOT EXISTS 子句可以使您免于错误（并且在复制环境中也没有问题），但与往常一样，请务必检查警告以了解正在发生的事情，否则，您仍然没有用户需要。

最后的想法

当您想在 MySQL 中修改权限/用户时，您绝对应该使用 Account Management Statements，因此您不必担心所有细节。

我想与您分享这个特定场景，希望这将使 MySQL 如何管理内部权限更加清晰，并且如果您手动修改任何内容，内存中的表是至关重要的事情。

最后，请记住文档是您的朋友：

如果您直接使用 INSERT、UPDATE 或 DELETE 等语句（不推荐）修改授权表，则这些更改不会影响权限检查，直到您告诉服务器重新加载表或重新启动它。因此，如果您直接更改授权表但忘记重新加载它们，则这些更改在您重新启动服务器之前无效。这可能会让您想知道为什么您的更改似乎没有任何区别！

原文标题：When Manipulating MySQL User Tables Goes Wrong: Troubleshooting ERROR 1396
原文作者：Mauricio Cacho
原文地址：https://www.percona.com/blog/when-manipulating-mysql-user-tables-goes-wrong-troubleshooting-error-1396/

墨力翻译 mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者