I'm running a C# (v2.2) application that accessing my database (MySQL) using Entity Framework.
This is my code:
public static async Task<int> IncerementByAsync(string id, int incrBy, string counterType)
{
using (var context = new CacheDbContext())
{
int result = 0;
using (var command = context.Database.GetDbConnection().CreateCommand())
{
if (command.Connection.State != ConnectionState.Open)
{
Console.WriteLine($"Connection staus is {command.Connection.State.ToString()}. Opening");
context.Database.OpenConnection();
}
command.CommandType = CommandType.StoredProcedure;
command.CommandText = $"Counter_increment2";
command.Parameters.Add(new MySqlParameter("_Id", id));
command.Parameters.Add(new MySqlParameter("_CounterType", counterType));
command.Parameters.Add(new MySqlParameter("_IncrBy", incrBy));
command.Parameters[command.Parameters.Add(new MySqlParameter("_NewValue", result))].Direction = ParameterDirection.Output;
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
return (int)command.Parameters[3].Value; // Read output parameter
}
}
}
I'm running the stored procedure Counter_increment2
and read the OUT
parameter from the response.
When I tested it on the development environment - it's working fine. When tested it on the production environment, when a lot of calls are being made, I started to get errors from time to time:
MySql.Data.MySqlClient.MySqlException (0x80004005): Connect Timeout expired.
System.OperationCanceledException: The operation was canceled.
at System.Threading.SemaphoreSlim.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ConnectionPool.cs:line 46
at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 507
1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 523
at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable
at MySql.Data.MySqlClient.MySqlConnection.OpenAsync(Nullable1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 232
3 operation, Func`3 verifySucceeded) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.OpenConnection(DatabaseFacade databaseFacade) at ***.IncrementByAsync(String id, Int32 incrBy, String counterType) in /****/CounterOperations.cs:line 46
at MySql.Data.MySqlClient.MySqlConnection.Open() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 218
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func
My DB connection string limits the number of concurrent connections for a certain number. I guess that at some point, all the optional connections were used and I got connect time exception.
Since I'm using the EF connection pool, I expected to reuse connections from the pool. Surprisingly, each call to the function was opened a new connection. This is the print that I got every call:
Connection status is Closed. Opening
This mean that a connection is opened every time I enter this code.
It must be some sort of wrong code here, otherwise, I hadn't experienced those connection problems. But what I'm doing wrong? How to manage my database connection?
According to @Ivan comment, I modified the code to this:
public static async Task<int> IncerementByAsync(string id, int incrBy, string counterType)
{
using (var context = new CacheDbContext())
{
int result = 0;
using (var command = context.Database.GetDbConnection().CreateCommand())
{
bool wasOpen = command.Connection.State == ConnectionState.Open;
if (!wasOpen)
{
Console.WriteLine($"Connection status is {command.Connection.State.ToString()}. Opening");
context.Database.OpenConnection();
}
try
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = $"Counter_increment2";
command.Parameters.Add(new MySqlParameter("_Id", id));
command.Parameters.Add(new MySqlParameter("_CounterType", counterType));
command.Parameters.Add(new MySqlParameter("_IncrBy", incrBy));
command.Parameters[command.Parameters.Add(new MySqlParameter("_NewValue", result))].Direction = ParameterDirection.Output;
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
return (int)command.Parameters[3].Value; // Read output parameter
}
finally
{
if (!wasOpen)
command.Connection.Close();
}
}
}
}
After this change, I still can see the same behaviour (Connect timeout exception). What else should I check?
User contributions licensed under CC BY-SA 3.0