.NET应用从MySQL获取数据时触发致命执行错误

问题现象 #

.NET应用从MySQL数据库读取数据时，偶尔会抛出以下连接超时相关异常：

MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution.
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered attempting to read the resultset.
---> MySql.Data.MySqlClient.MySqlException (0x80004005): 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
    at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
    at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
    --- End of inner exception stack trace ---
    at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
    at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
    at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
    at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
    at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
    at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
    at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
    at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
    at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
    at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
    at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
    at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
    at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
    at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

现有代码 #

_mySqlConn = new MySqlConnection(ConfigurationManager.ConnectionStrings["BiometricConnectionPush"].ToString());
_mySqlConn.Open();
_strQry = "SELECT * FROM biologs WHERE id>" + LastPunchId + " ORDER BY id ASC LIMIT " + RowLimit.ToString();

MySqlCommand _sqlCmd = new MySqlCommand();
_sqlCmd.CommandText = _strQry;
_sqlCmd.CommandTimeout = 100000;
_sqlCmd.Connection = _mySqlConn;
_sqlCmd.CommandType = CommandType.Text;

Data = new DataSet();
_mySqlDa = new MySqlDataAdapter(_sqlCmd);
_mySqlDa.Fill(Data);

排查与解决步骤 #

1. 修复SQL注入风险并优化查询性能 #

直接拼接SQL字符串存在严重的SQL注入风险，同时可能导致查询计划无法复用，降低执行效率。改用参数化查询：

_strQry = "SELECT * FROM biologs WHERE id > @LastPunchId ORDER BY id ASC LIMIT @RowLimit";

通过MySqlParameter传递参数，避免注入问题，同时提升查询稳定性。

2. 优化连接资源管理 #

未使用using语句会导致数据库连接无法及时释放，可能引发连接池耗尽、闲置连接被服务器断开等问题。用using包裹所有实现IDisposable的数据库对象，确保资源自动释放。

3. 调整连接字符串参数 #

在连接字符串中添加以下参数，优化连接稳定性：

• Connection Timeout=30：设置连接超时时间（建议30秒，根据实际情况调整）
• Keepalive=60：每隔60秒发送心跳包，防止防火墙/路由器断开闲置连接
• SslMode=None：如果不需要SSL加密，关闭以减少网络开销（根据实际环境调整）
• 同时检查MySQL服务器的wait_timeout和interactive_timeout参数，设置合理值（例如3600秒），避免服务器主动断开闲置连接。

4. 合理设置命令超时 #

当前CommandTimeout=100000（约27小时）过于夸张，应设置为合理值（例如300秒，即5分钟），如果查询确实需要长时间执行，需同步调整MySQL服务器的max_execution_time参数，允许长时运行的查询。

5. 网络与服务器层面排查 #

• 检查应用服务器与MySQL服务器之间的网络稳定性，排查是否存在丢包、延迟过高的情况
• 确认防火墙、路由器是否有闲置连接超时设置，避免自动断开连接
• 查看MySQL服务器的CPU、内存负载，确保服务器资源充足，能及时响应请求

优化后的完整代码示例 #

var connectionString = ConfigurationManager.ConnectionStrings["BiometricConnectionPush"].ToString();
using (var mySqlConn = new MySqlConnection(connectionString))
{
    mySqlConn.Open();
    const string strQry = "SELECT * FROM biologs WHERE id > @LastPunchId ORDER BY id ASC LIMIT @RowLimit";
    
    using (var sqlCmd = new MySqlCommand(strQry, mySqlConn))
    {
        sqlCmd.CommandTimeout = 300; // 设置合理的命令超时时间
        sqlCmd.Parameters.AddWithValue("@LastPunchId", LastPunchId);
        sqlCmd.Parameters.AddWithValue("@RowLimit", RowLimit);
        
        using (var mySqlDa = new MySqlDataAdapter(sqlCmd))
        {
            Data = new DataSet();
            mySqlDa.Fill(Data);
        }
    }
}

内容的提问来源于stack exchange，提问作者Ashok Kumar