Error 'Character set '#255' is not a compiled ch aracter set and is not specified in the '/usr/local/mariadb10/share/mysql/charse ts/Index.xml' file' on query. Default database: 'wordpress'. Query: 'BEGIN'

以前通过 家里ADSL上网无固定外网IP的群晖NAS安全实现与公网MySQL服务器主从同步 配置之后,群晖自带的 MariaDB 10.3.21 可以非常流畅的与服务器上的 MySQL 7.x 版本进行主从同步。

前两天系统从 ubuntu 18.04 升级到 ubuntu 20.04 (MySQL 8.x)之后,发现已经无法进行主从同步。

报告如下错误:

网上查询很久,找到原因 MySQL 8.016 master with MariaDB 10.2 slave on AWS RDS, Character set '#255' is not a compiled character set

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 is utf8mb4_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.

probable fix is to switch to the next best general-purpose collation, utf8mb4_unicode_520_ci (246) (based on Unicode 5.20). This would require ALTERing all the columns' collations. ALTER TABLE .. CONVERT TO .. might be the fastest way. Those could be generated via a SELECT .. information_schema.tables ....

大致原因就是MySql 8.0默认使用了最新的utf8mb4_0900_ai_ci字符集,然而MariaDB 10.3.x版本不支持这个字符集,导致无法同步。

8.0.17:

MariaDB 10.2.30:

目前暂时没打算修改主服务器上的数据库,暂时等 MariaDB 更新吧。

参考链接


发布者

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注