Troubleshooting

The following sections provide examples of common issues and error messages to help you troubleshoot heterogenous DMS migrations.

Troubleshooting MySQL-specific issues

The following issues are specific to using AWS DMS with MySQL databases.

Topics

• CDC Task Failing for Amazon RDS DB Instance Endpoint Because Binary Logging Disabled

• Connections to a target MySQL instance are disconnected during a task

• Adding Autocommit to a MySQL-compatible Endpoint

• Disable Foreign Keys on a Target MySQL-compatible Endpoint

• Characters Replaced with Question Mark

• "Bad event" Log Entries

• Change Data Capture with MySQL 5.5

• Increasing Binary Log Retention for Amazon RDS DB Instances

• Log Message: Some changes from the source database had no impact when applied to the target database.

• Error: Identifier too long

• Error: Unsupported Character Set Causes Field Data Conversion to Fail

• Error: Codepage 1252 to UTF8 [120112] A field data conversion failed

CDC task failing for Amazon RDS DB instance endpoint because binary logging is disabled

This issue occurs with Amazon RDS DB instances because automated backups are disabled. Enable automatic backups by setting the backup retention period to a non-zero value.

Connections to a target MySQL instance are disconnected during a task

If you have a task with LOBs that is getting disconnected from a MySQL target with the following type of errors in the task log, you might need to adjust some of your task settings.

[TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 
2013 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.16-log]Lost connection 
to MySQL server during query [122502] ODBC general error.

To solve the issue where a task is being disconnected from a MySQL target, do the following:

• Check that you have your database variable max_allowed_packet set large enough to hold your largest LOB.

• Check that you have the following variables set to have a large timeout value. AWS suggests you use a value of at least five minutes for each of these variables.

• net_read_timeout

• net_write_timeout

• wait_timeout

• interactive_timeout

Adding Autocommit to a MySQL-compatible endpoint

To add autocommit to a target MySQL-compatible endpoint, use the following procedure:

1. Sign in to the AWS Management Console and select DMS.

2. Select Endpoints.

3. Select the MySQL-compatible target endpoint that you want to add autocommit to.

4. Select Modify.

5. Select Advanced, and then add the following code to the Extra connection attributes text box:

   Initstmt= SET AUTOCOMMIT=1

6. Choose Modify.

Disable foreign keys on a target MySQL-compatible endpoint

You can disable foreign key checks on MySQL by adding the following to the Extra Connection Attributes in the Advanced section of the target MySQL, Amazon Aurora with MySQL compatibility, or MariaDB endpoint.

To disable foreign keys on a target MySQL-compatible endpoint, use the following procedure:

1. Sign in to the AWS Management Console and select DMS.

2. Select Endpoints.

3. Select the MySQL, Aurora MySQL, or MariaDB target endpoint that you want to disable foreign keys.

4. Select Modify.

5. Select Advanced, and then add the following code to the Extra connection attributes text box:

   Initstmt=SET FOREIGN_KEY_CHECKS=0

6. Choose Modify.

Characters replaced with question mark

The most common situation that causes this issue is when the source endpoint characters have been encoded by a character set that AWS DMS doesn't support. For example, AWS DMS engine versions prior to version 3.1.1 don't support the UTF8MB4 character set.

Bad event log entries

Bad event entries in the migration logs usually indicate that an unsupported DDL operation was attempted on the source database endpoint. Unsupported DDL operations cause an event that the replication instance cannot skip so a bad event is logged. To fix this issue, restart the task from the beginning, which will reload the tables and will start capturing changes at a point after the unsupported DDL operation was issued.

Change data capture with MySQL 5.5

AWS DMS change data capture (CDC) for Amazon RDS MySQL-compatible databases requires full image row-based binary logging, which is not supported in MySQL version 5.5 or lower. To use AWS DMS CDC, you must up upgrade your Amazon RDS DB instance to MySQL version 5.6.

Increasing binary log retention for Amazon RDS DB instances

AWS DMS requires the retention of binary log files for change data capture. To increase log retention on an Amazon RDS DB instance, use the following procedure. The following example increases the binary log retention to 24 hours.

call mysql.rds_set_configuration('binlog retention hours', 24);
      

Log message: Some changes from the source database had no impact when applied to the target database

When AWS DMS updates a MySQL database column’s value to its existing value, a message of zero rows affected is returned from MySQL. This behavior is unlike other database engines such as Oracle and SQL Server that perform an update of one row, even when the replacing value is the same as the current one.

Error: Identifier too long

The following error occurs when an identifier is too long:

TARGET_LOAD E: RetCode: SQL_ERROR SqlState: HY000 NativeError: 
1059 Message: MySQLhttp://ODBC 5.3(w) Driverhttp://mysqld-5.6.10Identifier 
name '<name>' is too long 122502 ODBC general error. (ar_odbc_stmt.c:4054)        

When AWS DMS is set to create the tables and primary keys in the target database, it currently does not use the same names for the Primary Keys that were used in the source database. Instead, AWS DMS creates the Primary Key name based on the tables name. When the table name is long, the auto-generated identifier created can be longer than the allowed limits for MySQL. To solve this issue, prepare the tables and Primary Keys in the target database and use a task with the task setting Target table preparation mode set to Do nothing or Truncate to populate the target tables.

Error: Unsupported character set causes field data conversion to fail

The following error occurs when an unsupported character set causes a field data conversion to fail:

"[SOURCE_CAPTURE ]E: Column '<column name>' uses an unsupported character set [120112] 
A field data conversion failed. (mysql_endpoint_capture.c:2154)            

This error often occurs because of tables or databases using UTF8MB4 encoding. AWS DMS engine versions prior to 3.1.1 don't support the UTF8MB4 character set. In addition, check your database's parameters related to connections. The following command can be used to see these parameters:

SHOW VARIABLES LIKE '%char%';
      

Error: Codepage 1252 to UTF8 [120112] A field data conversion failed

The following error can occur during a migration if you have non codepage-1252 characters in the source MySQL database.

[SOURCE_CAPTURE ]E: Error converting column 'column_xyz' in table
'table_xyz with codepage 1252 to UTF8 [120112] A field data conversion failed. 
(mysql_endpoint_capture.c:2248)             

As a workaround, you can use the CharsetMapping extra connection attribute with your source MySQL endpoint to specify character set mapping. You might need to restart the AWS DMS migration task from the beginning if you add this extra connection attribute.

For example, the following extra connection attribute could be used for a MySQL source endpoint where the source character set is utf8 or latin1. 65001 is the UTF8 code page identifier.

CharsetMapping=utf8,65001
CharsetMapping=latin1,65001