how to resolve innodb cluster conflict

Question

I am currently maintaining an innodb cluster with three nodes. It runs well but sometimes one node will be MISSING then I have to bring it up online again.

The problem is I inserted a table without primary key. Then a node failed. When I want to rejoin the failure node to cluster it says it can not join because there is a table without a primary key. I altered the table in cluster to give it primary key and the failure node still complains the same. So I deleted the table in failure node and expected it will rebuild the table.

Now it says

ERROR: Group Replication join failed.
ERROR: Error joining instance to cluster: '192.168.123.45@3306' - Query failed. 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication (RuntimeError)

after I run cluster.checkInstanceState('[email protected]:3306');

The instance '192.168.123.45:3306' is invalid for the cluster.
The instance contains additional transactions in relation to the cluster.

{
    "reason": "diverged", 
    "state": "error"
}

I know it is because the inconsistent state in different db. But I searched the internet but there's no documentation about how to resolve conflict in innodb cluster. Any advice would be helpful!

Answer 1

I have fixed this issue recently, steps below:

I tried to rejoin the instance to cluster as follows:

cluster.rejoinInstance(instance);

Now the cluster.status() function reports, member status as "RECOVERING", but after few minutes, again it reports as "MISSING".

I have logged into the missing instance and verified the processlist SHOW FULL PROCESSLIST; but I could not see any active traffic and I verified the data inconsistency by querying few user tables.

Also, checked the cluster status with cluster.status({extended:true, queryMembers:true}); function and observed, lastApplied block and startTimestamp was showing the older date, this confirm that, recent transactions are not applied on this instance.

Then, I did the below steps to remove the instance from cluster:

cluster.removeInstance(instance);

And added back to the instance as below:

cluster.removeInstance(instance);

This produced the error: The instance is already part of the another Group Replication;

So, I queried the below table on missing instance, and figured it out the instance status again:

select * from performance_schema.replication_group_members;

To add the back to the cluster, the I have below 2 options now:

• Rebuild the instance with fresh backup and restore from primary
• Remove cluster metadata from the instance and join back to cluster.

1st option is my last choice always. So I tried the below steps:

• Step 1: var cluster = dba.getCluster();
• Step 2: cluster.rescan();
• Step 3: Press 'Y' to remove the missing node on the interactive MySQL Shell window.
• Step 4: Login to Missing node and set the super_read_only = OFF;
• Step 5: Stop the group replication: STOP GROUP_REPLICATION;
• Step 5: Reset the slave: RESET SLAVE ALL;

• Step 6: Drop the cluster metadata database:

  DROP DATABASE mysql_innodb_cluster_metadata;

• Step 7: Go to shell and add the node back to cluster:cluster.addInstance(instance);

• Step 8: On interactive window: Select the recovery method as "Clone" (MySQL 8.0.16 and above).

I did not try Incremental Recovery in this case, but, in an another instance, it worked.

If you are unable to use Clone or Incremental Recovery options for any reason, use method 1 to rebuild the instance.

I hope it helps!

Answer 2

Well... So I ended up partially wipe the database and resync it again by reset master and drop database mysql_innodb_cluster_metadata. Now It works fine.

Still wondering if there's anyway to rewind gtid instead of full reset...