MySQL Bugs: #91971: Memory leak with fetchmany on mysql-connector-python==8.0.12

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

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)
```

example code to reproduce

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

Might be related to #91643?

Looks duplicate/related to bug: https://bugs.mysql.com/bug.php?id=91643.

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.

This also impacts Windows 10 when using Py 3.6.4

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

Bug #95567 marked as duplicate of this one

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.

Hello,

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

Thanks,

Posted by developer:
 
This bug was fixed by BUG#30608703 in release 8.0.20.

Cool, thank you.

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

Thanks,

Posted by developer:
 
Connector/Python 8.0.20 is available on PyPI at https://pypi.org/project/mysql-connector-python/

Good job.
Thanks

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.