Why did my AWS DMS CDC task fail with a 1236 error when using MySQL as source?

7 minute read
0

I'm using AWS Database Migration Service (AWS DMS) to migrate my data from a source MySQL database engine to a target engine. But the task is failing with a 1236 error. How do I troubleshoot this issue?

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 changes from the source engine's transaction logs. When using MySQL as a source, AWS DMS reads changes from the row-based binary logs (binlogs). AWS DMS then migrates those changes to the target.

Error 1236 is caused by issues with the binary logs. So, before troubleshooting, make sure that all binary logging parameters are correctly configured to support AWS DMS CDC. For more information, see Using a self-managed MySQL-compatible database as a source for AWS DMS and Using an AWS-managed MySQL-compatible database as a source for AWS DMS.

Resolution

Follow the steps below, depending on the root cause of your error.

Error 1236 (Could not find first log file name in binary log index file) reading binlog

Error in 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

This error indicates that the binary log that AWS DMS uses to replicate data changes to the target has been purged from the source MySQL database. This happens for two reasons:

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

Run these commands to confirm if the binary log is available or not.

List all binary log files:

mysql> SHOW BINARY LOGS;

List the current binary log file and position:

mysql> SHOW MASTER STATUS;

To resolve this error, first review the binary log retention period on the source MySQL database. Increase the retention period if needed. Restart the AWS DMS task to run the full-load phase again.

Follow these steps, depending on what type of instance you are using.

Self managed MySQL databases - On premises or Amazon Elastic Compute Cloud (Amazon EC2)

Review the value of expire_logs_days to check the binary logs retention period. It's a best practice to set this parameter to a value of 1 or greater at global level.

AWS managed MySQL databases - Amazon Relational Database Service (Amazon RDS) for MySQL or Amazon Aurora MySQL-Compatible Edition

1.    Check the binlog retention hours set on the MySQL database by running the mysql.rds_show_configuration command:

mysql> call mysql.rds_show_configuration;

2.    To increase log retention to 24 hours, run the mysql.rds_set_configuration command:

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

Error 1236 (log event entry exceeded max_allowed_packet; increase max_allowed_packet on master;...)

Error in 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)

This error has two possible causes:

  1. The value of max_allowed_packet on the source is smaller than the size of binlog events on the source.
  2. The binlog is corrupted on the source database.

To resolve these issues, follow these steps:

1.    On the source, set max_allowed_packet to a higher value. By doing this, you can rule out binlog event sizes as a possible cause of the error. This parameter can have a value up to 1 GB.

  1. If setting a higher max_allowed_packet value does not resolve the issue, then the binlogs on the source might be corrupted. The error message contains this text:

"the first event '/mnt/data/logs/mysql-bin.056367' at 123, the last event read from '/mnt/data/logs/mysql-bin.056367' at 787323693"

So, check mysql-bin.056367 for corruption by running these commands:

1.    Check if the binlog exists:

mysql> SHOW BINARY LOGS;

2.    View the events in the binary log:

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

3.    Download the binary logs:

mysql> MYSQLBINLOG;

Error 1236 (binlog truncated in the middle of event; consider out of disk space on master;...)

Error in 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)

There are two primary causes of this error:

  1. There is a sync_binlog != 1 on the primary server. This means that binary log events might not be synchronized on the disk.
  2. The binlog is corrupted on the source database.

To resolve this error:

1.    Check the value of the sync_binlog parameter on the source.

2.    Modify the value of sync_binlog, and set it to 1.

3.    Restart the task.

Note: If the sync_binlog parameter is already set to 1, then review the binary log for corruption using the steps previously detailed for error Error 1236 (log event entry exceeded max_allowed_packet; increase max_allowed_packet on master;...)

Error 1236 (Client requested master to start replication from impossible position...)

Error in 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 usually occurs if the source MySQL database server stops unexpectedly. This might be the result of a hardware failure like a disk error or a power loss.

To resolve this error, do the following depending on your AWS DMS task type:

  • Full load and CDC tasks - Restart the AWS DMS task.
  • CDC only tasks - Start the AWS DMS task from the next binary log position

Error 1236 (Client requested master to start replication from position > file size)

Error in 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 be caused by encrypted binary logs. If your source MySQL database is running MySQL version 8.0, and the binary logs are encrypted, then AWS DMS can't read the logs at task initialization. As a result, AWS DMS logs this error. AWS DMS doesn't support CDC replication using MySQL 8.0 as a source when binary log encryption is turned on.

1.    Check your MySQL version:

mysql> SELECT VERSION();

2.    Check if binlog_encryption is ON:

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

3.    Turn off binlog encryption:

mysql> SET GLOBAL binlog_encryption = OFF;

-or-

Start the AWS DMS task with binlog_encryption turned off, and then turn on binlog_encryption:

mysql> SET GLOBAL binlog_encryption = ON;

Related information

How do I troubleshoot binary logging errors that I received when using AWS DMS with Aurora MySQL as the source?

AWS OFFICIAL
AWS OFFICIALUpdated 2 years ago