Unable to connect to any of the specified MySQL hosts

dgulliver

New member
Joined
Feb 8, 2016
Messages
1
Programming Experience
10+
Good Evening,

I have searched this issue all day. The mySQL Database is hosted at a hosting company. The error does not happen on our Web application which is hosted on a windows server at the same company.

However I have created a Windows Form Application and intermittently I get this error.

For instance create a connection run a query and I loop through the records. I use the App.Config to store my connection string:
(Server, User ID, Password and database have been changed)
VB.NET:
  <connectionStrings>
    <add name="LIVE" connectionString="server=www.gbtf.**;user id=GBTFHO; password=a*h^~=r.E; database=GBTF003L; default command timeout=25; pooling=false; port=3306;Convert Zero Datetime=True;"/>
    <add name="TEST" connectionString="server=www.gbtf.**;user id=GBTFHO; password=a*h^~=r.E; database=GBTF; default command timeout=25; pooling=false; port=3306;Convert Zero Datetime=True;"/>
  </connectionStrings>

cnnStr = ConfigurationManager.ConnectionStrings("TEST").ConnectionString

I generate a query and loop through the records calling this Method:

VB.NET:
 Private Function CalcPartnerComm(BP_ID As Integer, PriceBandMax As Decimal, PurchasePrice As Decimal, _
                                     GrossCommission As Decimal, RetailerRefund As Decimal, _
                                     AirportCommission As Decimal) As Decimal
        Dim dCommission As Decimal = 0
        Dim lclRate As Single = 0
        Dim BaseValue As Decimal = 0
        Dim lclRateType As Integer = 0
        Dim lclRate1 As Single = 0
        Dim lclRate2 As Single = 0
        Dim lclRateMax As Single = 0
        Dim lclPurchasePrice As Double = 0


        Using conn As New MySqlConnection(cnnStr)
            conn.Open()
            Dim sSQL As String = _
                "SELECT tblBusinessPartner.BP_ID, tblBusinessPartner.BP_CODE, tblBusinessPartner.BP_NAME, tblBusinessPartner.BP_CR_ID," & _
                "tblCommissionRule.CR_TYPE, tblCommissionRule.CR_RATE_1, tblCommissionRule.CR_RATE_2, tblCommissionRule.CR_RETAILER_MAX_RATE_1,  " & _
                "tblCommissionRule.CR_RETAILER_MAX_RATE_2, tblBusinessPartner.BP_REBATE " & _
                "FROM tblBusinessPartner INNER JOIN tblCommissionRule ON tblBusinessPartner.BP_CR_ID = tblCommissionRule.CR_ID " & _
                "WHERE tblBusinessPartner.BP_ID = " & BP_ID


            Dim cmd As New MySqlCommand(sSQL, conn)
            Dim myreader As MySqlDataReader = cmd.ExecuteReader()


            If myreader.HasRows Then
                While myreader.Read()
                    lclRateType = CInt(myreader.Item("CR_TYPE"))
                    lclRate1 = CSng(myreader.Item("CR_RATE_1")) ' / 100
                    lclRate2 = CSng(myreader.Item("CR_RATE_2")) ' / 100
                    lclRateMax = CSng(myreader.Item("CR_RETAILER_MAX_RATE_1"))


                    BaseValue = CDec(IIf(lclRateType = 1, PurchasePrice, GrossCommission - RetailerRefund - AirportCommission))
                    lclRate = CSng(IIf(CSng(myreader.Item("BP_REBATE")) > lclRateMax, lclRate2, lclRate1))
                    dCommission = CDec(IIf(GrossCommission <> 0, Math.Round((BaseValue * lclRate) / 100, 2), 0))
                    If lclRateType = 1 Then dCommission = CDec(IIf(PriceBandMax < dCommission, PriceBandMax, dCommission))


                End While


            Else
                dCommission = 0
            End If




        End Using


        Return dCommission


    End Function

Once I have the Commission I then update that record:

VB.NET:
Using conn2 As New MySqlConnection(cnnStr)


                        Dim daUpdate As New MySqlDataAdapter
                        conn2.Open() 'Sometimes it crashes here.
                        daUpdate.UpdateCommand = conn2.CreateCommand
                        daUpdate.UpdateCommand.CommandText = "UPDATE tblVoucherHeader SET VH_AQUIRER_COMM = " & decAquirerComm & ", " & _
                                                             "VH_FP_COMM = " & decFinanceComm & ", " & _
                                                             "VH_PG_COMM = " & decPaymentGatewayComm & " " & _
                                                            "WHERE VH_ID = " & VH_ID
                        daUpdate.UpdateCommand.ExecuteNonQuery()


                    End Using

Currently this method is being called over 31000 times and sometimes when this line is executed conn.Open() I get "Unable to connect to any of the specified MySQL hosts", As I say this can be happily called 100 times but on the 101 call it errors.

I've seen many posts saying check your server is configured correctly and also check the port number, but as I'm watching the data being queried and updated ok then things must be configured :)

What baffles me is when it does crash conn.open() is obviously highlighted with the exception details - if I press F5 the connection is made and it carries on happily until it encounters a problem connecting to the DB again.

This is the exception in full:
MySql.Data.MySqlClient.MySqlException was unhandled
ErrorCode=-2147467259
HResult=-2147467259
Message=Unable to connect to any of the specified MySQL hosts.
Number=1042
Source=MySql.Data
StackTrace:
at MySql.Data.MySqlClient.NativeDriver.Open()
at MySql.Data.MySqlClient.Driver.Open()
at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
at MySql.Data.MySqlClient.MySqlConnection.Open()
at FixCommssions.frm1.CalcPartnerComm(Int32 BP_ID, Decimal PriceBandMax, Decimal PurchasePrice, Decimal GrossCommission, Decimal RetailerRefund, Decimal AirportCommission) in C:\VB_Source\trunk\FixCommssions\frm1.vb:line 167
at FixCommssions.frm1.FixAquirerCommissions() in C:\VB_Source\trunk\FixCommssions\frm1.vb:line 116
at FixCommssions.frm1.btnGO_Click(Object sender, EventArgs e) in C:\VB_Source\trunk\FixCommssions\frm1.vb:line 49
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at FixCommssions.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: System.Net.Sockets.SocketException
ErrorCode=10060
HResult=-2147467259
Message=A connection attempt failed because the connected party did not properly respond after a period of time or established connection failed because connected host has failed to respond 88.202.224.240:3306
NativeErrorCode=10060
Source=System
StackTrace:
at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
at MySql.Data.Common.MyNetworkStream.CreateSocketStream(MySqlConnectionStringBuilder settings, IPAddress ip, Boolean unix)
at MySql.Data.Common.MyNetworkStream.CreateStream(MySqlConnectionStringBuilder settings, Boolean unix)
at MySql.Data.Common.StreamCreator.GetStream(MySqlConnectionStringBuilder settings)
at MySql.Data.MySqlClient.NativeDriver.Open()
InnerException:

Any help on understanding this error or a way I can handle the error and mimic the F5 so it continues I'd be very grateful.

Thanks
Darren
 
In some of the ways, spacing and the order of parameters in the MySql connection string does matters. So, stick to the standard format:

C#:
MysqlConn.ConnectionString = "Server=localhost;Port=1234;Database=My_Mysql_Database;Uid=root;Pwd=root;"

If the above connection string fails, try update your c# mysql connection string as shown below (without port variable as well):

VB.NET:
MysqlConn.ConnectionString = "Server=localhost;Database=My_Mysql_Database;Uid=root;Pwd=root;"

Or, sometime the problem could be on your windows firewall, make sure your server allow access to all port associated with your mysql database.
 
Back
Top