MySQL Bugs: #91972: Group Replication prevents the creation of multi-primary cluster

Bug #91972	Group Replication prevents the creation of multi-primary cluster
Submitted:	10 Aug 2018 19:20	Modified:	25 Nov 2019 16:56
Reporter:	Wilmar Calderón Torres	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell AdminAPI InnoDB Cluster / ReplicaSet	Severity:	S3 (Non-critical)
Version:	5.7.23	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	Multi-Primary Cluster, MySQL Group Replication, mysql innodb cluster, MySQL Shell AdminAPI

Description:
When creating a multi-master cluster with the instruction:

var cluster = dba.createCluster('myCluster', {multiPrimary: true, force: true})

The following error occurs:

[ERROR] Plugin group_replication reported: 'Table instances has a foreign key with 'CASCADE' clause. This is not compatible with Group Replication'.

This error occurs because table instances of the mysql_innodb_cluster_metadata schema has a foreign key constraint (instances_ibfk_2); which, has a referential action (SET NULL) in the ON DELETE event.

How to repeat:
Scenario:
- 3 multi-master nodes.

Steps to repeat

1. With the following configuration in the MySQL Configuration File (my.cnf) in each node.

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction = XXHASH64

#Group Replication Settings
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "10.1.0.1:33061"
loose-group_replication_group_seeds= "10.1.0.1:33061,10.1.0.2:33061,10.1.0.3:33061"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_single_primary_mode=OFF
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_ip_whitelist="10.1.0.1,10.1.0.2,10.1.0.3"

Changing the values corresponding to each node for the following parameters:

server_id = 1,2,3
loose-group_replication_local_address = "10.1.0.1:33061", "10.1.0.2:33061", "10.1.0.3:33061"

2. Having installed the replication plugin (group_replication.so) and configured the local instances in each node, we tried to create the multi-primary cluster from node 1, through the MySQL ShellAPI, with the following instructions:

mysqlsh --log-level = DEBUG3

var n1 = 'root@10.1.0.1: 3306'

shell.connect(n1)

dba.checkInstanceConfiguration(n1)

var cluster = dba.createCluster('myCluster', {multiPrimary: true, force: true})

At this point, the following error is generated:

[ERROR] Plugin group_replication reported: 'Table instances has a foreign key with 'CASCADE' clause. This is not compatible with Group Replication'

Suggested fix:
It is assumed that the MySQL ShellAPI or Group Replication plugin are responsible for creating the schema: mysql_innodb_cluster_metadata along with all its objects; for this reason I believe that this should not create a foreign keys with cascading constraints.

The implemented solution was to eliminate the constraint and recreate it without the referential action ON DELETE SET NULL.

ALTER TABLE `mysql_innodb_cluster_metadata`.`instances` DROP FOREIGN KEY `instances_ibfk_2`;
    
ALTER TABLE `mysql_innodb_cluster_metadata`.`instances` ADD CONSTRAINT `instances_ibfk_2` 
FOREIGN KEY (`replicaset_id`) REFERENCES `replicasets` (`replicaset_id`);

Thanks for your report!

all best
Bogdan

Suggested fix cannot be applied when two tables are joined this way when Primary Key (NOT NULL) is used as Foreign Key. 
In this case 'ON DELETE NO ACTION' has to be set.

Posted by developer:
 
This problem gets solved in metadata version 2.0.0

In metadata version 2.0.0, the offending constraints are no longer present, removing this limitation.

So to fix this use MySQL Shell 8.0.19, if the cluster was created using a lower version, the metadata should be upgraded using dba.upgradeMetadata()

Thanks for submitting this report!

Posted by developer:
 
Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 8.0.19 changelog:

It was not possible to create a multi-primary cluster due to to cascading constraints on the InnoDB cluster metadata tables. This has been fixed in version 8.0.19 and so to solve this issue upgrade your cluster using dba.upgradeMetadata().