mirror of
https://github.com/hanzoai/tasks.git
synced 2026-10-04 02:04:03 +00:00
Tim Deeb-SwihartandGitHub
be7b5514a5
## What changed? Both our PostgreSQL and MySQL database backends will now automatically reconnect to the database when certain errors occur: all errors chosen have been experienced when testing this behavior through an AWS Aurora RDS failover of either MySQL or PostgreSQL. For both backends we will reconnect when we see: - `ECONNRESET` - `ECONNABORTED` - `ECONNREFUSED` - `io.EOF` - `io.ErrUnexpectedEOF` - `database/sql/driver.ErrBadConn` for postgres we will also reconnect on the following SQLStates: - `25006` read-only transaction - `57P03` cannot connect now - `0A000` feature not supported, but ONLY when the message is `cannot set transaction read-write mode during recovery` for mysql we will also reconnect when we see the following error codes: - `1040` too many connections - `1792` read-only transaction (SQLstate `25006`) - `1836` running in read-only mode This logic is easily extensible should we discover more failure modes over time ## Why? We've had multiple community reports of Temporal problems during RDS failover. One part of this is the fact that we wouldn't necessarily reconnect; we were at the whims of our chosen SQL abstraction's connection pooling logic. ## How did you test it? I manually tested this functionality in the presence of repeated RDS failovers: - [x] postgres12 plugin with pq driver - [x] postgres12 plugin with pgx driver - [x] mysql plugin Automated testing will be added to our regular testing pipelines once our infrastructure friends have added the support I need (it's in progress) ## Potential risks We're concerned there's a correctness issue in our PostgreSQL backend that's related to our behavior during an RDS failover. If we merge this before I figure out what's going on, we could hide the issue and make it harder to reproduce. ## Documentation N/A ## Is hotfix candidate? Yes?
Using the SQL schema tool
This package contains the tooling for temporal sql operations. The tooling itself is agnostic of the storage engine behind the sql interface. So, this same tool can be used against, say, OracleDB and MySQLDB
For localhost development
SQL_USER=$USERNAME SQL_PASSWORD=$PASSWD make install-schema-mysql
For production
Create the binary
- Run
make temporal-sql-toolon the root of repositroy - You should see an executable
temporal-sql-tool - Temporal officially support MySQL and Postgres for SQL.
- For other SQL database, you can add it easily as we do for MySQL/Postgres following our code in sql-extensions
Do one time database creation and schema setup for a new cluster
- All command below are taking MySQL as example. For postgres, simply use with "--plugin postgres12"
./temporal-sql-tool --ep $SQL_HOST -p $port --db temporal --plugin mysql8 create
./temporal-sql-tool --ep $SQL_HOST -p $port --db temporal_visibility --plugin mysql8 create
./temporal-sql-tool --ep $SQL_HOST -p $port --plugin mysql8 --db temporal setup-schema -v 0.0 -- this sets up just the schema version tables with initial version of 0.0
./temporal-sql-tool --ep $SQL_HOST -p $port --plugin mysql8 --db temporal update-schema -d ./schema/mysql/v8/temporal/versioned -- upgrades your schema to the latest version
./temporal-sql-tool --ep $SQL_HOST -p $port --plugin mysql8 --db temporal_visibility setup-schema -v 0.0 -- this sets up just the schema version tables with initial version of 0.0 for visibility
./temporal-sql-tool --ep $SQL_HOST -p $port --plugin mysql8 --db temporal_visibility update-schema -d ./schema/mysql/v8/visibility/versioned -- upgrades your schema to the latest version for visibility
Update schema as part of a release
You can only upgrade to a new version after the initial setup done above.
./temporal-sql-tool --ep $SQL_HOST -p $port --plugin mysql8 --db temporal update-schema -d ./schema/mysql/v8/temporal/versioned -v x.x -- executes the upgrade to version x.x
./temporal-sql-tool --ep $SQL_HOST -p $port --plugin mysql8 --db temporal_visibility update-schema -d ./schema/mysql/v8/visibility/versioned -v x.x -- executes the upgrade to version x.x