Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

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

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: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. 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: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.
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...