c# - MySqlClient EndOfStreamException: Fatal error when attempting to read past the end of the stream

Question

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();

Screenshot of the error:

The data has been written to the database until this error pops up.

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:UsersAdminsourceBargstedt.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();
                }



        }

Just to make it more clear I'll include a screenshot of the csv file and db structure as well.

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.

screenshot of the phpMyAdmin db table

Edit:

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:UsersAdminsource
eposackup newSSHtestSSHtestProgram.cs:line 248
   at SSHtest.Service1.timer1_Tick(Object sender, ElapsedEventArgs e) in C:UsersAdminsource
eposackup newSSHtestSSHtestService1.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

This is a very frequently reported error in MySQL Connector/NET.

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:

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.

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.