Use SET TRANSACTION which is better supported by various MySQL version

[samarabbas]

Using SQL persistence adapter with default parameters fails with following error on Aurora DB:

{"level":"fatal","ts":"2019-08-29T21:23:18.739Z","msg":"failed to create metadata manager","service":"cadence-frontend","error":"Error 1193: Unknown system variable 'transaction_isolation'","logging-call-at":"service.go:161","stacktrace":"github.com/uber/cadence/common/log/loggerimpl.(*loggerImpl).Fatal\n\t/cadence/common/log/loggerimpl/logger.go:139\ngithub.com/uber/cadence/service/frontend.(*Service).Start\n\t/cadence/service/frontend/service.go:161\nmain.execute\n\t/cadence/cmd/server/server.go:214"}

the problem are are changes made in MySQL 5.7 and 8 - ‘tx_isolation’ has been depreciated in 5.7.20 and an alias has been added called ’transaction_isolation. In MySQL 8 tx_isolation has been removed entirely leaving the former alias.

The means that cells will not run with MySQL 8 currently. Code changes are needed to allow both, pre 5.7.20 (tx_isolation) and 5.7.20 and later (transaction_isolation). It should however not be a big task I guess, as this variable is rarely used. An option would be by to use the “set transaction” statement instead of setting the isolation level as connection parameter. “set transaction” is supported in all versions and will not cause compatibility issues - it will, however, add a slight overhead due to the additional statement needed.

Looks like execution SET TRANSACTION statement after connection is established is better supported across various SQL versions.

[maikeffi]

i hit the same issue .. our Managed instance on GCP is version 5.7 ..

"msg":"failed to create shard manager","service":"cadence-history","error":"Error 1193: Unknown system variable 'transaction_isolation'","logging-call-at":"service.go:355"

[maikeffi]

Setting this param in config.yaml . Solved this issue for me !

sql: connectAttributes: tx_isolation: READ-COMMITTED

[jredl-va]

For people who ended up here while bootstrapping via the banzai cadence chart this is documented here : https://github.com/banzaicloud/banzai-charts/blob/master/cadence/values.yaml#L108

[longquanzheng]

CLI examples to pass this attribute:

cadence-sql-tool --connect-attributes tx_isolation=READ-COMMITTED

docker run --rm ubercadence/cli:master --address <> adm db unsupported-workflow --conn_attrs tx_isolation=READ-COMMITTED  --db_type mysql    --db_address

https://stackoverflow.com/questions/67732005/unable-to-run-unsupported-workflow-error-1193-unknown-system-variable-transac/67732666#67732666