How to reproduce sysown/proxysql#1850
Setup a 3-node GR cluster and 1 async node in a single VM or Container.
[root@mgrsvr ~]# ps aux|grep mysql[d]
mysql 514 127 2.7 2443360 451456 ? Sl 01:46 1:35 mysqld --defaults-file=/config/s1.conf --user=mysql --log_error=/data/s1.log
mysql 515 129 2.7 2575004 451456 ? Sl 01:46 1:37 mysqld --defaults-file=/config/s2.conf --user=mysql --log_error=/data/s2.log
mysql 516 127 2.7 2443360 451032 ? Sl 01:46 1:35 mysqld --defaults-file=/config/s3.conf --user=mysql --log_error=/data/s3.log
mysql 819 0.0 2.5 1998760 414172 ? Sl 02:24 0:43 mysqld --defaults-file=/config/s4.conf --user=mysql --log_error=/data/s4.log
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | a86826fe-d369-11e9-a95a-00163e90c836 | mgrsvr | 24801 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | b1d598ba-d369-11e9-85c3-00163e90c836 | mgrsvr | 24802 | ONLINE | SECONDARY | 8.0.15 |
| group_replication_applier | babb4425-d369-11e9-a554-00163e90c836 | mgrsvr | 24803 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)
Setup the async node as async slave of Primary GR cluster node (24801), set node with super_read_only=1:
[root@mgrsvr ~]# mysql4 -e "show slave status\G" | head -5
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mgrsvr
Master_User: rpl_user
Master_Port: 24801
Setup a ProxySQL 2.0 server in a separate VM or Container:
mysql> select * from mysql_servers;
+--------------+----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | mgrsvr | 24801 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | mgr1 |
| 11 | mgrsvr | 24802 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | mgr2 |
| 11 | mgrsvr | 24803 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | mgr3 |
| 21 | mgrsvr | 24804 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | slave0 |
+--------------+----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)
mysql> select * from runtime_mysql_replication_hostgroups;
Empty set (0.02 sec)
mysql> select * from runtime_mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 10 | 4 | 11 | 1 | 1 | 1 | 1 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.02 sec)
Ensure that monitor is enabled on ProxySQL and there are no errors on mysql_server_connect_log nor on mysql_server_ping_log tables.
Optional, run a simple insert query in a loop through port 6033.
Execute the run1850.sh script and get the following results:
[root@proxysql20 ~]# bash run1850.sh
[Step 1] check configuration tables
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM runtime_mysql_replication_hostgroups
--------------
Empty set (0.01 sec)
Bye
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM runtime_mysql_group_replication_hostgroups
--------------
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 10 | 4 | 11 | 1 | 1 | 1 | 1 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.01 sec)
Bye
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM mysql_replication_hostgroups
--------------
Empty set (0.00 sec)
Bye
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM mysql_group_replication_hostgroups
--------------
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 10 | 4 | 11 | 1 | 1 | 1 | 1 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)
Bye
[Step 2] Insert into mysql_replication_hostgroups, then LOAD and SAVE configs then check mysql_replication_hostgroups tables
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
INSERT INTO mysql_replication_hostgroups VALUES (20,21,'super_read_only','Async')
--------------
Query OK, 1 row affected (0.00 sec)
--------------
LOAD MYSQL SERVERS TO RUNTIME
--------------
Query OK, 0 rows affected (0.02 sec)
--------------
SAVE MYSQL SERVERS TO DISK
--------------
Query OK, 0 rows affected (0.28 sec)
--------------
SELECT * FROM runtime_mysql_replication_hostgroups
--------------
+------------------+------------------+-----------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+-----------------+---------+
| 20 | 21 | super_read_only | Async |
+------------------+------------------+-----------------+---------+
1 row in set (0.04 sec)
--------------
SELECT * FROM mysql_replication_hostgroups
--------------
+------------------+------------------+-----------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+-----------------+---------+
| 20 | 21 | super_read_only | Async |
+------------------+------------------+-----------------+---------+
1 row in set (0.00 sec)
Bye
[Step 3] sleep for 10s
[Step 4] check configuration tables
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM runtime_mysql_replication_hostgroups
--------------
Empty set (0.01 sec)
Bye
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM runtime_mysql_group_replication_hostgroups
--------------
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 10 | 4 | 11 | 1 | 1 | 1 | 1 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.01 sec)
Bye
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM mysql_replication_hostgroups
--------------
Empty set (0.00 sec)
Bye
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM mysql_group_replication_hostgroups
--------------
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 10 | 4 | 11 | 1 | 1 | 1 | 1 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)
Bye
As you can see above I was able to reproduce, but this was i believe 1 of only 2 times of many times I tried to reproduce this case.
This is very hard to consistently reproduce.
Bash script used:
[root@proxysql20 ~]# cat run1850.sh
PSQL='mysql -h127.0.0.1 -P6032 -vvv'
echo -e "####"
echo -e "\n[Step 0] Cleanup mysql_replication_hostgroups and the runtime table"
$PSQL -e "DELETE FROM mysql_replication_hostgroups; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"
echo -e "\n[Step 1] check configuration tables"
$PSQL -e "SELECT * FROM runtime_mysql_replication_hostgroups;"
$PSQL -e "SELECT * FROM runtime_mysql_group_replication_hostgroups;"
$PSQL -e "SELECT * FROM mysql_replication_hostgroups;"
$PSQL -e "SELECT * FROM mysql_group_replication_hostgroups;"
echo -e "\n[Step 2] Insert into mysql_replication_hostgroups, then LOAD and SAVE configs then check mysql_replication_hostgroups tables"
$PSQL -e "INSERT INTO mysql_replication_hostgroups VALUES (20,21,'super_read_only','Async'); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; SELECT * FROM runtime_mysql_replication_hostgroups; SELECT * FROM mysql_replication_hostgroups;"
echo -e "\n[Step 3] sleep for 5s"
sleep 5;
echo -e "\n[Step 4] check configuration tables"
$PSQL -e "SELECT * FROM mysql_replication_hostgroups;"
$PSQL -e "SELECT * FROM mysql_group_replication_hostgroups;"
$PSQL -e "SELECT * FROM runtime_mysql_replication_hostgroups;"
$PSQL -e "SELECT * FROM runtime_mysql_group_replication_hostgroups;"