限制连接超时异常

• pymysql.err.OperationalError

情况一：MySQL server has gone away

我设置wait_timeout和interactive_timeout，但仍没能重现这个错误

情况二：Lost connection to MySQL server during query

通过设置较短的wait_timeout和interactive_timeout值可以重现这个错误

Traceback (most recent call last):
...
File "/root/.local/share/virtualenvs/orange_new-H9_NlwNb/lib/python3.4/site-packages/pymysql/connections.py", line 1037, in _read_bytes
    CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

这种情况有两种解决思路

• 一：利用db.ping(reconnect=True)自动重连
• 二：设置db.ping(reconnect=True)手动重连尝试

class OpsDB(object):
    def __init__(self):
        logging.info('初始化数据库连接.')
        self.db = pymysql.connect(**mysql_settings)
        logging.info('初始化完成')

    def check_db_conn(self):
        try:
            logging.info('检查数据库连接.')
            self.db.ping(reconnect=False)
            logging.info('连接正常.')
            return True
        except OperationalError as e:
            logging.info('执行数据库连接异常，异常信息: {}'.format(str(e)))
            while True:
                try:
                    logging.info('正在尝试重连')
                    self.db = pymysql.connect(**mysql_settings)
                    logging.info('重连成功.')
                    return True
                except Exception as re_conn_err:
                    logging.info('尝试重连失败，异常信息: {}'.format(str(re_conn_err)))

    def execute(self, sql, get_one=None, get_all=None):
        self.check_db_conn()
        with self.db.cursor() as cursor:
            logging.info('正在执行SQL: {}'.format(sql))
            cursor.execute(sql)
            self.db.commit()
            logging.info('执行完成，正在返回数据')
            if get_one and not get_all:
                return cursor.fetchone()
            elif get_all and not get_one:
                return cursor.fetchall()
            else:
                logging.warning('参数错误: get_one, get_all只能存在一个')

设置mysql超时时间

mysql> set global interactive_timeout  = 30;
mysql> set global wait_timeout = 30;

等待30秒后请求tornado输出

INFO:root:检查数据库连接.
[I 180327 10:55:26 ops_db:15] 检查数据库连接.
INFO:root:执行数据库连接异常，异常信息: (2013, 'Lost connection to MySQL server during query')
[I 180327 10:55:26 ops_db:20] 执行数据库连接异常，异常信息: (2013, 'Lost connection to MySQL server during query')
INFO:root:正在尝试重连
[I 180327 10:55:26 ops_db:23] 正在尝试重连
INFO:root:重连成功.
[I 180327 10:55:26 ops_db:25] 重连成功.
INFO:root:正在执行SQL: SELECT * FROM server_remote_manage WHERE id=1 and server_id=9
[I 180327 10:55:26 ops_db:33] 正在执行SQL: SELECT * FROM server_remote_manage WHERE id=1 and server_id=9
INFO:root:执行完成，正在返回数据
[I 180327 10:55:26 ops_db:36] 执行完成，正在返回数据
INFO:root:(1, 9, '192.168.1.150', '22', 'root', 'd2VjYW4yMDE2\n', 'pwd')
[I 180327 10:55:26 handlers:28] (1, 9, '192.168.1.150', '22', 'root', 'd2VjYW4yMDE2\n', 'pwd')
INFO:tornado.access:200 GET /webshell?server_id=9&remote_manage_id=1 (127.0.0.1) 5.18ms
[I 180327 10:55:26 web:2064] 200 GET /webshell?server_id=9&remote_manage_id=1 (127.0.0.1) 5.18ms