[CONPY-174] pool_recycle : reconnect automatically staled/closed connections into the pool Created: 2021-10-20  Updated: 2021-10-21

Status: Open
Project: MariaDB Connector/Python
Component/s: Connection Pooling
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: el dge Assignee: Georg Richter
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Hi,

In SQLAlchemy, pool_recycle is super. It replaces staled/closed old connection.
It's possible to have this feature in CONPY ?

There is an almost equivalent with ping/reconnect https://mariadb.com/docs/reference/conpy/api/reconnect/, but that would be great that this mechanism (ping/reconnect if necessary) be integrated automatically into ConnectionPool.

Otherwise, just a timer in ConnectionPool parameter, and at its expiration reconnect.
Because I know the wait_timeout of my MariaDB server.

Thx a lot in advance.

 Comments   
Comment by el dge [ 2021-10-20 ]

Sorry, I think I found a solution with auto_reconnect.

taille_pool = 9
 
pool = mariadb.ConnectionPool(default_file="./mariadb.cnf", pool_name="nom", pool_size=taille_pool, pool_reset_connection = False, compress=True)
 
for _ in range(taille_pool):
 
    cnx = pool.get_connection()
 
    cnx.auto_reconnect = True
 
    cnx.close()

And it's ok, after MariaDB has closed the connections (after wait_timeout expire), cnx.get_connection() still works.

PS: It's not very elegant solution, why not insert an auto_reconnect parameter into the constructor of ConnectionPool ?

Comment by el dge [ 2021-10-20 ]

However, auto_reconnect does not work if MariaDB is restarted...

Comment by el dge [ 2021-10-21 ]

It's perhaps an error of programming , but when I spawn 10 process (0 thread) with uwsgi of my program. When all connection are closed, and auto_reconnect come, 90 (size of pool * number of process) connections appear instead 9 (size of pool) !

I can provide code example if necessary.

Generated at Thu Feb 08 03:30:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.