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

Key Takeaways

Hello, I'm Innocent, a cloud engineer here at the AWS office in Captown. Today I'm going to show you how to troubleshoot the 1236 error that can occur when using MySQL as the source for database migration services change data capture CDC task. Let's get started. When using my SQL as the source for a DMS CDC task, AWSDMS read changes from the row based binary logs called bin logs. The 1236 error usually indicates issues with those binary logs such as retention period, corruption, encryption, or missing logs. To resolve these errors, check the relevant settings and then restart the task. Here are some of the common scenarios that lead to this error. Error 1236 could not find first log file name in binary log index file. This error tells you that binary log that DMS was using was paged from the MySQL source. To resolve this error, first check the binary log retention settings and increase the settings if needed. For self-manage my SQL, check the expire logs days parameter in my.in for Windows system or my CNF for Linux systems. For AWS managed databases such as Amazon relational database services or Amazon Aurora, use the MySQL. RDS show configurations to check bin log retention hours. If the value for the retention hours is set to null, then use this command to increase the retention value. After you save this change, restart the DMS task to run the full load again. Error 12 36 log event entry exceed mags allowed packet. This error occurs when either the mags allowed packet setting on my SQL is smaller than the bin log event size or the bin log is corrupted. First increase the max allowed packet to a higher value such as 1 gig with this command on RDS. You set the max allowed packet on the custom parameter group. If this change does not resolve the error, the bin log might be corrupted. Check the bin log with this command. Error 1236 bin log truncated in the middle of event. This error occurs when either sync bin log is not set to one on the primary or bin log is corrupted. First check the sync bin log with the this command. If the value of bin log is not set to one then set it using this command. However, for RDS, you set it on custom parameter group. After you save this change, restart the DMS task. If sync bin log was already set to one, check for bin log corruption as you did in the previous case. Error 1236. Client requested master to start replication from impossible position. This error usually tells you that the MySQL server stopped unexpectedly. For full load and CDC task, just restart the DMS task. For CDC only, start from the next bin log position. Error 1236. client requested master to start replication from position greater than file size. This error can occur when you use my SQL 8.0 with encrypted binary log because DMS doesn't support CTC. Check my SQL version with this command. and then check that bin log encryption is on with this command. If blog encryption is on, you must turn it off before you start the DMS task with this command. And now you know how to troubleshoot the 1236 error that can occur when using my SQL as the source database for DMS task. Thank you for watching and happy cloud computing from all of us here at AWS.