[Amazon RDS MySQL - Blue/Green] The RDS Green instance encountered the error "Corrupted replication event was detected" after changing the schema.

0

I need to update the production database schema to enhance a service related to a date attribute, using an Amazon RDS MySQL instance. Currently, our table has a datetime precision of 0, but we aim to change it to 3. The challenge lies in the fact that the production database contains millions of records, and this precision alteration disrupts database operations, causing service downtime. To address this, we implemented Amazon RDS BLUE/GREEN on the production instance. However, when modifying the schema of the GREEN instance and inserting data via an ORM (Sequelize), replication ceases, resulting in the following log message: 'Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin-changelog.006591, end_log_pos 384546; Could not execute Write_rows event on table schema-example.table-example; Corrupted replication event was detected, Error_code: 1610; handler error No Error!; the event's source log mysql-bin-changelog.006591, end_log_pos 384546'.
The error is generic, and I'm struggling to discern the distinctions between events generated during a successful operation and those resulting in an error (corrupted event). Additionally, it seems the issue only arises when my GREEN instance has a different schema precision than the BLUE instance, specifically when inserting data via ORM. Interestingly, I encountered no errors during manual insertions via the workbench, even in the same scenario with schemas having different precision. I seek guidance on what aspects to investigate to identify the root cause and explore potential solutions.

Tested scenarios:

  1. Do not change the schema of the GREEN instance and insert data via Workbench.
  2. Do not change the schema of the GREEN instance and insert data via the service (ORM).
  3. Change the schema (datetime(0) -> datetime(3)) of the GREEN instance and insert data via Workbench.
  4. Change the schema (datetime(0) -> datetime(3)) of the GREEN instance and insert data via the service (ORM).
  5. Test scenarios 2 and 3 by setting the column precision with the DATE attribute in the Sequelize model to 3.

P.S: Scenarios that triggered this error: 4 and 5.

P.S²: Brought evidence to assist in the analysis; the data is in a homologation/non-sensitive state. The column that had its precision changed was "statement_date".

Before executing ALTER TABLE (successful replication):

  • Sequelize output:
INSERT INTO `table-example` (`id`,`operation_type`,`amount`,`balance`,`status`,`statement_date`,`other_info`,`created_at`,`updated_at`,`version`,`account_id`,`card_transaction_id`,`card_reversal_id`,`card_cancellation_id`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?); {
  VALUES: [
    'TEST_CREDIT',
    4,
    4,
    'COMPLETED',
    '2023-11-13 10:54:19',
    '{}',
    '2023-11-13 10:54:19',
    '2023-11-13 10:54:19',
    0,
    '477',
    null,
    null,
    null
  ]
}
  • Binary log:
#231113 10:54:19 server id 1512585851  end_log_pos 2357 CRC32 0xc25ef598 	Write_rows: table id 758 flags: STMT_END_F

BINLOG '
iypSZRN7OihabQAAANIIAAAAAPYCAAAAAAMAFWV3YWxseV9hY2NvdW50LWRldi1rYwASYWNjb3Vu
dF9zdGF0ZW1lbnRzAA8DDwgI/hL8DxISAwMDAwMK/QL3AQAC/QIAAKBwAQEAAgEhlvIeXQ==
iypSZR57OihaYwAAADUJAAAAAPYCAAAAAAEAAgAP//+AcII6AwALAFRFU1RfQ1JFRElUBAAAAAAA
AAAEAAAAAAAAAAGZsZqtkwIAe32ZsZqtk5mxmq2TAAAAAN0BAACY9V7C
'/*!*/;
### INSERT INTO `schema-example`.`table-example`
### SET
###   @1=211586
###   @2='TEST_CREDIT'
###   @3=4
###   @4=4
###   @5=1
###   @6='2023-11-13 10:54:19'
###   @7='{}'
###   @8=NULL
###   @9='2023-11-13 10:54:19'
###   @10='2023-11-13 10:54:19'
###   @11=0
###   @12=477
###   @13=NULL
###   @14=NULL
###   @15=NULL
# at 2357

After executing ALTER TABLE (unsuccessful replication):

  • Sequelize output:
INSERT INTO `table-example` (`id`,`operation_type`,`amount`,`balance`,`status`,`statement_date`,`other_info`,`created_at`,`updated_at`,`version`,`account_id`,`card_transaction_id`,`card_reversal_id`,`card_cancellation_id`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?); {
  VALUES: [
    'TEST_CREDIT',
    3,
    3,
    'COMPLETED',
    '2023-11-13 11:04:57',
    '{}',
    '2023-11-13 11:04:57',
    '2023-11-13 11:04:57',
    0,
    '478',
    null,
    null,
    null
  ]
}
  • Binary log:
#231113 11:04:57 server id 1512585851  end_log_pos 384546 CRC32 0xb2390c99 	Write_rows: table id 758 flags: STMT_END_F

BINLOG '
CS1SZRN7OihabQAAAL/dBQAAAPYCAAAAAAMAFWV3YWxseV9hY2NvdW50LWRldi1rYwASYWNjb3Vu
dF9zdGF0ZW1lbnRzAA8DDwgI/hL8DxISAwMDAwMK/QL3AQAC/QIAAKBwAQEAAgEhuq8wtg==
CS1SZR57OihaYwAAACLeBQAAAPYCAAAAAAEAAgAP//+AcIM6AwALAFRFU1RfQ1JFRElUAwAAAAAA
AAADAAAAAAAAAAGZsZqxOQIAe32ZsZqxOZmxmrE5AAAAAN4BAACZDDmy
'/*!*/;
### INSERT INTO `schema-example`.`table-example`
### SET
###   @1=211587
###   @2='TEST_CREDIT'
###   @3=3
###   @4=3
###   @5=1
###   @6='2023-11-13 11:04:57'
###   @7='{}'
###   @8=NULL
###   @9='2023-11-13 11:04:57'
###   @10='2023-11-13 11:04:57'
###   @11=0
###   @12=478
###   @13=NULL
###   @14=NULL
###   @15=NULL
# at 384546
#231113 11:04:57 server id 1512585851  end_log_pos 384577 CRC32 0xea8b0a09 	Xid = 2893453
COMMIT/*!*/;
# at 384577
  • "performance_schema.replication_applier_status_by_worker" output
"CHANNEL_NAME","WORKER_ID","THREAD_ID","SERVICE_STATE","LAST_ERROR_NUMBER","LAST_ERROR_MESSAGE","LAST_ERROR_TIMESTAMP","LAST_APPLIED_TRANSACTION","LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP","LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP","LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP","LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP","APPLYING_TRANSACTION","APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP","APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP","APPLYING_TRANSACTION_START_APPLY_TIMESTAMP","LAST_APPLIED_TRANSACTION_RETRIES_COUNT","LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER","LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE","LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP","APPLYING_TRANSACTION_RETRIES_COUNT","APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER","APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE","APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP"
"",1,,OFF,1610,"Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin-changelog.006591, end_log_pos 384546; Could not execute Write_rows event on table `schema-example`.`table-example`; Corrupted replication event was detected, Error_code: 1610; handler error No Error!; the event's source log mysql-bin-changelog.006591, end_log_pos 384546","2023-11-13 14:08:11.486384","","0000-00-00 00:00:00","0000-00-00 00:00:00","0000-00-00 00:00:00","0000-00-00 00:00:00",ANONYMOUS,"2023-11-13 14:04:57.852386","2023-11-13 14:04:57.852386","0000-00-00 00:00:00",0,0,"","0000-00-00 00:00:00",0,0,"","0000-00-00 00:00:00"
1 Answer
0

I am not 100% clear what the issue is here. But I'd make a few suggestions. I assume you are enabling writes to the green instance by modifying the read_only flag? Also, you should consider stopping replication on green before you run the DDL to modify the green table. Then once the DDL completes restart. It might be that the issue is due to the DDL competing with the replication applier. hth,

CALL mysql.rds_stop_replication;
AWS
MODERATOR
philaws
answered 5 months ago
  • We are not inserting data into the GREEN instance directly, but rather into the BLUE instance and hoping it replicates correctly.

  • The error occurred again. I performed the following steps:

    • Executed the command "mysql.rds_stop_replication" on the GREEN instance.
    • Modified the table schema in the GREEN instance.
    • Conducted the data insertion test via service (ORM) on the BLUE instance.
    • Executed the command "mysql.rds_start_replication."
    • Checked the error using the query "select * from performance_schema.replication_applier_status_by_worker."

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions