Description
Using ProxySQL v1.2.0e, if I manually mark all MySQL servers in a hostgroup as offline and send a new query to the hostgroup, it seems that the proxy simply picks a backend and starts sending new queries to it, overriding the state of the backend server. Is this intended behavior?
I would have expected in this scenario for the proxy to not send the query anywhere and just block until either a timeout is reached or a server in the hostgroup is marked as online. There may be reasons the administrator doesn't want particular databases to be used under any circumstances, even if they appear to the proxy to be healthy. Is there currently any way to get my "expected" behavior?
As a minimal test case, I've set up ProxySQL in a vagrant environment, configured to route all traffic to hostgroup 0.
[vagrant@client ~]$ mysql -uadmin -pREDACTED -h192.168.33.13 -P6032 -e 'select * from mysql_servers'
[vagrant@client ~]$ mysql -uroot -pREDACTED -h192.168.33.13 -P6033 information_schema -e 'select @@hostname'
ERROR 1045 (#2800) at line 1: Max connect timeout reached while reaching hostgroup 0 after 10000ms
No MySQL backends have been configured, so the query cannot be routed.
[vagrant@client ~]$ mysql -uadmin -pREDACTED -h192.168.33.13 -P6032 -e "insert into mysql_servers (hostname) values ('192.168.33.11'); LOAD MYSQL SERVERS TO RUNTIME"
[vagrant@client ~]$ mysql -uadmin -pREDACTED -h192.168.33.13 -P6032 -e 'select * from mysql_servers'
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 0 | 192.168.33.11 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
[vagrant@client ~]$ mysql -uroot -pREDACTED -h192.168.33.13 -P6033 information_schema -e 'select @@hostname'
+-----------------+
| @@hostname |
+-----------------+
| db1.localdomain |
+-----------------+
Now that a backend is added to the hostgroup, queries are routed to it.
[vagrant@client ~]$ mysql -uadmin -pREDACTED -h192.168.33.13 -P6032 -e "update mysql_servers set status='OFFLINE_HARD'; LOAD MYSQL SERVERS TO RUNTIME"
[vagrant@client ~]$ mysql -uadmin -pREDACTED -h192.168.33.13 -P6032 -e 'select * from mysql_servers'
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
| 0 | 192.168.33.11 | 3306 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
[vagrant@client ~]$ mysql -uroot -pREDACTED -h192.168.33.13 -P6033 information_schema -e 'select @@hostname'
+-----------------+
| @@hostname |
+-----------------+
| db1.localdomain |
+-----------------+
Queries are still sent to the backend, despite having been marked as offline. I would have instead expected the same error message as in the first query.
[vagrant@client ~]$ mysql -uadmin -pREDACTED -h192.168.33.13 -P6032 -e 'select * from mysql_servers'
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
| 0 | 192.168.33.11 | 3306 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 |
+--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+
[vagrant@client ~]$ mysql -uadmin -pREDACTED -h192.168.33.13 -P6032 -e 'SAVE MYSQL SERVERS FROM RUNTIME'
[vagrant@client ~]$ mysql -uadmin -pREDACTED -h192.168.33.13 -P6032 -e 'select * from mysql_servers'
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 0 | 192.168.33.11 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
Pulling the runtime config back into memory reveals that this server has been returned to an online state.