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

[29 Aug 2019 20:05] Xin Xiao
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
[29 Aug 2019 20:14] Xin Xiao
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.

[29 Aug 2019 20:15] Xin Xiao
the execution of repeated fetchmany

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

[30 Aug 2019 10:46] Umesh Shastry
Hello Xin iao,

Thank you for the report.

regards,
Umesh
[13 Sep 2019 14:45] Joshua Adelman
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.
[12 Dec 2019 5:07] Umesh Shastry
It turned out internally that it is duplicate of Bug #97830.
Marking this as duplicate of Bug #97830.

regards,
Umesh