mysql - 插入特定表时频繁超时

Question

您好，感谢您的阅读
我遇到以下问题:
我从 vbnet 插入到谷歌云 mysql，从 MSSQL 读取(这就是我同时使用 SqlCommand 和 MySqlCommand 的原因)在两个不同的表中:一个是好的(可以插入数千行，完全没有错误)，第二个其中一个经常出现超时(大约 15%)。
使用的 Vbnet 代码是相同的，并且表格也相当相同。
我有时会超时，几分钟，然后又好几秒钟(连续插入几十秒)，然后再次失败。

希望有人有一个想法......我在这里迷路了!!!

收到的错误:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at UpdateWebBDD.UpdateMySQL.uploadResults()

此代码给出超时:

Function uploadGames() As Double
    Dim lrd As SqlDataReader
    Dim cmdMS As New SqlCommand
    Dim tot As Double
    Dim cur As Double
    Dim ko As Double
    cmdMS.Connection = conMSSQL
    uploadGames = 0
    lrd = ReadTable("Games")
    tot = 0
    While (lrd.Read)
        tot = tot + 1
    End While
    lrd = ReadTable("Games")
    Dim cmd As New MySqlCommand
    While lrd.Read
        Cur = Cur + 1
        Try
            cmd.Connection = conMySQL
            cmd.CommandText = "INSERT INTO " & Mainform.tb_db.Text & ".Games VALUES(@GameNR, @FileName,@gametime,@gametype,@scorered,@scoregreen,@scoremixed,@nbPlayer,@game40,@scorepurple,@sitecode)"
            cmd.Prepare()

            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@GameNR", lrd.GetValue(0))
            cmd.Parameters.AddWithValue("@FileName", lrd.GetValue(1))
            cmd.Parameters.AddWithValue("@gametime", lrd.GetValue(2))
            cmd.Parameters.AddWithValue("@gametype", lrd.GetValue(3))
            cmd.Parameters.AddWithValue("@scorered", lrd.GetValue(4))
            cmd.Parameters.AddWithValue("@scoregreen", lrd.GetValue(5))
            cmd.Parameters.AddWithValue("@scoremixed", lrd.GetValue(6))
            cmd.Parameters.AddWithValue("@nbPlayer", lrd.GetValue(7))
            cmd.Parameters.AddWithValue("@game40", lrd.GetValue(8))
            cmd.Parameters.AddWithValue("@scorepurple", lrd.GetValue(9))
            cmd.Parameters.AddWithValue("@sitecode", Mainform.tb_Site.Text)


            If cmd.ExecuteNonQuery() = 0 Then
                ko = ko + 1
                WriteError(1, "Error inserting Games: no rows inserted" & vbCrLf, 2001)
            Else
                cmdMS.CommandText = "Update [Games] set online = 1 where GameNR = @GameNR"
                cmdMS.Parameters.Clear()
                cmdMS.Parameters.AddWithValue("@GameNR", lrd.GetValue(0))
                cmdMS.ExecuteNonQuery()
                uploadGames = uploadGames + 1
            End If

下一步:

        Catch edw As Exception
            WriteError(1, "Error inserting Games: exception " & vbCrLf & edw.ToString, 2002)
        End Try
        updateMainForm(uploadGames, ko, cur, tot)
    End While
    lrd.Close()
    cmdMS.Dispose()
    updateMainForm(uploadResults, ko, cur, tot)
    cmd.Dispose()
End Function

这个永远不会超时:

Function uploadResultsDetails() As Double
        Dim lrd As SqlDataReader
        Dim cmdMS As New SqlCommand
        Dim cmd As New MySqlCommand
        Dim tot As Double
    Dim cur As Double
    Dim ko As Double
    cmdMS.Connection = conMSSQL
    uploadResultsDetails = 0
    lrd = ReadTable("ResultsDetails")
    tot = 0
    While (lrd.Read)
        tot = tot + 1
    End While
    lrd = ReadTable("ResultsDetails")
    While lrd.Read
        Cur = Cur + 1
        Try
            cmd.Connection = conMySQL
            cmd.CommandText = "INSERT INTO " & Mainform.tb_db.Text & ".ResultsDetails VALUES(@ResultDetailNR, @hitshot,@totalline,@team,@playernr,@resultnr,@front,@rear,@shoulder,@gun,@total,@sitecode)"
            cmd.Prepare()

            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@ResultDetailNR", lrd.GetValue(0))
            cmd.Parameters.AddWithValue("@hitshot", lrd.GetValue(1))
            cmd.Parameters.AddWithValue("@totalline", lrd.GetValue(2))
            cmd.Parameters.AddWithValue("@team", lrd.GetValue(3))
            cmd.Parameters.AddWithValue("@playernr", lrd.GetValue(4))
            cmd.Parameters.AddWithValue("@resultnr", lrd.GetValue(5))
            cmd.Parameters.AddWithValue("@front", lrd.GetValue(6))
            cmd.Parameters.AddWithValue("@rear", lrd.GetValue(7))
            cmd.Parameters.AddWithValue("@shoulder", lrd.GetValue(8))
            cmd.Parameters.AddWithValue("@gun", lrd.GetValue(9))
            cmd.Parameters.AddWithValue("@total", lrd.GetValue(10))
            cmd.Parameters.AddWithValue("@sitecode", Mainform.tb_Site.Text)


            If cmd.ExecuteNonQuery() = 0 Then
                ko = ko + 1
                WriteError(1, "Error inserting ResultsDetails: no rows inserted" & vbCrLf, 4001)
            Else
                cmdMS.CommandText = "Update [ResultsDetails] set online = 1 where ResultDetailNR = @ResultDetailNR"
                cmdMS.Parameters.Clear()
                cmdMS.Parameters.AddWithValue("@ResultDetailNR", lrd.GetValue(0))
                cmdMS.ExecuteNonQuery()
                uploadResultsDetails = uploadResultsDetails + 1
            End If

下一步:

        Catch edw As Exception
            WriteError(1, "Error inserting ResultsDetails: exception " & vbCrLf & edw.ToString, 4002)
        End Try
        updateMainForm(uploadResultsDetails, ko, cur, tot)
    End While
    lrd.Close()
    cmdMS.Dispose()
    updateMainForm(uploadResults, ko, cur, tot)
    cmd.Dispose()
End Function

表定义(它们只是 MSSQL BDD 的在线备份，与 PHP 一起使用，它们没有索引，什么都没有)该表给出了超时

drop table if exists  `games` ;
CREATE TABLE `Games`(
    `GameNR` bigint  NOT NULL,
    `FileName` varchar(255) NOT NULL,
    `gametime` datetime NULL,
    `gametype` nchar(10) NULL,
    `scorered` double NULL,
    `scoregreen` double NULL,
    `scoremixed` double NULL,
    `nbPlayer` double NULL,
    `game40` tinyint NULL,
    `scorepurple` double NULL,
    `SiteCode` nchar(4) NULL
)
;

这不是:

drop table if exists  `ResultsDetails` ;
CREATE TABLE `ResultsDetails`(
    `ResultDetailNR` bigint NOT NULL,
    `hitshot` tinyint NOT NULL,
    `totalline` tinyint NOT NULL,
    `team` varchar(1) NULL,
    `playernr` bigint NULL,
    `resultnr` bigint NOT NULL,
    `front` nchar(2) NOT NULL,
    `rear` nchar(2) NOT NULL,
    `shoulder` nchar(2) NOT NULL,
    `gun` nchar(2) NOT NULL,
    `total` nchar(4) NOT NULL,
    `SiteCode` nchar(4) NULL
)
;

Answer 1

当您向 MySQL 数据库插入数据或从 MySQL 数据库读取数据时，为什么要使用 System.Data.SqlClient？
它可能正在寻找一个有 MySQL 服务器的 Microsoft-SQL-Server，然后找不到，然后等待超时...

System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at UpdateWebBDD.UpdateMySQL.uploadResults()

据我所知，MySql命令位于命名空间MySql.Data.MySqlClient