How do I troubleshoot replication errors for Amazon RDS for MySQL?

Resolution

The following are the most common errors that you might receive when you use Amazon RDS for MySQL binlog replication:

• Error 1236
• Error 1062
• Error 1872

For instructions on how to monitor read replication, see Monitoring MySQL read replicas.

Error 1236

Log event entry exceeded max_allowed_packet

This error might occur for these reasons:

• The value of the max_allowed_packet parameter on the replication source is smaller than the size of binlog files
• Corrupted binlog files on the source

To resolve these errors, modify the max_allowed_packet parameter to a value that's greater than the size of the largest binlog.

If the error persists after you increase the max_allowed_packet parameter, then run these commands to check the binlog file for corruption:

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

For more information, see Read replica create fails or replication breaks with fatal error 1236.

Could not find first log file name in binary log index file

This error indicates that the binlog file doesn't exist on the replication source. If your replication setup involves an external MySQL database, then the binlogs might have deleted on the source database.

To resolve this error, run this command to verify that the binlog files exist:

mysql> SHOW BINARY LOGS;

Then, restart your replication.

Binlog truncated in the middle of event

-or-

Client requested master to start replication from impossible position

These errors indicate that the binlog file might be incomplete. This is usually because the parameter settings aren't atomicity, consistency, isolation, and durability (ACID) compliant. For example, if the sync_binlog parameter is set to a value other than 1, then this turns off synchronization of the binary log to disk.

To resolve this error, set the sync_binlog parameter to 1, and then recreate the replica.

Note: A setting other than 1 might improve performance. But, in the event of a power failure or operating system crash, data loss in the binary logs might occur. For more information, see the MySQL documentation for sync_binlog.

Error 1062

Handler error HA_ERR_FOUND_DUPP_KEY

This error can occur for a variety of reasons, but it's commonly caused by one of these actions:

• A transaction attempts to insert a row with a duplicate primary key value.
• A transaction attempts to insert a row with a duplicate unique key value.
• A transaction attempts to update a row to a value that already exists in another row with a unique key or primary key constraint.
• A transaction initiated by event scheduler generated a data inconsistency.

To resolve this error, identify the table or index the duplicate key value associated with the table. Then, review your code or data to determine why the duplicate occurs.

Note: It's a best practice to turn off the event scheduler on the replica before you begin replication. For more information, see the MySQL documentation for Replication of invoked features.

Error 1872

Slave failed to initialize relay log info structure from the repository

This error might occur when you turn on multithreaded replication (slave_parallel_workers > 0) and you turn off the slave_preserve_commit_order parameter.

Note: If you use the preceding settings, then there might be gaps in the sequence of transactions.

To resolve this error, turn off multithreaded replication. To turn off multithreaded replication, you must update the replica_parallel_workers parameter to a value of zero (0) in your custom parameter group. Or, use global transaction identifier (GTID) based replication for Amazon RDS for MySQL.

Note:

• If you turn off the slave_preserve_commit_order parameter, then transactions apply in parallel on the replica even when the sequence of transactions is out of order.
• If you turn on the slave_preserve_commit_order parameter, then this prevents gaps in the sequence of transactions but this doesn't prevent source binary log position lag.
• For more information, see the MySQL documentation for slave_preserve_commit_order.

Related information

Best practices for configuring parameters for Amazon RDS for MySQL, part 2: parameters related to replication