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

2

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:

enter image description here

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



        }

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

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

enter image description here

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:\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.
c#
mysql
windows-services
asked on Stack Overflow Sep 18, 2018 by Simon • edited Sep 18, 2018 by Simon

2 Answers

3

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.

answered on Stack Overflow Sep 18, 2018 by Bradley Grainger
0

I faced with same issue and none of the solutions above worked for me. In my case, I was running more than a MySQL insert command with the same connection. After I begin to use transaction for multiple commands, problem resolved.

Environment: .NET Core 3.1 Web API on IIS 10.

answered on Stack Overflow Mar 15, 2020 by ihsany • edited Mar 15, 2020 by Dharman

User contributions licensed under CC BY-SA 3.0