DMS: Illegal mix of collations

Question

I am running a migration task on AWS DMS. The source is MariaDB 10.5, the target is Aurora Mysql 5.7 compatible.
The full load is working fine, and the ongoing migration (CDC) works well too for a while, until I am running into the following error:

```
SqlState: HY000 NativeError: 1267 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.12-log]Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' [1022502]  (ar_odbc_stmt.c:3929)
```
Example query that causes the error:

```
Failed to prepare statement 'UPDATE `zonnepanelenaanbieders`.`cache` INNER JOIN `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E` ON ( `zonnepanelenaanbieders`.`cache`.`key`=`seg1` AND `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E`.`seq` >= ? and `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E`.`seq` <= ? )  SET `zonnepanelenaanbieders`.`cache`.`value`= CASE  WHEN `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E`.`col1` IS NULL THEN `zonnepanelenaanbieders`.`cache`.`value` WHEN `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E`.`col1` = '' THEN NULL ELSE `col1` END ,`zonnepanelenaanbieders`.`cache`.`key`= CASE  WHEN `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E`.`col2` IS NULL THEN `zonnepanelenaanbieders`.`cache`.`key` WHEN `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E`.`col2` = '' THEN NULL ELSE `col2` END ,`zonnepanelenaanbieders`.`cache`.`expiration`= CASE  WHEN `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E`.`col3` IS NULL THEN `zonnepanelenaanbieders`.`cache`.`expiration` WHEN `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E`.`col3` = '' THEN NULL ELSE  CAST( `awsdms_control`.`awsdms_changes4F4AFCC22FD4BD3E`.`col3` as  SIGNED)  END ' [1022502]  (odbc_endpoint_imp.c:4542)
```

It seems that the temporary tables that DMS creates seem to use the wrong collation type. Is there anything that I can do to fix this, without changing our table structure/collations?

Answer

Thank you for your answer, but I think the problem is that we have different collations per table. Most tables don't have this issue, and others do. The temp tables that DMS is creating should have the same collation as the target table, that will resolve the issue I think. I just don't know how to do that, or if it's even possible.

Update:
The collation of the source and target tables is already the same. It's the temp table that DMS creates that falls back to the default for the awsdms_control database. I can change that one of course, but as we use a few different collations for different tables, there will always be conflicts I think.

Answer

Hi,

can you try to set the default collation of your target database identical to source database  via https://dev.mysql.com/doc/refman/8.0/en/charset-database.html ?

That may help DMS to also use 1 same collation for temporary tables and solve your problems.

If you really need the current target collation, you can re-alter you target db with it after DMS migration is complete.

If the above doesn't work, you will probably have to open a support ticket from your AWS account.

Hope it helps

Didier

DMS: Illegal mix of collations

Relevant content