When using Connection Pooling, it is not possible to send a Validation Query.

[ikpil]

Software versions
MySqlConnector version: 2.3.7
Server type (MySQL, MariaDB, Aurora, etc.) and version: MySql 8.0.3
.NET version: .net 8.0
(Optional) ORM NuGet packages and versions: Dapper 2.1.44

Describe the bug
We are using the ConnectionPool feature.
We are maintaining three sessions.

However, the Validation Query(SELECT 1) should be used by all three sessions, but due to pooling, it cannot be used in the remaining two sessions.

As a result of this issue, sessions keep getting disconnected due to the wait_timeout.
How can we send the Validation Query to all sessions?

Exception
Full exception message and call stack (if applicable)

System.Net.Sockets.SocketException (10053): 현재 연결은 사용자의 호스트 시스템의 소프트웨어의 의해 중단되었습니다.
   at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.CreateException(SocketError error, Boolean forAsyncThrow)
   at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.SendAsync(Socket socket, CancellationToken cancellationToken)
   at System.Net.Sockets.Socket.SendAsync(ReadOnlyMemory`1 buffer, SocketFlags socketFlags, CancellationToken cancellationToken)
   at MySqlConnector.Protocol.Serialization.SocketByteHandler.DoWriteBytesAsync(ReadOnlyMemory`1 data) in /_/src/MySqlConnector/Protocol/Serialization/SocketByteHandler.cs:line 133
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
   at MySqlConnector.Protocol.Serialization.SocketByteHandler.DoWriteBytesAsync(ReadOnlyMemory`1 data)
   at MySqlConnector.Protocol.Serialization.SocketByteHandler.WriteBytesAsync(ReadOnlyMemory`1 data, IOBehavior ioBehavior) in /_/src/MySqlConnector/Protocol/Serialization/SocketByteHandler.cs:line 114
   at MySqlConnector.Core.ServerSession.TryResetConnectionAsync(ConnectionSettings cs, MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 629
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
   at MySqlConnector.Core.ServerSession.TryResetConnectionAsync(ConnectionSettings cs, MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken)
   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Int32 timeoutMilliseconds, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 71
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Int32 timeoutMilliseconds, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken)
   at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int32 startTickCount, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 929
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
   at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int32 startTickCount, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken)
   at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 423
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
   at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken)
   at MySqlConnector.MySqlConnection.OpenAsync(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 384
   at Dapper.SqlMapper.TryOpenAsync(IDbConnection cnn, CancellationToken cancel) in /_/Dapper/SqlMapper.Async.cs:line 399
   at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 488
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
   at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command)
   at Dapper.SqlMapper.QueryFirstOrDefaultAsync[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.Async.cs:line 119
   at Gemini.Food.Database.DbConnection.<>c__DisplayClass12_0`1.<SelectSingleAsync>b__0(MySqlConnection conn) in D:\workspace\src\Gemini.Food.Database\DbConnection.cs:line 72
   at Gemini.Food.Database.DbConnection.<>c__DisplayClass18_0`1.<ExecuteAsync>b__0(MySqlConnection conn) in D:\workspace\src\Gemini.Food.Database\DbConnection.cs:line 164
   at Gemini.Food.Database.DbExecutor.<>c__DisplayClass13_0`1.<ExecuteAsync>b__0() in D:\workspace\src\Gemini.Food.Database\DbExecutor.cs:line 171
   at Gemini.Food.Core.Threading.SingleThreadExecutor.<>c__DisplayClass9_0`1.<ExecuteAsync>b__0() in D:\workspace\src\Gemini.Food.Core\Threading\SingleThreadExecutor.cs:line 95
   at Gemini.Food.Core.Threading.SingleThreadExecutor.PollingInternal(TickThreadContext ctx) in D:\workspace\src\Gemini.Food.Core\Threading\SingleThreadExecutor.cs:line 142
   at Gemini.Food.Core.Threading.TickThreadContext.StartInternal() in D:\workspace\src\Gemini.Food.Core\Threading\TickThreadContext.cs:line 62
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task, ConfigureAwaitOptions options)

Code sample

        builder.Server = connStr.Host;
        builder.Port = (uint)connStr.Port;
        builder.Database = connStr.DbName;
        builder.UserID = connStr.UserName;
        builder.Password = connStr.UserPasswd;
        builder.SslMode = MySqlSslMode.Disabled;
        builder.Keepalive = 60; 
        builder.CharacterSet = "utf8mb4";
        builder.ConvertZeroDateTime = true;
        builder.AllowLoadLocalInfile = true;
        builder.AllowPublicKeyRetrieval = true;
        builder.Pooling = true;
        builder.MinimumPoolSize = 3;
        builder.MaximumPoolSize = 3
        builder.DnsCheckInterval = 5; 
.......



/* A concise code sample to reproduce the bug */
			// check for a waiting session
			lock (m_sessions)
			{
				if (m_sessions.Count > 0)
				{
					session = m_sessions.First!.Value; 
					m_sessions.RemoveFirst();  <------ like stack 
				}
			}

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

[bgrainger]

There's currently no way to force every connection in the pool to send a query.

As a result of this issue, sessions keep getting disconnected due to the wait_timeout.

Can you control the wait_timeout and set it to a higher value?

Alternatively, if you know there are exactly three connections in the pool, you could try something like:

// open all three connections from the pool at once
using (var connection1 = new MySqlConnection(builder.ConnectionString))
using (var connection2 = new MySqlConnection(builder.ConnectionString))
using (var connection3 = new MySqlConnection(builder.ConnectionString))
{
    // send a message on each open connection
    connection1.Ping();
    connection2.Ping();
    connection3.Ping();
}

(with appropriate error handling for failing to retrieve a connection from the pool in time)

[ikpil]

Can you control the wait_timeout and set it to a higher value?

In the case of wait_timeout,
there is a challenge in altering DB settings across many environments.

Alternatively, if you know there are exactly three connections in the pool, you could try something like:

The method you proposed seems to have an issue where all sessions in the connection pool change synchronously in an instant.

---

I believe ValidationQuery is a very good method that can cover many database environments.
For example: https://commons.apache.org/proper/commons-dbcp/configuration.html

Upon examining the library internals, it appears that there is already a scheduler running within the ConnectionPool.
What do you think about adding Validation Query as an option here?

[bgrainger]

The method you proposed seems to have an issue where all sessions in the connection pool change synchronously in an instant.

I don't know what this means, sorry.

I believe ValidationQuery is a very good method that can cover many database environments.
For example: https://commons.apache.org/proper/commons-dbcp/configuration.html

That documentation says, "The SQL query that will be used to validate connections from this pool before returning them to the caller." It doesn't imply AFAICT that the ValidationQuery will be run periodically in the background. Is that what the testWhileIdle setting does?

[ikpil]

The method you proposed seems to have an issue where all sessions in the connection pool change synchronously in an instant.

From what I understand, the scenario that changes synchronously is as follows:

When the connection pool has a minimum of 3 and a maximum of 3 connections:

1. Let's assume there are threads A and B.
2. Thread A is using the connection pool.
3. Thread B opens 3 connections to send a Validation Query.

At this point, it becomes synchronous.

From Thread A's perspective:

• Thread A will wait until Thread B opens and closes 3 connections.

From Thread B's perspective:

• Thread B will wait for Thread A to close the connections.

In this way, it changes synchronously.
The risk always exists unless all queries are used asynchronously from Thread A.

[ikpil]

That documentation says, "The SQL query that will be used to validate connections from this pool before returning them to the caller." It doesn't imply AFAICT that the ValidationQuery will be run periodically in the background. Is that what the testWhileIdle setting does?

I'm sorry for the confusion.
The proposal for the Validation Query feature wasn't clear.

The intention behind the provided link was to explain the use of Validation Query for checking the connection state, not necessarily suggesting that the feature should run in the background.

Let me re-propose the feature:
A functionality that allows validation of the validity of all sessions connected when using connection pooling through a Validation Query.

What do you think about this proposed feature?

[bgrainger]

A functionality that allows validation of the validity of all sessions connected when using connection pooling through a Validation Query.

What do you think about this proposed feature?

It's interesting, but I don't see the benefit over Connection Reset=true;. Furthermore, you should be able to simulate it yourself by adding an event handler to DbConnection.StateChange and running the query then. https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbconnection.statechange?view=net-8.0

Back to the OP:

System.Net.Sockets.SocketException (10053): 현재 연결은 사용자의 호스트 시스템의 소프트웨어의 의해 중단되었습니다.
...
   at MySqlConnector.Core.ServerSession.TryResetConnectionAsync(ConnectionSettings cs, MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 629
...
   at MySqlConnector.MySqlConnection.OpenAsync(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 384
   at Dapper.SqlMapper.TryOpenAsync(IDbConnection cnn, CancellationToken cancel) in /_/Dapper/SqlMapper.Async.cs:line 399

Are you observing this SocketException in your code? That "shouldn't" be possible because it should be caught here:

MySqlConnector/src/MySqlConnector/Core/ServerSession.cs

Lines 644 to 647 in bc6785e

	catch (SocketException ex)
	{
	Log.IgnoringFailureInTryResetConnectionAsync(m_logger, ex, Id, "SocketException");
	}

What should be happening is that MySqlConnector detects an invalid connection in the pool and transparently creates and returns a new one to you. Are you not seeing that happen?