MySql实体框架-连接超时过期
MySql实体框架-连接超时过期
提问于 2020-12-02 10:56:52
我使用Google和MySql v5.7来自C# .NET-核心2.2和实体框架6应用程序。
在我的日志中，我可以从代码中的多个位置看到以下异常，我从以下代码中使用了数据库：
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)当数据库中有一些负载(不是很高，大约占数据库计算机cpu的20% )时，这种情况会暂时发生。
配置上下文的：
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);
}
);
}
}这设置为15次重试，每次重试最多30秒。
从日志中可以看出，MySqlConnector没有对这个特定的错误进行重试。
我的尝试
尝试将瞬态错误号添加到要添加的错误编号列表中：
builder.EnableRetryOnFailure(15, TimeSpan.FromSeconds(30), MySqlErrorCodes.TransientErrors);其中MySqlErrorCodes.TransientErrors被定义为：
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; }
}这不管用。
问题
我怎样才能解决这个问题？是否有办法使实体框架能够更好地应对这种连通性问题？
编辑
当我使用此代码执行原始sql命令以调用存储过程时，会出现此问题：
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;
}
}
}我认为这可能是连接超时没有重试的原因。
如何在不执行同一存储过程两次的情况下安全地重试？
编辑
这些是全局变量：
显示全局变量，如‘%超时值%’
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显示全局状态，如‘%超时值%’
Ssl_default_timeout 7200
Ssl_session_cache_timeouts 0显示全局状态，如'%uptime%'
Uptime 103415
Uptime_since_flush_status 103415除了连接超时问题外，我还看到以下日志：
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这似乎是与数据库连接有关的一个相关问题。
在这种例外情况下再试一次安全吗？
回答 1
Stack Overflow用户
发布于 2020-12-16 08:35:27
发生此问题的原因是连接字符串中的maximumpoolsize值较低。
如果有多个线程使用数据库，并且没有足够的条件来处理所有请求，这可能会导致Connect超时。
若要将连接字符串中的此更改更改为更高的值，请执行以下操作：
Max Pool Size={maxConnections};页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65106697
复制相关文章
相似问题
腾讯云开发者
