HomeElementl

[dagster-mysql] `wait_timeout` fix for MySQL Engine Connections

Description

[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

Details

Provenance
Sidharth Menon <menon.sid.k@gmail.com>Authored on May 20 2021, 7:34 PM
Reviewer
alangenfeld
Differential Revision
D8020: [dagster-mysql] `wait_timeout` fix for MySQL Engine Connections
Parents
R1:6e77fd881a4d: Resolve circular import issue
Branches
Unknown
Tags
Unknown