MySQL Bugs: #7246: Connection Pooling Problem

Bug #7246	Connection Pooling Problem
Submitted:	13 Dec 2004 22:43	Modified:	5 Jan 2005 20:06
Reporter:	Mark Frisbee	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.3	OS:	Windows (Windows XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
With version 1.0.3 I am still having 2 problems with the closed Bug #6634 .

ERROR ONE:

If pooling = true and the MySqlConnection object tries to reuse a killed or expired connection thread, the error "An established connection was aborted by the software in your host machine" is raised, the killed or expired connection thread is removed from the pool, and a good connection thread will be created the next time a MySqlConnection is created. 

I would prefer if the error was handled internally and a good connection was created automatically. I don't believe this error was raised in the ByteFX provider.

ERROR TWO:

Worse yet if the myReader.Close() line of code in my 'how to repeat' code is remarked (error simulation) and the MySqlConnection object tries to reuse a killed or expired connection, Error one above will occur and then all subsequent calls to create a connection will raise the error "Cannot access a disposed object named 'System.Net.Sockets.NetworkStream'."

How to repeat:
To simulate the problem I used MySQL Administrator to kill the connection threads and the following code.

private void button1_Click(object sender, System.EventArgs e)
{
   MySqlConnection myConnection = 
		new MySqlConnection("...;pooling=true;");
   MySqlCommand myCommand = 
		new MySqlCommand("SHOW TABLES",myConnection);
   MySqlDataReader myReader;
	
   try 
   {
       myConnection.Open();
       myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
				
       /*
       ...
       If Error occurs MySqlDataReader won't close
       */

       myReader.Close();
	 
   } 
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message);
   }
      finally
   {
      myCommand.Dispose();
      myConnection.Dispose();
   }		
}

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Mark

In my test suite, I have some tests that check for this.  Basically it opens a connection using pooling, then closes it (connection thread still active).  I then execute a function I wrote that kills the thread and I verify using show processlist that the thread is dead.  Then the code attempts to reopen that connection.  Testing against 4.1.7, I have no problem with this.  Can you send me some sample code that shows how to reproduce this?

Reggie,

Thank you for your help. I am still experiencing the problem. Here is some more information and new code to reproduce the problem.

MySQL Server Version: 4.1.7
MySQL Connector NET Version: 1.0.3.31712
MS Development Environment 2003 Version: 7.1.3088
.NET Runtime Version: 1.1.4322 SP1
Windows XP SP1 and Windows Server 2000 SP4

In a Windows application the following code produces the error "Unable to read from the transport connection. 

In an ASP.NET application the code produces the error "An established connection was aborted by the software in your host machine".

private void CreateConnectionPoolingError()
{
	// Create Three Connections
	MySqlConnection myConnection1 = new MySqlConnection(
		"server=localhost;database=test;" + 
		"user id=root,password=;pooling=true;");
			
	MySqlConnection myConnection2 = new MySqlConnection(
		"server=localhost;database=test;" + 
		"user id=root,password=;pooling=true;");
		
	MySqlConnection myConnection3 = new MySqlConnection(
		"server=localhost;database=test;" + 
		"user id=root,password=;pooling=true;");

	// Create A Command Object
	// and Set its Connection Property to Connection2
	MySqlCommand myCommand = 
		new MySqlCommand("KILL ?ThreadId",myConnection2);

	try 
	{
		// Open Connection 1 
		myConnection1.Open();
		myConnection2.Open();

		// Get Connection 1's Thread Id
		myCommand.Parameters.Add(
			"?ThreadId",MySqlDbType.Int32).Value
			= myConnection1.ServerThread;

		// Close and Dispose of Connection 1
		myConnection1.Close();
		myConnection1.Dispose();

		// Kill the Thread that Connection 1 was using
		myCommand.ExecuteNonQuery();

		// Open Connection 3
		// This will throw an error 
		myConnection3.Open(); 
	} 
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
	}
	finally
	{
		myConnection1.Dispose();
		myConnection2.Dispose();
		myConnection3.Dispose();
	}
}