MySQL Bugs: #102053: Python sql connection pool not updating after query with zero results

Bug #102053	Python sql connection pool not updating after query with zero results
Submitted:	22 Dec 2020 19:37	Modified:	17 Mar 2021 4:32
Reporter:	James Morris	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / Python	Severity:	S1 (Critical)
Version:	8.0.22	OS:	Ubuntu (18.04)
Assigned to:		CPU Architecture:	x86
Tags:	connection-pool, python

Description:
https://stackoverflow.com/questions/65399454/python-sql-connection-pool-not-updating-value

When a query using a connection pool has no results, the connection is corrupted. The same query will always return no results even if another connection inserts the record.

Below is my code and this is the behavior:

Starting with empty table, I do SELECT query for non-existing value (no results)
Then I do INSERT query, it successfully inserts the value
HOWEVER, after inserting a new value, if I try to do more SELECT statements it only works 2 out of 3 times, always fails exactly every 3rd try (with pool size=3. ie with pool size=10 it will work exactly 9 out of 10 times)
finally, if i restart the script, with the initial SELECT commented out (but the value is in table before script ones) I get the inserted value and it works every time.
Why does this code seem to 'get stuck returning empty result for the connection that had no result' until restarting the script?

(note that it keep opening and closing connections from connection pool because this is taken from a web application where each connect/close is a different web request. Here i cut the whole 'web' aspect out of it)

How to repeat:
#!/usr/bin/python
import mysql.connector

dbvars = {'host':'h','user':'u','passwd':'p','db':'d'}

# db has 1 empty table 'test' with one varchar field 'id'

con = mysql.connector.connect(pool_name="mypool", pool_size=3, pool_reset_session=False, **dbvars)
cur = con.cursor()
cur.execute("SELECT id FROM test WHERE id = '123';")
result = cur.fetchall()
cur.close()
con.close()

con = mysql.connector.connect(pool_name="mypool")
cur = con.cursor()
cur.execute("INSERT INTO test VALUES ('123');")
con.commit()
cur.close()
con.close()

for i in range(12):
    con = mysql.connector.connect(pool_name="mypool")
    cur = con.cursor()
    cur.execute("SELECT id FROM test WHERE id = '123';")
    result = cur.fetchall()
    cur.close()
    con.close()

    print result

--------OUTPUT-------------
[(u'123',)]
[]
[(u'123',)]
[(u'123',)]
[]
[(u'123',)]
[(u'123',)]
[]
[(u'123',)]
[(u'123',)]
[]
[(u'123',)]

--------------------------

Again, if I don't do the initial SELECT before the insert, then all of them return 123 (if it's already in db). It seems the initial SELECT 'corrupts' one of the connections of the connection pool. Further, if I do 2 SELECTs for empty results before the INSERT, then 2 of the 3 connections are 'corrupt'. Finally if I do 3 SELECTs before the insert, it still works 1 of 3 times, because it seems the INSERT 'fixes' the connection (presumably by having 'results').

Ubuntu 18.04
Python 2.7.17 (released Oct 2019)
mysql-connector-python 8.0.21 (June 2020)
MySql server 5.6.10  (AWS)

Hello James Morris,

Thank you for the report!

regards,
Umesh

Hello James, and thanks for submit your bug. The behavior you described is
not a bug and is an expected behavior due to the default value of the
`autocommit` option, which is set to False by default, in short by setting
`autocommit=True` or adding `'autocommit':True` to your connection settings
dictionary object you will get a more aligned behavior to your expectations.

However, please allow me to try to explain why you got this behavior even
after you have invoked the `commit()` method after your insert statement and
how you can get the same results with the `autocommit` option set to False.

By default, `autocommit` option is set to False, whenever you use a pool of
connections or a single connection; this allow to each connection work as in
a different session, and any change will not be applied or see be visible by
other sessions until commit is invoked. And in addition, that for each
session it is very important to invoke or execute `commit()` statement but
not just to commit the changes on this session, it becomes very important in
order of accept the changes committed by other sessions after this session
were started. Furthermore, while implementing pooling due to the nature of
how it works internally because on pooling the connections are not fully
close and reopen;  they are just cleanup in order of be reused later, which
is faster than open new connections.

If we apply this theory adding a commit call prior of the `select` statement,
will do the trick
-----------8<---------
    for _ in range(12):
        con = mysql.connector.connect(pool_name="mypool")
        cur = con.cursor()
        con.commit()  # This commit accepts the inserts by the other session.
        cur.execute("SELECT id FROM test WHERE id = '123';")
        result = cur.fetchall()
        cur.close()
        con.close()

        print(result)
----------->8---------

and gives us the expected output:

[('123',)]
[('123',)]
[('123',)]
[('123',)]
[('123',)]
[('123',)]
[('123',)]
[('123',)]
[('123',)]
[('123',)]
[('123',)]
[('123',)]

In the following script, at the run_test() method you can find the same test
code but with the call to `commit()` prior the select:
#!/usr/bin/python
import mysql.connector

def create_clean():
    # db has 1 empty table 'test' with one varchar field 'id'
    # Drop table if exists, and create it new
    con1 = mysql.connector.connect(**dbvars)
    cur1 = con1.cursor()
    stmt_drop = "DROP TABLE IF EXISTS test"
    cur1.execute(stmt_drop)

    stmt_create = (
        "CREATE TABLE test ("
        "id VARCHAR(30) DEFAULT '' NOT NULL, "
        "PRIMARY KEY (id))"
        )
    cur1.execute(stmt_create)
    cur1.execute("COMMIT;")
    return True

def insert():
    con1 = mysql.connector.connect(**dbvars)
    cur1 = con1.cursor()
    cur1.execute("INSERT INTO test VALUES ('789');")
    cur1.execute("COMMIT;")
    return True

def run_test():
    con = mysql.connector.connect(pool_name="mypool", pool_size=3,
    pool_reset_session=False, **dbvars)
    cur = con.cursor()
    cur.execute("SELECT id FROM test WHERE id = '123';")
    result = cur.fetchall()
    cur.close()
    con.close()

    con = mysql.connector.connect(pool_name="mypool")
    cur = con.cursor()
    cur.execute("INSERT INTO test VALUES ('123');")
    con.commit()
    cur.close()
    con.close()

    for _ in range(12):
        con = mysql.connector.connect(pool_name="mypool")
        cur = con.cursor()
        con.commit()  # This commit accepts the inserts by the other session.
        cur.execute("SELECT id FROM test WHERE id = '123';")
        result = cur.fetchall()
        cur.close()
        con.close()

        print(result)

if __name__ == '__main__':
    dbvars = {
        'host':'localhost',
        'user':'root','passwd':'',
        'db':'test',
        "port": 4824,
        #"use_pure":True,
        #'autocommit':True
    }
    create_clean()
    run_test()

I hope this behavior is clarified now, finally once again thank you for open
a bug and use C/py.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".