MySQL Bugs: #57365: Datareader premature end of stream

Bug #57365	Datareader premature end of stream
Submitted:	11 Oct 2010 11:28	Modified:	9 Jan 2015 16:42
Reporter:	Darren Bridle	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.3.4	OS:	Windows (Windows 2003 Server)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Connector/Net, DataReader, endofstream

Description:
In this example, I am issuing a command to a MysqlConnection that returns approx 150,000 records.  I executereader on the command and leave the connection open.

There is a simple while(datareader.read) loop that reads through every record and does some processing.  This processing includes reading/writing to other mysql tables, using a seperate mysqlconnection, which is closed after every read/write.

The following exception occurs:

MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during data read. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed. ---> System.IO.EndOfStreamException: Attempted to read past the end of the stream.
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.FetchDataRow(Int32 statementId, Int32 columns)
   at MySql.Data.MySqlClient.Driver.FetchDataRow(Int32 statementId, Int32 columns)
   at MySql.Data.MySqlClient.ResultSet.GetNextRow()
   at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlDataReader.Read()
   at MySql.Data.MySqlClient.MySqlDataReader.Read()
   at (Name removed).frmMain.btnStart_Click(Object sender, EventArgs e) in (Path removed)\frmMain.vb:line 251
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I ran this twice in our live environment, and both times, the above error was triggered at 5,022 records exactly.  I found this to be too much of a coincidence.

I then changed the mysql command to limit to 4500 records, thinking this might be a limitation of the reader (I could not find any information to verify that).  I ran this against our development environment.  The above error was triggered after 3,568 records and then 3,422 records.  This made me think perhaps it was a timing issue.  The connection was timing out around the same time?

To verify this, I change the limit to 2,000 records.  And added a connection close and re-open before the dataread.  So far, the development environment is on its 4th iteration of 2,000 records, so this seems to have solved the issue.

Is this expected behaviour?  The following are the timings for the first 4 runs, the first 2 are live, second 2 are dev:

1st Run - 12 Minutes 36 Seconds
2nd Run - 18 Minutes 39 Seconds
3rd Run - 14 Minutes 44 Seconds
4th Run - 12 Minutes 06 Seconds

The MySql server is local to the code running and is version 5.1.

Kindest regards,

Darren Bridle

How to repeat:
Fill a datareader with at least 5,000 records and loop through each record.

This may happen if server closes the socket because you query took too long.
in this case, it is likely net_write_timeout, that probably makes sense to increase.

Also, there is a client-side timeout, configurable with MySqlCommand.CommandTimeout

It should be set sufficiently high for big returned result sets. Only network (e.g socket) operations count here. You would get a TimeoutException of this timeout expires

Thank you for your comments.  I will check the net_write_timeout tomorrow on the Dev environment.

I am not receiving any exceptions other than access denied code along with the end of stream error.  So I'm not sure if its a connection timeout? Unless I'm not catching the exception correctly, again I will look into that tomorrow and report back.

Access denied is something different from timeouts. It sounds like a missing GRANT. or maybe wrong password or non-existing user.

That does not seem to add up 5,000+ records through a dataset read though? The access denied I am referring to is in the error code at the top of my submission.  Did I perhaps read that wrong?

What I refer to as "access denied" is the error sent by server , that results in MySqlException with exception text similar to "Access denied for user 'someone' (using password 'YES')" .  If you get this , it is always on MySqlConnection.Open().

I'm wondering what are you refer to as "access denied", it must be something different if it is related to this bug.

Yes I should probably explain that a little better:

The following error:
MySql.Data.MySqlClient.MySqlException (0x80004005)

The (0x80004005) seems to be a generic error in windows for Access Denied, its used everywhere, I simply assumed that MySql shared the same error code.  So I was wondering if it was related or not.

Hoep that clears that up!

Kindest regards,

Darren Bridle

To tell the truth , I do not know where 0x80004005 comes from. If I might guess, perhaps the debugger does not have symbols so it comes from it. I tend to decipher 0x80004005 as generic COM error (E_FAIL from winerror.h)

Anyway, EOF seems to be most interesting, and as far I can tell it would be net_wrtie _timeout. 
I.e server has generated a large result set which is not completely read by client within this timeout . In your case it is rather likely, there is a lot of processing done when iterating through result set with the DataReader.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This is a stupid bug. 

I am working on windows 2003 server, win 7 client trying to develop a winforms application using c#. I am taking large amount of data from mysql via  Mysqldatareader.read()  . It works good for a short time. 
But suddenly it crashes , throwing the error "fatal error encountered during data read " ;

@alper y: 
It is not a crash, but an exception. As I commented previously, large amounts of data need to be consumed relatively fast by the client application, otherwise server closes the socket. Client .NET application cannot handle this situation silently, it *needs* to throw an exception, because it has not read the full server response.

Which means, either you increase the corresponding net_xxx_timeout on the server side, or prepared to handle the exception, or reduce the amount of data read by  the application, or speedup the data processing time.

@Vladislav Vaintroub

I am doing the same process with oracle, sqlserver without any error. So as a ordinary mysql user I am waiting mysql to handle the process.

" either you increase the corresponding net_xxx_timeout on the server side" .you said.   I set the command timeout to a huge number, is this what you said? Can you give some details. What is net_xxx_timeout ? How can I set this value on mysql. Thanks in advance.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_net_write_timeo...

is the description of the parameter. Google will return couple of more links.

Add
net_write_timeout=999

or something like that into the server's configuration file my.ini, and you should be fine. If you're not fine changing server parameters, luckily this one is per-session, you can ExecuteNonQuery "set net_write_timeout=999" in case if you're using huge results sets you're unable to drain within a minute.

@Vladislav Vaintroub

I have added into the server's configuration file my.ini  ,restarted server again and again. The problem still continues. Do you have any other recommendations? 

net_write_timeout=3999
net_read_timeout=3999

I want to again describe my case : I am using MySqlDataReader class in C# to read a large table. For a while MySqldatareader works good but suddenly it throws the "fatal error" exception .

@alper y

I'm sorry, but I left Oracle many month ago. I'm getting these bugdb because I was assigned to the bug in the past, and the bug was not reassigned to anyone else after I left.

Thus, I cannot spend much time analyzing your particular situation (and anyway, it is something that paid Oracle support does, and unpaid forums, or stackoverflow.com, this is not something for the bugdb). 

Hope it makes things clear. 

The last recommendation would be to restart the server after you changed parameters, if this is not done already. 

On a network, many things may go wrong. TCP can go bad in a number of ways, and there are firewalls that may close connections. You also try to understand the stacktrace including all inner stack traces it contains. In case TCP connection is lost, there is no magic that will make it alive again on the .NET client side. In this case, you may complain at the server or firewall or bad internet connectivity, the best thing .NET driver can do is to bring an exception that contains original exception in its the stacktrace or inner stacktrace.

Oh, I see you restarted the server already. So my recommendation is void. 

Nevermind, you mind to collect some pieces of information in the stacktrace, this still stands.

No feedback was provided. The bug is being suspended because we assume that you are no longer experiencing the problem. If this is not the case and you are able to provide the information that was requested earlier, please do so and change the status of the bug back to "Open". Thank you.

Hi I am Kubo in Tokyo Japan.

    I also accounterd this error. I tried to read more than a few million records during Connector/Net. But after read 1,000,000 records, this exception was thrown. I set the NET_READ_TIMEOUT parameter to 999. How can I avoid this error?

MySQL.Data.dll Version 6.4.4

MySql.Data.MySqlClient.MySqlException: Fatal error encountered during data read. ---> MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. ---> System.IO.EndOfStreamException: Attempted to read past the end of the stream.
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.FetchDataRow(Int32 statementId, Int32 columns)
   at MySql.Data.MySqlClient.Driver.FetchDataRow(Int32 statementId, Int32 columns)
   at MySql.Data.MySqlClient.ResultSet.GetNextRow()
   at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlDataReader.Read()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlDataReader.Read()
   at my2ora.MoveData(String strTable, ArrayList listDef, Boolean bDelete)

Hi,

The error is due to server closing the socket due to timeout, so the client side can do little here, but you can
a) Increase the net_write_timeout & net_read_timeout (like 999999).
b) Read the data in small chunks, like
select * from table1 limit 1,100000
select * from table1 limit 100000,199999
select * from table1 limit 199999,299999
and so on.

Hi, I am KUBO in Tokyo Japan. Thnak you for a advice.

    I tried the idea a) . I increased both the NET_WRITE_TIMEOUT parameter and the NET_READ_TIMEOUT parameter to 999,999. This exception is not thrown now. But a new problem has occured. After the program began running, 15 hours have passed, but the program is still running. It seems to be pending, CPU is not used. About a million records have been read. Do I need change other parameters? Anyone who has a nice idea, please help me.

    I think the idea b) may be steady way. But I need to set the transaction isolation level to SERIALIZABLE, because other transaction can be affect the data. It has bad influnces on perfomance.

Hi Kubo,

Here's a third approach: use handler syntax to read one record per query (still since this (using handlers) is one of the faster ways to read a table in MySql, it shouldn't be slow), here is a complete sample:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace InterpreterTest
{
  public class Program
  {
    static void Main(string[] args)
    {
      MySqlConnection con = new MySqlConnection("server=localhost;User Id=xxx;database=sakila;Allow User Variables=true;");
      con.Open();
      MySqlCommand cmd = new MySqlCommand(
        "create temporary table tmp select * from actor", con);
      cmd.ExecuteNonQuery();
      cmd.CommandText = "handler tmp open as ta";
      cmd.ExecuteNonQuery();
      while (true)
      {
        int cnt = 0;
        //cmd.CommandText = "handler ta read `primary` next limit 10";
        cmd.CommandText = "handler ta read next limit 1";
        MySqlDataReader r = cmd.ExecuteReader();
        while (r.Read())
        {
          for (int j = 0; j < r.FieldCount; j++)
          {
            Console.Write( "{0}: {1}", r.GetName( j ), r.GetValue( j ) );
          }
          Console.WriteLine();
          cnt++;
        }
        r.Close();
        if (cnt < 1) break;
      }
      cmd.CommandText = "handler ta close";
      cmd.ExecuteNonQuery();
      cmd.CommandText = "drop table tmp";
      cmd.ExecuteNonQuery();
      con.Close();
    }
  }
}

More documentation on handler syntax: http://dev.mysql.com/doc/refman/5.0/en/handler.html

Another comment, the documentation for this variables is avialable here http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_net_read_timeou...

No mention of max value, however I checked at the server code 5.6.4 and found that the biggest value allowed for these net_xx_timeout variables is 31536000.

I tested it and is set correctly with the queries:

set net_write_timeout = 31536000
show variables like 'net_write_timeout'

Source:

sql\sys_vars.c
static Sys_var_ulong Sys_net_write_timeout(
       "net_write_timeout",
       "Number of seconds to wait for a block to be written to a connection "
       "before aborting the write",
       SESSION_VAR(net_write_timeout), CMD_LINE(REQUIRED_ARG),
       VALID_RANGE(1, LONG_TIMEOUT), DEFAULT(NET_WRITE_TIMEOUT), BLOCK_SIZE(1),
       NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
       ON_UPDATE(fix_net_write_timeout));

sql\sql_const.h
#define LONG_TIMEOUT ((ulong) 3600L*24L*365L)

Hi, I am KUBO in Tokyo Japan.

    Thank you for giving me an idea, I'll try it.
    Now, I'm trying the idea b) using LIMIT, It takes more than a week. I'll kill the process.