Description:
If there is an exception resetting the underlying session, pooled connections will be "leaked". In particular, if a client is holding a pooled connection when the server goes away (mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query"), if the client then closes that connection, it will "leak" from the pool, and one less connection will be available in the pool.
Strictly speaking, this isn't the pool leaking. Instead, the pool will gradually be reduced in size to zero. The effect is the same:
mysql.connector.errors.PoolError: Failed getting connection; pool exhausted
How to repeat:
(Note: this is pseudo-code-ish; I've not actually run it, but it is indicative of what it takes.)
connection = mysql.connector.connect(
option_files='/etc/my.cnf', pool_size=3)
cursor = connection.cursor()
try:
sql = "select ..." # make this a long-running query
cursor.execute(sql) # now, while it is running, unplug the
# connection to the db
except Exception:
# You'll get the above-mentioned exception or something like it.
cursor.close()
connection.close()
`connection.close()` will raise a new exception (mysql.connector.errors.OperationalError: MySQL Connection not available.) while trying to reset the connection. As a result, the connection will never get added back into the pool, and our pool will be down to two members. Repeat two more times, and you get:
mysql.connector.errors.PoolError: Failed getting connection; pool exhausted
Suggested fix:
mysql.connector.pooling.PooledMySQLConnection.close() should always add a connection back into the pool. The pool should survive transient database connectivity problems. Logically, it should be equivalent to this, but I recognize that there may be state in the underlying connection that makes the solution less trivial than this.
def close(self):
"""Do not close, but add connection back to pool
The close() method does not close the connection with the
MySQL server. The connection is added back to the pool so it
can be reused.
When the pool is configured to reset the session, the session
state will be cleared by re-authenticating the user.
"""
try:
cnx = self._cnx
if self._cnx_pool.reset_session:
cnx.reset_session()
finally:
self._cnx_pool.add_connection(cnx)
self._cnx = None
Description: If there is an exception resetting the underlying session, pooled connections will be "leaked". In particular, if a client is holding a pooled connection when the server goes away (mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query"), if the client then closes that connection, it will "leak" from the pool, and one less connection will be available in the pool. Strictly speaking, this isn't the pool leaking. Instead, the pool will gradually be reduced in size to zero. The effect is the same: mysql.connector.errors.PoolError: Failed getting connection; pool exhausted How to repeat: (Note: this is pseudo-code-ish; I've not actually run it, but it is indicative of what it takes.) connection = mysql.connector.connect( option_files='/etc/my.cnf', pool_size=3) cursor = connection.cursor() try: sql = "select ..." # make this a long-running query cursor.execute(sql) # now, while it is running, unplug the # connection to the db except Exception: # You'll get the above-mentioned exception or something like it. cursor.close() connection.close() `connection.close()` will raise a new exception (mysql.connector.errors.OperationalError: MySQL Connection not available.) while trying to reset the connection. As a result, the connection will never get added back into the pool, and our pool will be down to two members. Repeat two more times, and you get: mysql.connector.errors.PoolError: Failed getting connection; pool exhausted Suggested fix: mysql.connector.pooling.PooledMySQLConnection.close() should always add a connection back into the pool. The pool should survive transient database connectivity problems. Logically, it should be equivalent to this, but I recognize that there may be state in the underlying connection that makes the solution less trivial than this. def close(self): """Do not close, but add connection back to pool The close() method does not close the connection with the MySQL server. The connection is added back to the pool so it can be reused. When the pool is configured to reset the session, the session state will be cleared by re-authenticating the user. """ try: cnx = self._cnx if self._cnx_pool.reset_session: cnx.reset_session() finally: self._cnx_pool.add_connection(cnx) self._cnx = None