Bug #91971 Memory leak with fetchmany on mysql-connector-python==8.0.12
Submitted: 10 Aug 2018 18:47 Modified: 30 May 2019 9:21
Reporter: Jerome Roy Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:8.0.16 OS:Mac OS X
Assigned to: CPU Architecture:x86

[10 Aug 2018 18:47] Jerome Roy
Description:
Hi,

Switching from mysql-connector-python==8.0.11 to 8.0.12 makes my SQL query fail with fetchmany(size): the command never returns and eats memory until the OS kills it. 

The code is supposed to fetch 215647 lines from the public genetic database ensembldb.ensembl.org. 

Inside a python3.6 virtualenv with only mysql-connector-python installed, and testing on both Mac OSX 10.13.4 and CentOS 7.3:

- The code works for mysql-connector-python==8.0.11 using fetchmany()
- The code works if results are returned with fetchall()
- The code fails if mysql-connector-python==8.0.12 AND using fetchmany(size)
- The code fails for different values of the fetchmany argument

When the bug appears, my output file grows after line 215647 by repeating infinitely the first line of the last batch queried (i.e. line 200001 for size=100000, line 215001 for size=1000, and line 215641 for size=10).

How to repeat:
```
import mysql.connector

def main():
    connection = mysql.connector.connect(
        host='ensembldb.ensembl.org',
        user='anonymous',
        password='',
        port='3337')

    sql = """
    SELECT stable_id, biotype
    FROM homo_sapiens_core_85_37.transcript;
    """
    try:
        cursor = connection.cursor()
        cursor.execute(sql)
        while True:
            rows = cursor.fetchmany(10)
            if not rows:
                break
            for row in rows:
                yield row
    finally:
        cursor.close()
        connection.close()

for line in main():
    print(line)
```
[10 Aug 2018 18:48] Jerome Roy
example code to reproduce

Attachment: mysqlbug#91971.py (text/x-python-script), 622 bytes.

[10 Aug 2018 18:50] Jerome Roy
Might be related to #91643?
[13 Aug 2018 17:28] Miguel Solorzano
Looks duplicate/related to bug: https://bugs.mysql.com/bug.php?id=91643.
[10 May 2019 22:05] a f
Any update on this?  Confirmed it's still happening in v8.0.12-8.0.16, we've had to revert back to 8.0.11 in order to use mysql-connector-python.
[10 May 2019 22:08] a f
This also impacts Windows 10 when using Py 3.6.4
[30 May 2019 9:21] Umesh Shastry
Hello Jerome Roy,

Thank you for the report and test case.
Verified as described using VS 2019, Python 3.7.3 and MySQL Connector/Python 8.0.16.

regards,
Umesh
[30 May 2019 9:26] Umesh Shastry
Bug #95567 marked as duplicate of this one
[11 Dec 2019 19:55] Jacob Williams
I am still seeing this in 8.0.18. I was able to narrow down the specific error conditions a bit:

If the number of rows in the result set is less than the fetch size, it correctly returns 0 on subsequent calls.

If the number of rows in the final batch is equal to the fetch size, the bug does not occur and it returns 0 on subsequent calls.

As a result I am able to work around by breaking my loop at the end after checking that the length of the list is less than the fetch size and breaking there.

This probably only works when using unbuffered connections, which was my use case.