MySqlClient EndOfStreamException:尝试读取流的末尾时发生致命错误 [英] MySqlClient EndOfStreamException: Fatal error when attempting to read past the end of the stream
问题描述
我已经开发了Windows服务，可以从csv中读取数据并将其写回到数据库中.
I have developed a windows service to read data from a csv and write them back to the database.
执行程序后，它将正常运行，直到到达以下行几分钟后触发错误为止
After executing the program it will work fine until triggering an error after a few minutes when reached the below line
cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();
错误的屏幕截图:
数据已写入数据库，直到出现此错误.
The data has been written to the database until this error pops up.
最多可能需要2-3分钟才能触发错误.
It will take up to 2-3 minutes to trigger the error.
我将提供与此问题相关的代码块.
I will include the related code chunk for this issue.
public void Insert()
{
if (this.OpenConnection() == true)
{
using(var reader = new StreamReader(@"C:\Users\Admin\source\Bargstedt.csv"))
{
List<string> listA = new List<string>();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
string querynew = "INSERT INTO new_jobs"
+ "(job_reference,status,code,no1,no2,no3,dimension,date_assigned,root,variable,number,stable,constant)"
+ "VALUES (?jobNo, ?strClientName, ?strClientReference, ?strJobCategory, ?datCommisioned, ?datPromisedDelivery, ?division, ?date_assigned, ?root, ?variable, ?number, ?stable, ?constant)";
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText= querynew;
cmd.Parameters.Add("?jobNo", MySqlDbType.VarChar).Value = (values[0]);
cmd.Parameters.Add("?strClientName", MySqlDbType.VarChar).Value =(values[1]);
cmd.Parameters.Add("?strClientReference", MySqlDbType.VarChar).Value = values[2];
cmd.Parameters.Add("?strJobCategory", MySqlDbType.VarChar).Value = values[3];
cmd.Parameters.Add("?datCommisioned", MySqlDbType.VarChar).Value = values[4];
cmd.Parameters.Add("?datPromisedDelivery", MySqlDbType.VarChar).Value = values[5];
cmd.Parameters.Add("?division", MySqlDbType.VarChar).Value = values[7];
cmd.Parameters.Add("?date_assigned", MySqlDbType.VarChar).Value = values[9];
cmd.Parameters.Add("?root", MySqlDbType.VarChar).Value = values[10];
cmd.Parameters.Add("?variable", MySqlDbType.VarChar).Value = values[11];
cmd.Parameters.Add("?number", MySqlDbType.VarChar).Value = values[12];
cmd.Parameters.Add("?stable", MySqlDbType.VarChar).Value = values[13];
cmd.Parameters.Add("?constant", MySqlDbType.VarChar).Value = values[14];
cmd.ExecuteNonQuery(); **error line
}
}
this.CloseConnection();
}
}
为了更加清楚起见，我还将包括csv文件和db结构的屏幕截图.
Just to make it more clear I'll include a screenshot of the csv file and db structure as well.
csv中行中间有一些空格，这就是为什么我跳过了源代码中的第6行和第8行.
There are some spaces in the middle of the rows in csv and that is why I skipped row 6 and 8 in the source code.
phpMyAdmin数据库表的屏幕截图
screenshot of the phpMyAdmin db table
MySql.Data.MySqlClient.MySqlException
MySql.Data.MySqlClient.MySqlException
HResult=0x80004005
Message=Fatal error encountered during command execution.
Source=MySql.Data
StackTrace:
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at SSHtest.DBConnect.Insert() in C:\Users\Admin\source\repos\backup new\SSHtest\SSHtest\Program.cs:line 248
at SSHtest.Service1.timer1_Tick(Object sender, ElapsedEventArgs e) in C:\Users\Admin\source\repos\backup new\SSHtest\SSHtest\Service1.cs:line 87
at System.Timers.Timer.MyTimerCallback(Object state)
Inner Exception 1:
MySqlException: Fatal error encountered attempting to read the resultset.
Inner Exception 2:
MySqlException: Reading from the stream has failed.
Inner Exception 3:
EndOfStreamException: Attempted to read past the end of the stream.
推荐答案
The best solution I've seen discovered by the community was posted by Rui Fan:
我已经确定了此例外的一种情况的根本原因.如果您在第一次连接数据库时总是看到此异常，则我的解决方案可能适用于您.三种可能的解决方案:
I've identified the root cause of one case of this exception. If you always see this exception at the first connection to the database, my solutions may apply to you. The 3 possible solutions:
解决方案1:如果不需要SSL.由于它是由SSL引起的，因此我们可以通过在连接字符串后附加"SslMode = None"来关闭SSL.
Solution 1: If SSL is not required. Since it is caused by SSL, we can turn off SSL by appending "SslMode=None" to the connection string.
解决方案2:如果需要SSL，则服务器身份很重要，需要进行验证.将服务器连接到互联网，然后重试.
Solution 2: If SSL is required, server identity is important and needs to be verified. Connect the server to the internet and try again.
解决方案3:如果需要SSL，但是服务器身份并不重要.通常，在这种情况下，SSL仅用于加密网络传输.我们可以关闭CTL更新:
Solution 3: If SSL is required but the server identity is not important. Typically SSL is only used to encrypt the network transport in this case. We can turn off CTL update:
- 按
Win+R打开运行"对话框 - 键入
gpedit.msc并按Enter - 在本地组策略编辑器"中，依次展开计算机配置"，管理模板"，系统"，"Internet通讯管理"，然后单击"Internet通讯设置".
- 在详细信息面板中，双击关闭自动根证书更新"，单击启用"，然后单击确定".此更改将立即生效，而无需重新启动.
- Press
Win+Rto open the "Run" dialog - Type
gpedit.mscand press Enter - In the "Local Group Policy Editor", expand "Computer Configuration", expand "Administrative Templates", expand "System", expand "Internet Communication Management", and then click "Internet Communication settings".
- In the details panel, double-click "Turn off Automatic Root Certificates Update", clickEnabled, then click OK. This change will be effective immediatelly without restart.
更多详细信息可以在我的博客中找到.
More details can be found in my blog.
或者，您可以将MySQL ADO.NET库切换为 MySqlConnector .它修复了许多 MySQL连接器/NET错误，并且从未报告过此特定问题.
Alternatively, you could switch MySQL ADO.NET libraries to MySqlConnector. It fixes many MySQL Connector/NET bugs, and hasn't ever had this particular issue reported.
这篇关于MySqlClient EndOfStreamException:尝试读取流的末尾时发生致命错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
