Resolving MySQL Replication Failure Caused by Host Field Mismatch in mysql.user

Background

When a client created a MySQL user, they mistakenly entered an incorrect host value and later updated the mysql.user table on the master to adjust the host. The slave replication status then failed, and the article reproduces and resolves the issue.

Scenario Reproduction

Environment Information

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';

Simulating the Fault

Execute UPDATE

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

Check User Information

On the master the host was updated successfully:

mysql> select user, host from mysql.user where user='test';
+------+--------------+
| user | host         |
+------+--------------+
| test | 101.86.60.64 |
+------+--------------+

On the slave the host remained unchanged:

mysql> select user, host from mysql.user where user='test';
+------+--------------+
| user | host         |
+------+--------------+
| test | 10.186.60.63 |
+------+--------------+

Problem Analysis

The slave shows Slave_SQL_Running = No with an error indicating a type conversion problem in mysql.user . The root cause is a difference in the definition of the host column between MySQL 8.0.34 (char(255) ascii) and MySQL 8.0.13 (char(60) utf8_bin).

Host Field Definitions

MySQL 8.0.34

CREATE TABLE `user` (
  `Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT ''
  ...
);

MySQL 8.0.13

CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT ''
  ...
);

Solution

After confirming the host field mismatch, set slave_type_conversions='ALL_LOSSY' on the slave, restart replication, verify the update, and then reset the parameter to its default value.

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

mysql> start slave;
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 |
+------+--------------+

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

mysql> select @@slave_type_conversions;
+--------------------------+
| @@slave_type_conversions |
+--------------------------+
|                          |
+--------------------------+

Conclusion

The host column definition differs between MySQL versions, causing replication failures when directly updating mysql.user .

Use matching MySQL versions for master and slaves to avoid such issues.

Prefer using RENAME USER or creating a new user instead of modifying system tables directly.

Recommendations

Create a replacement user with the same privileges and delete the old one.

Use RENAME USER for host changes.

Additional Knowledge

slave_type_conversions Parameter

Controls type conversion behavior during replication. Values include:

ALL_LOSSY – allows conversions that may lose data.

ALL_NON_LOSSY – allows only safe conversions.

ALL_SIGNED, ALL_UNSIGNED – affect integer sign handling.

Default (empty) – requires identical column types.

RENAME USER Considerations

When renaming users, custom objects (procedures, triggers, etc.) retain their original DEFINER, which may cause permission issues if the original user is dropped. Update DEFINER fields or use ALTER PROCEDURE ... SQL SECURITY INVOKER to mitigate.

Reference

slave_type_conversions parameter: MySQL Documentation