DMS: Illegal mix of collations

0

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` = '<att_null>' 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` = '<att_null>' 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` = '<att_null>' 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?

已提问 10 个月前488 查看次数
2 回答
0

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

profile pictureAWS
专家
已回答 10 个月前
0

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.

已回答 10 个月前
  • See above my update: you can alter tables individually.

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则