MySQL Bugs: #96701: using fetchmany of mysql python connector returns incorrect result

Bug #96701	using fetchmany of mysql python connector returns incorrect result
Submitted:	29 Aug 2019 20:05	Modified:	12 Dec 2019 5:07
Reporter:	Xin Xiao	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / Python	Severity:	S3 (Non-critical)
Version:	8.0.17, 8.0.18	OS:	Linux (Linux Mint 19.2)
Assigned to:		CPU Architecture:	Any

Description:
I have create a table in mySQL with three rows.  Then I tried to use the mysql connector to retrieve data from my database.

I thought that by repeatedly calling cursor.fetchmany(n), eventually, one should get an empty list, since there are only finitely many records in a table in the database. However, I found that the cursor.fetchmany commands could repeated return the most recent item. For example, suppose the table has three records:
<record_1>
<record_2>
<record_3>

Then calling cursor.fetchmany(1) results the following:
cursor.fetchmany(1) returns <record_1>
cursor.fetchmany(1) returns <record_2>
cursor.fetchmany(2) returns <record_2>
...

How to repeat:
On Linux Mint 19.2:

(1) create a database in MySQL, and create a table with three records
(2) install mySQL python connector. 
(3) start python3
(4) open a connection to the database (cnx = mysql.connector.connect(<your database connection setting>)
(5) create a cursor object:
cursor = cnx.cursor()
(6) execute a select query:
cursor.execute('select * from <your table name>')
(7) evaluate:
cursor.fetchmany(1) => get <record_1>
cursor.fetchmany(1) => get <record_2>
cursor.fetchmany(1) => get <record_2>

(8) observe that the second fetchmany command, and the third fetchmany command returns the second record, and does not advance to the third record

the screenshot showing the table in my database

Attachment: Screen Shot 2019-08-29 at 4.13.00 PM.png (image/png, text), 1.80 MiB.

the execution of repeated fetchmany

Attachment: Screen Shot 2019-08-29 at 4.13.53 PM.png (image/png, text), 2.42 MiB.

Hello Xin iao,

Thank you for the report.

regards,
Umesh

I wanted to add that I am seeing this bug as well using Python 3.7 and mysql-connector-python 8.0.17 on MacOS 10.14.6. Reproducing code:

```
import mysql.connector

batch_size = 10000

# Create of `auth` removed for privacy
auth = {...}

src_con = mysql.connector.connect(**auth)
cursor = src_con.cursor()

qstr = 'select * from data'

cursor.execute(qstr)
while True:
    records = cursor.fetchmany(size=batch_size)
    rc_cnt = len(records)
    print(f'rc_cnt: {rc_cnt}')
    
    if rc_cnt == 0:
        break
```

This prints:

```
rc_cnt: 10000
rc_cnt: 10000
rc_cnt: 10000
rc_cnt: 10000
rc_cnt: 10000
rc_cnt: 10000
rc_cnt: 10000
rc_cnt: 7142
rc_cnt: 1
rc_cnt: 1
rc_cnt: 1
rc_cnt: 1
... (until interrupted)
```

where `fetchmany` continues to return a list containing what appears to be a random record in the table (but that same record over and over) until I kill the process. I have run the same code using the same version of mysql-connector-python on Windows 10 and do not see the issue and the data is returned as expected.

It turned out internally that it is duplicate of Bug #97830.
Marking this as duplicate of Bug #97830.

regards,
Umesh