配置信息

• python3 3.6
• Flask-SQLAlchemy 2.3.2
• win10

報錯:

-----> [2018-07-16 17:22:42,041] [ERROR] [base.py<131>-base.run_job]: Job "auto_rollback.<locals>.wrapper (trigger: interval[0:30:00], next run at: 2018-07-16 17:52:42 CST)" raised an exception
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 277, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/apscheduler/executors/base.py", line 125, in run_job
    retval = job.func(*job.args, **job.kwargs)
  File "/home/zza/eth_crawler/crawler_script/utils.py", line 28, in wrapper
    raise err
  File "/home/zza/eth_crawler/crawler_script/utils.py", line 24, in wrapper
    return func(*args, **kwargs)
  File "/home/zza/eth_crawler/crawler_script/token_tracker.py", line 240, in update
    db_address = db.session.query(Token.contract_address).filter(None == Token.total_supply).all()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2773, in all
    return list(self)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2925, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2948, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 277, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: 'SELECT token.contract_address AS token_contract_address \nFROM token \nWHERE token.total_supply IS NULL'] (Background on this error at: http://sqlalche.me/e/e3q8)

已使用解決方案

• 出錯后需要rollback奶栖，為了后續(xù)程序能運行茄袖，給每個涉及sql語句的函數(shù)用了裝飾器愿汰。

def auto_rollback(func):
    def wrapper(*args, **kwargs):
        try:
            return func(*args, **kwargs)
        except Exception as err:
            db.session.rollback()
            log.error(err)
            raise err

    return wrapper

治標不治本系列

• 把SQLALCHEMY_POOL_RECYCLE設(shè)成一個較小的數(shù)

app.config['SQLALCHEMY_POOL_SIZE'] = 128  # 線程池大小
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 90  # 超時時間
app.config['SQLALCHEMY_POOL_RECYCLE'] = 3  # 空閑連接自動回收時間
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 128  # 控制在連接池達到最大值后可以創(chuàng)建的連接數(shù)坑鱼。

• 根據(jù)錯誤日志 在需要用數(shù)據(jù)庫的地方先斷開連接

db.session.remove()

失敗

• 將單獨的sql語句改成nopool連接方式

class nullpool_SQLAlchemy(SQLAlchemy):
    def apply_driver_hacks(self, app, info, options):
        super(nullpool_SQLAlchemy, self).apply_driver_hacks(app, info, options)
        from sqlalchemy.pool import NullPool
        options['poolclass'] = NullPool
        del options['pool_size']

解決后又會出現(xiàn)

sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back

失敗

• 每次訪問數(shù)據(jù)庫重新生成sqlalchemy連接 ?

    from xxx import SQLAlchemy
    from xxx import app
    db = SQLAlchemy(app)

最粗暴但是最有效的解決方式枷遂，這個問題困擾了將近3周湿蛔，emmm

image.png

解決參考

flask-alchemy mysql gone away問題 連接重連 程序方向
MySQL server has gone away 問題的解決方法 mysql反向
nullpool_SQLAlchemy代碼
數(shù)據(jù)庫方向解決問題