MySQL 8.0.2 is out, some change is required to still use ProxySQL with GR

As you may know by now, MySQL 8.0.2 DMR is out ! \o/

Many features have been added (see Geir Høydalsvik‘s announcement) and of course the replication also brought new features and improvements (see this post from Luís Soares).

But some improvements in the monitoring broke the added sys view used by ProxySQL to monitor the state of a member of the group:

mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+

This is because now the table group_replication_member_stats in performance_schema also returns the status of all the members of the Group:

mysql> select * from performance_schema.replication_group_member_stats\G                                       
*************************** 1. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 15004030793848916:3
                                 MEMBER_ID: b94b8ae4-6672-11e7-8255-08002718d305
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 189441
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 6
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1351db06-66f4-11e7-8363-08002718d305:1-34321:1000003-1000039,
388b357a-6be8-11e7-bfb3-08002718d305:1-185437:1000003-1000009,
62ad8685-667f-11e7-9056-08002718d305:1-8,
7d37e993-6bb0-11e7-be8f-08002718d305:1-14382,
abe0ad9e-6674-11e7-9056-08002718d305:1-16,
b42e6e27-66f7-11e7-9d7e-08002718d305:1-20118:1000003-1000033,
b9549b64-6672-11e7-8d90-08002718d305:1-43,
bbd5b661-667f-11e7-8792-08002718d305:1-100866:1000011-1005455:2000011-2000012
            LAST_CONFLICT_FREE_TRANSACTION: 388b357a-6be8-11e7-bfb3-08002718d305:189053
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 189442
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 2. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 15004030793848916:3
                                 MEMBER_ID: b9549b64-6672-11e7-8d90-08002718d305
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 189434
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 3
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1351db06-66f4-11e7-8363-08002718d305:1-34321:1000003-1000039,
388b357a-6be8-11e7-bfb3-08002718d305:1-185437:1000003-1000009,
62ad8685-667f-11e7-9056-08002718d305:1-8,
7d37e993-6bb0-11e7-be8f-08002718d305:1-14382,
abe0ad9e-6674-11e7-9056-08002718d305:1-16,
b42e6e27-66f7-11e7-9d7e-08002718d305:1-20118:1000003-1000033,
b9549b64-6672-11e7-8d90-08002718d305:1-43,
bbd5b661-667f-11e7-8792-08002718d305:1-100866:1000011-1005455:2000011-2000012
            LAST_CONFLICT_FREE_TRANSACTION: 388b357a-6be8-11e7-bfb3-08002718d305:188801
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 9
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 189427
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
*************************** 3. row ***************************
                              CHANNEL_NAME: group_replication_applier
                                   VIEW_ID: 15004030793848916:3
                                 MEMBER_ID: b9c218cc-6672-11e7-a747-08002718d305
               COUNT_TRANSACTIONS_IN_QUEUE: 0
                COUNT_TRANSACTIONS_CHECKED: 189462
                  COUNT_CONFLICTS_DETECTED: 0
        COUNT_TRANSACTIONS_ROWS_VALIDATING: 266
        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1351db06-66f4-11e7-8363-08002718d305:1-34321:1000003-1000039,
388b357a-6be8-11e7-bfb3-08002718d305:1-188801:1000003-1000009,
62ad8685-667f-11e7-9056-08002718d305:1-8,
7d37e993-6bb0-11e7-be8f-08002718d305:1-14382,
abe0ad9e-6674-11e7-9056-08002718d305:1-16,
b42e6e27-66f7-11e7-9d7e-08002718d305:1-20118:1000003-1000033,
b9549b64-6672-11e7-8d90-08002718d305:1-43,
bbd5b661-667f-11e7-8792-08002718d305:1-100866:1000011-1005455:2000011-2000012
            LAST_CONFLICT_FREE_TRANSACTION: 388b357a-6be8-11e7-bfb3-08002718d305:189458
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 2
         COUNT_TRANSACTIONS_REMOTE_APPLIED: 189453
         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 7
         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
3 rows in set (0.32 sec)

So if you are using ProxySQL with its native MySQL Group Replication support, you have to use the following addition to sys: addition_to_sys_8.0.2.sql

Enjoy discovering 8.0.2 !