MySQL Bugs: #12928: Empty recordset returned incorrectly

Bug #12928	Empty recordset returned incorrectly
Submitted:	1 Sep 2005 12:25	Modified:	28 Aug 2007 10:03
Reporter:	Anker Berg-Sonne	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.11 and others	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
When accessing our MySQL database from ADO (not .NET) through MyODBC we have a query that incorrectly returns an empty recordset when we use a client cursor. When running the exact same query with a server cursor the correct, non-empty, recordset is returned. This problem only occurs on some where statements against the join.

The connection string is "DRIVER={MySQL ODBC 3.51 Driver};SERVER=server;PORT=3306;DATABASE=database;USER=root;PASSWORD=password;Option=131072"

The query is "select f.* from measures_facilityresults f where f.facilityid = 1235 and f.StartDate = '20040901' and f.EndDate = '20050301'"

The measures_facilityresults table is very large, so I haven't included it. If you need the tables I can either try to remove rows or I can ftp the full pages to you..

A final comment, I have tried a number of different OPTION values, including 0, 1, 2, 3 and 131072.

This problem is serious because incorrect results are returned with no error code.

Thank you/Anker

How to repeat:
If needed, I can provide a program and database tables.

I mentioned a join and submitted a non-join select statement. We first found the problem in a join, but I was able to reproduce it with a simple select. The original join was "select f.* from measures_facilityresults f inner join MDS_ClientFacilities c on f.facilityid = c.id where c.oldid = '{11796268-720D-4E68-84F0-151004CC067A}' and f.StartDate = '20040901' and f.EndDate = '20050301'".

Anker

Anker -

Please do send a dump file with the appropriate table structures and data to reproduce the problem.

Thank you,
//Michael

Here is a dump of the database with one table with one row. The problem is reproducible with just this row. Run the query through ADO with Client corsors and get an empty resultset and then with Server cursors and get a one row resultset.

I tried to include the script language we use and a script to demonstrate the bug, but your file size limit defeated me.

Anker

Verified per customer example. I am running the same version of ODBC Driver on 5.0.11

I just installed server version 5.0.12-beta and when i am querying a field of type Decimal(10,4) an empty recordset is returned without any error.

I am using VB6 and MYODBC ver3.5.9 but i also tried the 3.5.11

This was working perfectly with server ver 4.1.7

I have found this to be a problem with tables containing ANY number of rows, not just large tables. Some tables work return the bof and eof properties correctly, others return bof and eof as true regardless of the number of rows. I cannot determine any pattern. This has basically wrecked the application, however, it runs fine on one pc, with the same mysql odbc, database version and ado library, on the other it gives the bad behaviour as reported. Please advise urgently!

I am having this same issue.  I have tried different types of connections (ODBC, DSN-less, etc) with the same result every time.

Some tables will return data, others give a blank recordset every time.

Does anyone know of a workaround until the solution is found?  Does anything work to get some records back on the affected tables?

Hi,
I've tested this problem with the following VB code using MyODBC 3.51.17 against server 5.0.37, and have been unable to reproduce the issue you are raising.  Without knowing the create and data of the MDS_ClientFacilities table in your join it's not really possible to test the join case.  Can you also provide the source for your DLL so we can see what you're doing with your code? Thanks

Sub test()
    On Error GoTo EH
    Dim strSQL, strConn As String
    Dim connection1 As adodb.Connection
    Dim recset1 As adodb.Recordset
    Dim fld As Field

    Set connection1 = New adodb.Connection
    Set recset1 = New adodb.Recordset

    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;PORT=3306;DATABASE=repository;" & _
        "USER=root;PASSWORD=mypass;Option=131072"
        
   strSQL = "select f.* from measures_facilityresults f " & _
            "where f.facilityid = 1235 and " & _
            "f.StartDate = '20040901' and f.EndDate = '20050301'"
        
    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseClient
    connection1.Open
    
   ' open recordset
   recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText
    
    For Each fld In recset1.Fields
        Debug.Print fld.Name & "=" & fld.Value
    Next

    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
EH:
    Debug.Print Err.Number & ":" & Err.Description
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
End Sub

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

Hi all. As no one challenged Eric's findings, I'm setting this to CRp.

I've been experiencing the same problem.  I've just gone through the process of dropping the database and re-creating it.  That solved the problem.

Strangely the problem was linked only to the ODBC driver.  JDBC read the table correctly.  As a side note...
"SELECT * FROM TABLE" - Returns empty result set
"SELECT FIELD FROM TABLE" - returns valid result set
"SELECT FIELD1, FIELD2, ... FROM TABLE" (Thus selecting all fields) - Returns empty result set...

Similar problem solved by installing Microsoft .NET v1.1

I am still experiencing this issue, with 3.1, and 5.1 ODBC Drivers, newest .Net platform.

I am on XP SP3

any new news?

Duplicate of #30958

ALTER TABLE to allow NULL on all non auto-inc fields, and records will be returned.  

... what a hack.

I am forwarding this to Eric for reverification.