The mysql database charm has an option for setting wait-timeout for the number of seconds it will allow. The default is 8 hours but is often set to a much lower limit such as 180 seconds.
The database/connection_recycle_time in aodh (via oslo_config) is 3600. If the mysql wait-timeout is significantly shorter than aodh's connection_recycle_time then we frequently get messages like this in the aodh-listener.log file:
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines [-] Database connection was found disconnected; reconnecting: DBConnectionError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: u'SELECT 1']
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines Traceback (most recent call last):
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/engines.py", line 73, in _connect_ping_listener
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines connection.scalar(select([1]))
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 877, in scalar
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines return self.execute(object, *multiparams, **params).scalar()
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 945, in execute
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines return meth(self, multiparams, params)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines return connection._execute_clauseelement(self, multiparams, params)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines compiled_sql, distilled_params
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines context)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1398, in _handle_dbapi_exception
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines util.raise_from_cause(newraise, exc_info)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines reraise(type(exception), exception, tb=exc_tb, cause=cause)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines context)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 470, in do_execute
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines cursor.execute(statement, parameters)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/cursors.py", line 165, in execute
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines result = self._query(query)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/cursors.py", line 321, in _query
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines conn.query(q)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 860, in query
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1061, in _read_query_result
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines result.read()
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1349, in read
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines first_packet = self.connection._read_packet()
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 991, in _read_packet
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines packet_header = self._read_bytes(4)
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1037, in _read_bytes
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines DBConnectionError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: u'SELECT 1']
2018-12-05 22:00:03.179 56296 ERROR oslo_db.sqlalchemy.engines
This is because aodh will hold on to the connection for an hour but mysql will drop the connection after 180 seconds.
It would be nice to be able to fine-tune the database/connection_recycle_time value in aodh.
Note that the configuration option was database/idle_timeout prior to queens.
