Why did my AWS DMS CDC task fail with "Error 1236" when I used MySQL as the source?

Short description

With AWS DMS, you can perform one-time migrations, and you can replicate ongoing changes to keep sources and targets in sync. To read ongoing changes from the source database, AWS DMS uses engine-specific API actions to read the source engine's transaction logs. When you use MySQL as a source, AWS DMS reads changes from the row-based binary logs (binlogs). Then, AWS DMS migrates those changes to the target.

If there are issues with the binary logs, then you receive the "Error 1236" message. Make sure that you correctly configure all binary logging parameters to support AWS DMS CDC when you use a self-managed or AWS managed MySQL-compatible database.

Resolution

To troubleshoot "Error 1236", take the following actions based on the error message that you receive.

"Could not find first log file name in binary log index file reading binlog"

Example error from the task logs:

[SOURCE_CAPTURE  ]I: Setting position in binlog 'mysql-bin-changelog.014448' at 119624570  (mysql_endpoint_capture.c:886)
[SOURCE_CAPTURE  ]I: Position was set in binlog 'mysql-bin-changelog.014448' at 119624570  (mysql_endpoint_capture.c:922)
[SOURCE_CAPTURE  ]E: Error 1236 (Could not find first log file name in binary log index file) reading binlog [1020493] 
[TASK_MANAGER    ]I: Task - ABCDXXXXXXXXXXXXXX is in ERROR state, updating starting status to AR_NOT_APPLICABLE

The preceding error occurs when the source MySQL database removed the binary log that AWS DMS uses to replicate data changes to the target. MySQL might remove the binary log for the following reasons:

• The binary log retention period is too low.
• The AWS DMS task is stuck or stopped because of an issue.

To confirm whether the binary log is available, run the following command to list all binary log files:

mysql> SHOW BINARY LOGS;

Then, run the following command to list the current binary log file and position:

mysql> SHOW MASTER STATUS;

For more information about the previous commands, see SHOW BINARY LOGS statement and SHOW MASTER STATUS statement on the MySQL website.

To resolve the error, check the binary log retention period on the source MySQL database. If needed, then increase the retention period. Restart the AWS DMS task to run the full-load phase again.

Take the following actions based on your database type.

Self-managed MySQL databases

To check the binary logs retention period for on-premises or Amazon Elastic Compute Cloud (Amazon EC2), review the value of expire_logs_days. For more information, see expire logs days on the MySQL website.

Note: It's a best practice to set the global parameter of the SET variable to 1 or greater. For more information, see SET syntax for variable assignment on the MySQL website.

AWS managed MySQL databases

Check the binary log retention hours that are set on an Amazon Relational Database Service (Amazon RDS) for MySQL or Amazon Aurora MySQL-Compatible Edition database. Run the following command:

mysql> call mysql.rds_show_configuration;

To increase log retention to 24 hours, run the following command:

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

"Log event entry exceeded max_allowed_packet; increase max_allowed_packet on master..."

Example error from the task logs:

[SOURCE_CAPTURE  ]I:  Position was set in binlog 'mysql-bin.056367' at 787323674  (mysql_endpoint_capture.c:922)
[SOURCE_CAPTURE  ]D:  net_safe_read error 1236 (log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mysql-bin.056367' at 787323674, the last event read from '/mnt/data/logs/mysql-bin.056367' at 123, the last byte read from '/mnt/data/logs/mysql-bin.056367' at 787323693.)  (mysql_endpoint_capture.c:1119)
[SOURCE_CAPTURE  ]I:  Error 1236 (log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mysql-bin.056367' at 787323674, the last event read from '/mnt/data/logs/mysql-bin.056367' at 123, the last byte read from '/mnt/data/logs/mysql-bin.056367' at 787323693.) reading binlog. Try reconnect  (mysql_endpoint_capture.c:1123)

The preceding error can occur for the following reasons:

• A row has more data than the value of the max_allowed_packet on the source. For more information, see max allowed packet on the MySQL website.
• A single transaction contains large amounts of data or there are multiple row updates in a single transaction.
• There's binary log corruption on the source database.

If you use binary large object (BLOB) columns or long strings, then set the max_allowed_packet value to the largest BLOB that you use. This parameter can have a value up to 1 GB. For more information, see The BLOB and TEXT types on the MySQL website.

To check the size of your largest transaction, review your binary logs. Make sure that the transaction size doesn't exceed the size of the max_allowed_packet. For information about how to break up a large transaction, see Packet too large on the MySQL website.

If you still experience the error, then there might be corruption in the source binary logs.

To check the binary logs for issues, complete the following steps:

1. Run the following command to check whether the binary log exists:

   mysql> SHOW BINARY LOGS;

2. To view the events in the binary log, run the following command:

   mysql> SHOW BINLOG EVENTS IN 'binlog file' FROM position;

   Note: Replace binlog file with the binary log name and position with the position that the event occurs at.

3. To download the binary logs, run the following command:

   shell> mysqlbinlog \
       --read-from-remote-server \
       --host=MySQLInstance1.cg034hpkmmjt.region.rds.amazonaws.com \
       --port=3306  \
       --user ReplUser \
       --password \
       --raw \
       --verbose \
       --result-file=/tmp/ \
       binlog.00098

4. Check the binary log that the error message mentions for corruption.

5. If the binary log is corrupted, then create an AWS Support case.

"Binlog truncated in the middle of event; consider out of disk space on master..."

Example error from the task logs:

[SOURCE_CAPTURE ]I: Read next binary log event failed; net_safe_read error 1236 (binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin-changelog.017672' at 486, the last event read from '/rdsdbdata/log/binlog/mysql-bin-changelog.017672' at 125, the last byte read from '/rdsdbdata/log/binlog/mysql-bin-changelog.017672' at 4756.) (mysql_endpoint_capture.c:1069)
[SORTER ]I: Transaction consistency reached (sorter_transaction.c:347)
[TASK_MANAGER ]I: Starting replication now (replicationtask.c:2774)
[TASK_MANAGER ]I: Task - MGLVRIRUJH6FE2GP6F7SW46BPBW6YKF2JUJPSVY is in RUNNING state, updating starting status to AR_RUNNING (repository.c:5110)

The preceding error can occur for the following reasons:

• There's a sync_binlog != 1 on the primary server. This means that binary log events might not synchronize on the disk. For more information, see sync binlog on the MySQL website.
• There's binary log corruption on the source database.

To resolve this error, make sure that the value of the sync_binlog parameter on the source is set to 1. Then, restart the task.

If the sync_binlog parameter is already set to 1, then review the binary log for corruption. For instructions, see the preceding "Log event entry exceeded max_allowed_packet; increase max_allowed_packet on master..." section.

"Client requested master to start replication from impossible position"

Example error from the task logs:

[SOURCE_CAPTURE  ]I:  Position was set in binlog 'mysql-bin-changelog.007989' at 1631  (mysql_endpoint_capture.c:922)
[SOURCE_CAPTURE  ]I:  Read next binary log event failed; net_safe_read error 1236 (Client requested master to start replication from impossible position; the first event 'mysql-bin-changelog.007989' at 1631, the last event read from 'mysql-bin-changelog.007989' at 4, the last byte read from 'mysql-bin-changelog.007989' at 4.)  (mysql_endpoint_capture.c:1053)
[SOURCE_CAPTURE  ]D:  Error reading binary log. [1020493]  (mysql_endpoint_capture.c:3995)
[SOURCE_CAPTURE  ]E:  Error 1236 (Client requested master to start replication from impossible position; the first event 'mysql-bin-changelog.007989' at 1631, the last event read from 'mysql-bin-changelog.007989' at 4, the last byte read from 'mysql-bin-changelog.007989' at 4.) reading binlog events [1020493]  (mysql_endpoint_capture.c:1074)

This error occurs when the source MySQL database server stops unexpectedly. An unexpected stop might occur because of a hardware failure such as a disk error or a power loss.

To resolve this error, take one of the following actions based on your AWS DMS task type:

• For full load and CDC tasks, restart the AWS DMS task.
• For CDC-only tasks, start the AWS DMS task from the next binary log position.

"Client requested master to start replication from position > file size"

Example error from the task logs:

[SOURCE_CAPTURE  ]I:  Position was set in binlog 'binlog.000012' at 2179  (mysql_endpoint_capture.c:922)
[SOURCE_CAPTURE  ]I:  Read next binary log event failed; net_safe_read error 1236 (Client requested master to start replication from position > file size)  (mysql_endpoint_capture.c:1052

This error might occur because of encrypted binary logs. If your source MySQL database runs MySQL version 8.0 and you encrypt the binary logs, then AWS DMS can't read the logs at task initialization. As a result, AWS DMS logs this error. When you activate binary log encryption, you can't use CDC replication that uses MySQL 8.0 as a source. For more information, see Encrypting binary log files and relay log files on the MySQL website.

To resolve this issue, complete the following steps:

1. Run the following command to check your MySQL version:

   mysql> SELECT VERSION();

2. Run the following command to check whether binlog_encryption is ON:

   mysql> SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'binlog_encryption';

3. To turn off binlog_encryption, run the following command:

   mysql> SET GLOBAL binlog_encryption = OFF;

   -or-
   Start the AWS DMS task with binlog_encryption turned off, and then run the following command to turn on binlog_encryption:

   mysql> SET GLOBAL binlog_encryption = ON;

Related information

How do I troubleshoot binary logging errors that I receive when I use AWS DMS with Aurora MySQL-Compatible as the source?