How to Fix MySQL Replication Errors Caused by User Host Changes

1. Background

A customer accidentally entered incorrect host information when creating a MySQL user. Subsequently, they performed an update operation on the primary database's mysql.user table to adjust the user's host value. Immediately after this, the replication status of the replica database in the cluster became abnormal. This article will reproduce and resolve the fault.

2. Scenario Reproduction

2.1 Environment Information

• Primary database version: MySQL 8.0.34
• Replica database version: MySQL 8.0.13
• Host information:
  • Primary: 10.186.60.64
  • Replica: 10.186.60.63

2.2 Environment Preparation

Create a test user.

create user test@'10.186.60.63' identified by 'test';
grant all privileges on test.* to test@'10.186.60.63';

3. Simulating the Fault

3.1 Execute UPDATE Operation

mysql> update mysql.user set host='101.86.60.64' where user='test' and host='10.186.60.63';
Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

3.2 Check User Information

Check the user information in the mysql.user table on the primary (10.186.60.64) and find that the update was successful.

mysql> select user , host from mysql.user where user='test';
+------+--------------+
| user | host         |
+------+--------------+
|test | 101.86.60.64 |
+------+--------------+
1 row in set (0.00 sec)

‍

Check the user information in the mysql.user table on the replica (10.186.60.63) and find that it is still the original host address.

mysql> select user , host from mysql.user where user='test';
+------+--------------+
| user | host         |
+------+--------------+
| test | 10.186.60.63 |
+------+--------------+
1 row in set (0.01 sec)

4. Problem Analysis

Get the replication status of the replica (10.186.60.63) and find that Replica_SQL_Running is No, with the following error:

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '6c77afd9-6387-11ef-a8ad-02000aba3c40:413' at primary log mysql-bin.000003, end_log_pos 294043. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Analyze the primary log mysql-bin.000003 on the primary (10.186.60.64) based on the error information and find that the erroneous SQL is the previously executed UPDATE statement.

SET @@SESSION.GTID_NEXT= '6c77afd9-6387-11ef-a8ad-02000aba3c40:413'/*!*/;
# at 293326
#240826 16:51:15 server id 259286858  end_log_pos 293402        Query   thread_id=59    exec_time=0     error_code=0
SET TIMESTAMP=1724662275/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
BEGIN;93402
#240826 16:51:15 server id 259286858  end_log_pos 293505        Rows_query
# update mysql.user set host='101.86.60.64' where user='test' and host='10.186.60.63'
# at 293505
#240826 16:51:15 server id 259286858  end_log_pos 293699        Table_map: `mysql`.`user` mapped to number 62

‍

Check the mysql-error.log on the replica (10.186.60.63) and find the following error:

2024-08-26T16:51:15.781430+08:00 117 [ERROR] [MY-013146] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction '6c77afd9-6387-11ef-a8ad-02000aba3c40:413' at primary log mysql-bin.000003, end_log_pos 294043; Column 0 of table 'mysql.user' cannot be converted from type 'char(255(bytes))' to type 'char(180(bytes) utf8)', Error_code: MY-013146

Based on the error information from the replica, it is determined that the replication failure is due to a difference in the definition of the host field in the mysql.user table between the primary and replica databases. Further inspection of the mysql.user table on both databases reveals that there is indeed a difference in the definition of the host field between MySQL 8.0.34 and MySQL 8.0.13!

The specific definition of the host field in MySQL is as follows:

MySQL 8.0.34

mysql>  show create table mysql.user \G
*************************** 1. row ***************************
    Table: user
Create Table: CREATE TABLE `user` (
`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
....................

‍

MySQL 8.0.13

mysql> show create table mysql.user \G
*************************** 1. row ***************************
    Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
....................

5. Resolution

After confirming that there is a difference in the host field definition, adjust the replica's replica_type_conversions to 'ALL_LOSSY' and restart replication. Observe that replication returns to normal and the user update is successful, then reset replica_type_conversions to empty.

mysql> set global replica_type_conversions='ALL_LOSSY';
Query OK, 0 rows affected (0.00 sec)

mysql> start replica;
Query OK, 0 rows affected (0.12 sec)

mysql> select user , host from mysql.user where user='test';
+------+--------------+
| user | host         |
+------+--------------+
| test | 101.86.60.64 |
+------+--------------+
1 row in set (0.00 sec)

mysql> set global replica_type_conversions='';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@replica_type_conversions;
+--------------------------+
| @@replica_type_conversions |
+--------------------------+
|                          |
+--------------------------+
1 row in set (0.00 sec)

6. Conclusion

Through experimentation, it is confirmed that there is indeed a difference in the definition of the mysql.user table between these two versions of MySQL, and the operation of updating the MySQL user's host information did not comply with operational standards, hence the abnormal replication status of the replica. When adding a replica to a MySQL cluster, it is advisable to choose a version identical to the primary to avoid data replay anomalies on the replica due to version issues. For scenarios involving user host adjustments, the RENAME method can be used to avoid direct DML operations on MySQL system tables, with specific operations detailed below.

7. Suggestions

For scenarios involving modifications to a user's host, an alternative user with the same permissions and password can be created, followed by deletion of the original user. The RENAME USER method can be used to rename the user.

8. Knowledge Supplement

The role of the replica_type_conversions parameter is to adjust the type conversion during primary-replica replication. In addition to the default empty value, other optional values for this parameter include: ALL_LOSSY, ALL_NON_LOSSY, ALL_SIGNED, ALL_UNSIGNED, and the following is MySQL's explanation for each value under different settings:

• ALL_LOSSY: This mode allows type conversions that may result in information loss.
• ALL_NON_LOSSY: This mode allows conversions that do not require truncation or other special handling of the source value; that is, it allows conversions where the target type has a wider range than the source type.
• ALL_LOSSY, ALL_NON_LOSSY: When this mode is set, all supported type conversions are permitted, whether or not they are lossy conversions.
• ALL_SIGNED: Treat promoted integer types as signed values (default behavior).
• ALL_UNSIGNED: Treat promoted integer types as unsigned values.
• ALL_SIGNED, ALL_UNSIGNED: If possible, treat promoted integer types as signed; otherwise, treat them as unsigned.
• Default value (empty): When not set, attribute promotion and demotion are not allowed; this means that all columns in the source and target tables must be of the same type.

‍