CODEWITHSUNDEEP
pythonmysqlconnection-pooling
AwokeKnowing
AwokeKnowing

Reputation: 8206

Python sql connection pool not updating value

I have an issue which is related to connection pool but I don't understand it.

Below is my code and this is the behavior:

  1. Starting with empty table, I do SELECT query for non-existing value (no results)
  2. Then I do INSERT query, it successfully inserts the value
  3. 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)
  4. 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)

#!/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

The output of the above is:

[(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').

Upvotes: 0

Views: 897

Answers (1)

AwokeKnowing
AwokeKnowing

Reputation: 8206

It seems to be a rather severe bug in the python driver for MySQL. Perhaps some configuration incompatibility but clearly a bug as no error is shown yet it returns wrong query results.

I filed the bug report with MySQL team and it's status is currently 'verified'.

https://bugs.mysql.com/bug.php?id=102053

Upvotes: 1

Related Questions