openstack-服务连接mysql报错

openstack集群nova/neutron等组件，经常报mysql lost connection 错误，非常烦人，具体报错如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
(Background on this error at: http://sqlalche.me/e/e3q8)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines Traceback (most recent call last):
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib/python2.7/site-packages/oslo_db/sqlalchemy/engines.py", line 73, in _connect_ping_listener
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     connection.scalar(select([1]))
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 920, in scalar
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     return self.execute(object_, *multiparams, **params).scalar()
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     return meth(self, multiparams, params)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     return connection._execute_clauseelement(self, multiparams, params)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     distilled_params,
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     e, statement, parameters, cursor, context
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1464, in _handle_dbapi_exception
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     util.raise_from_cause(newraise, exc_info)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     reraise(type(exception), exception, tb=exc_tb, cause=cause)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     cursor, statement, parameters, context
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     cursor.execute(statement, parameters)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     result = self._query(query)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     conn.query(q)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib/python2.7/site-packages/pymysql/connections.py", line 517, in query
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib/python2.7/site-packages/pymysql/connections.py", line 732, in _read_query_result
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     result.read()
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib/python2.7/site-packages/pymysql/connections.py", line 1075, in read
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     first_packet = self.connection._read_packet()
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib/python2.7/site-packages/pymysql/connections.py", line 657, in _read_packet
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     packet_header = self._read_bytes(4)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines   File "/var/lib/kolla/venv/lib/python2.7/site-packages/pymysql/connections.py", line 707, in _read_bytes
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines     CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines DBConnectionError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines [SQL: SELECT 1]
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines (Background on this error at: http://sqlalche.me/e/e3q8)
2021-09-08 18:28:05.866 132 ERROR oslo_db.sqlalchemy.engines
2021-09-08 18:28:06.035 132 ERROR oslo_db.sqlalchemy.engines [req-a50d7dba-7060-4983-932f-9a6fd1105b3a - - - - -] Database connection was found disconnected; reconnecting: DBConnectionError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')

这是什么原因呢？ 分析如下：

​ 从报错信息能看到是查询时和数据库mysql丢了连接？ 为什么？

因为openstack组件连接数据库使用oslo_db，封装的基于sqlalchemy的连接池，所以怀疑是连接池里的连接没有回收，导致使用了过期的连接。

上代码：

#vi /var/lib/kolla/venv/lib/python2.7/site-packages/oslo_db/options.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
cfg.IntOpt(
        'connection_recycle_time',
        default=3600,
        deprecated_opts=[
            cfg.DeprecatedOpt('idle_timeout',
                              group="DATABASE"),
            cfg.DeprecatedOpt('idle_timeout',
                              group="database"),
            cfg.DeprecatedOpt('sql_idle_timeout',
                              group='DEFAULT'),
            cfg.DeprecatedOpt('sql_idle_timeout',
                              group='DATABASE'),
            cfg.DeprecatedOpt('idle_timeout',
                              group='sql')
        ],
        help='Connections which have been present in the connection '
             'pool longer than this number of seconds will be replaced '
             'with a new one the next time they are checked out from '
             'the pool.'),

默认回收时间3600s， 但是我的mysql wait_time 为 1800s，所以只需mysql wait_timeout 大于回收时间就行拉

done~