2931 插入后 MySql 错误 "Unable to connect to any of the specified MySQL hosts."

Question

我在 vb.net (visual studio 2010) 中创建了可执行应用程序。总的来说，我正在尝试将我所有的 MSSQL 数据移动到 MySQL。

环境:MS Server 2003 R2 上的应用程序 - x64 SP2MS Server 2008 R2 标准 x64 SP1 上的 MSSQLUbunto 上的 MySql使用:2.0 的连接器/网络

现在，如果我只执行 1000 次，它工作正常，从 sql server 循环读取 1000 次，并且为每条记录调用 inserttable 函数并插入记录。当我尝试 5000 条记录时，问题就来了。在记录 2932(以及之后的所有条目)，插入产生以下内容:错误:

MySql.Data.MySqlClient.MySqlException: Unable to connect to any of the specified MySQL hosts. ---> System.Net.Sockets.SocketException: Only one usage of each socket address (protocol/network address/port) is normally permitted 192.168.0.14:3306
   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()
   --- End of inner exception stack trace ---
   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()

知道为什么在 2900 个条目之后会发生这种情况吗？

连接字符串

<add name="MSSQLdb" connectionString="Data Source=192.168.0.13; Initial Catalog=sqlserverdb; User ID = IUSR_sqlserverusername; Password=sqlserverpwd"/>
    <add name="MySQLdb" connectionString="server=192.168.0.14;Port=3306;Uid=dbusername; Pwd=dbpassword;database=dbname; pooling=false" providerName="MySql.Data.MySqlClient" />

主要功能

Dim myCon As SqlConnection = New SqlConnection(MSSQLDBConn)
                Dim sSQL As String = 'SELECT TOP 5000 * FROM mssqlservertable'
                Dim myCmd As SqlCommand = New SqlCommand(sSQL, myCon)

                Dim iRetVal As Integer = 0

                Try
                    If myCon.State <> Data.ConnectionState.Open Then
                        myCon.Open()
                    End If

                    Dim drOrder As SqlDataReader = myCmd.ExecuteReader()

                    If drOrder.HasRows Then
                        Dim sOutput As String = ""
                        Do While drOrder.Read()
                            Dim sRetVal As String = ""
                            sRetVal = InsertTable(drOrder("ID"), _
                                                        drOrder("ID2"), _
                                                        drOrder("Resume"), _
                                                        drOrder("DateCreated"), _
                                                        drOrder("DateModified"))

                            If sRetVal.StartsWith("ERROR") = True Then
                                LogIt("ID: " & drOrder("ID") & " iCnt: " & iCnt & " Error: " & sRetVal)
                                ToolStripStatusLabel1.Text = "Error found."
                            End If

                            iCnt += 1
                            If iCnt Mod 100000 = 0 Then
                                ToolStripStatusLabel1.Text = "Current count: " & iCnt
                                Application.DoEvents()
                            End If
                        Loop

                        ToolStripStatusLabel1.Text = "Final count: " & iCnt
                    Else
                        ToolStripStatusLabel1.Text = "No rows"
                    End If

                Catch sqlEx As MySqlException
                    MessageBox.Show("Sql error: " & sqlEx.ToString)
                Catch ex As Exception
                    MessageBox.Show("Regular error: " & ex.ToString)
                Finally
                    myCon.Close()
                    myCon.Dispose()
                End Try
            Catch ex As Exception
                MessageBox.Show("Error: " & ex.ToString)
            End Try

插入函数

Function InsertTable(ByVal ID As Integer, ByVal ID2 As Integer, _
                                         ByVal Resume As String, ByVal DateCreated As DateTime, _
                                         ByVal DateModified As DateTime) As String
        Dim sRetVal As String = ""
        Dim myCon As MySqlConnection = New MySqlConnection(MySQLDBConn)
        Dim sSQL As String = "INSERT INTO mysqltable (ID, ID2, Resume,DateCreated, DateModified)" _
                             & " VALUES (?ID, ?ID2, ?Resume, ?DateCreated, ?DateModified);"
        Dim myCmd As MySqlCommand = New MySqlCommand(sSQL, myCon)
        myCmd.CommandType = Data.CommandType.Text
        myCmd.Parameters.Add(New MySqlParameter("?ID", MySqlDbType.Int32)).Value = ID
        myCmd.Parameters.Add(New MySqlParameter("?ID2", MySqlDbType.Int32)).Value = ID2
        myCmd.Parameters.Add(New MySqlParameter("?Resume", MySqlDbType.LongText)).Value = Resume
        myCmd.Parameters.Add(New MySqlParameter("?DateCreated", MySqlDbType.DateTime)).Value = DateCreated
        myCmd.Parameters.Add(New MySqlParameter("?DateModified", MySqlDbType.DateTime)).Value = DateModified

        Try
            If myCon.State <> Data.ConnectionState.Open Then
                myCon.Open()
            End If
            myCmd.ExecuteNonQuery()
            myCmd.Dispose()
        Catch sqlEx As MySqlException
            sRetVal = "ERROR: Sql error: " & sqlEx.ToString
        Catch ex As Exception
            sRetVal = "ERROR: Regular error: " & ex.ToString
        Finally
            myCon.Close()
            myCon.Dispose()
        End Try

        Return sRetVal
    End Function

Answer 1

我想您正在用尽所有可用的临时 TCP/IP 端口，因为快速连续地打开/关闭数据库连接。您应该打开一次连接并为每个插入使用该连接。史蒂夫关于使用池的建议也应该为您解决这个问题，因为连接将以类似的方式透明地共享。