We have a MySQL 8.0.28 RDS instance. It was originally MySQL 5.6 (may have been MySQL 5.5 also, I do not remember exactly) which was upgraded to 8.0 in multiple steps as and when new MySQL versions were released. We want to create a replica of this in another AWS account.

We created a snapshot, transferred it to the other account and started a new instance from the snapshot there. We then tried to connect that new instance to the primary db using mysql.rds_set_external_master. However, a call to this fails with the message ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'regexp_like' The exact same steps were working fine when we were on MySQL 5.7.

The following information may be helpful:

mysql> show variables like "%collation%";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_unicode_ci |
| collation_database            | utf8mb4_unicode_ci |
| collation_server              | utf8mb4_unicode_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

mysql> SHOW PROCEDURE STATUS LIKE 'rds_set_external_master'\G
*************************** 1. row ***************************
                  Db: mysql
                Name: rds_set_external_master
                Type: PROCEDURE
             Definer: rdsadmin@localhost
            Modified: 2022-04-05 04:57:07
             Created: 2022-04-05 04:57:07
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_unicode_ci
1 row in set (0.00 sec)

Any ideas on how we can fix this error and create the replica in another account will be appreciated.