problems using the Context Manager Protocol
Loreton opened this issue · 5 comments
Hi,
I am experiencing problems using the Context Manager Protocol. it seems doesn't release the connections, while manually it works fine.
Env:
pymysql-pool-0.3.5
Python 3.9.5
OS: "Raspbian GNU/Linux 10 (buster)"
Using the following code and running 3 queries:
print("pool size before....:", ResourcesPool.size())
conn=ResourcesPool.get_connection()
logger.warning("Using Context_Manager_Protocol")
with conn as cur:
data=cur.execute_query(query=query_string, args=field_values)
print("pool size after....:", ResourcesPool.size())
I get:
query-01
pool size before....: 0
pool size after....: 4
query-02
pool size before....: 4
pool size after....: 7
query-03
pool size before....: 7
pool size after....: 10
on the next query I get:
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/pi/.local/lib/python3.9/site-packages/pymysqlpool.py", line 184, in put_connection
self._pool.put_nowait(conn)
File "/usr/local/lib/python3.9/queue.py", line 191, in put_nowait
return self.put(item, block=False)
File "/usr/local/lib/python3.9/queue.py", line 137, in put
raise Full
queue.Full
.... until stack is full
Instead using the following:
print("pool size before....:", ResourcesPool.size())
conn=ResourcesPool.get_connection()
try:
cur=conn.cursor()
cur.execute(query=query_string, args=field_values)
data=cur.fetchall()
except (Exception) as e:
logger.error(str(e))
return None
# raise
finally:
conn.close()
print("pool size after....:", ResourcesPool.size())
I get:
query-01
pool size before....: 0
pool size after....: 1
query-02
pool size before....: 1
pool size after....: 1
query-03
pool size before....: 1
pool size after....: 1
Is there a my error?
Pool_jeekie_ISSUE.py.txt
Regards
Thanks for your report, I had make a test as same as your code, but I didn't find that error. You may add debug to observe the connectionget out and put in pool clearly, by add this line pymysqlpool.logger.setLevel('DEBUG')
My test as listed below：
>>> import pymysqlpool
>>> def exceut_query(cmp): ### a test function I think as same sa yours
con = pool.get_connection()
if cmp:
with con as cur:
cur.execute('select 1+1')
else:
cur=con.cursor()
cur.execute('select 1+1')
cur.close()
con.close()
>>> pymysqlpool.logger.setLevel('DEBUG')
>>> pool=pymysqlpool.ConnectionPool(name='pool1', **opsutils_config)
>>> pool.size() ### without the 'pre_create=True' arg, the pool is empyt when beginning
0
>>> exceut_query(True)
02-25 11:11:09 DEBUG: Create new connection in pool(pool1) ### create a new connction when needed
02-25 11:11:09 DEBUG: Put connection back to pool(pool1)
>>>
>>> exceut_query(True)
02-25 11:11:33 DEBUG: Get connection from pool(pool1)
02-25 11:11:33 DEBUG: Put connection back to pool(pool1)
>>> exceut_query(True)
02-25 11:11:36 DEBUG: Get connection from pool(pool1)
02-25 11:11:36 DEBUG: Put connection back to pool(pool1)
>>> exceut_query(False)
02-25 11:11:39 DEBUG: Get connection from pool(pool1)
02-25 11:11:39 DEBUG: Put connection back to pool(pool1)
>>> pool.size() ### as we expect
1
>>>
>>> con1=pool.get_connection() ### get a connection from pool and not return back it for a while
02-25 11:11:48 DEBUG: Get connection from pool(pool1)
>>> pool.size() ### as we expect, now pool is empty
0
>>> exceut_query(True)
02-25 11:11:58 DEBUG: Create new connection in pool(pool1) ### as we expect, need to create another connection
02-25 11:11:58 DEBUG: Put connection back to pool(pool1)
>>> exceut_query(True)
02-25 11:12:01 DEBUG: Get connection from pool(pool1)
02-25 11:12:01 DEBUG: Put connection back to pool(pool1)
>>> exceut_query(False)
02-25 11:12:08 DEBUG: Get connection from pool(pool1)
02-25 11:12:08 DEBUG: Put connection back to pool(pool1)
>>> exceut_query(False)
02-25 11:12:13 DEBUG: Get connection from pool(pool1)
02-25 11:12:13 DEBUG: Put connection back to pool(pool1)
>>> pool.size() ### as we expect
1
>>> con1.close()
02-25 11:12:20 DEBUG: Put connection back to pool(pool1)
>>> pool.size() ### all as we expect
2
>>>
Hi jkklee,
thanks for your answer.
I don't know why but in interactive mode I'm getting error with cmp==True....
The following tests were executed in two different servers with identical results.
--------------- CONFIG ---------------
config={
"host" : os.getenv('MARIADB_HOST'),
"port" : int(os.getenv('MARIADB_PORT')),
"user" : os.getenv('MARIADB_USER'),
"password" : os.getenv('MARIADB_PASSWORD'),
"pre_create" : False,
"database" : db_name,
'autocommit' : True,
"sql_mode" : 'ANSI_QUOTES', # permette di usare ["fld name"] per i nomi delle colonne invece di backtick [`fld name`]
"charset" : 'utf8mb4',
"cursorclass" : pymysql.cursors.DictCursor # A cursor which returns results as a dictionary
}
def exceut_query(cmp):
con = pool.get_connection()
if cmp:
with con as cur:
cur.execute('select 1+1')
else:
cur=con.cursor()
cur.execute('select 1+1')
cur.close()
con.close()
--------------- CMP = False ------------------
pymysqlpool.logger.setLevel('DEBUG')
pool=pymysqlpool.ConnectionPool(name='pool1', **config)
>>> pymysqlpool.logger.setLevel('DEBUG')
>>> pool=pymysqlpool.ConnectionPool(name='pool1', **config)
>>>
>>> pool.size()
0
>>> exceut_query(False);pool.size()
02-28 08:25:24 DEBUG: Create new connection in pool(pool1)
02-28 08:25:24 DEBUG: Put connection back to pool(pool1)
1
Above we can see that for 1 new connection, 1 conneection is released
>>> exceut_query(True);pool.size()
02-28 08:25:24 DEBUG: Get connection from pool(pool1)
02-28 08:25:24 DEBUG: Put connection back to pool(pool1)
02-28 08:25:24 DEBUG: Put connection back to pool(pool1)
02-28 08:25:24 WARNING: Close not reusable connection in pool(pool1) caused by 'Connection' object has no attribute 'execute'
02-28 08:25:24 DEBUG: Create new connection in pool(pool1) due to connection broken
Traceback (most recent call last):
File "<stdin>", line 5, in exceut_query
AttributeError: 'Connection' object has no attribute 'execute'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "<stdin>", line 5, in exceut_query
File "/home/pi/.local/lib/python3.9/site-packages/pymysqlpool.py", line 53, in __exit__
self._pool.put_connection(Connection(*self._args, **self.kwargs))
AttributeError: 'Connection' object has no attribute '_args'
>>> pool.size()
2
>>> exceut_query(False)
02-28 08:28:59 DEBUG: Get connection from pool(pool1)
02-28 08:28:59 DEBUG: Put connection back to pool(pool1)
Above we can see that for 1 new connection, the first release was OK but a second tentative is performed going to exception...
----------- SCRIPT with CMP=True ----------------
pool size before....: 0
[pymysqlpool.get_connection : 162 30706]: DEBUG Create new connection in pool(pool1)
[__main__.execute_sql : 53 30706]: WARNING Using Context_Manager_Protocol
[pymysqlpool.put_connection : 185 30706]: DEBUG Put connection back to pool(pool1)
[pymysqlpool.put_connection : 185 30706]: DEBUG Put connection back to pool(pool1)
[pymysqlpool.put_connection : 185 30706]: DEBUG Put connection back to pool(pool1)
[pymysqlpool.put_connection : 185 30706]: DEBUG Put connection back to pool(pool1)
pool size after....: 4
Using the script with DEBUG, for 1 connection we can see 4 releases.
In your latest interactive mode test, I can't find anything for now; but in file Pool_jeekie_ISSUE.py.txt, I find the root of the problem, in function execute_sql(), with CMP you use the execute_query() method of the overwrited Connection class, without CMP, you use the native execute() method.
In the execute_query() it already has a with statement, so result the tow release.
You may try the new version 3.3.6 which more flexible and robustness and resolve the problem of release more than once.
Wow, You are right!
It was a my mistake. Thank You very much for Your support.
I tried the new version and I find it more complete and I agree with you even more robust.
Just a little thought of mine.
Probably size parameter could be replaced by min_conn and could cover the meaning of pre_create_num
min_conn = 0 [ if min_conn>0 they will be pre-created ]
max_conn> 0 maximum number of connections expected
Great job and thanks for this helpful module
I'm closing the issue. Thanks a lot.