Bug #116834 Replication between MySQL 8.0.35 (master) and MySQL 5.7.44 (slave) fails due to an incompatibility with character sets.
Submitted: 2 Dec 2024 7:08 Modified: 2 Dec 2024 8:34
Reporter: bear chen Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:8.0.35 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: cross-version replication, MySQL Replication, Replication Failure

[2 Dec 2024 7:08] bear chen 
Description:
Replication between MySQL 8.0.35 (master) and MySQL 5.7.44 (slave) fails due to an incompatibility with character sets. 

How to repeat:
Reproduction Environment:
Primary Node: MySQL 8.0.35
Replica Node: MySQL 5.7.44

Steps to Reproduce:
1. Reset master and check master status:
```sql
reset master;
show master status;
```

2. Configure the Slave to Start Replication:
```sql
change master to master_host='192.168.210.154',master_user='repl',master_password='repl',Master_Port=3306,master_auto_position=1;
```

3. Execute the following statement on the MySQL 8.0.35 primary node:
```sql
USE `db01`;
SET autocommit=1,
    transaction_isolation=2,
    character_set_client=utf8mb4,
    character_set_connection=utf8mb4,
    character_set_results=utf8mb4,
    collation_connection=utf8mb4_general_ci;
CREATE TABLE t1 (id int)
```

4. Observe the replication status on the MySQL 5.7.44 replica node:
```sql
show slave status\G
```

On the slave MySQL 5.7.44 instance, replication fails with the following error message:
```
Last_SQL_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/local/mysql-5.7.44-linux-glibc2.12-x86_64/share/charsets/Index.xml' file' on query. Default database: 'db01'. Query: 'create table t1 (id int)'
```

Detail:
Primary node:
hotdb_datasource@192.168.210.154:(none) 8.0.35 02:45:18> use `db01`;SET autocommit=1,transaction_isolation=2,character_set_client=utf8mb4,character_set_connection=utf8mb4,character_set_results=utf8mb4,collation_connection=utf8mb4_general_ci
Database changed
    -> ;
Query OK, 0 rows affected (0.00 sec)

hotdb_datasource@192.168.210.154:db01 8.0.35 02:45:21> create table t1 (id int);
Query OK, 0 rows affected (0.07 sec)

hotdb_datasource@192.168.210.154:db01 8.0.35 02:45:29> select version();
+-----------+
| version() |
+-----------+
| 8.0.35    |
+-----------+
1 row in set (0.00 sec)

hotdb_datasource@192.168.210.154:db01 8.0.35 02:47:28> 

Replica node:
root@localhost:(none) 5.7.44-log 02:45:24> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.210.154
                  Master_User: repl
                  Master_Port: 3202
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 346
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 370
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 22
                   Last_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/local/mysql-5.7.44-linux-glibc2.12-x86_64/share/charsets/Index.xml' file' on query. Default database: 'db01'. Query: 'create table t1 (id int)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 157
              Relay_Log_Space: 766
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 22
               Last_SQL_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/local/mysql-5.7.44-linux-glibc2.12-x86_64/share/charsets/Index.xml' file' on query. Default database: 'db01'. Query: 'create table t1 (id int)'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2101543202
                  Master_UUID: 608cba30-8f9b-11ef-bc0d-566f4b8d0087
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 241202 14:45:52
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 608cba30-8f9b-11ef-bc0d-566f4b8d0087:1
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Suggested fix:
Replication between MySQL 8.0.35 (master) and MySQL 5.7.44 (slave) should work without issues, and there should be no replication errors related to character set configuration.
[2 Dec 2024 7:15] bear chen 
How to repeat -> step2 add:
```sql
start slave;
```
[2 Dec 2024 8:34] MySQL Verification Team 
> Replication between MySQL 8.0.35 (master) and MySQL 5.7.44 (slave) fails due to an incompatibility with character sets. 

I am sorry but this is not supported. You can do it the other way around, run 5.7 master to 8.0 slave but vice versa is not possible. Doing it from 8.0 to 5.7 would require 5.7 to support a lot of changes introduced in new versions.

You can read more about replication compatibility here:
https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-compatibility.html

Thank you for using MySQL