Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-4466

GR 5.7.22 not compatible with earlier versions due to lower_case_table_names

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.22-22, Not 5.6.x, Not 8.0.x
    • Fix Version/s: 5.7.23-23, 8.0.12-2rc1
    • Component/s: None
    • Labels:

      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

      On all nodes:

      mysql> select @@lower_case_table_names;
      --------------------------

      @@lower_case_table_names

      --------------------------

      0

      --------------------------
      1 row in set (0.00 sec)

      This error only happens when group_replication_ssl_mode='required'!

      When it's DISABLED it's working fine.
      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

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              gryp Kenny Gryp (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: