I am using Google Cloud SQL with MySql v5.7 from C# .NET-core 2.2 and entity framework 6 application.
In my logs I can see the following exception from multiple locations in the code that I use the database from:
MySql.Data.MySqlClient.MySqlException (0x80004005): Connect Timeout expired. ---> System.OperationCanceledException: The operation was canceled.
at System.Threading.CancellationToken.ThrowOperationCanceledException()
at System.Threading.SemaphoreSlim.WaitUntilCountOrTimeoutAsync(TaskNode asyncWaiter, 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 42
at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 507
at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 523
at MySql.Data.MySqlClient.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 232
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
This happens temporarily for split second when there is a some load on the database(not very high, about 20% cpu of the database machine).
Configuring The Context:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseMySql(
new System.Net.NetworkCredential(string.Empty, ConfigurationManager.CacheCS).Password, builder =>
{
builder.EnableRetryOnFailure(15, TimeSpan.FromSeconds(30), null);
}
);
}
}
This sets up to 15 retries and maximum of 30 seconds between retries.
It seems from the log that the MySqlConnector
does not retry on this specific error.
My Tries
Tried adding transient error numbers to the list of error numbers to add:
builder.EnableRetryOnFailure(15, TimeSpan.FromSeconds(30), MySqlErrorCodes.TransientErrors);
where MySqlErrorCodes.TransientErrors
is defined as:
public enum MySqlErrorCode
{
// Too many connections
ConnectionCountError = 1040,
// Unable to open connection
UnableToConnectToHost = 1042,
// Lock wait timeout exceeded; try restarting transaction
LockWaitTimeout = 1205,
// Deadlock found when trying to get lock; try restarting transaction
LockDeadlock = 1213,
// Transaction branch was rolled back: deadlock was detected
XARBDeadlock = 1614
}
public class MySqlErrorCodes
{
static MySqlErrorCodes()
{
TransientErrors = new HashSet<int>()
{
(int)MySqlErrorCode.ConnectionCountError,
(int)MySqlErrorCode.UnableToConnectToHost,
(int)MySqlErrorCode.LockWaitTimeout,
(int)MySqlErrorCode.LockDeadlock,
(int)MySqlErrorCode.XARBDeadlock
};
}
public static HashSet<int> TransientErrors { get; private set; }
}
This didn't work.
Questions
How can I solve this issue? Is there a way to make Entity Framework more resilient to such connectivity issues?
Edit
The issue occurs when I use this code to execute a raw sql command to call a stored procedure:
public static async Task<RelationalDataReader> ExecuteSqlQueryAsync(this DatabaseFacade databaseFacade,
string sql,
CancellationToken cancellationToken = default(CancellationToken),
params object[] parameters)
{
var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = databaseFacade
.GetService<IRawSqlCommandBuilder>()
.Build(sql, parameters);
return await rawSqlCommand
.RelationalCommand
.ExecuteReaderAsync(
databaseFacade.GetService<IRelationalConnection>(),
parameterValues: rawSqlCommand.ParameterValues,
cancellationToken: cancellationToken);
}
}
...
using (var context = new CacheDbContext())
{
using (var reader = await context
.Database
.ExecuteSqlQueryAsync("CALL Counter_increment2(@p0, @p1, @p2)",
default(CancellationToken),
new object[] { id, counterType, value })
.ConfigureAwait(false)
)
{
reader.DbDataReader.Read();
if (!(reader.DbDataReader[0] is DBNull))
return Convert.ToInt32(reader.DbDataReader[0]);
else
{
Logger.Error($"Counter was not found! ('{id}, '{counterType}')");
return 1;
}
}
}
I think this may be why there are no retries for the connect timeout.
How can I retry this safely while not executing the same stored procedure twice?
Edit
These are the global variables:
SHOW GLOBAL VARIABLES LIKE '%timeout%'
connect_timeout 10
delayed_insert_timeout 300
have_statement_timeout YES
innodb_flush_log_at_timeout 1
innodb_lock_wait_timeout 50
innodb_rollback_on_timeout OFF
interactive_timeout 28800
lock_wait_timeout 31536000
net_read_timeout 30
net_write_timeout 60
rpl_semi_sync_master_async_notify_timeout 5000000
rpl_semi_sync_master_timeout 3000
rpl_stop_slave_timeout 31536000
slave_net_timeout 30
wait_timeout 28800
SHOW GLOBAL STATUS LIKE '%timeout%'
Ssl_default_timeout 7200
Ssl_session_cache_timeouts 0
SHOW GLOBAL STATUS LIKE '%uptime%'
Uptime 103415
Uptime_since_flush_status 103415
In addition to the connect time out issue I am also seeing the following log:
MySql.Data.MySqlClient.MySqlException (0x80004005): MySQL Server rejected client certificate ---> System.IO.IOException: Unable to read data from the transport connection: Broken pipe. ---> System.Net.Sockets.SocketException: Broken pipe
Which seems to be a related issue regarding the connection to the database.
Is it safe to retry on such exception?
The issue happened because of low value for maximumpoolsize
in the connection string.
When there are multiple threads using the database and not enough connetions to handle all the requests this may cause Connect Timeout.
To fix this change this in the connection string to a higher value:
Max Pool Size={maxConnections};
User contributions licensed under CC BY-SA 3.0