LOAD MYSQL SERVERS TO RUNTIME; fails to remove server from runtime pool

Joel Hanger

unread,

Oct 17, 2016, 6:30:50 PM10/17/16

to proxysql

Setup:

Simple 2 server system with proxysql frontend:
Server1 (Master)
Server2 (Slave)
ProxySQL

Just went through the simple setup for testing.

I have 1 user in mysql_users.

Pretty Standard Basic "functional" setup.

The problem is I accidentally added the slave replica to the write group and now I cannot delete it from runtime or non-runtime!

$ sudo rpm -qa | grep proxysql

proxysql-1.2.4-1.x86_6

The following transcript has been redacted of IP addresses but a simple replication of this should be doable.
I'm showing this from the point of changing the status of the intended to be removed server to OFFLINE_HARD.
I have also tried OFFLINE_SOFT and many combinations thereof each time loading servers to runtime.

Now I followed the instructions from here:

https://github.com/sysown/proxysql/wiki/Servers-configuration#completely-remove-a-backend

Am I missing something here?

mysql> select * from mysql_servers;
+--------------+------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | Server1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 0 | Server2 | 3306 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | Server2    | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

mysql> delete from mysql_servers where hostgroup_id=0 and hostname='Server2';
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql_servers;
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | Server1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | Server2 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

mysql> select * from runtime_mysql_servers;
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | Server1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 0 | Server2 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | Server2 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from runtime_mysql_servers;
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | Server1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 0 | Server2    | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | Server2    | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

mysql> select * from mysql_servers;
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | Server1    | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 0 | Server2    | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | Server2    | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

log Entries:

2016-10-17 21:58:28 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
HID: 0 , address: Server1 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 0 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 1 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
2016-10-17 21:58:28 MySQL_HostGroups_Manager.cpp:362:commit(): [WARNING] Removed server at address 139895843905792, hostgroup 0, address Server2 port 3306. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them
HID: 0 , address: Server1 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 0 , address: Server2 , port: 3306 , weight: 1 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 1 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 0 , address: Server1 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 0 , address: Server2 , port: 3306 , weight: 1 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 1 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
2016-10-17 21:58:28 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 0 , reader_hostgroup: 1, (null)
HID: 0 , address: Server1 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 1 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 0 , address: Server1 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 1 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 0 , address: Server1 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 1 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 0 , address: Server1 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 0 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 1 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
2016-10-17 21:58:29 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 0 , reader_hostgroup: 1, (null)
HID: 0 , address: Server1 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 0 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 1 , address: Server2 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment:

René Cannaò

unread,

Oct 17, 2016, 6:43:50 PM10/17/16

to Joel Hanger, proxysql

Hi Joel,

What is the value of read_only in Server2?
If read_only=0 on Server2, this behavior is expected.
If read_only=1 on Server2, this seems a bug.

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Joel Hanger

unread,

Oct 17, 2016, 6:53:49 PM10/17/16

to proxysql, jha...@gmail.com

Are you referring to mysql_replication_hostgroups?

I have it set like this, but never saw any straight forward documentation on how to use this table:

mysql> select * from mysql_replication_hostgroups;

+------------------+------------------+---------+

| writer_hostgroup | reader_hostgroup | comment |

+------------------+------------------+---------+

| 0 | 1 | NULL |

+------------------+------------------+---------+

1 row in set (0.00 sec)

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

René Cannaò

unread,

Oct 17, 2016, 7:00:29 PM10/17/16

to proxysql

Yes, more details here: https://github.com/sysown/proxysql/blob/master/doc/configuration_howto.md#mysql-replication-hostgroups

If a server is in an hostgroup listed in mysql_replication_hostgroups, depending from the value of read_only it is moved either in the writer_hostgroup or reader_hostgroup.

What I suspect is happening is that you are removing Server2 from hostgroup0 , but the Monitor module is adding it again because it has read_only=0 .

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

Joel Hanger

unread,

Oct 17, 2016, 7:06:27 PM10/17/16

to proxysql

Aaaah, ok that makes more sense now,

I thought that was needed for read/write splitting but that's all done in the mysql_query_rules table;

So my solution then is to remove that line from the mysql_replication_hostgroup table and the rules I setup for read/write splitting will be functional as expected and the monitor will stop inserting that row into the servers table!

René Cannaò

unread,

Oct 17, 2016, 7:09:04 PM10/17/16

to proxysql

Correct.

Or you can set read_only=1 on the slave, so no surprise of any sort :)

Thanks,

René

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

Reply all

Reply to author

Forward