MySQL Bugs: #36549: MySqlDataReader in C# locks up with "SELECT *".

Bug #36549	MySqlDataReader in C# locks up with "SELECT *".
Submitted:	6 May 2008 22:31	Modified:	9 May 2008 17:16
Reporter:	Glenn Pearson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	Server 5.0.51a	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	C-Sharp, C#, MySqlDataReader, SELECT *

Description:
From a C# program (run inside Visual Studio 2005 on Windows XP), I'm getting a lockup (without an exception being thrown) during a read loop that uses "SELECT *" form of command.  If instead the table columns are specified, the bug goes away.

(BTW, net_write_timeout and net_read_timeout have both been increased to 3600 in my.cnf, so that's not likely to be the source of the problem.  This problem is perhaps analogous to that on the IBM platform, #32789.)

How to repeat:
I have a 4-column table (let's call it MyTable), where all columns are varchar and columns 1 & 3 are the key.  (The fact of a compound key may or may not be germane.)
There are about 16000 entries.  If I use code such as this pseudo-code, I'll get a lockup in some iteration of the read loop:

using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Diagnostics;

namespace LockupExample
{
    class DatabaseOps
    {
	public const string DBConnectString = "SERVER=MyExampleServer; DATABASE=MyExampleDB; UID=MyName; PASSWORD=MyPassword;COMPRESS=FALSE";
	private int outcount;
	string Col1, Col2, Col3, Col4;
	MySqlConnection Myconnection = null;
	MySqlCommand cmd;
	MySqlDataReader dbReader = null;

	public void InitDatabaseOps()
	{
		Myconnection = new MySqlConnection(DBConnectString);
 		cmd = Myconnection.CreateCommand();
		Myconnection.Open();
	}

	public void ReadLockupExample() // called after InitDatabaseOps
	{
		cmd.CommandText = "SELECT * FROM MyTable ORDER BY Col1"; // ordering by 1 part of compound key.  May or may not be pertinent to bug.
 		dbReader = cmd.ExecuteReader();
        	outcount = 0;
		while (dbReader.Read())
		{
			outcount++;
			Console.WriteLine(outcount);
			Col1 = dbReader.GetString(0);
			Col2 = dbReader.GetString(1);
			Col3 = dbReader.GetString(2);
			Col4 = dbReader.GetString(3);

			// Real code has processing and db write here on a different connection, but bug happens even when all that is commented out.
 		}
		dbReader.Close();
	}
    }
}

The while() will lockup after a thousand-odd records, always at the same unremarkable record.

Suggested fix:
Workaround - If I instead enumerate the columns, everything works fine:

cmd.CommandText = "SELECT Col1, Col2, Col3, Col4 FROM MyTable ORDER BY Col1";

Hi Glenn and thanks for your report.

I tried for hours to repeat it but I just can't...

 1) 5.0.58PB on XP localhost. 250000 rows table with 27 columns
 2) Remote 5.0.58 on XP. Same table
 3) Remote 5.0.58 on XP. Table with 10 VARCHAR columns, 140000 rows
 4) Remote 5.0.58 on XP. Table with 7 VARCHAR columns, 50000 rows, 1st and 3rd columns being the part of only defined key

I really don't know what's happening on your side and I suggest you to gather as much info as you can and to debug your own code.

OK, thanks for trying.  Perhaps it got fixed in a later version, or there's something about my environment that invokes the problem.  I'm not setup to delve deeper at this point, and I have the workaround.  Just another of life's mysteries.  You can mark this closed if you wish.