MySQL Bugs: #102742: Distributed recovery fails if performed while many rows are being inserted

Bug #102742	Distributed recovery fails if performed while many rows are being inserted
Submitted:	25 Feb 2021 18:27	Modified:	25 May 2021 16:05
Reporter:	Keith Lammers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Group Replication	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Windows (Windows Server 2019)
Assigned to:		CPU Architecture:	x86

Description:
If an instance is offline for a while and rows are continuously being inserted, the instance will fail to recover using distributed recovery when brought back online. Distributed recovery runs for a while, then eventually fails with these errors in the log:

2021-02-25T14:37:32.893214Z 10 [ERROR] [MY-013309] [Repl] Plugin group_replication reported: 'Transaction '1:3637794' does not exist on Group Replication consistency manager while receiving remote transaction prepare.'
2021-02-25T14:37:32.894427Z 10 [ERROR] [MY-011452] [Repl] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'
2021-02-25T14:37:32.895462Z 10 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'

How to repeat:
* Run something that is inserting a lot of rows (leave it running for the duration of these steps)
* Shutdown one of the R/O instances (I shutdown the entire OS)
* Wait some time (I waited 5 or 6 minutes)
* Start the OS
* MySQL starts up and tries to auto-join, distributed recovery starts
* After some time, the instance is offline and the errors in the description above are logged

As a workaround, stopping all inserts and performing removeInstance and addInstance will sometimes work.

Last resort if the workaround fails is to perform removeInstance and then addInstance('INSTANCENAME',{recoveryMethod:'clone'})

Hi,
Can you share your config, I'm having issues reproducing this:

> 
> * Wait some time (I waited 5 or 6 minutes)
> 

I tried with up to 30minutes and I never reproduced the problem.

> Last resort if the workaround fails is to perform removeInstance and then addInstance('INSTANCENAME',{recoveryMethod:'clone'})

addinstance with recoverymethod clone is a proper way to recover from this situation. I'm not sure how you get into this situation in 5-6 minutes, this is something I'm failing to reproduce.

all best
Bogdan

Sure thing! You want the my.ini from each instance?

Hi,

Yes, please. Also - how did you create the cluster?

all best
Bogdan

Important values to check

- How is Group_replication_consistency configured (is it AFTER ?)?
- what node is shut down? Primary or one of the other nodes
- How is cluster configured? RW/RO/RO or ? Is the RO node shutdown?

all best
Bogdan

Ok, config files attached. To answer your other questions:

* Cluster was created using MySQL Shell AdminAPI
* group_replication_consistency is set to AFTER
* The node that is shutdown during this test is a R/O node
* The cluster is 3 instance, single-primary

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I had replied with the answers to the questions and sent in the config files as a private attachment, so I'm not sure why this is being marked as no feedback. If there's anything else you need, please let me know.

Thanks!

To add another update to this, I've setup a new InnoDB cluster on Linux instead of Windows, and I can still reproduce this behaviour. Interestingly, with this cluster, instead of just automatically disabling GR after distributed recovery fails with that error, it falls back to clone recovery automatically.

The instance wasn't down for more than 2 minutes when I did this test. These are the errors reported in the log:

2021-03-30T01:18:53.040387Z 0 [System] [MY-011490] [Repl] Plugin group_replication reported: 'This server was declared online within the replication group.'
2021-03-30T01:18:53.044236Z 10 [ERROR] [MY-013309] [Repl] Plugin group_replication reported: 'Transaction '1:10578' does not exist on Group Replication consistency manager while receiving remote transaction prepare.'
2021-03-30T01:18:53.044295Z 10 [ERROR] [MY-011452] [Repl] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'
2021-03-30T01:18:53.044479Z 10 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'
2021-03-30T01:18:56.591557Z 0 [System] [MY-011504] [Repl] Plugin group_replication reported: 'Group membership changed: This member has left the group.'

It actually looks kind of like distributed recovery finished based on that first log line, then it hits the "'Transaction '1:10578' does not exist on Group Replication consistency manager" error immediately after.

Don't use "after" consistency until now.
Lots of bugs will occur when view is changed.

Hi,

Thanks for the report, I verified the behavior, we are already working on the fix but the dev team might have additional questions so please monitor this report.

all best
Bogdan

Excellent, thanks Bogdan! I've been working the last couple of days on trying to narrow down some more specific repeatable steps, and also trying to find a workaround. If you need any other info at all, please don't hesitate to ask.

Bin mentioned not using the AFTER consistency level, so I will re-test with EVENTUAL and BEFORE_AND_AFTER as well.

Hi,

check out https://mysqlhighavailability.com/group-replication-consistent-reads/

EVENTUAL is the safest but you can have stale reads. We do appreciate any bugs reported for AFTER, BEFORE, BEFORE_AND_AFTER .. especially if we can reproduce them.

Thanks
Bogdan

Can confirm that this bug does not occur with the consistency set to EVENTUAL.

I need the reads to be 100% consistent so I think a potential workaround is to use EVENTUAL consistency, but have the app servers use the R/W port (6446) on MySQL router so that all connections are made to the primary only.

Changelog entry added for MySQL 8.0.26:

With the Group Replication system variable group_replication_consistency =
AFTER set, if a view change event was delayed until after a locally prepared
transaction was completed, a different GTID could be applied to it, causing
errors in replication. The data is now processed in the same sequence it is
received to avoid the situation.