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:\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.

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:\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.

Please edit your question to include the full exception details, including call stacks and all inner exceptions: http://idownvotedbecau.se/noexceptiondetails/ (didn't downvote) — Bradley Grainger, Sep 18 '18 at 02:58
@ Bradley Grainger Thanks for pointing out a weakness of my question. I have edited my question with a descriptive context of the MySql exception. — Simon, Sep 18 '18 at 04:36

score 3 · Accepted Answer · answered Sep 18 '18 at 04:58

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:

Press Win+R to open the "Run" dialog

Type gpedit.msc and 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.

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.

score 0 · Answer 2 · edited Mar 15 '20 at 00:35

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.

edited Mar 15 '20 at 00:35

Dharman

30,962
25
85
135

answered Mar 15 '20 at 00:18

ihsany

870
10
12

I'm getting a "Expected to read 4 header bytes but only received 0." error. Was this your error also? I think your behavior is similar to mine and I am surprised that I can't find any resource that would be of help in this. – Savo Pejović Jan 27 '22 at 22:29
@SavoPejović, your error message is not the same error. I am not sure my solution works for your case, but maybe you can check these SO links below; https://stackoverflow.com/questions/52489221/expected-to-read-4-header-bytes-but-only-received-0 https://stackoverflow.com/questions/57974994/how-to-fix-error-expected-to-read-4-header-bytes-but-only-received-0-in-c – ihsany Jan 28 '22 at 15:14

score 0 · Answer 3 · answered Aug 11 '20 at 06:53

In my case, the records are too many. Memory usage on client is too high, so GC and OS virtual memory manager paused the thread for too long, which caused the server to abort the connection. This happened with both MySqlConnector and MySql.Data.

After improving my client code, there are no exceptions any more.

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

3 Answers3

Linked