MySQL Bugs: #6688: Connection termination

Bug #6688	Connection termination
Submitted:	17 Nov 2004 20:08	Modified:	23 Nov 2005 12:48
Reporter:	Marek Paska	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.2	OS:	Linux (GNU/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When my application highly loads the database, it sometimes fails during MySqlReader closing with following exception:

MySql.Data.MySqlClient.MySqlException: Connection unexpectedly terminated
in <0x00222> MySql.Data.MySqlClient.PacketReader:Read (byte[]&,long,long)
in <0x000bc> MySql.Data.MySqlClient.PacketReader:Skip (long)
in <0x0005b> MySql.Data.MySqlClient.PacketReader:OpenPacket ()
in <0x00014> MySql.Data.MySqlClient.NativeDriver:OpenDataRow (int,bool)
in <0x00060> MySql.Data.MySqlClient.CommandResult:Consume ()
in <0x00023> MySql.Data.MySqlClient.MySqlDataReader:Close ()
in <0x004d5>
FastIndexLib.FastIndex2Builder:UpdateIndexFileViaDeletingUnusedFileIds

After this error all attempts to close any other reader or the database connection itself emits the same exception. The connection is totaly broken.

This error was in original ByteFX provider, in 1.0.0 as well as 1.0.1 and it is 1.0.2. In 1.0.2 there is only a better error checking - the exception has a nicer name. (It formerly emited "Unexpected data end".) 

This problem was observed with folowing software:
various versions of MySql 4.0
various versions of Mono (1.0.x, 1.1.x)
Gentoo as well as Mandrake 10

The win32 platform was not tested.

How to repeat:
This error happens only under high load and obviously after several hours. There are is an intensive work with database - many selects and many updates. But the program has only one thread. 

Suggested fix:
It may be a synchronization problem or a race condition. You should make some load tests. It also may be error in Mono.

We'll keep looking for this one but these can be hard to find.

I approve this error is still present in 1.0.3

I have this problem too. Can recreate everytime. 

Caused by the following code sequence:

        string sqlCmd = "LOAD DATA LOCAL INFILE '" + sMySqlFileName + "' INTO TABLE rawgoogle " +
          "FIELDS TERMINATED BY ',' IGNORE 8 LINES;";

        MySqlHelper.ExecuteNonQuery(m_sql.sqlConnection, sqlCmd);

It DOES NOT happen when the file has 1000 lines (288 K) and DOES happen every time when it has 19500 lines. (4.7 MB)

i'm experiencing almost same problem - when a query returns 0 rows, it takes long time until the driver recognizes that the resultset has 0 rows.

My favorite bug still in 1.0.4 :-)

On Windows 2000 connecting to Tao Linux box, I get the "Connection unexpectedly terminated" error while trying to read several hundred thousand records. It happens intermittently, sometimes right away, sometimes deep into the reading. I have yet to completely get through reading all records.

I'm having this problem too and I'm using connector .net 1.0.6.  I, too, am trying to read a lot of records and perform updates on about 10% of them.

I just reorganized my program, and when it was reading several hundred thousand records from one table and only doing insert/updates to another table (or two), I never had this problem.  

But part of the reorganization was to pre-populate one of the result tables and then read from one of the result tables and make updates back to the table.  Now I get Connection Unexpectedly Terminated every couple thousand rows.

The curious part is that neither process is putting any significant load on either the db server nor the processing machine.  Having much of the data pre-populated actually lowers the amount of work being done between myReader.Read() calls considerably, but now I can't go more than 1900 rows without blowing up.

I don't know if it's related to the fact that the old setup was reading from one table and only updating others, or to the fact that the old query result set used a lot of group bys and totals so the result set was all in memory vs the new query which is doing a table scan and returning records only as it finds them.

I'm unclear on what timeout (if any) would actually be getting triggered

I was not able to reproduce the bug on 1.0.6. Could you please re-verify the bug on 1.0.6 ?

This bug (exception "Connection unexpectedly terminated") also occurs on Windows with Connector 1.0.6, MySQL Server 4.1.14 and .NET Framework 1.1 SP1.

The bug needs to be fixed asap.

I was able to reproduce this bug with the following small piece of C# code:

try
{
	// Edit connection settings here!
	MySqlConnection conn = new MySqlConnection( "server=localhost;user id=xxx;password=yyy;database=zzz;port=3306" );
	conn.Open();

	// Do a SELECT on a table with many rows here!
	MySqlCommand cmd = new MySqlCommand( "SELECT * FROM xyz LIMIT 1000", conn );
	MySqlDataReader reader = cmd.ExecuteReader();

	while ( reader.Read() )
	{
		// Let's sleep for a moment. Important for reproducing the bug!
		Thread.Sleep( 2400 );
	}

	MessageBox.Show( this, "Done" );
}
catch( Exception ex )
{
	MessageBox.Show( this, ex.Message, "Exception!", MessageBoxButtons.OK, MessageBoxIcon.Error );
}

Since the bug seems to be strongly connected to some timeout, I've tried to manipulate several server variables which are related to timeout settings. I found out that if the value of the variable net_write_timeout is increased to 3600 (previously 60) the error does not occur!

I am willing to send the test project to you if you give me an email address.

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".

I have the same problem.

My server is MySQL 5.0.32-Debian, connecting via mysql-connector-net-5.0.6 to a client application written in .NET running on XP clients.

I have a loop
 
    while (oSQLReader.Read())

reading 100.000 or so records. The error usually happens after about 50.000 reads.

MySql.Data.MySqlClient.MySqlException: Connection unexpectedly terminated.\r\n
at MySql.Data.MySqlClient.MySqlStream.LoadPacket()\r\n   at MySql.Data.
MySqlClient.MySqlStream.OpenPacket()\r\n   at MySql.Data.MySqlClient.
NativeDriver.FetchDataRow(Int32 statementId, Int32 pageSize, Int32 columns)\r\n
at MySql.Data.MySqlClient.MySqlDataReader.Read()\r\n   at Fetch.given.GetResult2(Char StartChar, regions MinRegion,
regions MaxRegion, String& strError) in U:\\NAS\\Versa\\
Fetch\\Qlib\\given.cs:line 437

Hi,

I have spent ages trying to figure out this problem. After lots of investigation I have concluded the following.

After performing a large SELECT query, the results are then sent to the client which requested it. This can take some time (especially if you are not connecting from localhost).

This error occurs (often at random places depending on network traffic), because the net_write_timeout limit was reached before all the rows had been returned.

I don't beleive changing this timeout limit is the best way to resolve the problem, instead change your SELECT query from:

SELECT * FROM YourTableName

To:

SELECT SQL_BUFFER_RESULT * FROM YourTableName

The use of SQL_BUFFER_RESULT will make sure your MySQL server doesn't lock out or timeout while sending the results of your query to the client.

Hope this helps.

Chris

Chris

This is not correct.  net write timeout only triggers when the client doesn't read for the timeout period.  You do not have to read all rows in this timeout, only do *some* reading.  So if you read a single row in your app and then go spend 60 seconds doing something with it then it will timeout.

i am using ByteFX.MySqlClient.dll in my windows application i am getting error while trying to use at ByteFX.Data.MySqlClient.MySqlDataReader.Read() function as " specified method not supported " please anyone help me out on this scenario i am using 0.7.6.15073 version of ByteFX.MySqlClient.dll