Bug #96721 Add or remove instances to InnoDB cluster fails in mult-primary mode
| Submitted: | 2 Sep 2019 20:00 | Modified: | 22 Sep 2019 19:24 |
|---|---|---|---|
| Reporter: | Shubhra Prakash Nandi | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | Shell AdminAPI InnoDB Cluster / ReplicaSet | Severity: | S2 (Serious) |
| Version: | 8.0.15 | OS: | CentOS (7) |
| Assigned to: | CPU Architecture: | x86 (x86_64) | |
| Tags: | addinstance, admin api, group_replication_enforce_update_everywhere_checks, InnoDB Cluster, multi-primary, mysql shell, removeinstance | ||
[2 Sep 2019 20:00]
Shubhra Prakash Nandi
[2 Sep 2019 20:28]
Shubhra Prakash Nandi
I think I have mentioned the CASCADE clause by mistake, there is no CASCADE clause but ON DELETE SET NULL. Also the constraint `instances_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`host_id`) ON DELETE RESTRICT can be ignored from fix since RESTRICT is the default clause.
[6 Sep 2019 14:06]
MySQL Verification Team
Hi, Thanks for the report, verified as stated.
[13 Sep 2019 10:44]
Shubhra Prakash Nandi
I am providing a fix but this does not still solve the issue with adding new instances when group_replication_enforce_update_everywhere_checks = ON since the plugin tries to create the table mysql_innodb_cluster_metadata.instances with old structure. The fix works for removing existing instances and dissolving the cluster. USE mysql_innodb_cluster_metadata; ALTER TABLE instances DROP FOREIGN KEY instances_ibfk_2; ALTER TABLE instances ADD CONSTRAINT instances_ibfk_2 FOREIGN KEY (replicaset_id) REFERENCES replicasets (replicaset_id); DELIMITER // CREATE TRIGGER bd_replicasets BEFORE DELETE ON replicasets FOR EACH ROW BEGIN UPDATE instances inst SET inst.replicaset_id = NULL WHERE inst.replicaset_id = OLD.replicaset_id; END // DELIMITER ;
[22 Sep 2019 19:24]
Shubhra Prakash Nandi
Well I am providing a work around for this issue which works for both - adding a new instance and removing and existing instance without taking the cluster down. This only works with Mysql 8 and not with any 5.7 version. -- To add or remove an instance use the below UDF function to change the cluster mode to single-primary SELECT group_replication_switch_to_single_primary_mode(); Now you can add or remove an instance without facing issues since this will automatically set group_replication_enforce_update_everywhere_checks to OFF. -- To restore back the cluster mode to multi-primary use the below UDF function SELECT group_replication_switch_to_multi_primary_mode(); This will automatically set group_replication_enforce_update_everywhere_checks to ON which is mandatory in a multi-primary cluster. But still a fix is required for the original problem since it affects both 5.7 and 8.0 versions and I would consider it as a bug.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.