connection_max_age_ms does not bound connection age: reset() restarts the age counter

Summary

mysql-connection_max_age_ms does not bound the age of a backend connection. It bounds the time since the connection was last reset, so a connection that keeps being picked up, used and recycled can survive indefinitely and never be closed.

The same bug exists on the PostgreSQL side with pgsql-connection_max_age_ms, in a slightly worse form (see below).

Reported by @takaidohigasi in #5653: https://github.com/sysown/proxysql/issues/5653#issuecomment-5524377787 — observed in production as a small population of backend connections surviving indefinitely, visible as a steady Com_backend_change_user rate.

Root cause

MySQL_Connection::reset() stamps the creation time (lib/mysql_connection.cpp:3305):

void MySQL_Connection::reset() {
	...
	creation_time = monotonic_time();

and the age predicate is relative to that value (lib/mysql_data_stream.cpp:1822):

unsigned long long intv = mysql_thread___connection_max_age_ms;
intv *= 1000;
if (
	(( (intv) && (mc->last_time_used > mc->creation_time + intv) )
	|| ( mc->local_stmts->get_num_backend_stmts() > (unsigned int)GloMTH->variables.max_stmts_per_connection ))
	&& sess->status != PINGING_SERVER
) {
	if (mysql_thread___reset_connection_algorithm == 2 && mc->healthy) {
		sess->create_new_session_and_reset_connection(this);   // COM_CHANGE_USER
	} else {
		destroy_MySQL_Connection_From_Pool(true);
	}
}

So an aged-out connection takes the COM_CHANGE_USER branch, reset() refreshes creation_time, and the connection returns to the pool looking brand new. The age counter restarts on every recycle.

Why the one-line fix is a regression

The obvious fix — drop the creation_time assignment from reset() — is exactly the behaviour that #1393 fixed in 2018:

ec7e9e496  2018-02-27  "Reset connection creation time on CHANGE USER #1393"

and #1393 describes the resulting symptom:

Because creation_time is not reset, if mysql-connection_max_age_ms is set the connection is continuously reset when this age is reached.

The loop is still reachable in current code:

1. MySQL_Data_Stream::return_MySQL_Connection_To_Pool() sees the connection aged out → create_new_session_and_reset_connection()
2. → new session with status = RESETTING_CONNECTION → MySQL_Session::handler_again___status_RESETTING_CONNECTION() (lib/MySQL_Session.cpp:2233)
3. → async_change_user() succeeds → reset() → return_MySQL_Connection_To_Pool() is called again (lib/MySQL_Session.cpp:2246-2249)

With creation_time preserved, step 3 re-evaluates the same predicate, it is still true, and the connection goes straight back to step 1. The sess->status != PINGING_SERVER guard does not break the cycle: set_status(session_status___NONE) runs after the inner return_MySQL_Connection_To_Pool(), so the status is still RESETTING_CONNECTION at that point.

The 2018 change stopped the loop by making aged connections effectively immortal, rather than by closing them. That is the underlying design problem.

Three recycling paths, not one

Any real fix has to account for every place that recycles a connection instead of closing it:

1. MySQL_Data_Stream::return_MySQL_Connection_To_Pool() → create_new_session_and_reset_connection() → COM_CHANGE_USER, inline (lib/mysql_data_stream.cpp:1822).

2. MySQL_HostGroups_Manager::destroy_MyConn_from_pool() (lib/MySQL_HostGroups_Manager.cpp:2605). If the server is ONLINE, c->send_quit is set, the reset queue is not full and the connection is ASYNC_IDLE, it does to_del = false; queue.add(c) instead of deleting. The HGCU_thread_run() consumer then calls myconn->reset() and MyHGM->push_MyConn_to_pool(myconn) (lib/MySQL_HostGroups_Manager.cpp:227).

   This means destroy_MySQL_Connection_From_Pool(true) does not reliably destroy anything — so simply routing aged-out connections to the "destroy" branch is not sufficient either. They land on the reset queue and come back.

3. PgSQL: PgSQL_Data_Stream::destroy_MySQL_Connection_From_Pool(true) (lib/PgSQL_Data_Stream.cpp:1256) calls create_new_session_and_reset_connection() directly.

The only path that genuinely deletes aged connections is the idle purge in MySQL_HostGroups_Manager (lib/MySQL_HostGroups_Manager.cpp:3031), and it only reaches connections that sit idle in the pool long enough to be scanned. That is why a busy subset survives forever, which matches the production report.

PostgreSQL side

Same bug, worse shape:

• PgSQL_Connection::reset() stamps creation_time (lib/PgSQL_Connection.cpp:2601).
• PgSQL_Data_Stream::return_MySQL_Connection_To_Pool() (lib/PgSQL_Data_Stream.cpp:1222) has no reset_connection_algorithm / healthy guard at all — it always takes the reset path.
• PgSQL_Session::handler_again___status_RESETTING_CONNECTION() (lib/PgSQL_Session.cpp:1150) mirrors the MySQL flow: async_reset_session() → reset() → return_MySQL_Connection_To_Pool().

Proposed fix

Treat "expired" as a property checked at every recycle decision point, meaning close, never recycle:

1. reset() no longer touches creation_time, in both protocols. It resets session state; it does not create a new connection.
2. Add is_expired(now) to MySQL_Connection / PgSQL_Connection, wrapping the connection_max_age_ms comparison so the policy lives in one place.
3. return_MySQL_Connection_To_Pool(): separate the two triggers that currently share one action. too_many_stmts keeps the COM_CHANGE_USER path (loop-free — CHANGE_USER deallocates the server-side prepared statements, so the predicate goes false). An expired connection is closed.
4. destroy_MyConn_from_pool(): never place an expired connection on the reset queue.
5. Mirror 3 and 4 on the PgSQL side.

Termination argument: with (1) and (4), an expired connection can never re-enter the pool, so the #1393 loop cannot recur.

Caveats

• send_quit is overloaded. destroy_MyConn_from_pool() gates reset-queue eligibility on c->send_quit, so destroy_MySQL_Connection_From_Pool(false) closes the connection but also skips the graceful COM_QUIT. The fix should decouple "close, don't recycle" from "don't send COM_QUIT" rather than abuse sq = false.
• Behaviour change: an aged-but-busy connection is now closed at its next return to the pool instead of being reset, so reconnect rates rise, bounded by connection_max_age_ms. This should be called out in the changelog. @takaidohigasi anticipated this in #5653.

Test plan

There is currently no TAP coverage for connection_max_age_ms (nothing in test/tap/ exercises it). Given that the failure mode of getting this wrong is a reset loop, the regression test matters more than usual and should assert both directions:

• connections actually age out — Server_Connections_created grows under sustained traffic with a short connection_max_age_ms;
• they do not loop — Com_backend_change_user stays bounded (this is what would catch a #1393 regression);
• coverage for reset_connection_algorithm 1 and 2, and for the PgSQL equivalent.