def checkout(dbapi_connection, connection_record, connection_proxy):
pid = os.getpid()
if connection_record.info['pid'] != pid:
connection_record.connection = connection_proxy.connection = None
error = "Connection record belongs to pid %s, "\
"attempting to check out in pid %s" % \
(connection_record.info['pid'], pid)
self.logger1.error(error)
raise exc.DisconnectionError(error)
cursor = dbapi_connection.cursor()
try:
cursor.execute(self.ping_query)
self.logger1.debug('Ping connection OK')
except:
# optional - dispose the whole pool
# instead of invalidating one at a time
# connection_proxy._pool.dispose()
# raise DisconnectionError - pool will try
# connecting again up to three times before raising.
connection_record.connection = connection_proxy.connection = None
self.logger1.error('Invalidate connection')
raise exc.DisconnectionError('Connection ping fails')
cursor.close()
def _ping_listener(dbapi_conn, connection_rec, connection_proxy):
"""Ensures that MySQL connections checked out of the pool are alive.
Modified + borrowed from: http://bit.ly/14BYaW6.
"""
try:
dbapi_conn.cursor().execute('select 1')
except dbapi_conn.OperationalError as ex:
if _in_any(six.text_type(ex.args[0]), MY_SQL_GONE_WAY_AWAY_ERRORS):
LOG.warn('Got mysql server has gone away', exc_info=True)
raise sa_exc.DisconnectionError("Database server went away")
elif _in_any(six.text_type(ex.args[0]), POSTGRES_GONE_WAY_AWAY_ERRORS):
LOG.warn('Got postgres server has gone away', exc_info=True)
raise sa_exc.DisconnectionError("Database server went away")
else:
raise
def checkout(dbapi_con, con_record, con_proxy):
"""
Making sure the connection is live, and preventing against:
'MySQL server has gone away'
Copied from:
http://stackoverflow.com/questions/30630120/mysql-keeps-losing-connection-during-celery-tasks
"""
try:
try:
if hasattr(dbapi_con, 'ping'):
dbapi_con.ping(False)
else:
cursor = dbapi_con.cursor()
cursor.execute("SELECT 1")
except TypeError:
app.logger.debug('MySQL connection died. Restoring...')
dbapi_con.ping()
except dbapi_con.OperationalError as e:
app.logger.warning(e)
if e.args[0] in (2006, 2013, 2014, 2045, 2055):
raise exc.DisconnectionError()
else:
raise
return db
def checkout(dbapi_connection, connection_record, connection_proxy):
pid = os.getpid()
if connection_record.info['pid'] != pid:
connection_record.connection = connection_proxy.connection = None
raise exc.DisconnectionError("Connection record belongs to pid %s, "
"attempting to check out in pid %s" %
(connection_record.info['pid'], pid))
def checkout(dbapi_connection, connection_record, connection_proxy):
"""Checkout sub-processes connection for sub-processing if needed.
Checkout is called when a connection is retrieved from the Pool.
Args:
dbapi_connection: A SqlALchemy DBAPI connection.
connection_record: The SqlALchemy _ConnectionRecord managing the
DBAPI connection.
connection_proxy: The SqlALchemy _ConnectionFairy object which
will proxy the public interface of the DBAPI connection for the
lifespan of the checkout.
Returns:
None
"""
# Get PID of main process
pid = os.getpid()
# Detect if this is a sub-process
if connection_record.info['pid'] != pid:
# substitute log.debug() or similar here as desired
log_message = ('''\
Parent process {} forked ({}) with an open database connection, \
which is being discarded and recreated.\
'''.format(connection_record.info['pid'], pid))
log.log2debug(20073, log_message)
connection_record.connection = connection_proxy.connection = None
raise exc.DisconnectionError('''\
Connection record belongs to pid {}, attempting to check out in pid {}\
'''.format(connection_record.info['pid'], pid))
def ping_connection(dbapi_connection, connection_record, connection_proxy):
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except:
raise exc.DisconnectionError()
cursor.close()
def _ping_listener(engine, dbapi_conn, connection_rec, connection_proxy):
"""Ensures that MySQL, PostgreSQL or DB2 connections are alive.
Borrowed from:
http://groups.google.com/group/sqlalchemy/msg/a4ce563d802c929f
"""
cursor = dbapi_conn.cursor()
try:
ping_sql = 'select 1'
if engine.name == 'ibm_db_sa':
# DB2 requires a table expression
ping_sql = 'select 1 from (values (1)) AS t1'
cursor.execute(ping_sql)
except Exception as ex:
if engine.dialect.is_disconnect(ex, dbapi_conn, cursor):
msg = _LW('Database server has gone away: %s') % ex
LOG.warning(msg)
# if the database server has gone away, all connections in the pool
# have become invalid and we can safely close all of them here,
# rather than waste time on checking of every single connection
engine.dispose()
# this will be handled by SQLAlchemy and will force it to create
# a new connection and retry the original action
raise sqla_exc.DisconnectionError(msg)
else:
raise
def checkout(dbapi_connection, connection_record, connection_proxy):
if engine.dialect.name == 'sqlite':
return
pid = os.getpid()
if connection_record.info['pid'] != pid:
# substitute log.debug() or similar here as desired
log.info(
"Parent process %(orig)s forked (%(newproc)s) with an open "
"database connection, "
"which is being discarded and recreated." % {
"newproc": pid,
"orig": connection_record.info['pid']
})
connection_record.connection = connection_proxy.connection = None
raise exc.DisconnectionError(
"Connection record belongs to pid %s, "
"attempting to check out in pid %s" %
(connection_record.info['pid'], pid))
# Following code prevents 'Mysql has gone away' errors
try:
try:
dbapi_connection.ping(False)
except TypeError:
dbapi_connection.ping()
except dbapi_connection.OperationalError as e:
if e.args[0] in (2006, 2013, 2014, 2045, 2055):
raise e.DisconnectionError()
else:
raise
def checkout(dbapi_connection, connection_record, connection_proxy):
"""Checkout sub-processes connection for sub-processing if needed.
Checkout is called when a connection is retrieved from the Pool.
Args:
dbapi_connection: Connection object
connection_record: Connection record object
connection_proxy: Connection proxy object
Returns:
None
"""
# Get PID of main process
pid = os.getpid()
# Detect if this is a sub-process
if connection_record.info['pid'] != pid:
# substitute log.debug() or similar here as desired
log_message = ('Parent process %s forked (%s) with an open '
'database connection, '
'which is being discarded and recreated.'
'') % (connection_record.info['pid'], pid)
log.log2debug(1079, log_message)
connection_record.connection = connection_proxy.connection = None
raise exc.DisconnectionError(
"Connection record belongs to pid %s, "
"attempting to check out in pid %s" %
(connection_record.info['pid'], pid))
def _ping_listener(dbapi_conn, connection_rec, connection_proxy):
"""
Ensures that MySQL connections checked out of the
pool are alive.
Borrowed from:
http://groups.google.com/group/sqlalchemy/msg/a4ce563d802c929f
"""
try:
dbapi_conn.cursor().execute('select 1')
except Exception as ex:
if engine.dialect.is_disconnect(ex, dbapi_conn, cursor):
msg = _LW('Database server has gone away: %s') % ex
LOG.warning(msg)
# if the database server has gone away, all connections in the pool
# have become invalid and we can safely close all of them here,
# rather than waste time on checking of every single connection
get_engine().dispose()
# this will be handled by SQLAlchemy and will force it to create
# a new connection and retry the original action
raise sqla_exc.DisconnectionError(msg)
else:
raise
def check_connection(dbapi_con, con_record, con_proxy):
'''Listener for Pool checkout events that pings every connection before using.
Implements pessimistic disconnect handling strategy. See sqlalchemy docs'''
try:
dbapi_con.ping()
except exc.OperationalError, ex:
raise exc.DisconnectionError()
def ping_connection(dbapi_connection, connection_record, connection_proxy):
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except:
# connecting again up to three times before raising.
raise exc.DisconnectionError()
cursor.close()
def checkout(dbapi_connection, connection_record, connection_proxy): # pylint: disable=unused-variable
pid = os.getpid()
if connection_record.info['pid'] != pid:
connection_record.connection = connection_proxy.connection = None
raise exc.DisconnectionError(
"Connection record belongs to pid {}, "
"attempting to check out in pid {}".format(connection_record.info['pid'], pid)
)
def ping_connection(dbapi_connection, connection_record, connection_proxy):
cursor = dbapi_connection.cursor()
try:
cursor.execute('SELECT 1')
except Exception:
# raise DisconnectionError - pool will try
# connecting again up to three times before raising.
raise exc.DisconnectionError()
cursor.close()
def checkout(dbapi_connection, connection_record, connection_proxy):
"""On checkout event."""
pid = os.getpid()
if connection_record.info['pid'] != pid:
connection_record.connection = connection_proxy.connection = None
raise exc.DisconnectionError(
'Connection record belongs to pid %s, '
'attempting to check out in pid %s' %
(connection_record.info['pid'], pid))
def _on_checkout(dbapi_connection, connection_record, connection_proxy):
logger.debug('connection retrieved from pool')
if POOL_PESSIMISTIC_MODE:
cursor = dbapi_connection.cursor()
try:
cursor.execute('SELECT 1')
except:
raise exc.DisconnectionError()
finally:
cursor.close()
def checkout(dbapi_connection, connection_record, connection_proxi):
"""
Make sure we do not re-use connection with different process (this is for oracle)
"""
pid = os.getpid()
if connection_record.info['pid'] != pid:
connection_record.connection = connection_proxi.connection = None
raise exc.DisconnectionError(
"Connection belongs to pid: {} - attempting to check out in pid: {}"
.format(connection_record.info['pid'], pid))
def ping_connection(dbapi_connection, connection_record, connection_proxy):
# 在每次从连接池获取一个连接时，首先测试连接池是否畅通
# 如果不畅通，则断开重新建立连接，及时防丢
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except:
SysLogger.error('database pool has gone away')
raise exc.DisconnectionError()
cursor.close()
def checkout(self, dbapi_con, con_record, con_proxy):
"""Event triggered when a connection is checked out from the pool."""
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError as ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
raise exc.DisconnectionError()
else:
raise
# MariaDB seems to timeout the client in a different
# way than MySQL and PXC, which manifests itself as
# an invalid packet sequence. Handle it as well.
except pymysql_err.InternalError as ex:
if "Packet sequence number wrong" in ex.message:
raise exc.DisconnectionError()
else:
raise
def ping_connection(dbapi_connection, connection_record, connection_proxy):
"""
Keep alive check
"""
del connection_proxy, connection_record
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except:
raise exc.DisconnectionError()
cursor.close()
def ping_connection(dbapi_connection, connection_record, connection_proxy):
'''
Disconnect Handling - Pessimistic, taken from:
http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html
'''
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except:
raise exc.DisconnectionError()
cursor.close()
def ping_connection(dbapi_connection, connection_record, connection_proxy):
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except dbapi_connection.OperationalError, ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
# raise DisconnectionError - pool will try
# connecting again up to three times before raising.
raise exc.DisconnectionError()
else:
raise
def checkout(self, dbapi_con, con_record, con_proxy):
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError, ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
raise exc.DisconnectionError()
else:
raise
def ping_connection(dbapi_connection, connection_record,
connection_proxy):
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except: # noqa: E722
connection_proxy._pool.dispose()
# raise DisconnectionError - pool will try
# connecting again up to three times before raising.
raise exc.DisconnectionError()
cursor.close()
def checkout(dbapi_connection, connection_record, connection_proxy):
log.debug('checkout listener fired for %s' % connection_record.info)
pid = os.getpid()
if connection_record.info.get('pid', None) != pid:
log.info('checkout recycling connection in new pid %s, orig: %s' %
(pid, connection_record.info.get('pid', None)))
connection_record.connection = connection_proxy.connection = None
raise exc.DisconnectionError(
"Connection record belongs to pid %s, "
"attempting to check out in pid %s" %
(connection_record.info.get('pid', None), pid))
def ping_connection(dbapi_connection, connection_record, connection_proxy):
del connection_record
del connection_proxy
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except exc.OperationalError:
# raise DisconnectionError - pool will try
# connecting again up to three times before raising.
raise exc.DisconnectionError()
cursor.close()
def checkout(self, dbapi_con, con_record, con_proxy):
"""Event triggered when a connection is checked out from the pool."""
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError as ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
raise exc.DisconnectionError()
else:
raise
def ping_connection(dbapi_connection, connection_record, connection_proxy):
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except:
# optional - dispose the whole pool
# instead of invalidating one at a time
# connection_proxy._pool.dispose()
# raise DisconnectionError - pool will try
# connecting again up to three times before raising.
raise exc.DisconnectionError()
cursor.close()
def checkout(self, dbapi_con, con_record, con_proxy):
"""Event triggered when a connection is checked out from the pool."""
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError as ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
raise exc.DisconnectionError()
else:
raise
# MariaDB seems to timeout the client in a different
# way than MySQL and PXC
except pymysql_err.InternalError as ex:
if "Packet sequence number wrong" in str(ex):
raise exc.DisconnectionError()
elif 'Connection was killed' in str(ex):
raise exc.DisconnectionError()
else:
raise
def _on_checkout(dbapi_connection, connection_record, connection_proxy):
logger.debug("connection retrieved from pool")
if POOL_PESSIMISTIC_MODE:
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except:
# raise DisconnectionError - pool will try
# connecting again up to three times before raising.
raise exc.DisconnectionError()
finally:
cursor.close()
