Excel VBA通过ODBC连接TiDB执行特定查询时偶发连接丢失问题

我用Excel VBA通过MySQL ODBC 8.0驱动连接TiDB v5.1.5，多数查询正常，但涉及INFORMATION_SCHEMA.COLUMNS的WITH子查询（如script5、script6）会偶发丢连接错误，报错信息：

[mysqld-5.7.25-TiDB-v5.1.5] Lost connection to MySQL server during query

100次执行中约20-30次触发，已尝试调整ODBC连接超时、数据包大小，TiDB端wait_timeout/net_read_timeout设为600秒，ADODB.Command超时设为300秒，均无效，怀疑和TiDB特性相关，求排查思路和解决方案。

• TiDB INFORMATION_SCHEMA特性差异：TiDB的INFORMATION_SCHEMA是动态生成的视图而非物理表，WITH子查询多次访问时可能引发元数据加载冲突，尤其是循环执行的高频场景
• ODBC驱动兼容性：MySQL ODBC 8.0对TiDB的CTE（WITH子句）支持存在偶发兼容问题，TiDB的SQL解析、执行逻辑和原生MySQL有差异
• 连接频繁创建销毁：循环内每次执行都做CONNECT+DISCONNECT，短时间内大量连接的创建销毁可能触发TiDB连接池或网络层面的异常
• 元数据缓存失效：TiDB的元数据缓存机制在频繁访问INFORMATION_SCHEMA时可能出现缓存不一致，导致查询执行时异常中断

1. 替换CTE为普通JOIN查询
   避免使用WITH子句，直接对INFORMATION_SCHEMA.COLUMNS做JOIN查询，减少TiDB元数据视图的重复解析压力：

   -- 原script5替换写法
   SELECT COUNT(*) 
   FROM (SELECT LOWER(column_name) AS col FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? AND table_name = ?) V
   LEFT JOIN (SELECT LOWER(column_name) AS col FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? AND table_name = ?) T
   ON V.col = T.col 
   WHERE T.col IS NULL
   

2. 复用数据库连接
   不要在循环内每次创建销毁连接，改为循环前建立一次连接，循环结束后再关闭，减少连接开销。

3. 使用参数化查询
   避免拼接SQL字符串，改用ADODB.Command的参数绑定，减少SQL解析开销、避免注入风险，同时让TiDB更好地缓存执行计划。

4. 调整TiDB元数据相关参数

   • 增大tidb_meta_refresh_interval（默认10秒），减少元数据刷新频率：SET GLOBAL tidb_meta_refresh_interval = 60;
   • 开启tidb_enable_table_cache，启用表元数据缓存：SET GLOBAL tidb_enable_table_cache = ON;

5. 调整ODBC数据包大小
   把连接字符串中的Packet Size从最大值降低到65535（MySQL默认最大值），避免TiDB处理过大数据包时出现异常。

Dim CN As ADODB.Connection ' 全局连接对象，复用连接

Sub CONNECT()
    Dim Server_Name As String
    Dim Port As String
    Dim User_ID As String
    Dim Password As String
    
    Server_Name = "localhost"
    Port = "4000"
    User_ID = "test"
    Password = "passw0rd"

    If (CN Is Nothing) Then
        Set CN = New ADODB.Connection
    End If
    
    If Not (CN.State = 1) Then
        CN.Open "Driver={MySQL ODBC 8.0 Unicode Driver}" & _
            ";Server=" & Server_Name & ":" & Port & _
            ";Uid=" & User_ID & _
            ";Pwd=" & Password & _
            ";OPTION=3;AUTO_RECONNECT=1;Packet Size=65535" & _
            ";ConnectTimeout=300;InteractiveTimeout=300;"
    End If
End Sub

Sub DISCONNECT()
    If Not (CN Is Nothing) Then
        If CN.State = 1 Then
            CN.Close
            Set CN = Nothing
        End If
    End If
End Sub

' 带参数绑定的查询子过程
Sub QRYWithParams(dest As Range, script As String, ParamArray params())
    On Error GoTo errHandling
    Dim rs As ADODB.Recordset
    Dim cmd As New ADODB.Command

    Set cmd.ActiveConnection = CN
    cmd.CommandText = script
    cmd.CommandTimeout = 300
    cmd.CommandType = adCmdText

    ' 绑定参数
    Dim i As Integer
    For i = 0 To UBound(params)
        cmd.Parameters.Append cmd.CreateParameter("param" & i + 1, adVarChar, adParamInput, 255, params(i))
    Next i

    Set rs = cmd.Execute
    dest.CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    Set cmd = Nothing
    Exit Sub

errHandling:
    If InStr(Err.Description, "Lost connection") <> 0 Then
        Debug.Print Format(Now(), "hh:mm:ss") & " " & dest.Address & " Lost Connection Error: " & Err.Description
    End If
    Set rs = Nothing
    Set cmd = Nothing
End Sub

' 原QRY过程保留，用于表名拼接的查询（确保输入可信）
Sub QRY(dest As Range, script As String)
    On Error GoTo errHandling
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command

    cmd.ActiveConnection = CN
    cmd.CommandText = script
    cmd.CommandTimeout = 300

    Set rs = cmd.execute
    dest.CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    Exit Sub

errHandling:
    If InStr(Err.Description, "Lost connection") <> 0 Then
        Debug.Print (Format(Now(), "hh:mm:ss") & " " & dest.Address & " Lost Connection Error")
    End If
End Sub

Sub EXECUTE()
    Dim script1 As String, script2 As String
    Dim script3 As String, script4 As String
    Dim script5 As String, script6 As String
    Dim i As Integer

    ' 提前建立连接，整个循环复用
    CONNECT
    
    ' 定义参数化SQL语句
    script1 = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?"
    script2 = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?"
    script5 = "SELECT COUNT(*) FROM (SELECT LOWER(column_name) AS col FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? AND table_name = ?) V LEFT JOIN (SELECT LOWER(column_name) AS col FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? AND table_name = ?) T ON V.col = T.col WHERE T.col IS NULL"
    script6 = "SELECT COUNT(*) FROM (SELECT LOWER(column_name) AS col FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? AND table_name = ?) T LEFT JOIN (SELECT LOWER(column_name) AS col FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? AND table_name = ?) V ON T.col = V.col WHERE V.col IS NULL"

    For i = 1 To 100
        Dim SS As String, SV As String
        Dim TS As String, TT As String
        
        SS = Range("C" & i).Value
        SV = Range("D" & i).Value
        TS = Range("E" & i).Value
        TT = Range("F" & i).Value

        ' 执行参数化查询
        QRYWithParams Range("H" & i), script1, SS, SV
        QRYWithParams Range("I" & i), script2, TS, TT
        ' 表名无法直接参数化，若输入可信则拼接执行
        QRY Range("J" & i), "SELECT COUNT(*) FROM " & SS & "." & SV
        QRY Range("K" & i), "SELECT COUNT(*) FROM " & TS & "." & TT
        QRYWithParams Range("L" & i), script5, SS, SV, TS, TT
        QRYWithParams Range("M" & i), script6, TS, TT, SS, SV
    Next i

    ' 循环结束后统一关闭连接
    DISCONNECT
End Sub

内容的提问来源于stack exchange，提问作者Dino Dinz