ProxySQL2.0 is not sending COM_PING when multiplexing is disabled

[altmannmarcelo]

ProxySQL 2.0 is not refreshing connection from its pool if multiplexing is disabled.

How to repeat:

On Proxysql Admin interface:

set mysql-multiplexing='false';
set mysql-ping_interval_server_msec = 10000;
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

On MySQL backend:

SET GLOBAL wait_timeout=30;
SET GLOBAL interactive_timeout=30;

Make sure you don't have any backend connection already stablish otherwise the new timeouts won't take effect.

• Connect via proxysql on port 6033 and issue any query like SELECT 1
• Disconnect
• Check via stats_mysql_free_connections that the connection came back to pool
• Monitor SHOW PROCESSLIST directly on backend, you will see an idle connection that will be killed after 30s
• Connect again via proxysql on port 6033 and issue any query like SELECT 1
  At this point you will see an error in the proxysql log stating it tried to use a connection from the pool that was already closed by MySQL

Notes:

• This doesn't happen with multiplexing enabled
• This doesn't happen with ProxySQL 1.4

[root@marcelo-altmann-bugs-proxysql-1 ~]# proxysql --version
ProxySQL version 2.0.4-percona-1.2, codename Truls
[root@marcelo-altmann-bugs-proxysql-1 ~]# uname -a
Linux marcelo-altmann-bugs-proxysql-1 4.20.13-1.el7.elrepo.x86_64 #1 SMP Wed Feb 27 10:02:05 EST 2019 x86_64 x86_64 x86_64 GNU/Linux
[root@marcelo-altmann-bugs-proxysql-1 ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)

[jbreen]

As I started this with Percona, I'd like to add this comment to Marcelo's description.
production environment ProxySQL version 2.0.4-116-g7d371cf, codename Truls

To add to this, if the variable mysql-connection_max_age_ms is enabled, connections in the free pool that have been sleeping for mysql-connection_max_age_ms amount of time are disconnected as expected. HOWEVER, all pool connections where multiplexing is disabled (or if multiplexing is globally disabled) are terminated at the same time, basically flushing the connection pool. For those connections that multiplexing is disabled, the idle_ms field is incremented identically across all connections with the same value.

[renecannao]

I didn't verify this yet, but I suspect a bug here.

[ajayanty]

@renecannao when is this going to be fixed?

[renecannao]

Fixed in upcoming 2.0.6

[houndegnonm]

Thanks @renecannao, do you have an ETA when 2.0.6 will be released

[renecannao]

Closing as fixed