Remove shunned servers

[robellison]

i've got a fairly dynamic environment where servers can come and go with different IP addresses
at the moment when a server on a particular IP is removed from the back-end cluster, it remains in the proxySQL mysql_servers list (obviously)
the problem is that even though the servers are shunned, periodically the come back online which causes issues - as they are then used for connections, even though there is no server there.
i need to either fix this behaviour so ProxySQL checks that the server is actually there before marking it back online or remove the shunned servers

on the latter i have this query, but for some reason it doesn't work
DELETE mysql_servers
FROM mysql_servers
LEFT JOIN
stats.stats_mysql_connection_pool
on mysql_servers.hostname = stats.stats_mysql_connection_pool.srv_host
where stats.stats_mysql_connection_pool.status = 'SHUNNED';

any help appreciated

[pondix]

Hi @robellison

The "SHUNNED" status is a temporary status, ProxySQL keeps checking if the node is back online by design in order to send connections to the server once it is available. This differs from the various OFFLINE statuses.

It is strongly recommended to DELETE the specific servers which will become inactive from ProxySQL before removing the IP address from the backend host. The SHUNNED status may be set on a host which is temporarily unavailable due to other reasons (i.e. a temporary network issue on a host that should in fact remain ONLINE) and you may inadvertently remove servers from your configuration which are still active by using the approach you've described.

In any case, regarding the query you are trying to execute, please keep in mind that although ProxySQL Admin supports the MySQL Protocol the backend is SQLite3 and you can also use the runtime_mysql_servers table to identify the actual status of a backend server. The correct statement would be:

DELETE FROM mysql_servers 
WHERE EXISTS 
  (SELECT * FROM runtime_mysql_servers WHERE status = 'SHUNNED');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

We'll look at ways to improve this behaviour.

Thanks!

INTERNAL NOTE: Left issue open and added the enhancement label to look at better ways of handling this scenario.

[pondix]

Fixed in https://github.com/sysown/proxysql/releases/tag/v1.4.8