The purpose of connection_keepalive is to keep the less used connections alive in multi-connection services like readwritesplit. With readconnroute this behavior is not needed at all as there's only one connection and it naturally is kept in sync with the backend server's wait_timeout.

Here's an example scenario that describes the current correct behavior: a readwritesplit session connects to servers srv1 and srv2. The client executes a very long-running query that ends up being routed to srv2 that takes many hours to complete. While the query is running, the idleness value of srv1 is checked and if it exceeds connection_keepalive a ping is sent to keep it alive. Once the query on srv2 completes, the client is now logically idle: no results are expected and no queries are in-flight. At this point the pings are only sent to srv1 while the connection to srv2 has been idle for less than connection_keepalive seconds. Once the connection to srv2 has been idle for longer than connection_keepalive, the pings to srv1 stop completely. After this point the wait_timeout of the database takes over and controls how long the connections are alive.

Older MaxScale versions implemented this wrong and it unconditionally pinged any connections that were idle for longer than the configured value of connection_keepalive. This included connections to readconnroute service that normally would not benefit from connection_keepalive. MXS-4139 fixed the behavior of connection_keepalive and now only connections that weren't logically idle or were idle for a shorter time than connection_keepalive were pinged. The fixing of this problem caused a change in behavior for applications that expected to work with the old behavior of what amounted to an infinitely large wait_timeout. An option to fall back to the old unconditional pinging behavior would be needed for those applications that are unable to cope with the correct behavior.

Original description:

Since the "COM_PING" packet was removed from MaxScale new versions as an enhancement/bug fix, this creates un desirable side effects to many of the customer applications.

Particularly apps using connection pools such as JBOSS or Hikari JDBC pools. The sessions are getting terminated by the backend and pool is not able to identify these.

We request to implement a new switch (Boolean) to bring back the old ping which would send a "COM_PING" packet back to the DB every 300 seconds which would prevent the connection from going into idle state and prevent the problems that these apps are facing.

This new parameter needs to be able to set at the listener level or global level such as

connection_keepalive=true

The impact for these apps is too great and we can't ask all of them to change.