MySQL Bugs: #5583: Reusing a pooled connection fails

Bug #5583	Reusing a pooled connection fails
Submitted:	15 Sep 2004 0:49	Modified:	28 Sep 2004 9:59
Reporter:	Svetoslav Milenov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.0 beta	OS:
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Description of the problem:
When reusing a connection from the pool, reusing fails when against server version 3.22.32. It works OK with 4.0.18 server.

How to repeat:
Test equipment:
client - MySQL Connector/Net beta 1.0.0, Win2kPro, .Net v. 1.1.
servers:
	srv3 - ver. 3.22.32 - this is shipped with AvantGo Server 5.2. for Linux.
	srv4 - ver. 4.0.18 on SuSE 9.1

using System;
using MySql.Data.MySqlClient;

namespace testAG
{
	class Class1
	{
		[STAThread]
		static void Main(string[] args)
		{
			string cs = "Persist Security Info=False;database=Data1;";
			cs += "server=" + args[0]
			cs += ";user id=root;Password=xxx";

			MySqlConnection con = new MySqlConnection(cs);
			con.Open();
			con.Close();

			MySqlConnection con2 = new MySqlConnection(cs);
			con.Open();
			con.Close();
		}
	}
}

When executed against srv3, the second Open method throws MySQLException: Unknown command, with exception.Number 1047.

Suggested fix:
It seems that the problem is in the Driver.ResetUser() method. It starts a new packet with a DBCmd.CHANGE_USER as a first byte. After that, when this packet is send to the server, the response is an error message.

I tried to find if there was (and what) a change in the commands between v. 3.22.5 and 4.0.18, but no success.

The fix should check for the version, and apply different command as needed.

Hmm, I found this:
http://dev.mysql.com/doc/mysql/en/mysql_change_user.html

So, for MySQL server prior 3.23.3 there is nop CHANGE_USER function.

I have changed the MySqlPool.GetPooledConnection() to:

private InternalConnection GetPooledConnection()
{
	InternalConnection conn = null;

	// if there are no idle connections and the in use pool is full
	// then return null to indicate that we cannot provide a connection
	// at this time.
	if (idlePool.Count == 0 && inUsePool.Count == maxSize) return null;

	lock (idlePool.SyncRoot) 
	{
		for (int i=idlePool.Count-1; i >=0; i--)
		{
			conn = (idlePool[i] as InternalConnection);
			if (conn.IsAlive()) 
			{
				lock (inUsePool) 
				{
					inUsePool.Add( conn );
				}
				idlePool.RemoveAt( i );
				break;
			}
			else 
			{
				conn.Close();
				idlePool.RemoveAt(i);
				conn = null;
			}
		}
	}

	if (conn != null) 
	{
		conn.Settings = settings;

		//Next line added by Sunny
		if (conn.Driver.Version.isAtLeast(3, 23, 3))
			conn.Reset();
	}
	else if ((idlePool.Count+inUsePool.Count) < maxSize)
	{
		// if we couldn't get a pooled connection and there is still room
		// make a new one
		conn = CreateNewPooledConnection();
		if (conn == null) return null;

		lock (idlePool.SyncRoot)
			lock (inUsePool.SyncRoot) 
			{
				idlePool.Remove( conn );
				inUsePool.Add( conn );
			}
		}

	return conn;
}

Actually, I do not see any reason for Reset() method, as the pools are hashed based on the connection string, which contains the user name, i.e. the connection will be the same, and there is no reason for reauthentication, except of course if the password is changed.

P.S. In most of the closed bugs here, in the final comment is written that if I want to build from latest dev tree, I should follow the instructions from here:
http://www.mysql.com/doc/en/Installing_source_tree.html.

But there is no tree name for Connector/Net, so how can I get the latest dev. sources?

Thanks

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Connector/Net doesn't support MySql versions prior to 3.23.  We use the COM_CHANGEUSER and COM_PING commands that are not available prior to 3.23.

And it is necessary to reset the user when a connection is coming from the pool.  The reset user does more than reauthenticate, it clears user variables, temp tables, prepared statement ids, etc.  Even though the same user is pulling the connection, the assumption is that the connection is "clean".  Not doing this could lead to some very hard to find bugs.