MySQL Bugs: #119887: The entire connection creation phase should be covered by a configurable timeout

Bug #119887	The entire connection creation phase should be covered by a configurable timeout
Submitted:	10 Feb 20:15	Modified:	11 Feb 13:14
Reporter:	Dennis Kniep	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	9.6.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I discovered an issue when creating a connection in the following situation:
Connection creation was started and the authentication was successfully completed. Then there are certain commands executed afterwards to get server information (charset, timezone, isolation level etc.) But those commands are not covered by the LoginTimeout. The timeout for those commands are set to 0, which means no timeout. This lead to hanging connection creation thread if the server does not answer to those commands to whatever reason (network issue, died, killed, etc.)

Here is the code where the LoginTimeout is only applied to the first part of the connection creation in mysql-connector-j Driver:
https://github.com/mysql/mysql-connector-j/blob/fdef61f4af21fa9e0ac334ff0664ec754c164cc0/s...

Further operations don´t have a timeout set, see initializePropsFromServer:

https://github.com/mysql/mysql-connector-j/blob/fdef61f4af21fa9e0ac334ff0664ec754c164cc0/s...

In my particular case it is Agroal DB-Pool end up hanging, but its independent of that specific implementation. The situation could occur in general.

How to repeat:
To reproduce the Problem, I built a tiny demo with a dirty implemented MySQL fake server

Here is the mock MySQL service (handling the connection):
https://github.com/denniskniep/agroal/blob/task/troubleshooting-connection-creation/agroal...

Here I make it wait (network issue, died, killed, etc.)
https://github.com/denniskniep/agroal/blob/task/troubleshooting-connection-creation/agroal...

This is the test which asserts that it hang for more than the specified timeout.
https://github.com/denniskniep/agroal/blob/09e53daac8914c3d281f3bdba1a17a34e2298637/agroal...

Suggested fix:
Just thinking out loud:
It would be great to have either a dedicated timeout which spans across the entire connection creation phase, or extend the already existing login timeout to cover the whole connection creation phase.

Probably there exists more alternatives how to implement it, just wanted to start the discussion with two proposals:

1) Before each command is executed, setting the socket timeout to the remaining duration until the timeout is reached.

2) Wrapping the connection creation logic inside a dedicated thread at the very beginning. This is the way how postgres connector is solving that (see https://github.com/pgjdbc/pgjdbc/blob/47f7e3bba211dc2078bcbe3bcc1ffede855d2b1e/pgjdbc/src/...)

Hi Dennis Kniep, 

Thank you for your interest in MySQL Connector/J and for taking the time to write this report. 

MySQL Connector/J implements three timeout values relevant to your situation: 

- `DriverManager.getLoginTimeout(n)`: This covers the duration from the start of the connection attempt until a successful login on the server. As you mentioned, it does not apply to any of the internal queries executed after the login.
- Connection property `connectTimeout=n`: Sets the time limit for establishing the network socket.
- Connection property `socketTimeout=n`: Sets the default timeout for all network operations, including the internal queries that are executed while initializing the connection.

With this in mind, you should be able to set a socket timeout in your connection string to avoid the hanging connection issue you described. We consider these internal queries the same as any others you may execute, so they use the default socket timeout.

We believe this should resolve your issue. This report will be closed, but please feel free to reopen it or file a new report if you observe any behavior different from what is described above. 

Best regards,

Thanks for the quick response. Reason for reopening

Setting a general socket timeout in the jdbc connection string might not fit both usage scenarios:
a) connection creation
b) normal query execution
because a use case might obligate a large or undefined socket timeout during normal query execution, but obviously not during connection creation.

To solve this we can set the SocketTimeout in the jdbc properties to a low duration, so that it quickly times out during connection creation.
And after connection creation set it via Connection.setNetworkTimeout to a different duration which fits the normal query execution usage scenario.

But then there is one thing left. The socket timeouts might sum up during connection creation and leading to a unexpected long connection creation.

What you propose is setting (for example):
5sec login timeout
5sec socket timeout

This could end up in a connection creation phase like:
authentication -> take 4sec (login timeout not reached, socket timeout not reached; continue)
First command -> take 4sec (socket timeout not reached; continue)
Second command -> take 4sec (socket timeout not reached; continue)
Third command -> take 4sec (socket timeout not reached; continue)
etc.

End up in a connection creation phase that take > 16sec
This makes the connection creation phase unpredictable long.

Would be nice to have the same behavior of the loginTimeout across drivers. From my understanding all drivers describe the loginTimeout in a way that covers the entire connection creation phase.

Postgres:
Specify how long to wait for establishment of a database connection
https://jdbc.postgresql.org/documentation/use/

MS SQL Server:
loginTimeout is the amount of time, in seconds, the driver waits to establish a connection to the server.
https://learn.microsoft.com/en-us/sql/connect/jdbc/understand-timeouts?view=sql-server-ver...

Oracle:
Specifies the timeout for opening a JDBC connection
https://docs.oracle.com/en/database/oracle/oracle-database/26/jajdb/oracle/jdbc/OracleConn...

Also the JDBC specification suggest that the loginTimeout should cover the entire connection creation phase:
Sets the maximum time in seconds that a driver will wait while attempting to connect to a database once the driver has been identified.
https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/DriverManager.html#se...)

As far as I can see that is not the way how its implemented in mysql.
Would be great if we can fix this.

I also think that it would be good for `connectTimeout` to cover the whole connection establishment including the TCP socket, authentication and initial commands that are executed by the connector to get/set things like autocommit etc. I think this is what users expect instead of having to know the underlying implementation (e.g. what things are set with client flags and what is set by running SQL)

Correction: I meant loginTimeout instead of connectTimeout

Thank you Dennis and Daniel for your feedback.

I'll check carefully all the information provided and see if there's anything we can do about this.