replicating from MySQL 8.0 to 5.x, unsupported collation

MySQL 5.7 does not support utfmb4_0900_ai_ci.

Test case:

• src: MySQL 8.0 with collation_server = 'utfmb4_0900_ai_ci'
• dst: MySQL 5.7

[asiroliu]

verify version:
9.9.9.9-master-40fbcbe

step:

1. src MySQL

$ dbdeployer deploy single 8.0 --port 3306 --sandbox-directory sandbox --port-as-server-id --remote-access % --bind-address 0.0.0.0 -c skip-name-resolve -c binlog_format=ROW -c binlog_row_image=FULL -c log_slave_updates=ON --gtid
$ ln -sf /root/opt/mysql/8.0.18/bin/mysql /usr/bin

2. dest MySQL

$ dbdeployer deploy single 5.7 --port 3306 --sandbox-directory sandbox --port-as-server-id --remote-access % --bind-address 0.0.0.0 -c skip-name-resolve -c binlog_format=ROW -c binlog_row_image=FULL -c log_slave_updates=ON --gtid
$ ln -sf /root/opt/mysql/5.7.25/bin/mysql /usr/bin

3. conf MySQL

rm -rf /root/sandboxes/sandbox/data/auto.cnf
bash /root/sandboxes/sandbox/restart
bash /root/sandboxes/sandbox/use -BN -e "select 1"
bash /root/sandboxes/sandbox//use -u root

CREATE USER 'test'@'%' IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;


CREATE USER 'test_src'@'%' IDENTIFIED BY 'test_src';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'test_src'@'%';


CREATE USER 'test_dest'@'%' IDENTIFIED BY 'test_dest';
GRANT ALTER, CREATE, DROP, INDEX, REFERENCES, INSERT, DELETE, UPDATE, SELECT, TRIGGER ON *.* TO 'test_dest'@'%';


vi /etc/my.cnf
[client]
user=test
password=test
socket=/tmp/mysql_sandbox3306.sock

4. data

use test
drop table if exists char_columns;
create table char_columns(id int(11) not null primary key, c_char char(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into char_columns values (0,NULL);
insert into char_columns values (1,'');
insert into char_columns values (2,' ');
insert into char_columns values (3,'a a a ');
insert into char_columns values (4,'abcdefghijklmnopqrstuvwxyz');
insert into char_columns values (5,'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
insert into char_columns values (6,'~`!@#$%^&*()-=_+{}[]|:;<>,.?/');
insert into char_columns values (7,'1234567890');
insert into char_columns values (8,1234567890);
insert into char_columns values (9,'数据库sql测试');
insert into char_columns values (10,'·~！@#￥%……&*（）-=——+{}【】、|；‘：“，。/《》？');
insert into char_columns values (11,0x5061756c);
insert into char_columns values (12,x'4D7953514C');
insert into char_columns values (13,'\0');
insert into char_columns values (14,'\n');
insert into char_columns values (15,'\t');
insert into char_columns values (16,'\r');
insert into char_columns values (17,'\b');
insert into char_columns values (18,'\'');
insert into char_columns values (19,'\"');
insert into char_columns values (20,'''');
insert into char_columns values (21,"""");
insert into char_columns values (22,'\\');