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 5:26	Modified:	18 Jun 2009 12:33
Reporter:	Christopher Jerdonek
Status:	Closed
Category:	Connector/Net	Severity:	S3 (Non-critical)
Version:	5.2.6	OS:	Microsoft Windows (XP SP3)
Assigned to:		Target Version:
Tags:	port, hosts, specified, Connection, connect, tcp, exception, socket

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.