proxysql runtime_mysql_replication_hostgroups #1850

haisen923 · 2019-01-09T09:04:25Z

ProxySQL version 1.4.12
when config mysql_replication_hostgroups and load to runtime,table runtime_mysql_replication_hostgroups is null，but monitor table has read_only check record. is something wrong？

example:
insert into mysql_servers(hostgroup_id,hostname,port) values(678,'xxx.18',4591);
insert into mysql_servers(hostgroup_id,hostname,port) values(678,'xxx.37',4591);
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(676,678,'datacenter cluster');

save mysql servers to disk;
load mysql servers to runtime;

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
+-------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+-------------+------+------------------+-----------------+-----------+-------+
| xxxx.37 | 4591 | 1547020972908350 | 320 | 1 | NULL |
| xxxx.18 | 4591 | 1547020972876992 | 438 | 1 | NULL |
+-------------+------+------------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)

but runtime_mysql_replication_hostgroups is null
Admin> select * from runtime_mysql_replication_hostgroups;
Empty set (0.00 sec)

renecannao · 2019-01-09T09:19:19Z

I can't reproduce this:

mysql> SELECT * FROM mysql_replication_hostgroups;
Empty set (0.00 sec)

mysql> SELECT * FROM runtime_mysql_replication_hostgroups;
Empty set (0.00 sec)

mysql> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(676,678,'datacenter cluster');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.06 sec)

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql_replication_hostgroups;
+------------------+------------------+--------------------+
| writer_hostgroup | reader_hostgroup | comment            |
+------------------+------------------+--------------------+
| 676              | 678              | datacenter cluster |
+------------------+------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM runtime_mysql_replication_hostgroups;
+------------------+------------------+--------------------+
| writer_hostgroup | reader_hostgroup | comment            |
+------------------+------------------+--------------------+
| 676              | 678              | datacenter cluster |
+------------------+------------------+--------------------+
1 row in set (0.00 sec)

Hypothesis: did you run any DDL, for example dropping and recreating runtime_mysql_replication_hostgroups ?
System tables aren't meant to be modified.

haisen923 · 2019-01-09T09:59:50Z

never run any ddl on runtime table.
does mysql_group_replication_hostgroups conflict with mysql_group_replication_hostgroups ?
i have 3 mgr nodes config in mysql_group_replication_hostgroups ,and other master-slave config in mysql_replication_hostgroups .

haisen923 · 2019-01-09T10:02:19Z

Admin> select * from runtime_mysql_replication_hostgroups;
Empty set (0.00 sec)

Admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin> select * from runtime_mysql_replication_hostgroups;
Empty set (0.00 sec)

Admin>

renecannao · 2019-01-09T10:49:03Z

Are you able to reproduce this on a fresh installation?

haisen923 · 2019-01-10T03:00:21Z

can't reproduce on a fresh installation.
but in the env. i restart proxysql ,and load again.but dose not work.

renecannao · 2019-01-10T03:06:45Z

Again, I suspect you ran some DDL, probably importing some backup.
Maybe you have the same tablename in another schema, and this is conflicting.

haisen923 · 2019-01-10T06:26:54Z

yeah. i build a proxysql,and made some change.such as mysql variables.save to disk.and copy proxysql.db to build other proxysql.
work right in this way？

haisen923 · 2019-01-14T09:27:40Z

hi may be i found the reason. in fresh proxysql, insert rec into mysql_replication_hostgroups,and load to runtime,that work. but when i also insert rec in mysql_group_replication_hostgroups, and load mysql server to runtime. then loss rec in runtime_mysql_replication_hostgroups.

admin >select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+-------------------------------+
| writer_hostgroup | reader_hostgroup | comment                       |
+------------------+------------------+-------------------------------+
| 998              | 999              | datacenter cluster for report |
+------------------+------------------+-------------------------------+
1 row in set (0.00 sec)

admin >
admin >select * from mysql_group_replication_hostgroups ;
Empty set (0.00 sec)

admin >select * from mysql_replication_hostgroups;
+------------------+------------------+-------------------------------+
| writer_hostgroup | reader_hostgroup | comment                       |
+------------------+------------------+-------------------------------+
| 998              | 999              | datacenter cluster for report |
+------------------+------------------+-------------------------------+
1 row in set (0.00 sec)

admin >select * from runtime_mysql_replication_hostgroups ;
+------------------+------------------+-------------------------------+
| writer_hostgroup | reader_hostgroup | comment                       |
+------------------+------------------+-------------------------------+
| 998              | 999              | datacenter cluster for report |
+------------------+------------------+-------------------------------+
1 row in set (0.00 sec)

admin >insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader) values(10,20,30,40,1,0);
Query OK, 1 row affected (0.00 sec)

admin >load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

admin >select * from runtime_mysql_replication_hostgroups ;
Empty set (0.00 sec)

admin >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               | 20                      | 30               | 40                | 1      | 1           | 0                     | 0                       | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

haisen923 · 2019-01-14T09:31:15Z

when i delete rec in mysql_group_replication_hostgroups, and load servers to runtime, mysql_replication_hostgroups rec can load to runtime.

admin >load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

admin >select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+-------------------------------+
| writer_hostgroup | reader_hostgroup | comment                       |
+------------------+------------------+-------------------------------+
| 998              | 999              | datacenter cluster for report |
+------------------+------------------+-------------------------------+
1 row in set (0.00 sec)

admin >
admin >select * from mysql_group_replication_hostgroups ;
Empty set (0.00 sec)

admin >select * from mysql_replication_hostgroups;
+------------------+------------------+-------------------------------+
| writer_hostgroup | reader_hostgroup | comment                       |
+------------------+------------------+-------------------------------+
| 998              | 999              | datacenter cluster for report |
+------------------+------------------+-------------------------------+
1 row in set (0.00 sec)

admin >select * from runtime_mysql_replication_hostgroups ;
+------------------+------------------+-------------------------------+
| writer_hostgroup | reader_hostgroup | comment                       |
+------------------+------------------+-------------------------------+
| 998              | 999              | datacenter cluster for report |
+------------------+------------------+-------------------------------+
1 row in set (0.00 sec)

admin >insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader) values(10,20,30,40,1,0);
Query OK, 1 row affected (0.00 sec)

admin >load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

admin >select * from runtime_mysql_replication_hostgroups ;
Empty set (0.00 sec)

admin >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               | 20                      | 30               | 40                | 1      | 1           | 0                     | 0                       | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

admin >
admin >
admin >delete from mysql_group_replication_hostgroups ;
Query OK, 1 row affected (0.00 sec)


admin >
admin >load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)


admin >select * from runtime_mysql_replication_hostgroups ;
+------------------+------------------+-------------------------------+
| writer_hostgroup | reader_hostgroup | comment                       |
+------------------+------------------+-------------------------------+
| 998              | 999              | datacenter cluster for report |
+------------------+------------------+-------------------------------+
1 row in set (0.00 sec)

renecannao · 2019-01-14T14:48:56Z

mysql> select @@version;
+--------------------------+
| @@version                |
+--------------------------+
| 1.4.14-25-g61435af_DEBUG |
+--------------------------+
1 row in set (0.00 sec)

mysql> select * from runtime_mysql_replication_hostgroups ;                                                                                                                  Empty set (0.00 sec)

mysql> select * from runtime_mysql_group_replication_hostgroups ;                                                                                                            Empty set (0.01 sec)

mysql> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(676,678,'datacenter cluster');
Query OK, 1 row affected (0.01 sec)

mysql> load mysql servers to runtime;                                                                                                                                        Query OK, 0 rows affected (0.00 sec)

mysql> select * from runtime_mysql_replication_hostgroups ;
+------------------+------------------+--------------------+
| writer_hostgroup | reader_hostgroup | comment            |
+------------------+------------------+--------------------+
| 676              | 678              | datacenter cluster |
+------------------+------------------+--------------------+
1 row in set (0.01 sec)

mysql> select * from runtime_mysql_group_replication_hostgroups ;
Empty set (0.01 sec)

mysql> insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader) values(10,20,30,40,1,0);                                                                                                                                                                  Query OK, 1 row affected (0.00 sec)

mysql> load mysql servers to runtime;                                                                                                                                        Query OK, 0 rows affected (0.01 sec)

mysql> select * from runtime_mysql_replication_hostgroups ;                                                                                                                  +------------------+------------------+--------------------+
| writer_hostgroup | reader_hostgroup | comment            |
+------------------+------------------+--------------------+
| 676              | 678              | datacenter cluster |
+------------------+------------------+--------------------+
1 row in set (0.00 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               | 20                      | 30               | 40                | 1      | 1           | 0                     | 0                       | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

I really can't reproduce it.
I used 1.4.14 , but no relevant changes since 1.4.12

haisen923 · 2019-01-15T14:18:49Z

thk,
when 1.4.14 GA? then i test it again.

haisen923 · 2019-02-18T08:25:39Z

hi i have new found。
there is important thing is that. monitor user is realy connect to backend mysql sever.
in fresh proxysql，reproduce the problem.

PS:mysql in mysql server is mysql 8 in mgr ，single primary mod.and read to connect.
monitor variables ,should config and should connect to mysql server.

Admin> select * from mysql_replication_hostgroups;
Empty set (0.00 sec)

Admin> select * from mysql_group_replication_hostgroups;
Empty set (0.00 sec)

Admin>
Admin> select * from runtime_mysql_replication_hostgroups;
Empty set (0.00 sec)

Admin> select * from runtime_mysql_group_replication_hostgroups;
Empty set (0.00 sec)

Admin> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(899,999);
Query OK, 1 row affected (0.00 sec)

Admin> insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader) values(10,20,30,40,1,0);
Query OK, 1 row affected (0.00 sec)

Admin>
Admin> save mysql servers to disk;
load mysql servers to runtime;
Query OK, 0 rows affected (0.06 sec)

Admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.01 sec)

Admin>
Admin>
Admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.21.16.6',4888);
Query OK, 1 row affected (0.00 sec)

Admin> save mysql servers to disk;
Query OK, 0 rows affected (0.08 sec)

Admin> load mysql server to runtime;
ERROR 1045 (#2800): near "load": syntax error
Admin>
Admin>
Admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin>
Admin> select * from runtime_mysql_servers;
+--------------+------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 10.21.16.6 | 4888 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.00 sec)

Admin>
Admin>
Admin> UPDATE global_variables SET variable_value='proxy_mon' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

Admin>
Admin>
Admin> save mysql variables to disk;
save admin variables to disk;
Query OK, 97 rows affected (0.01 sec)

Admin> save admin variables to disk;

Query OK, 31 rows affected (0.02 sec)

Admin>
Admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.01 sec)

PS:modify monitor password to incorrent password，then don not make runtime_mysql_replication_hostgroups lose data.

Admin>
Admin> UPDATE global_variables SET variable_value='aa#34kk' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

Admin> save mysql variables to disk;
save admin variables to disk;

Query OK, 97 rows affected (0.01 sec)

Admin> save admin variables to disk;
load mysql variables to runtime;
Query OK, 31 rows affected (0.02 sec)

Admin>
Admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.01 sec)

PS:modify monitor password to actual password，then runtime_mysql_replication_hostgroups lost data。
Admin> UPDATE global_variables SET variable_value='xxxxx' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

Admin>
Admin> save mysql variables to disk;
save admin variables to disk;

Query OK, 97 rows affected (0.01 sec)

Admin> save admin variables to disk;
load mysql variables to runtime;
Query OK, 31 rows affected (0.02 sec)

Admin>
Admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

Admin>
Admin> select * from runtime_mysql_replication_hostgroups;
Empty set (0.00 sec)

Admin> 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 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

haisen923 · 2019-02-18T09:00:00Z

proxysql version: |1.4.15-1-g61bc777
mysql : mysql version 8.0.13 ,in mgr ，single primary mod.
proxysql monitor user can connect to mysql to monitor check.

reproduct step:
1,
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(899,999);
insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader) values(10,20,30,40,1,0);

2, insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.21.16.6',4888);

3,
load mysql servers to runtime;
save mysql servers to disk;

4, check runtime_mysql_replication_hostgroups & runtime_mysql_group_replication_hostgroups data ok.

5, update monitor variables, then monitor mod can connect to mysql do monitor check.

UPDATE global_variables SET variable_value='proxy_mon' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor-passwd-in-your-mysql' WHERE variable_name='mysql-monitor_password';

save mysql variables to disk;
save admin variables to disk;

load mysql variables to runtime;
load admin variables to runtime;

6, check runtime_mysql_replication_hostgroups & runtime_mysql_group_replication_hostgroups data .
then runtime_mysql_replication_hostgroups lose data.

ycg · 2019-03-08T08:36:00Z

if you use mgr， you only config the mysql_group_replication_hostgroups table，don't config mysql_replication_hostgroups.

i not config two table at the same time.

haisen923 · 2019-03-08T09:10:54Z

in Production Environment，have master-salve application and MGR application。
sometime MGR application need to access master-slave tables.
so i put mgr and master-salve in the same proxy.

this can reproduce fllow the step i post.can fixed this problem?

svetasmirnova · 2019-08-11T09:07:22Z

@haisen923

I cannot repeat this either.

Could you please send us your MySQL topology?

For example, I start master and slave using MySQL Test Framework:

$ cd mysql-test
$ ./mtr --start --suite=rpl rpl_alter &
$ mysql -h127.0.0.1 -P13001 -uroot -e "CHANGE MASTER TO master_host='127.0.0.1', master_port=13000, master_user='root'; START SLAVE;"

This creates a simple master-slave setup.

Please let us know what you do in your case. If you are using group replication, please, describe how many nodes and how do you start them.

svetasmirnova · 2019-08-11T10:11:57Z

Not repeatable also with 3-nodes group replication cluster which is master of single-node asynchronous slave.

riveraja · 2019-08-11T14:09:20Z

I've tested this and was able to reproduce missing runtime_mysql_group_replication_hostgroups data even after LOAD MYSQL SERVERS TO RUNTIME and SAVE MYSQL SERVERS TO RUNTIME was executed.

Setup:
3-node MySQL Group Replication - single-primary cluster
1 async slave node replicating from node3

mysql> select * from runtime_mysql_servers;
+--------------+----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port  | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | mgrsvr   | 24803 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | mgr3    |
| 21           | mgrsvr   | 24804 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | slave0  |
| 11           | 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    |
+--------------+----------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
5 rows in set (0.02 sec)

runtime_mysql_group_replication_hostgroups is configured like this:

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.01 sec)

The idea was to configure ProxySQL in such a way that if all group replication members are lost the remaining asynchronous slave would have to be the next writer node. This type of topology may not have been tested or may not even be supported but then I found out that inserting data in mysql_replication_hostgroups and trying to persist it with LOAD and SAVE commands will work for a few seconds but eventually runtime_mysql_replication_hostgroups table will become empty, give or take 5 seconds, without manual intervention. I have tested this with mysql_group_replication_hostgroups configured. I wonder if it is possible to configure both mysql_group_replication_hostgroups and mysql_replication_hostgroups in the same ProxySQL instance, or if this is a limitation.

So with the setup above, insert data into mysql_replication_hostgroups and then execute LOAD/SAVE then check table content.

Here's an example script to simulate the issue.

[root@proxysql20 ~]# cat run1850.sh
PSQL='mysql -uadmin -padmin -h127.0.0.1 -P6032 -vvv'

echo -e "[Step 1] check mysql_replication_hostgroups table"
$PSQL -e "SELECT * FROM mysql_replication_hostgroups;"

echo -e "\n[Step 2] check runtime_mysql_replication_hostgroups table"
$PSQL -e "SELECT * FROM runtime_mysql_replication_hostgroups;"

echo -e "\n[Step 3] insert data into mysql_replication_hostgroups and run LOAD and SAVE then check table contents"
$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 4] sleep 5s"
sleep 5;

echo -e "\n[Step 5] check runtime_mysql_replication_hostgroups table"
$PSQL -e "SELECT * FROM runtime_mysql_replication_hostgroups;"

echo -e "\n[Step 6] check mysql_replication_hostgroups table"
$PSQL -e "SELECT * FROM mysql_replication_hostgroups;"

Result of above run:

[root@proxysql20 ~]# bash run1850.sh
[Step 1] check mysql_replication_hostgroups table
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM mysql_replication_hostgroups
--------------

Empty set (0.00 sec)

Bye

[Step 2] check runtime_mysql_replication_hostgroups table
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

[Step 3] insert data into mysql_replication_hostgroups and run LOAD and SAVE then check table contents
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.02 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 4] sleep 5s

[Step 5] check runtime_mysql_replication_hostgroups table
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

[Step 6] check mysql_replication_hostgroups table
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
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

See in Step 5 runtime_mysql_replication_hostgroups is empty even though in Step 3 we can see it was not.

renecannao · 2019-08-13T17:26:38Z

@riveraja : please attach the full error log.
Thanks

riveraja · 2019-08-13T22:36:32Z

proxysql.log
See attached error log. Please ignore lines after

2019-08-12 10:16:34 ProxySQL_Admin.cpp:9070:flush_error_log(): [INFO] ProxySQL version 2.0.4-116-g7d371cf_DEBUG
2019-08-12 10:16:34 ProxySQL_Admin.cpp:9073:flush_error_log(): [INFO] Detected OS: Linux proxysql20 4.15.0-55-generic #60-Ubuntu SMP Tue Jul 2 18:22:20 UTC 2019 x86_64
2019-08-12 10:16:34 ProxySQL_Admin.cpp:9076:flush_error_log(): [INFO] ProxySQL SHA1 checksum: 7d367656a568d8d80abcec0e5637837b8606f2ef
2019-08-12 10:16:34 main.cpp:1296:ProxySQL_daemonize_phase2(): [INFO] Starting ProxySQL

The proxysql server was inadvertently restarted but tests were done before this time.

Fwiw, now that the GR nodes and async node are offline and inaccessible, the proxysql configuration looks correct with data in runtime_mysql_group_replication_hostgroups available. So I am not sure what I'm missing here.

mysql> select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+-----------------+---------+
| writer_hostgroup | reader_hostgroup | check_type      | comment |
+------------------+------------------+-----------------+---------+
| 20               | 21               | super_read_only | Async   |
+------------------+------------------+-----------------+---------+
1 row in set (0.02 sec)

riveraja · 2019-09-18T06:02:45Z

Rene, it seems like the issue is reproducible with a clustered ProxySQL at least 2-nodes, then setup a 3-node MySQL GR cluster + 1 async slave. setup should be simple. Based on my tests this only happens if a secondary GR member will be set to OFFLINE_HARD by ProxySQL if slave starts lagging for this you'll need to run sysbench to put at least one secondary in lagging state.
not sure if ProxySQL clustering is needed to reproduce but to me it seems easier with a clustered ProxySQL

dbadapt · 2019-11-11T15:27:26Z

Hi René, we have tested with 2.0.7 and we are still seeing the problem.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

proxysql runtime_mysql_replication_hostgroups #1850

proxysql runtime_mysql_replication_hostgroups #1850

haisen923 commented Jan 9, 2019

renecannao commented Jan 9, 2019

haisen923 commented Jan 9, 2019 •

edited

Loading

haisen923 commented Jan 9, 2019

renecannao commented Jan 9, 2019

haisen923 commented Jan 10, 2019

renecannao commented Jan 10, 2019

haisen923 commented Jan 10, 2019

haisen923 commented Jan 14, 2019 •

edited by renecannao

Loading

haisen923 commented Jan 14, 2019 •

edited by renecannao

Loading

renecannao commented Jan 14, 2019

haisen923 commented Jan 15, 2019

haisen923 commented Feb 18, 2019 •

edited

Loading

haisen923 commented Feb 18, 2019 •

edited

Loading

ycg commented Mar 8, 2019

haisen923 commented Mar 8, 2019

svetasmirnova commented Aug 11, 2019

svetasmirnova commented Aug 11, 2019

riveraja commented Aug 11, 2019

renecannao commented Aug 13, 2019

riveraja commented Aug 13, 2019

riveraja commented Sep 18, 2019

dbadapt commented Nov 11, 2019

proxysql runtime_mysql_replication_hostgroups #1850

proxysql runtime_mysql_replication_hostgroups #1850

Comments

haisen923 commented Jan 9, 2019

renecannao commented Jan 9, 2019

haisen923 commented Jan 9, 2019 • edited Loading

haisen923 commented Jan 9, 2019

renecannao commented Jan 9, 2019

haisen923 commented Jan 10, 2019

renecannao commented Jan 10, 2019

haisen923 commented Jan 10, 2019

haisen923 commented Jan 14, 2019 • edited by renecannao Loading

haisen923 commented Jan 14, 2019 • edited by renecannao Loading

renecannao commented Jan 14, 2019

haisen923 commented Jan 15, 2019

haisen923 commented Feb 18, 2019 • edited Loading

haisen923 commented Feb 18, 2019 • edited Loading

ycg commented Mar 8, 2019

haisen923 commented Mar 8, 2019

svetasmirnova commented Aug 11, 2019

svetasmirnova commented Aug 11, 2019

riveraja commented Aug 11, 2019

renecannao commented Aug 13, 2019

riveraja commented Aug 13, 2019

riveraja commented Sep 18, 2019

dbadapt commented Nov 11, 2019

haisen923 commented Jan 9, 2019 •

edited

Loading

haisen923 commented Jan 14, 2019 •

edited by renecannao

Loading

haisen923 commented Jan 14, 2019 •

edited by renecannao

Loading

haisen923 commented Feb 18, 2019 •

edited

Loading

haisen923 commented Feb 18, 2019 •

edited

Loading