[MYSQL] 用户存在, 但报错 ERROR 1396 (HY000): Operation ALTER USER failed

环境

mysql 5.7.44

搭建主从的时候, 主库导出数据, 从库导入, 执行change master后, start slave 发现报错

Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'e0fd21e2-b69d-11ee-b935-000c29909eff:196' at master log mysql-bin.000092, end_log_pos 428. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Worker 1 failed executing transaction 'e0fd21e2-b69d-11ee-b935-000c29909eff:196' at master log mysql-bin.000092, end_log_pos 428; Error 'Operation ALTER USER failed for 'u1'@'%'' on query. Default database: ''. Query: 'ALTER USER 'u1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*BB47560C5812E23C10150BF9D9862D0CAA484975''

分析

报错是执行alter语句有问题.

人工跑这个语句也是相同的报错, 说明不是主从的问题. 就是执行这个SQL的问题. 但是这个SQL语句本身是没得问题.(主库都执行成功了的). 查看mysql.user表也存在这个用户. 当前是root@localhost, 所以权限也是有的. 当前数据库是5.7.44, 最新版

玄学问题?

其实这是操作不当造成的, 也就是使用 update/insert 等DML 修改用户信息导致的. 官网有类似案例: https://bugs.mysql.com/bug.php?id=97693

解决

使用 FLUSH PRIVILEGES; 刷新权限即可