Bug #91971 Memory leak with fetchmany on mysql-connector-python==8.0.12
Submitted: 10 Aug 2018 18:47 Modified: 10 May 2022 21:31
Reporter: Jerome Roy Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:8.0.16 OS:MacOS
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] MySQL Verification Team
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] MySQL Verification Team
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] MySQL Verification Team
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.
[28 Apr 2020 7:36] namous seifeddine
Hello,

I am encountering the same problem with the version 8.0.19. Any update regarding this bug?

Thanks,
[28 Apr 2020 9:46] Nuno Mariz
Posted by developer:
 
This bug was fixed by BUG#30608703 in release 8.0.20.
[4 May 2020 8:00] namous seifeddine
Cool, thank you.

Do you have an idea when the package will be loaded to PyPI?

Thanks,
[5 May 2020 8:00] Nuno Mariz
Posted by developer:
 
Connector/Python 8.0.20 is available on PyPI at https://pypi.org/project/mysql-connector-python/
[5 May 2020 10:04] namous seifeddine
Good job.
Thanks
[10 May 2022 21:31] Philip Olson
Note: This bug was fixed via MySQL Bug #97830 in v8.0.20; setting this bug's status to closed. Thanks to everyone for the comments in this bug report, the bug number was added to the old release note.