I have a MySQL 8.016 master and a MariaDB 10.2 slave, both running on AWS RDS. This was working perfectly until today, when I figured I wanted to do chain replication instead, like this
MySQL 8.016 -> MySQL 8.016 -> MariaDB 10.2
However, after changing master on my MariaDB instance, I got the dreaded Error 'Character set '#255' is not a compiled character set
error. I then tried changing back to the old master, but I still got this error! I had this problem when I first got my setup to work a few months back, and the first time, I solved it by purging the utf8mb4_0900_ai_ci
collation from my MySQL instances, and as far as I can see, they are still purged.
Output from my MySQL 8.016 master instance
mysql> SHOW GLOBAL VARIABLES LIKE '%char%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/mysql-8.0.16.R1/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0,04 sec)
mysql> SHOW GLOBAL VARIABLES LIKE '%collat%'; +-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+
4 rows in set (0,03 sec)
Output from my MariaDB 10.2 slave instance
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.186
Master_User: mariadb_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-changelog.022828
Read_Master_Log_Pos: 532651
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 433
Relay_Master_Log_File: mysql-bin-changelog.022827
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.rds_sysinfo,innodb_memcache.cache_policies,mysql.rds_replication_status,mysql.plugin,mysql.rds_history,innodb_memcache.config_options
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 '/rdsdbbin/mariadb-10.2.21.R1/share/charsets/Index.xml' file' on query. Default database: 'magnublo_scraping'. Query: 'BEGIN'
Skip_Counter: 0
Exec_Master_Log_Pos: 124
Relay_Log_Space: 3150873
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 '/rdsdbbin/mariadb-10.2.21.R1/share/charsets/Index.xml' file' on query. Default database: 'magnublo_scraping'. Query: 'BEGIN'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 36970531
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
1 row in set (0,04 sec)
Is there some variable I could be missing?
Best Answer
This could be a serious problem when replicating between MySQL 8.0 and MariaDB 10.x.
The default (for good technical reasons)
COLLATION
for the 8.0 isutf8mb4_0900_ai_ci
. Note the "255" associated with it. MariaDB has not yet adopted the Unicode 9.0 collations.Furthermore, Oracle (MySQL 8.0) did a major rewrite of the collation code, thereby possibly making collation tables incompatible.
A probable fix is to switch to the next best general-purpose collation,
utf8mb4_unicode_520_ci
(246) (based on Unicode 5.20). This would requireALTERing
all the columns' collations.ALTER TABLE .. CONVERT TO ..
might be the fastest way. Those could be generated via aSELECT .. information_schema.tables ...
.8.0.17:
MariaDB 10.2.30: