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 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:
-
Sign in to the AWS Management Console and select DMS.
-
Select Endpoints.
-
Select the MySQL-compatible target endpoint that you want to add autocommit to.
-
Select Modify.
-
Select Advanced, and then add the following code to the Extra connection attributes text box:
Initstmt= SET AUTOCOMMIT=1
-
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:
-
Sign in to the AWS Management Console and select DMS.
-
Select Endpoints.
-
Select the MySQL, Aurora MySQL, or MariaDB target endpoint that you want to disable foreign keys.
-
Select Modify.
-
Select Advanced, and then add the following code to the Extra connection attributes text box:
Initstmt=SET FOREIGN_KEY_CHECKS=0
-
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