MySQL Bugs: #7711: ADO Client Cursor fails in returning large recordsets.

Bug #7711	ADO Client Cursor fails in returning large recordsets.
Submitted:	6 Jan 2005 17:20	Modified:	31 May 2013 7:22
Reporter:	Danny Bowman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
The following query will return 26,310 records in an ADO recordset:

    sSQL = "SELECT * FROM Booking"

Now, if you create a recordset based on this query with a client side cursor:

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open sSQL, cn, adOpenStatic, adLockOptimistic, adCmdText

And try to iterate through the recordset:

    Do Until rs.EOF
        i = i + 1
        rs.MoveNext <---error happens here
    Loop

Each time I get to record 8445, rs.EOF is False, but I receive a "Data provider or other service returned an E_FAIL status." error.  

This Booking table has 104 fields.  If I select only a few records from this table (for example: SELECT ID, CustName, CustFirstName) there is no error.  However, any query returning a large recordset with 30 or more fields will generate this error.

For example, if you take a more complicated query as:

    sSQL = "SELECT Booking.ID, VanLineAgents.Code, JobStatus, Salesperson, CustName, PUState, DelState,"
    sSQL = sSQL & "ContactDate, BookingDate, EstDate, EstTime," & vbCrLf
    sSQL = sSQL & "Weight, WtAdd, FTDisc, TTChrg," & vbCrLf
    sSQL = sSQL & "TTotal, AccCharge, MiscCharge, DiscMisc, TotalPacking," & vbCrLf
    sSQL = sSQL & "Cntnrs * ((100 - CDisc)/100) AS Containers," & vbCrLf
    sSQL = sSQL & "PackingChrg * ((100 - pcDisc)/100) AS PackingCharge," & vbCrLf
    sSQL = sSQL & "UnPkChrg * ((100 - upDisc)/100) AS UnpackingCharge," & vbCrLf
    sSQL = sSQL & "FUDate, PUDateFrom, PUDateTo, DelDateFrom," & vbCrLf
    sSQL = sSQL & "DelDateTo, ActualPUDate, ActualDelDate, IsPrimary, PUPhone1, Booking.VanLineID FROM ((Booking" & vbCrLf
    sSQL = sSQL & "LEFT OUTER JOIN Estimates ON Booking.ID = Estimates.ID) INNER JOIN Agents ON Booking.ID = Agents.ID)" & vbCrLf
    sSQL = sSQL & "INNER JOIN VanLineAgents on VanLineAgents.ID = Agents.Booking" & vbCrLf
    sSQL = sSQL & "WHERE ContactDate >= '2004-09-05'" & vbCrLf
    sSQL = sSQL & "AND ContactDate <= '2005-05-05'" & vbCrLf
    sSQL = sSQL & "AND  (Agents.AgencyID IN (141,144,145,248,697,146,455,3040,3041) OR Agents.Booking IN (141,144,145,248,697,146,455,3040,3041)" & vbCrLf
    sSQL = sSQL & "OR Agents.Origin IN (141,144,145,248,697,146,455,3040,3041)" & vbCrLf
    sSQL = sSQL & "OR Agents.Destination IN (141,144,145,248,697,146,455,3040,3041))" & vbCrLf
    sSQL = sSQL & "ORDER BY Booking.ID DESC"

This wil normally return 3700 records, but it will error out each time in the same Do iteration on record 819.

The practical impact of this is that when these queries are assigned to a VB FlexGrid, it will not display the entire recordset.  In the example above it would only display 819 records instead of the 3700 the query actually returns.  Our applications use these large recordsets to generate extended reports from a grid

How to repeat:
Generate any large ADO recordset with 30 or more fields and a few thousand or more records.  Use an ADO client-side cursor.  Iterate through the records.  At some point in the iteration the error will be generated.  When repeated, the error is thrown each time at exatcly the same point in the iteration.

Verified with a table containing 3000 records

Hi, i have the same error, but with less data.

I connect to a database in a server running mysql 4.0.22 , using the next code to connect in vb6 with odbc connector MyODBC-3.51.11-1-win:
   
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseServer
      
    Set rs = New ADODB.Recordset
    
    conn.CursorLocation = adUseClient
    
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=somesite;" _
            & "DATABASE=test;" _
            & "USER=username;" _
            & "PWD=password;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
 
    conn.Open
    
Now, if i execute this command no records returns...
    SQL = "select * from test"
    rs.Open SQL, conn 
    If Not rs.EOF Then ' here no records are displayed, actually has 2 records (is a test table)

If i execute this command :
   SQL = "select * from test order by id"
    rs.Open SQL, conn 
    If Not rs.EOF Then ' here all records are displayed. (only 2 records available)

The structure of the table is: 
15 fields ,text type no null
1 field bigint(20) no null
2 fields time no null
2 fields double no null
2 fields decimal (10,0) no null
Total: 22 fields and 2 records on it.

Well, I don know if  this error is a bug or not, I only know that is happening to me and I cant goon with my work
I hope this information will usefull to someone.
Thanks for reading my post.
Good luck

I forgot to say..

from my last post....
If i execute this command :
   SQL = "select * from test order by id"
    rs.Open SQL, conn 
    If Not rs.EOF Then ' here all records are displayed. (only 2 records
available)

Now..
But, if there is only one (1) record available in table test.... the result of the recordset = EOF.

Bye

Could you please test with latest released version 3.51.19. Thanks in
advance.

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've just experienced this bug as well. Changing to a server side cursor does the trick.

I should add that I'm using the latest release build of the ODBC driver. I can post VB 6 code and a copy of a  database that causes the problem (backed up using SQL Yog) if that would help.

The bug is definitely still there, and as I mentioned, the work around that I used was to switch to a server side cursor.

The idea to work around is: on open, rows from your table are fetched to recordset by default, then if you have a tiny or empty table, there is no problem with any CursorLocation.

But if your table contains like in my proper case 64 millions of rows.

By default, all rows should go into your recordset (wich is a memory image), then whatever, SQL server, MySQL, Oracle, or even Access, you are in trouble differently with out of memory, time out, and so on...

To solve this issue, you must understand how ADO or previously DAO, or more globally ODBC or JDBC work.

Each recordset acts like a smart SQL client or cursor with your RDBMS, that allows you to "bufferize" a set of rows locally and to send or update them by "batch".

This "buffer" is sized by .MaxRecords property of your recordset, to limit the number of rows that should be fetched by default on open.

In few words...

'Step 1: Establish a Client/Server connection with your RDBMS
Set cnct = New ADODB.Connection

'via an ODBC object that must be pre-defined, or can be re-defined independently
cnct.Open "DSN=" & rdbm & ";"

'via direct and explicite connection string
cnct.Open "DRIVER={MySQL};" & ...  & ";"

'Step 2: Prepare and bind/open recordset

set rcrd = New ADODB.Recordset:rcrd.MaxRecords = 10 
rcrd.open "myTable", ... , ... , adCmdTable
or
rcrd.open "SELECT * FROM myTable", ... , ... , adCmdText
'That will keep only 10 records on default initial fetch

People can also workaround "SELECT * FROM MyTable LIMIT 0,10"

Appolo17

Thank you for your bug report. This issue has already been fixed in the release version of Connector/ODBC 5.2, which you can download at

 http://www.mysql.com/downloads/connector/odbc/

Due to limitations of MySQL Network Protocol the result of SELECT query has to be either completely read and not a single row cannot be skipped or discarded once the query is executed.
There are three possibilities:

1. Read the entire (huge) result set in the memory. The fastest way, which is
   set by default. However, it might consume a lot of memory.
2. Read records one by one: NO_CACHE=1 in the connection string or "Don't 
   cache result of forward-only cursor"
3. Read data in chunks (PREFETCH=number_of_rows_to_prefetch). Recommended for
   large tables and queries that return all table rows. Impacts the server
   performance because SELECT query is run with LIMIT each time the new set
   of rows is to be fetched.