c# - 从具有 40+ 百万条记录的表中读取操作期间的 MySQl 命令超时

Question

我们有一个奇怪的情况，当我们在 EF6 中使用 mysql 时遇到错误。

MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. --->
MySql.Data.MySqlClient.MySqlException: Fatal error encountered attempting to read the resultset. ---> 
MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. ---> 
System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ---> 
System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond  

如果我们将生成的查询从 EF6 带到 MySql 工作台，它会在 75 秒内执行而不会出现任何错误。但是，在应用程序中运行时，它会在 30 秒内引发错误。

该表有大量记录，需要 70 多秒才能返回行。但这很好，唯一的事情是“它不应该由于读取超时失败而引发错误”。

试过的解决方案：

我们已将读取超时的全局和 mysql 设置更改为 99999，但仍然显示相同的错误。

SET GLOBAL net_read_timeout=999999;
SET net_read_timeout = 999999

标签： c#mysqlentity-framework-6mysql-workbenchconnection-timeout

Answer 1

您可以在连接字符串或 DbContext 数据库对象中增加超时（默认值为 30）。

C＃

DbContext.Database.CommandTimeout = 999;

连接字符串

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
default command timeout=999;