def checkout(self, dbapi_con, con_record, con_proxy):
from sqlalchemy.exc import DisconnectionError
from _mysql_exceptions import OperationalError
try:
dbapi_con.ping()
except OperationalError:
raise DisconnectionError("Database server went away")
def _insert_url(session, url):
""" insert into table Page if not exist and return the url id
Args:
url(str): wiki url to update
Returns:
int: url id
Raise:
DisconnectionError
MultipleResultsFound
"""
try:
if session.query(Page.id).filter_by(url=url).scalar() is None:
page = Page(url=url)
session.add(page)
session.commit()
url_id = session.query(Page).filter_by(url=url).first().id
_insert_link(session, url_id, url_id, 0)
except DisconnectionError:
raise DisconnectionError("There is error with DB connection")
except MultipleResultsFound:
raise MultipleResultsFound("Many rows found in DB to find url \
id of {}".format(url))
url_id = session.query(Page.id).filter_by(url=url).first()
return url_id.id
def ping_connection(dbapi_connection, connection_record, connection_proxy):
"""Event listener to handle disconnects.
Implements
`pessimistic disconnect handling <http://docs.sqlalchemy.org/en/rel_0_9/core/\
pooling.html#disconnect-handling-pessimistic>`_.
.. note::
Our implementation is mildly dialect specific, but works for sqlite and
PostgreSQL. For oracle, the 'ping' query should read *SELECT 1 FROM DUAL* or
similar.
"""
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
if not isinstance(dbapi_connection,
sqlite3.Connection): # pragma: no cover
cursor.execute("SET default_text_search_config TO 'english'")
except: # noqa: E722; # pragma: no cover
# 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 DisconnectionError()
cursor.close()
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 DisconnectionError(
"Connection record belongs to pid %s, "
"attempting to check out in pid %s" %
(connection_record.info["pid"], pid))
def checkout(self, dbapi_con, con_record, con_proxy):
try:
dbapi_con.cursor().execute('select 1')
except dbapi_con.OperationalError, ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
LOG.warn('Got mysql server has gone away: %s', ex)
raise DisconnectionError("Database server went away")
else:
raise
def ping_connection(dbapi_connection, connection_record, connection_proxy): # pylint: disable=unused-argument
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
cursor.close()
except:
connection_proxy._pool.dispose() # pylint: disable=protected-access
# Raise DisconnectionError so the pool would create a new connection
raise DisconnectionError()
def checkout_listener(dbapi_con, con_record, con_proxy):
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except Exception, e:
import sys
print >> sys.stderr, "Error: %s (%s)" % (Exception, e)
raise DisconnectionError()
def connection_validator(dbapi_con, con_record, con_proxy):
exc = None
if isinstance(dbapi_con, MySQLConnection):
# True will silently reconnect if the connection was lost
dbapi_con.ping(True)
elif isinstance(dbapi_con, OracleConnection):
try:
dbapi_con.ping()
except DatabaseError, e:
exc = DisconnectionError(str(e))
def checkout_listener(dbapi_con, con_record, con_proxy):
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError as exc:
if exc.args[0] in (2006, 2013, 2014, 2045, 2055):
raise DisconnectionError()
else:
raise
def checkout(dbapi_connection, connection_record,
connection_proxy):
from sqlalchemy.exc import DisconnectionError
pid = os.getpid()
if connection_record.info['pid'] != pid:
connection_record.connection = connection_proxy.connection = None
raise DisconnectionError(
"Connection record belongs to pid %s, attempting to check out in pid %s"
% (connection_record.info['pid'], pid))
def ping_connection(dbapi_connection, connection_record, connection_proxy): # pylint: disable=unused-argument
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
cursor.close()
except:
logger.info(
'fail to ping database server, disposing all cached connections')
connection_proxy._pool.dispose() # pylint: disable=protected-access
# Raise DisconnectionError so the pool would create a new connection
raise DisconnectionError()
def db2_on_checkout(engine, dbapi_conn, connection_rec, connection_proxy):
"""Ensures that DB2 connections checked out of the pool are alive."""
cursor = dbapi_conn.cursor()
try:
cursor.execute('select 1 from (values (1)) AS t1')
except Exception as e:
is_disconnect = engine.dialect.is_disconnect(e, dbapi_conn, cursor)
if is_disconnect:
LOG.warn(_('DB2 server has gone away: %s'), e)
raise DisconnectionError("Database server went away")
else:
raise
def ping_connection(dbapi_connection, connection_record, connection_proxy):
'''Ping a database connection before using it to make sure it is still
alive.'''
cursor = dbapi_connection.cursor()
try:
cursor.execute('SELECT 1')
except:
# optional - dispose the whole pool instead of invalidating separately
# connection_proxy._pool.dispose()
# pool will try connecting again up to three times before raising.
raise DisconnectionError()
cursor.close()
def engine_checkout(dbapi_connection, connection_record, connection_proxy):
"""
Listener for engine check-outs.
* Checks that we have the same PID as we did at connection time.
(Based on the example in the "Connection Pooling" section of the
SQLAlchemy documentation.)
"""
if connection_record.info['pid'] != getpid():
connection_record.connection = connection_proxy.connection = None
raise DisconnectionError('Connection belongs to another PID')
def check_connection(dbapi_con, con_record, con_proxy):
cursor = dbapi_con.cursor()
try:
cursor.execute("SELECT 1")
except OperationalError as ex:
if ex.args[0] in (
2006, # MySQL server has gone away
2013, # Lost connection to MySQL server during query
2055): # Lost connection to MySQL server
# caught by pool, which will retry with a new connection
raise DisconnectionError()
else:
raise
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 dbapi_conn.OperationalError, ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
LOG.warn('Got mysql server has gone away: %s', ex)
raise DisconnectionError("Database server went away")
else:
raise
def checkout_listener(dbapi_con, con_record, con_proxy):
"""
Ensures that connections in the pool are still valid before returning them
:param dbapi_con:
:param con_record:
:param con_proxy:
:return:
"""
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError as exc:
if exc.args[0] in (2006, 2013, 2014, 2045, 2055):
raise DisconnectionError()
else:
raise
def on_checkout(dbapi_con, con_record, con_proxy):
"""Ensure MySQL connections checked out of the pool are alive.
Borrowed from:
http://groups.google.com/group/sqlalchemy/msg/a4ce563d802c929f
"""
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):
LOGGER.debug('got mysql server has gone away: %s', ex)
# caught by pool, which will retry with a new connection
raise DisconnectionError()
else:
raise
def mysql_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
:param dbapi_conn: DBAPI connection
:param connection_rec: connection record
:param connection_proxy: connection proxy
"""
try:
dbapi_conn.cursor().execute('select 1')
except dbapi_conn.OperationalError as ex:
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
msg = 'Got mysql server has gone away: %s' % ex
raise DisconnectionError(msg)
else:
raise
def _checkout_listener(dbapi_con, con_record, con_proxy):
"""Prevent MySQL timeouts.
Taken from:
https://stackoverflow.com/questions/18054224/python-sqlalchemy-mysql-server-has-gone-away
"""
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except AttributeError:
# db doesn't seem to support pings...
pass
except dbapi_con.OperationalError as exc:
if exc.args[0] in (2006, 2013, 2014, 2045, 2055):
raise DisconnectionError()
else:
raise
def checkout_listener(dbapi_con, con_record, con_proxy):
"""
监听数据库连接
:param dbapi_con:
:param con_record:
:param con_proxy:
:return:
"""
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError as exc:
if exc.args[0] in (2006, 2013, 2014, 2045, 2055):
raise DisconnectionError()
else:
logger.error(con_record)
logger.error(con_proxy)
raise
def _insert_link(session, from_page_id, to_page_id, no_of_separation):
""" insert link into database if link is not existed
Args:
from_page_id(int): id of "from" page
to_page_id(int): id of "to" page
no_of_separation(int)
Returns:
None
Raise
DisconnectionError
MultipleResultsFound
"""
try:
if session.query(Link).filter_by(
from_page_id=from_page_id,
to_page_id=to_page_id,
number_of_separation=no_of_separation).scalar() is None:
link = Link(from_page_id=from_page_id,
to_page_id=to_page_id,
number_of_separation=no_of_separation)
session.add(link)
session.commit()
except DisconnectionError:
raise DisconnectionError("There is error with DB connection")
except MultipleResultsFound:
raise MultipleResultsFound(
"Many rows found in DB to store link from {} to {}\
with number of seperation {}".format(from_page_id, to_page_id,
no_of_separation))
def pool_checkout(dbapi_connection, connection_record, connection_proxy):
"""
Listener for pool check-outs.
* Checks that the connection is alive.
(Based on the pessimistic example in the "Connection Pooling" section
of the SQLAlchemy documentation.)
"""
cursor = dbapi_connection.cursor()
try:
cursor.execute('SELECT 1')
except:
raise DisconnectionError('Connection not responding')
finally:
try:
cursor.close()
except:
pass
def mysql_on_checkout(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
Error codes caught:
* 2006 MySQL server has gone away
* 2013 Lost connection to MySQL server during query
* 2014 Commands out of sync; you can't run this command now
* 2045 Can't open shared memory; no answer from server (%lu)
* 2055 Lost connection to MySQL server at '%s', system error: %d
from http://dev.mysql.com/doc/refman/5.6/en/error-messages-client.html
"""
try:
dbapi_conn.cursor().execute('select 1')
except dbapi_conn.OperationalError as e:
if e.args[0] in (2006, 2013, 2014, 2045, 2055):
LOG.warn(_('MySQL server has gone away: %s'), e)
raise DisconnectionError("Database server went away")
else:
raise
def check_for_live_db_connection(self, dbapi_connection):
# Try to check that the current DB connection is usable for DB queries
# by issuing a trivial SQL query. It can happen because the user set
# the 'sqlalchemy.pool_recycle' time too high or simply because the
# MySQL server was restarted in the mean time.
# Without this check a user would get an internal server error and the
# connection would be reset by the DBSessionRemoverMiddleware at the
# end of that request.
# This functionality below will prevent the initial "internal server
# error".
#
# This approach is controversial between DB experts. A good blog post
# (with an even better discussion highlighting pros and cons) is
# http://www.mysqlperformanceblog.com/2010/05/05/checking-for-a-live-database-connection-considered-harmful/
#
# In MediaCore the check is only done once per request (skipped for
# static files) so it should be relatively light on the DB server.
# Also the check can be disabled using the setting
# 'sqlalchemy.check_connection_before_request = false'.
#
# possible optimization: check each connection only once per minute or so,
# store last check time in private attribute of connection object.
# code stolen from SQLAlchemy's 'Pessimistic Disconnect Handling' docs
cursor = dbapi_connection.cursor()
try:
cursor.execute('SELECT 1')
except:
msg = u'received broken db connection from pool, resetting db session. ' + \
u'If you see this error regularly and you use MySQL please check ' + \
u'your "sqlalchemy.pool_recycle" setting (usually it is too high).'
log.warning(msg)
# The pool will try to connect again up to three times before
# raising an exception itself.
raise DisconnectionError()
cursor.close()
def session(self) -> Optional[AsyncSession]:
if postgresql_context_var.get() is None:
raise DisconnectionError('session not init')
return postgresql_context_var.get()