Open ikpil opened 2 months ago
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)
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?
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?
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:
At this point, it becomes synchronous.
From Thread A's perspective:
From Thread B's perspective:
In this way, it changes synchronously. The risk always exists unless all queries are used asynchronously from Thread A.
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?
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: https://github.com/mysql-net/MySqlConnector/blob/bc6785e88874627b04231799fdf12c1fc40169f5/src/MySqlConnector/Core/ServerSession.cs#L644-L647
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?
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)
Code sample
Expected behavior A clear and concise description of what you expected to happen.
Additional context Add any other context about the problem here.