Replication from percona 8.0.34 to Mariadb 10.232 & Mariadb 10.6

[anjaneyarajus]

we are testing the migration from Mariadb to Percona 8.0.34. i was able to setup replication between Mariadb (source) to Percona 8.0.34.

To test rollback plan, I have setup replication between percona8.0.34 to Mariadb 10.2.32 and Mariadb 10.6.16.

try to drop the test database from percona 8.0.34 that was replicated from mariadb 10.2.32

Replication was broken with below error on both Mariadb 10.2.32 and 10.6.16,
Last_SQL_Error: Error ‘Character set ‘#255’ is not a compiled character set and is not specified in the ‘/usr/share/mysql/charsets/Index.xml’ file’ on query. Default database: ‘test’. Query: ‘drop database if exists test’

is there a fix to make replication compatible by changing character set on percona.

character set on 10.2.32
±---------±----------------------------±--------------------±-------+
| Charset | Description | Default collation | Maxlen |
±---------±----------------------------±--------------------±-------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
±---------±----------------------------±--------------------±-------+

on mariadb 10.6.16
±---------±----------------------------±--------------------±-------+
| Charset | Description | Default collation | Maxlen |
±---------±----------------------------±--------------------±-------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
±---------±----------------------------±--------------------±-------+

[matthewb]

Hello @anjaneyarajus,

@anjaneyarajus

To test rollback plan, I have setup replication between percona8.0.34 to Mariadb 10.2.32 and Mariadb 10.6.16

As you discovered, you cannot replicate this way because MariaDB does not understand the newer UTF character sets and collations found in MySQL 8.0. You will need to ensure that all tables, all columns, are latin1 or are using the generic utf8 and not the specific utf8mb4 found in MySQL 8 by default. You can find several blogs about this.

You are not the first person to experience this issue. For the most part, upgrading to MySQL 8.0 has no rollback plan other than restore from backup. It’s been this way for years. Once you get to 8, you must stay there.

[anjaneyarajus]

Hi Mathew,

the tables that are present on percona were replicated from mariadb itself. None of the tables were utf8mb4, they are utf8mb3 after they were replicated to percona.

before loading data from mariadb to percona, if we change below values on percona to values similar mariadb, will the reverse replication work?

character_set_client
character_set_connection
character_set_database
character_set_results
character_set_server

[kedarpercona]

Hi @anjaneyarajus I’ll drag you to MariaDB documentation which should clear any further doubts.

Here: MariaDB versus MySQL - Compatibility - MariaDB Knowledge Base

MariaDB Server cannot replicate from a MySQL 8.0 primary server, because MySQL 8.0 has a binary log format that is incompatible.

Thanks,
K

[anjaneyarajus]

This would cause issues when migrating from MariaDB to Percona. For any critical services, we require a rollback plan in case of problems after a few days of cut-over. It will be impossible to rollback a larger database using mysqlump/mydumper, which requires massive downtime.

[kedarpercona]

That’s right @anjaneyarajus
…and that’s why you have to be absolutely sure and should do through testing… at times you need to be tricky handling such migrations.
If you want a “cutover” you might do a “blue-green” like deployment and rollback would be to point back to the original MariaDB including figuring out how would you get back the writes for the duration.
TL;DR you have to take a leap of faith in your testing abilities and preparedness

[matthewb]

@anjaneyarajus Even within the 8.0 series, you cannot rollback between patch versions. As @kedarpercona has said, you must do all possible testing beforehand. Know that you are ready.

You could put ProxySql between app and db and use proxysql’s mirroring feature to duplicate queries to the different writers.

[kedarpercona]

and re:proxysql mirroring… I’ve an interesting read

# How to replicate without binary logs in MySQL

[anjaneyarajus]

Hi Kedar,

It is an interesting blog; I will definitely check it out.
Adding a MySQL 5.7 server between Percona and MariaDB to replicate data, will that work?

mysql 8 —> mysql 5.7 —> mariadb

[kedarpercona]

mysql 8 —> mysql 5.7 → NO.
mysql 5.7 —> mariadb → You better test but have no hopes.