Bug #67043 Random rare "Unable to connect to any of the specified MySQL hosts." exception
Submitted: 1 Oct 2012 23:58 Modified: 8 Mar 2013 19:08
Reporter: Pavel Bazanov Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:Any OS:Microsoft Windows
Assigned to: Assigned Account CPU Architecture:Any
Tags: 1042, A socket operation was attempted to an unreachable host, MySqlException

[1 Oct 2012 23:58] Pavel Bazanov
Description:
A couple of times every month we get the same exception (43 exceptions for 2 years to be exact):

-----------------------------------------------------

Exception type: MySql.Data.MySqlClient.MySqlException
Exception message: Unable to connect to any of the specified MySQL hosts.
Additional/private data:
Server Error Code: 1042

Stack trace:
   at MySql.Data.MySqlClient.NativeDriver.Open() in C:\VCProjects\MySQL Connector .NET\Source\MySql.Data\NativeDriver.cs:line 203
   at MySql.Data.MySqlClient.Driver.Open() in C:\VCProjects\MySQL Connector .NET\Source\MySql.Data\Driver.cs:line 211
   at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) in C:\VCProjects\MySQL Connector .NET\Source\MySql.Data\Driver.cs:line 199
   at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() in C:\VCProjects\MySQL Connector .NET\Source\MySql.Data\MySqlPool.cs:line 172
   at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() in C:\VCProjects\MySQL Connector .NET\Source\MySql.Data\MySqlPool.cs:line 155
   at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() in C:\VCProjects\MySQL Connector .NET\Source\MySql.Data\MySqlPool.cs:line 244
   at MySql.Data.MySqlClient.MySqlPool.GetConnection() in C:\VCProjects\MySQL Connector .NET\Source\MySql.Data\MySqlPool.cs:line 257
   at MySql.Data.MySqlClient.MySqlConnection.Open() in C:\VCProjects\MySQL Connector .NET\Source\MySql.Data\Connection.cs:line 499
   at RA.Common.DAL.DbManager.InitConnection() in C:\VCProjects\Alex\RA\RA.Common\DAL\DbManager.cs:line 52
   at RA.Common.DAL.DbManager..ctor(Boolean createSeparateConnectionEvenIfUnderLocalTransaction) in C:\VCProjects\Alex\RA\RA.Common\DAL\DbManager.cs:line 90
   at RA.Common.DAL.DbManager..ctor() in C:\VCProjects\Alex\RA\RA.Common\DAL\DbManager.cs:line 81
   at RA.Common.DAL.ServerAccessor.GetServerCurrentDateTime() in C:\VCProjects\Alex\RA\RA.Common\DAL\ServerAccessor.cs:line 13
   at RA.ClientForms.MainForm.CheckTimeDifferenceWithServer(Object sender, EventArgs e) in C:\VCProjects\Alex\RA\RA\ClientForms\MainForm.cs:line 527
   at System.Windows.Forms.Timer.OnTick(EventArgs e)
   at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Inner exception:

Exception type: System.Net.Sockets.SocketException
Message: A socket operation was attempted to an unreachable host 192.168.1.141:3306
Stack trace:
   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
   at MySql.Data.Common.StreamCreator.CreateSocketStream(IPAddress ip, Boolean unix)
   at MySql.Data.Common.StreamCreator.GetStreamFromHost(String pipeName, String hostName, UInt32 timeout)
   at MySql.Data.Common.StreamCreator.GetStream(UInt32 timeout)
   at MySql.Data.MySqlClient.NativeDriver.Open()

-----------------------------------------------------

As I said, the exception is always the same, occurs only in 1 place (in the function that is called every 10 minutes by timer). There is no other occurrences of such an exception in another places of our 100KLOC application. Everything is working fine, but, as I said, ~2 times a month we get this exception.

Our details:
------------
Current MySql Server version: 5.1.58 (but the problem also occurred with older versions)
Current MySql Connector/Net version: 6.5.4 (but the problem also occurred with older versions)
Number of concurrent users: 5-7 (clients and mysql server are in local network)
The exception occurs on different machines: YES
Connection string: "server={0};database={1};User Id={2};password={3};Character Set=utf8"

The function that is called every 10 minutes by timer:
------------------------------------------------------

public static DateTime GetServerCurrentDateTime()
{
	using (var db = new DbManager())
	{
		return Convert.ToDateTime(db.ExecuteScalar("SELECT NOW()"));
	} // connection is disposed and returned to a connection pool
}

DbManager is just a wrapper around MySqlConnection, it just creates and opens a connection like this:

_conn = _dataProvider.CreateConnectionObject();
_conn.ConnectionString = _connectionString;
_conn.Open();

Our considerations:
-------------------

I believe it's somehow related to using a timer, or to a repetitive call. Maybe it's somehow related to connection pool.
Also, here is a thread where similar/same problem is discussed:
http://forums.mysql.com/read.php?38,13350,13350#msg-13350
and some people also noted that the exception occurs in the function that is called by a timer.

How to repeat:
---
[8 Feb 2013 19:08] Fernando Gonzalez.Sanchez
Hi,

There is an issue with MySql server/Windows that requires twaeking of TCP/IP settings, for details see: http://ziya.suzen.net/2009/01/mysql-high-volume-connection-issue-when.html

A related issue is documented here: http://forums.mysql.com/read.php?38,578677,578781#msg-578781 & http://support.microsoft.com/kb/2553549

Of course, this assumes your MySql server is running in Windows.
[9 Mar 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".