[dagster-mysql] wait_timeout fix for MySQL Engine Connections
Summary: By default, SQLAlchemy keeps a db.Engine's pooled connections open forever (pool_recycle=-1). This is a problem in MySQL because of a config value called wait_timeout, which sets the MySQL server to kill idle connections after 8 hours. Therefore, it was possible for code to check out a connection from SQLAlchemy (via self.connect() on one of the storages) where the underlying DB connection had been removed. To rectify this, I am setting the pooled connections to recycle after 1 hour.
Test Plan:
bk and Integration
Local testing involved setting the wait_timeout variable on the MySQL DB to 2 seconds & then sleeping for 3 or more seconds. Before this fix in Dagit we'd encounter an OperationalError as the MySQL connection was dead; however, after this fix (setting MYSQL_POOL_RECYCLE to instead be 1 second), this issue was not present in a long-running (15 minute+) session with frequent reloading.
The issue repro code looked something like this:
with self.connect() as conn: conn.execute('SET SESSION wait_timeout = 2;') time.sleep(3)
Reviewers: prha, catherinewu, alangenfeld
Reviewed By: alangenfeld
Subscribers: alangenfeld, owen
Differential Revision: https://dagster.phacility.com/D8020