Async replication setup fails when there's write load to primary

[paulomach]

Steps to reproduce

1. Deploy two clusters with more then one unit each
2. add write load to the one selected to become the primary
3. and relate to form a cluster-set

Expected behavior

Cluster set is formed

Actual behavior

Joining instances on secondary cluster fail to join the cluster

[github-actions[bot]]

https://warthogs.atlassian.net/browse/DPE-3890

[paulomach]

Upstream bug report: http://bugs.mysql.com/114624

Oracles internal bug report reference: 36403327

[taurus-forever]

@paulomach do we have an update from MySQL team here? I see no activity in http://bugs.mysql.com/114624

[paulomach]

@taurus-forever they've pinged directly on slack.

tl;dr; they cannot reproduce on sandboxed environment. There's something special about the way we are setting things. I've allocated some time to compare their instructions to the way we do it.

For the record, the instructions:

dba.deploy_sandbox_instance(3310, {'password': "", 'sandboxDir': "/nvme/anipinto/bug36514153_deadlock/sandbox"});
dba.deploy_sandbox_instance(3320, {'password': "", 'sandboxDir': "/nvme/anipinto/bug36514153_deadlock/sandbox"});
dba.deploy_sandbox_instance(3330, {'password': "", 'sandboxDir': "/nvme/anipinto/bug36514153_deadlock/sandbox"});

# 1. Deploy Group Replication cluster (A)

shell.connect("root@127.0.0.1:3310", "")

cluster = dba.create_cluster("my_cluster")

cluster.add_instance("127.0.0.1:3320", {'recoveryMethod':'incremental'});
cluster.add_instance("127.0.0.1:3330", {'recoveryMethod':'incremental'});

# 2. Create ClusterSet on A

clusterset = cluster.create_cluster_set("foobar")

# 3. Deploy standalone instance B1

dba.deploy_sandbox_instance(3340, {'password': "", 'sandboxDir': "/nvme/anipinto/bug36514153_deadlock/sandbox"});

# 4. Create ReplicaCluster on B1 from A (cluster_set.create_replica_cluster)

clusterset.create_replica_cluster("127.0.0.1:3340", "replica", {'recoveryMethod':'incremental'});

# 5. Add some write load to cluster A

# \sql
my_session = mysql.get_classic_session('root@localhost:3310', '');
my_session.run_sql("CREATE DATABASE test");
my_session.run_sql("USE test;");
my_session.run_sql("CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB;");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) values (2);");
my_session.run_sql("INSERT INTO t1 (c2) SELECT c2 FROM t1;");
my_session.run_sql("INSERT INTO t1 (c2) SELECT c2 FROM t1;");
my_session.run_sql("INSERT INTO t1 (c2) SELECT c2 FROM t1;");
my_session.run_sql("INSERT INTO t1 (c2) SELECT c2 FROM t1;");
my_session.run_sql("INSERT INTO t1 (c2) SELECT c2 FROM t1;");

On other terminal generate load with mysqlslap:

mysqlslap -S /nvme/anipinto/bug36514153_deadlock/sandbox/3310/sandboxdata/mysqld.sock --create-schema=test --delimiter=";" --iterations=300000 --query="INSERT INTO t1 (c2) SELECT c2 FROM t1 LIMIT 100" --concurrency=100 &

And on mysqlshell execute final steps:

shell.connect("root@127.0.0.1:3310", "")

dba.deploy_sandbox_instance(3350, {'password': "", 'sandboxDir': "/nvme/anipinto/bug36514153_deadlock/sandbox"});

cluster = dba.get_cluster("my_cluster")

cluster.add_instance("127.0.0.1:3350", {'recoveryMethod':'incremental'})