If you tried to migrate live from Amazon RDS/Aurora or any other Cloud to OCI MySQL HeatWave before December 6, 2022, you may have encountered issues with Amazon to OCI asynchronous replication.
See: Live Migration from Azure Database for MySQL to MySQL Database Service on OCI
This is because Amazon (an other Cloud) writes to certain system tables in the mysql schema. This system schema is protected in MySQL HeatWave. Therefore, the replication fails like this:
MySQL 10.0.1.27:3306 ssl SQL > show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: aws-xxxeast-1.rds.amazonaws.com
Source_User: rpluser001
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin-changelog.000031
Read_Source_Log_Pos: 197
Relay_Log_File: relay-log-replication_channel.000002
Relay_Log_Pos: 393
Relay_Source_Log_File: mysql-bin-changelog.000029
Replica_IO_Running: Yes
Replica_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 3944
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '1f6f9e0b-8b41-11ed-9dea-0ec245d1c325:707' at master log mysql-bin-changelog.000029, end_log_pos 543. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Source_Log_Pos: 197
Relay_Log_Space: 3013
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: Yes
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 3944
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '1f6f9e0b-8b41-11ed-9dea-0ec245d1c325:707' at master log mysql-bin-changelog.000029, end_log_pos 543. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1323125174
Source_UUID: 1f6f9e0b-8b41-11ed-9dea-0ec245d1c325
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State:
Source_Retry_Count: 0
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 230103 10:47:15
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 1f6f9e0b-8b41-11ed-9dea-0ec245d1c325:707-708
Executed_Gtid_Set: 1f6f9e0b-8b41-11ed-9dea-0ec245d1c325:1-706,
9f7690c0-7d40-11ed-b451-d72c4e44342f:1-705,
b0dc4cce-7d36-11ed-910e-02001716bf1a:1-4397
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: replication_channel
Source_TLS_Version: TLSv1.2,TLSv1.3
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace: mysqlSo in the output of SHOW REPLICA STATUS in the OCI MySQL HeatWave replica, we can see the following error:
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '1f6f9e0b-8b41-11ed-9dea-0ec245d1c325:707' at master log mysql-bin-changelog.000029, end_log_pos 543. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. And if we check in Performance_Schema in the table replication_applier_status_by_worker, we can see that the problem is indeed related to a table in the mysql schema:
MySQL 10.0.1.27:3306 ssl SQL > select * from
performance_schema.replication_applier_status_by_worker limit 1\G
*************************** 1. row ***************************
CHANNEL_NAME: replication_channel
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 3944
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '1f6f9e0b-8b41-11ed-9dea-0ec245d1c325:707' at master log mysql-bin-changelog.000029, end_log_pos 543; Error 'The query does not comply with variable require_row_format restrictions.' on query. Default database: ''. Query: 'INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1672742344335) ON DUPLICATE KEY UPDATE value = 1672742344335'
LAST_ERROR_TIMESTAMP: 2023-01-03 10:47:15.056640
LAST_APPLIED_TRANSACTION:
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION: 1f6f9e0b-8b41-11ed-9dea-0ec245d1c325:707
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2023-01-03 10:39:04.340903
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2023-01-03 10:39:04.340903
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2023-01-03 10:47:15.056462
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000The error is related to this query in the mysql.rds_heartbeat2 table:
INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1672742344335)
ON DUPLICATE KEY UPDATE value = 1672742344335 Fortunatley, MySQL HeatWave now has the ability to also use replication filters when using a replication channel !
Let’s modify our Replication Channel in OCI to use this new replication filters feature:
There are already some predefined templates for the most common services:
As I was using RDS MySQL 8.0, this is the one I selected:
When it is modified and resumed, the replication channel becomes active again:
And if we check the Replication Status from the MySQL Client, we can see the replication filters used in Replicate_Ignore_Table:
MySQL 10.0.1.27:3306 ssl SQL > show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: aws-db-1.xxx.rds.amazonaws.com
Source_User: rpluser001
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin-changelog.000033
Read_Source_Log_Pos: 9227
Relay_Log_File: relay-log-replication_channel.000011
Relay_Log_Pos: 490
Relay_Source_Log_File: mysql-bin-changelog.000033
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.rds_heartbeat2,mysql.rds_sysinfo
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 9227
Relay_Log_Space: 10020
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: Yes
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1323125174
Source_UUID: 1f6f9e0b-8b41-11ed-9dea-0ec245d1c325
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 0
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 1f6f9e0b-8b41-11ed-9dea-0ec245d1c325:707-714
Executed_Gtid_Set: 1f6f9e0b-8b41-11ed-9dea-0ec245d1c325:1-714,
9f7690c0-7d40-11ed-b451-d72c4e44342f:1-705,
b0dc4cce-7d36-11ed-910e-02001716bf1a:1-4397
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: replication_channel
Source_TLS_Version: TLSv1.2,TLSv1.3
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace: mysqlIt is now very easy to migrate to OCI MySQL HeatWave using MySQL Shell Dump & Load and Replication to catch up on transactions that were executed while loading the dump to the MySQL Instance in OCI.
Enjoy live migration to MySQL HeatWave on OCI !
Great fix and a nice article.
Can we configure Replica Set from AWS to OCI Heatwave?
Hi Mohsin,
Do you mean MySQL InnoDB ReplicaSet, managed with MySQL Shell ? No, as this is not for DBaaS. This could be possible if you use compute instances on both cloud and you will have to manage MySQL.