How do I resolve AWS DMS tasks that use a MySQL or MariaDB endpoint that fail with a FATAL_ERROR?

Short description

AWS DMS tasks that migrate data from MySQL or MariaDB databases might fail with a "Stop Reason FATAL_ERROR" for the following reasons:

• The AWS DMS task can't establish or validate connections to the source or target database endpoints.
• For change data capture (CDC) tasks, the replication task can't access or process the database transaction logs.
• The AWS DMS task can't start because of incompatible settings, missing permissions, or parameter configuration issues.

To determine the cause of the "Stop Reason FATAL_ERROR", check the Last failure message of the task that includes an error description. For more detailed error information, see Viewing and managing AWS DMS task logs.

Resolution

Endpoint failures

To resolve endpoint failures, review the task logs for detailed error messages.

Check that you correctly configured the following endpoint connections:

• The endpoint hostname or IP address is accurate and reachable.
• The port number matches your database configuration.
  Note: The default port for MySQL and MariaDB is 3306.
• The username and password credentials are correct.
• The SSL/TLS settings match your database requirements.

To troubleshoot endpoint connection issues, see How do I troubleshoot AWS DMS endpoint connectivity failures?

Check that the following network configurations allow communication between your AWS DMS replication instance and your database:

• The source database security group must allow inbound traffic from the replication instance's security group.
• The replication instance subnet must have the correct route tables to reach the database.
• Routes must exist for traffic to reach both the source and target databases.
• Your subnet network access control lists (network ACLs) must allow bidirectional traffic.
• For migrations across Amazon Virtual Private Cloud (Amazon VPC), you must have VPC peering or Transit Gateway connections.

Confirm that the MySQL or MariaDB database instance accepts connections. For Amazon Relational Database Service (Amazon RDS) instances, check that the instance status is Available in the Amazon Aurora and RDS console. For self-managed instances, test the connectivity with a MySQL client from the same VPC.

To retest the endpoint connection, complete the following steps:

1. Open the AWS DMS console.
2. In the navigation pane, choose Migrate or replicate, and then choose Endpoints.
3. Select your endpoint.
4. Choose Test connection.

Binary log issues

Prerequisite: Using a MySQL-compatible database as a source for AWS DMS.

If your replication task can't access or process the source binary logs, then check your binary log configuration.

To check whether you activated binary logs on your MySQL or MariaDB source database, run the following SQL command:

SHOW VARIABLES LIKE 'log_bin';

In the output, confirm that the value is set to ON.

To check whether you set binary log format to row-based logging, run the following SQL command:

SHOW VARIABLES LIKE 'binlog_format';

In the output, confirm that the value is set to ROW.

To check whether the binlog_row_image is logging all column changes, run the following SQL command:

SHOW VARIABLES LIKE 'binlog_row_image';

In the output, confirm that the value is set to FULL.

If your source database deletes binary logs before AWS DMS reads them, then the CDC task fails. Set the binary log retention period to at least 24 hours.

For Amazon RDS for MySQL and Amazon RDS for MariaDB instances, make sure that you turned on automatic backups. Then, use the mysql.rds_set_configuration procedure to set the binary log retention period.

For Amazon Aurora MySQL-Compatible clusters, run the following SQL command to check the current binary log retention period:

CALL mysql.rds_show_configuration;

In the output, confirm that you set the value for binlog retention hours to at least 24.
For self-managed instances, run the following SQL command to check the binary log retention period:

SHOW VARIABLES LIKE 'expire_logs_days';

In the output, confirm that you set the value to at least 1.

Note: In MySQL 8.0 and later, the expire_logs_days parameter is no longer supported, use binlog_expire_logs_seconds instead. For more information, see expire_logs_days and binlog_expire_logs_seconds on the MySQL website.

Configuration issues

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

If you didn't correctly configure your AWS DMS task, then the task can't start. To resolve configuration issues, complete the following steps:

1. To check that your AWS DMS task has the required dms-vpc-role AWS Identity and Access Management (IAM) role to perform the migration, run the get-role AWS CLI command:

   aws iam get-role --role-name dms-vpc-role

2. To confirm that you attached the AmazonDMSVPCManagementRole policy to your dms-vpc-role run the list-attached-role-policies AWS CLI command:

   aws iam list-attached-role-policies --role-name dms-vpc-role

3. To check that your AWS DMS task has the required role to write task logs to Amazon CloudWatch, run the following get-role AWS CLI command:

   aws iam get-role --role-name dms-cloudwatch-logs-role

4. To confirm that you attached the AmazonDMSCloudWatchLogsRole policy to the role, run the list-attached-role-policies AWS CLI command:

   aws iam list-attached-role-policies --role-name dms-cloudwatch-logs-role

5. Check that the AWS DMS database user has the required permissions on the source MySQL or MariaDB database. To view the current permissions and roles for a specific AWS DMS user, run the following SQL command:

   SHOW GRANTS FOR 'dms_user'@'host';

   Note: Replace dms_user with the username and host and with the host of the MySQL account that you used for your source endpoint.

If the preceding steps don't resolve the issue, then your replication instance might have insufficient resources. For more information, see How do I resolve the AWS DMS "Last Error Replication task out of memory" error? and How do I optimize AWS DMS memory usage for migration?

Related information

Troubleshooting tasks in AWS DMS

Best practices for AWS DMS