Replication Broken in MySQL8 Community Multi-AZ DB cluster

Question

We have a MySQL8 Community Multi-AZ DB cluster set up with 1 writer and 2 reader instances. (These servers have the mysql parameter lower_case_table_names as 1)
![Enter image description here](/media/postImages/original/IM48w24QOmTBK0gRg6tEk06w)

12 hours ago, the replication stopped on both reader instances. We tried to contact AWS support, but the solutions suggested does not seem to be working for us. 
They suggested, 
1. Manually fix the data conflict and resume the replication process. (CALL mysql.rds_start_replication;)
2. Skip the error and resume replication. (CALL mysql.rds_skip_repl_error;)
3. Create new replica instances and delete the old one's

These steps does not seem to work for Cluster Setup.
#1 and #2 steps wont work for us since we are in cluster setup. The SP rds_skip_repl_error is not there in mysql DB and rds_start_replication does not do anything.
#3, when we try to add a read replica, its using default MySQL 8 parameters (lower_case_table_names - 0) and errors out. When we try to specify parameter group in aws-cli (aws rds create-db-instance-read-replica),

> An error occurred (InvalidParameterCombination) when calling the CreateDBInstanceReadReplica operation: During the creation of a read replica in the same AWS Region as its source DB instance, a parameter group can't be specified for the following DB engine: mysql

If we try to create read replica without the specifying parameter group we see the following.

> An error occurred (InvalidDBInstanceState) when calling the ModifyDBInstance operation: The specified database instance is currently in incompatible-restore state, which is a terminal state.

What can be done here?

Answer

Hello,

I wanted to update you that there is a limitation with RDS Multi-AZ Clusters.
[+]https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/multi-az-db-clusters-concepts.html#multi-az-db-clusters-concepts.Limitations

RDS for MySQL Multi-AZ DB clusters supports only the following system-stored procedures:

* mysql.rds_rotate_general_log
* mysql.rds_rotate_slow_log
* mysql.rds_show_configuration
* mysql.rds_set_external_master_with_auto_position
* RDS for MySQL Multi-AZ DB clusters doesn't support other system-stored procedures.

This is why we were unable to run any of the stored procedures that we wanted on the database instances.

Further, I tried creating a read replica with a custom parameter group through aws-cli. However, I encountered an error message:

```
aws rds create-db-instance-read-replica --db-instance-identifier demooo --source-db-cluster-identifier database-2 --region us-east-1 --db-parameter-group-name test2

An error occurred (InvalidParameterCombination) when calling the CreateDBInstanceReadReplica operation: During the creation of a read replica in the same AWS Region as its source DB instance, a parameter group can't be specified for the following DB engine: mysql
```

As per the below documentation, specifying a parameter group for this operation is supported only for MySQL DB instances for cross-region read replicas and for Oracle DB instances, not for MySQL DB instances for same-region read replicas or for RDS Custom.

[+] https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance-read-replica.html

`--db-parameter-group-name (string)

The name of the DB parameter group to associate with this DB instance.

If you don't specify a value for DBParameterGroupName , then Amazon RDS uses the DBParameterGroup of the source DB instance for a same Region read replica, or the default DBParameterGroup for the specified DB engine for a cross-Region read replica.

Specifying a parameter group for this operation is only supported for MySQL DB instances for cross-Region read replicas and for Oracle DB instances. It isn't supported for MySQL DB instances for same Region read replicas or for RDS Custom.`

Additionally, I tried to create a read replica from the RDS MySQL Multi-AZ DB cluster instance, and I could also observe that the new read replica is starting with the default parameter group while my cluster is using a custom parameter group and went into 'Incompatible-restore'.

Recommendations:

1. Contact AWS Support: I kindly request that you reach out to AWS support through the existing case correspondence. Provide them with all the information shared here to enable a deeper investigation into this issue. 
2. Consider Point-in-Time Restore: In some cases, performing a point-in-time restore on the cluster before the replication issue happened might resolve the issue
[+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/multi-az-db-clusters-concepts.html

Replication Broken in MySQL8 Community Multi-AZ DB cluster

Relevant content