MySQL Bugs: #45021: MySQL Connector/Net should show better error text when socket exception occurs

Bug #45021	MySQL Connector/Net should show better error text when socket exception occurs
Submitted:	22 May 2009 3:26	Modified:	18 Jun 2009 10:33
Reporter:	Christopher Jerdonek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.2.6	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	connect, Connection, exception, hosts, port, socket, specified, tcp

Description:
Currently, if a certain socket exception occurs when trying to establish a MySQL database connection, MySQL Connector/Net displays an exception message that has nothing to do with the underlying problem.  This masks the problem and makes it harder for users to trouble-shoot.

For example, if, when establishing a database connection via TCP/IP, Windows on the local machine allocates an ephemeral port that conflicts with a socket address still in use, then Windows/.NET will throw a socket exception with the following error text:

"Only one usage of each socket address (protocol/network address/port) is normally permitted <IP ADDRESS/PORT HERE>."

However, MySQL masks this socket exception and displays an exception with the following text:

"Unable to connect to any of the specified MySQL hosts."

The above situation can occur if a connection is rapidly opened and closed, to the point where all of the available TCP/IP ephemeral ports are used up and in TIME-WAIT status.  Code to induce this situation is below.

Here is fuller exception text, along with .NET system.diagnostics socket tracing.

System.Net.Sockets Verbose: 0 : [5552] Socket#33574638::Socket(InterNetwork#2)
System.Net.Sockets Verbose: 0 : [5552] Exiting Socket#33574638::Socket() 
System.Net.Sockets Verbose: 0 : [5552] Socket#33574638::BeginConnect(1:3306#16780437)
System.Net.Sockets Verbose: 0 : [5552] Exiting Socket#33574638::BeginConnect() 	-> ConnectAsyncResult#33736294
System.Net.Sockets Verbose: 0 : [5552] Socket#33574638::EndConnect(ConnectAsyncResult#33736294)
System.Net.Sockets Error: 0 : [5552] Exception in the Socket#33574638::EndConnect - Only one usage of each socket address (protocol/network address/port) is normally permitted 127.0.0.1:3306
System.Net.Sockets Verbose: 0 : [5552] Socket#33574638::Close()
System.Net.Sockets Verbose: 0 : [5552] Socket#33574638::Dispose()
System.Net.Sockets Verbose: 0 : [5552] Exiting Socket#33574638::Close() 
System.Net Error: 0 : [5552] Exception in the 
#35191196::UnhandledExceptionHandler - Unable to connect to any of the specified MySQL hosts.
System.Net Error: 0 : [5552]    at MySql.Data.MySqlClient.NativeDriver.Open() in C:\VS2008 Projects\Projects\MySql.Data.Provider.5.2.6\Source\NativeDriver.cs:line 206
   at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) in C:\VS2008 Projects\Projects\MySql.Data.Provider.5.2.6\Source\Driver.cs:line 161
   at MySql.Data.MySqlClient.MySqlConnection.Open() in C:\VS2008 Projects\Projects\MySql.Data.Provider.5.2.6\Source\Connection.cs:line 466
   at ConsoleTestApp.Program.DbTest2() in C:\VS2008 Projects\Projects\ConsoleTestApp\ConsoleTestApp\Program.cs:line 201
   at ConsoleTestApp.Program.Main(String[] args) in C:\VS2008 Projects\Projects\ConsoleTestApp\ConsoleTestApp\Program.cs:line 59

How to repeat:
Execute a console app with code like the following.  The code executes the same query over and over again until an error occurs:

class Program
{

static void Main(string[] args)
{
	DbTest();
}

private static void DbTest()
{

// The connection string should have Pooling=false;
// The destination server can be localhost.

	string connectionString = "<INSERT CONNECTION STRING HERE>";

	MySqlClientFactory mysql = MySqlClientFactory.Instance;

	DbConnection connection = mysql.CreateConnection();
	connection.ConnectionString = connectionString;

	DbCommand command = mysql.CreateCommand();
	command.Connection = connection;
	command.CommandText = "<INSERT FAVORITE QUERY HERE>";

	int i = 0;

	while (true)
	{
		i++;

		try
		{
			Console.WriteLine(i);

			connection.Open();

			command.ExecuteNonQuery();
		}
		catch (Exception ex)
		{
			System.Diagnostics.Trace.Write(ex);
			Console.WriteLine(ex);

			throw;
		}
		finally
		{
			connection.Close();
		}
	}
}

}

To get socket exception logging, you can include a system.diagnostics section in the app.config similar to the following:

<system.diagnostics>
<sources>
  <source name="System.Net">
	<listeners>
	  <add name="System.Net"/>
	</listeners>
  </source>
  <source name="System.Net.Sockets">
	<listeners>
	  <add name="System.Net"/>
	</listeners>
  </source>
</sources>
<switches>
  <add name="System.Net" value="Verbose"/>
  <add name="System.Net.Sockets" value="Verbose"/>
</switches>
<sharedListeners>
  <add name="System.Net"
      type="System.Diagnostics.TextWriterTraceListener"
      initializeData="C:\Temp\Logs\NetworkTrace.log"
    />
</sharedListeners>
<trace autoflush="true"/>
</system.diagnostics>

Suggested fix:
MySQL Connector/Net should instead report an error that includes information about the underlying socket exception.  This way, the user can quickly understand that he/she is dealing with a particular type of TCP/IP problem.

If this turns out to be a limitation or issue with MySQL server rather than with the Connector, then this bug can be re-reported with MySQL server.  If that is the case, then perhaps MySQL server can be adjusted to do whatever is necessary so that the Connector can react to certain socket exceptions with appropriate exception information.

I checked out the Connector/Net 5.2.6 code, and the relevant section of code appears to be here.  This is in the implementation of the MySql.Data.Common.StreamCreator.CreateSocketStream(IPAddress ip, bool unix) method:

try
{
	socket.EndConnect(ias);
}
catch (Exception)
{
	socket.Close();
	return null;
}

Is there any reason not to re-throw the exception here so the caller can see it?

Perhaps you can adjust the code as follows:

try
{
	socket.EndConnect(ias);
}
catch (Exception)
{
	socket.Close();
	throw;
}

And then in StreamCreator.GetStream(uint timeout) (which is the only place that calls CreateSocketStream), you can adjust the second CreateSocketStream call in the foreach loop to something like the following:

try
{
	stream = CreateSocketStream(address, false);
}
catch (Exception)
{
	// "throw" or "continue" in this catch block
	// depending on whether there are more
	// IP addresses to consider in the dnsHosts list
	// and associated ipHE address lists.  For 
	// example, if there are more addresses to 
	// consider, then you can bury the exception, 
	// and continue on with the other addresses to 
	// come in the two while/foreach loops.

	// throw;
	// continue;
}

Question: are there actually circumstances where a SocketException is valid in the above foreach loop, and you would want to ignore the exception and continue on to the next IP address?  In other words, why not always rethrow the exception since it is an error state?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75295

fixed in 5.2.7 and 6.0.4+

An entry was added to both the 5.2.7 and 6.0.4 changelogs:

If a certain socket exception occurred when trying to establish a MySQL database connection, MySQL Connector/NET displayed an exception message that appeared to be unrelated to the underlying problem. This masked the problem and made diagnosing problems more difficult.

For example, if, when establishing a database connection via TCP/IP, Windows on the local machine allocated an ephemeral port that conflicted with a socket address still in use, then Windows/.NET would throw a socket exception with the following error text:

Only one usage of each socket address (protocol/network address/port) is normally
permitted IP ADDRESS/PORT.

However, Connector/NET masked this socket exception and displayed an exception with the following text:

Unable to connect to any of the specified MySQL hosts.