MySQL Bugs: #10014: [ODBC 3.51 Driver]Lost connection to MySQL server during query

Bug #10014	[ODBC 3.51 Driver]Lost connection to MySQL server during query
Submitted:	19 Apr 2005 21:27	Modified:	30 May 2013 12:37
Reporter:	Mark Keefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (Windows XP Pro)
Assigned to:		CPU Architecture:	Any

Description:
When using ODBC to retrieve data from MySQL Server, I consistently get the error "[MySQL][ODBC 3.51 Driver]Lost connection to MySQL server during query" from Access, Excel, and VBA.

I know this error has been experienced by many people, but I'm still unable to find a specific occurance of it after searching intently through the MySQL bugs database, and so far, after trying for several days, nothing has shown up in Google that solves the problem for me.  In my case, I am getting this error from Windows XP Pro, using the Connector/ODBC driver version 3.51.11-1 and previous version 3.51.07.

I get the same ODBC error from one client trying 4 different MySQL servers.  One of the is v 4.1 on RedHat Linux, the other three are version 4.0 on Solaris 9.

I've already checked to make sure this is not a connectivity issue or a timeout issue.  We're not running tcpwrappers, there is no reverse lookup occurring.  I have no problem connecting from MySQL browser or from JDBC to the same servers.

I seem unable so far to get Connector/ODBC to create a log or trace file.

How to repeat:
in Microsoft Excel, I use Data > Import External Data > Database Query, and choose a MySQL DSN.  This then connects successfully to MySQL server and shows me the tables and columns to add to the query.  Then I finish creating the query using the wizard.  At the last step, when data should be returned into Excel, I get the error.

There is a bug reported and verified that tables with field of
type date[time]/timestamp has problems with these kind of
application. It is your case ?

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".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.

Trying to connect to Mysql from Excel using ODBC 5.1 Unicode Driver. The same code runs perfect for a lesser number of rows. I am converting approx 5000 X 26 matrix from Excel to array then creating an insert query to input the data using the ADODB.command. Please find the code below, I don't think I have left any concepts:

Sub uploadDailyData()

Dim myLastRow As Long
myLastRow = getLastRow

ModFunctions.replaceBlankCurrencyWithDoubleValue

Load frmSetDate
frmSetDate.Show

If uploadDateSetByUser = "" Then
    MsgBox "Please re-enter the date and continue. Exiting Now .."
    Exit Sub
End If

    If myLastRow > 1 Then
    Sheet1.Range("Y1").Value = "Upload Date"
    Sheet1.Range("Y2:Y" & myLastRow).Value = Format(uploadDateSetByUser, "YYYY-MM-DD")
    Sheet1.Range("Z1").Value = "Date of last state change"
    Sheet1.Range("Z2:Z" & myLastRow).FormulaR1C1 = "=IF(RC[-4]=""Not Modified"","""",DATE(YEAR(LEFT(RC[-4],11)),MONTH(LEFT(RC[-4],11)),DAY(LEFT(RC[-4],11))))"
    Sheet1.Range("Z2:Z" & myLastRow).Copy
    Sheet1.Range("Z2:Z" & myLastRow).PasteSpecial xlPasteValues

    Dim insertQuery As String
        Dim uploadDataArray()
        Dim intRows, intCols, i, j
        
        intRows = Sheet1.UsedRange.Rows.Count
        intRowsArray = intRows
        
        intCols = Sheet1.UsedRange.Columns.Count
        intColsArray = intCols
        
        ReDim Preserve uploadDataArray(1 To intRowsArray, 1 To intColsArray)
        
            For i = 1 To UBound(uploadDataArray, 1)
                For j = 1 To UBound(uploadDataArray, 2)
                    If j = 25 Then
                    uploadDataArray(i, j) = Format(Sheet1.Cells(i, j), "YYYY-MM-DD")
                    Else
'                    If i = 13 And j = 15 Then
'                        Debug.Print i, j
'                    End If
                    uploadDataArray(i, j) = ModFunctions.escSingleQuote(Sheet1.Cells(i, j))
'                    Debug.Print uploadDataArray(i, j)
                    End If
                Next
            Next
            
        insertQuery = ModFunctions.createInsertQuery(uploadDataArray)
'        Debug.Print insertQuery
        
    Dim uploadConnection As ADODB.Connection
    Set uploadConnection = ModFunctions.mysqlConnect
    
    Dim uploadRecordSet As ADODB.Recordset
    Set uploadRecordSet = New ADODB.Recordset
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
  

    
'        insertQuery = "INSERT INTO `customer_account_data_pulse_master` (customer_Number, legal_Entity, account_Type, threshold_3, threshold_3_Policy, threshold_2, threshold_2_Policy, threshold_1, threshold_1_Policy, customer_email_list_primary, customer_email_list_secondary, tcl_email_list, email_address, user_Name, user_Note, customer_Name, collector_Name, net_Position, credit_Limit, over_Limit, current_Account_State, date_time_of_last_state_change, aR_Watch_Report_Timestamp, config_Data_Applied_To_AR_WATCH_Cycle, upload_Date) Values (28968,'TGBUS','Prepaid','200%','Notify','150%','Notify','100%','AutoBlock','ameer22@hotmail.com;','','jthakore@tatacommunications.com;shaiju.subramanian@tatacommunications.com;phil.dailey@tatacommunications.com;vivek.pandey@tatacommunications.com;sandeep.nair@tatacommunications.com;','sandeep.rajput@tatacommunications.com;','sstamp mathew 20120709','BABYLON STAR','','',,,,'unblock','NOT MODIFIED','Y','NOT PRESENT IN REPORT','2014-12-28');"
'        Debug.Print insertQuery
        
'        uploadRecordSet.Open insertQuery, uploadConnection, adUseClient, adLockBatchOptimistic
        cmd.CommandText = insertQuery
'        Debug.Print Err.Number
        cmd.Execute
        
        uploadConnection.Close
        Set uploadConnection = Nothing

        FilePath = ThisWorkbook.Path & Application.PathSeparator
        Open FilePath & "insertQueryDump" For Output As #1
        Write #1, insertQuery
    End If
    

End Sub

Function createInsertQuery(dataArray As Variant) As String
    Dim myQuery As String, intRowsQuery As Variant, intColsQuery As Variant, insertDataQuery As String, i As Long, j As Long
    MsgBox dataArray(2, 25)
    
    myQuery = "INSERT INTO `customer_account_data_pulse_master`"
    myQuery = myQuery & " (customer_Number, legal_Entity, account_Type, threshold_3, threshold_3_Policy, threshold_2, threshold_2_Policy, threshold_1, threshold_1_Policy, customer_email_list_primary, customer_email_list_secondary, tcl_email_list, email_address, user_Name, user_Note, customer_Name, collector_Name, net_Position, credit_Limit, over_Limit, current_Account_State, date_time_of_last_state_change, aR_Watch_Report_Timestamp, config_Data_Applied_To_AR_WATCH_Cycle, upload_Date)"
    myQuery = myQuery & " Values"
    insertDataQuery = ""
    intRowsQuery = intRowsArray
    intColsQuery = intColsArray
        
            For i = 2 To intRowsQuery
            insertDataQuery = insertDataQuery & "("
                For j = 1 To intColsQuery
                    If j = 1 Or j = 18 Or j = 19 Or j = 20 Then
                        insertDataQuery = insertDataQuery & dataArray(i, j) & ","
                    Else
                        insertDataQuery = insertDataQuery & "'" & dataArray(i, j) & "',"
                    End If
                Next
            insertDataQuery = Left(insertDataQuery, Len(insertDataQuery) - 1) & "), "
'            Debug.Print insertDataQuery
            Next
    myQuery = myQuery & insertDataQuery
'    VBA.RTrim (myQuery)
    myQuery = Left(myQuery, Len(myQuery) - 2) & ";"
'    Debug.Print myQuery
'    MsgBox myQuery
    
    createInsertQuery = myQuery
End Function