Description:
Upgrading a cluster from 5.7.21 to 5.7.22 by upgrading node by node is not possible.
Related Bug: https://bugs.mysql.com/bug.php?id=89287
Related PR: https://github.com/mysql/mysql-server/commit/8162de58b7baaaccca653fe7cd5a00074b670d19
How to repeat:
A. Set up 2 node cluster with 5.7.21
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | be05eb9d-3ea2-11e8-a55b-08002789cd2e | node2 | 3306 | ONLINE |
| group_replication_applier | be7bc9fd-3ea2-11e8-a33f-08002789cd2e | node3 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)
B. Add a third node that is running 5.7.22
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)
mysql> start group_replication;
2018-05-08T15:29:11.345609Z 2 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "REQUIRED"; server_key_file: "server-key.pem"; server_cert_file: "server-cert.pem"; client_key_file: "server-key.pem"; client_cert_file: "server-cert.pem"; ca_file: "ca.pem"; ca_path: ""; cipher: ""; tls_version: "TLSv1,TLSv1.1"; crl_file: ""; crl_path: ""'
2018-05-08T15:29:11.345937Z 2 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 10.0.2.15/24,127.0.0.1/8,192.168.56.2/24 to the whitelist'
2018-05-08T15:29:11.349677Z 2 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2018-05-08T15:29:11.350690Z 2 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "4a27bae6-3eae-11e8-8af1-08002789cd2e"; group_replication_local_address: "192.168.56.2:13306"; group_replication_group_seeds: "node2:13306,node3:13306"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"'
2018-05-08T15:29:11.350755Z 2 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2018-05-08T15:29:11.350764Z 2 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2018-05-08T15:29:11.350794Z 2 [Note] Plugin group_replication reported: 'Member configuration: member_id: 2390568997; member_uuid: "bbbda443-3ea2-11e8-a9a3-08002789cd2e"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; '
2018-05-08T15:29:11.351461Z 4 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2018-05-08T15:29:11.366599Z 7 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './node1-relay-bin-group_replication_applier.000031' position: 4
2018-05-08T15:29:11.367315Z 2 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2018-05-08T15:29:11.367340Z 2 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2018-05-08T15:29:11.367346Z 2 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 2390568997'
2018-05-08T15:29:11.398932Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2018-05-08T15:29:11.398966Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 13306'
2018-05-08T15:29:13.549503Z 0 [ERROR] Plugin group_replication reported: 'The member is configured with a lower_case_table_names option value '0' different from the group '4293526158'. The member will now exit the group. If there is existing data on member, it may be incompatible with group if created with a lower_case_table_names value different from the group.'
2018-05-08T15:29:13.549596Z 0 [Note] Plugin group_replication reported: 'Group membership changed to , node2:3306, node3:3306 on view 15257931333234357:3.'
2018-05-08T15:29:13.549705Z 2 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2018-05-08T15:29:17.078874Z 0 [Note] Plugin group_replication reported: 'Group membership changed: This member has left the group.'
2018-05-08T15:29:22.080366Z 2 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2018-05-08T15:29:22.080411Z 2 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2018-05-08T15:29:22.080646Z 7 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2018-05-08T15:29:22.082282Z 4 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
Settings:
mysql> show global variables like 'group%';
+----------------------------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------------------------+--------------------------------------+
| group_concat_max_len | 1024 |
| group_replication_allow_local_disjoint_gtids_join | OFF |
| group_replication_allow_local_lower_version_join | OFF |
| group_replication_auto_increment_increment | 7 |
| group_replication_bootstrap_group | OFF |
| group_replication_components_stop_timeout | 31536000 |
| group_replication_compression_threshold | 1000000 |
| group_replication_enforce_update_everywhere_checks | OFF |
| group_replication_flow_control_applier_threshold | 25000 |
| group_replication_flow_control_certifier_threshold | 25000 |
| group_replication_flow_control_mode | QUOTA |
| group_replication_force_members | |
| group_replication_group_name | 4a27bae6-3eae-11e8-8af1-08002789cd2e |
| group_replication_group_seeds | node2:13306,node3:13306 |
| group_replication_gtid_assignment_block_size | 1000000 |
| group_replication_ip_whitelist | AUTOMATIC |
| group_replication_local_address | 192.168.56.2:13306 |
| group_replication_member_weight | 50 |
| group_replication_poll_spin_loops | 0 |
| group_replication_recovery_complete_at | TRANSACTIONS_APPLIED |
| group_replication_recovery_reconnect_interval | 60 |
| group_replication_recovery_retry_count | 10 |
| group_replication_recovery_ssl_ca | |
| group_replication_recovery_ssl_capath | |
| group_replication_recovery_ssl_cert | |
| group_replication_recovery_ssl_cipher | |
| group_replication_recovery_ssl_crl | |
| group_replication_recovery_ssl_crlpath | |
| group_replication_recovery_ssl_key | |
| group_replication_recovery_ssl_verify_server_cert | OFF |
| group_replication_recovery_use_ssl | OFF |
| group_replication_single_primary_mode | ON |
| group_replication_ssl_mode | REQUIRED |
| group_replication_start_on_boot | OFF |
| group_replication_transaction_size_limit | 0 |
| group_replication_unreachable_majority_timeout | 0 |
+----------------------------------------------------+--------------------------------------+
36 rows in set (0.00 sec)
The important message is :
2018-05-08T15:29:13.549503Z 0 [ERROR] Plugin group_replication reported: 'The member is configured with a lower_case_table_names option value '0' different from the group '4293526158'. The member will now exit the group. If there is existing data on member, it may be incompatible with group if created with a lower_case_table_names value different from the group.'
Looking at the code:
* log_message shows it will print the cluster setting of lower_case_table_names, 4293526158 seems not a good value
https://github.com/mysql/mysql-server/commit/8162de58b7baaaccca653fe7cd5a00074b670d19#diff...
* the member_info of the older versions of mysql gr don't have the variable provided: https://github.com/mysql/mysql-server/commit/8162de58b7baaaccca653fe7cd5a00074b670d19#diff...
* there are no tests that test backwards compatibility https://github.com/mysql/mysql-server/commit/8162de58b7baaaccca653fe7cd5a00074b670d19#diff...
Suggested fix:
Options I see:
* Document that upgrading to 5.7.22 has to be done by bringing the whole cluster down (for production environments, this is quite a big issue)
* Make it backwards compatible
