c# - MySqlClient 结束流异常 : Fatal error when attempting to read past the end of the stream

Question

我开发了一个 Windows 服务来从 csv 中读取数据并将它们写回数据库。

执行程序后它会正常工作，直到几分钟后到达以下行时触发错误

cmd.ExecuteNonQuery();

错误截图:

弹出这个错误之前数据已经写入数据库了。

触发错误最多需要 2-3 分钟。

我将包括这个问题的相关代码块。

   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 文件和数据库结构的屏幕截图。

csv 中的行中间有一些空格，这就是我在源代码中跳过第 6 行和第 8 行的原因。

phpMyAdmin 数据库表的屏幕截图

编辑:

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.

Answer 1

这是一个 very frequently reported MySQL 连接器/NET 中的错误。

我见过社区发现的最佳解决方案是 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:

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.

Solution 2: If SSL is required, server identity is important and needs to be verified. Connect the server to the internet and try again.

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:

1. Press Win+R to open the "Run" dialog
2. Type gpedit.msc and press Enter
3. In the "Local Group Policy Editor", expand "Computer Configuration", expand "Administrative Templates", expand "System", expand "Internet Communication Management", and then click "Internet Communication settings".
4. 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 .它fixes many MySQL 连接器/NET 错误，并且从未报告过此特定问题。