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 pool_manager_cache:sqlalchemy/connection_recycle_time in designate defaults to 3600. If the mysql wait-timeout is significantly shorter than designate's connection_recycle_time then we frequently get messages like this in the designate logs:
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines [req-5c459b36-ab3d-48ec-8406-20b14149d1b0 - - - - -] 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 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines Traceback (most recent call last):
2018-12-05 23:35:42.765 145209 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 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines connection.scalar(select([1]))
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 877, in scalar
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines return self.execute(object, *multiparams, **params).scalar()
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 945, in execute
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines return meth(self, multiparams, params)
2018-12-05 23:35:42.765 145209 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 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines return connection._execute_clauseelement(self, multiparams, params)
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines compiled_sql, distilled_params
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines context)
2018-12-05 23:35:42.765 145209 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 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines util.raise_from_cause(newraise, exc_info)
2018-12-05 23:35:42.765 145209 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 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines reraise(type(exception), exception, tb=exc_tb, cause=cause)
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines context)
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 470, in do_execute
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines cursor.execute(statement, parameters)
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/cursors.py", line 165, in execute
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines result = self._query(query)
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/cursors.py", line 321, in _query
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines conn.query(q)
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 860, in query
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1061, in _read_query_result
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines result.read()
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1349, in read
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines first_packet = self.connection._read_packet()
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 991, in _read_packet
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines packet_header = self._read_bytes(4)
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1037, in _read_bytes
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines DBConnectionError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: u'SELECT 1']
2018-12-05 23:35:42.765 145209 ERROR oslo_db.sqlalchemy.engines
This is because designate 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 pool_manager_cache:sqlalchemy/connection_recycle_time value in designate.
Note that the configuration option was pool_manager_cache:sqlalchemy/idle_timeout prior to queens.
