How to automate the setup when use separated mysql (or any db)?

Community Support
1

I’m trying to use AWS RDS Aurora (MySQL)
It seems that I need to run temporal-sql-tool manually. GitHub - temporalio/helm-charts: Temporal Helm charts
I wonder how to automate this properly?
I will use a fixed temporal docker image version, so how to make sure I get the right version of schema and tool? I need to git clone the repo with a specific tag? Or is this tool and schema it uses in the docker image already?
Lastly, I’m providing admin credential to temporal docker image, why not have this automatically setup when temporal service start for the first time?

Thank you very much!

2

try this: Docker

3

How do I run this? I tried the following and none works
docker run -it --rm temporalio/admin-tools:1.7.0 bash
docker run -it --rm temporalio/admin-tools:1.7.0 sh
docker run -it --rm temporalio/admin-tools:1.7.0 /bin/bash

4

I figured it out, I have to run it in background then exec to it

docker run -d temporalio/admin-tools:1.7.0
docker exec -it 9b7e04a04ab4 bash

5

@Wenquan_Xing
No I did not figure out anything
I need to automate this
I tried
docker run --rm temporalio/admin-tools:1.7.0 /usr/local/bin/temporal-sql-tool
But the output is really strange
Something is wrong with this docker image

If I have to run it in the background then use exec to connect to it then there is no way to automate it

6

The entry point of the docker image is set to
“Entrypoint”: [“tail”,"-f","/dev/null"]
So it is not possible to run other commands
This docker image is meant to run forever
We need a different docker image in order to do just run temporal-sql-tool just once

Also my other question: why do we have to do this at all? Can’t this be part of the bootstrap?

7

I found this command works
docker run --rm --entrypoint=’’ temporalio/admin-tools:1.7.0 temporal-sql-tool

I should not have spammed the thread. Sorry

8 
docker run --entrypoint "/usr/local/bin/temporal-sql-tool" temporalio/admin-tools:1.7.0 -ep host.docker.internal -u temporal --pw temporal create --db temporal

docker run --entrypoint "/usr/local/bin/temporal-sql-tool" temporalio/admin-tools:1.7.0 -ep host.docker.internal -u temporal --pw temporal --db temporal setup-schema -v 0.0

docker run --entrypoint "/usr/local/bin/temporal-sql-tool" temporalio/admin-tools:1.7.0 -ep host.docker.internal -u temporal --pw temporal --db temporal update-schema -d ./schema/mysql/v57/temporal/versioned

docker run --entrypoint "/usr/local/bin/temporal-sql-tool" temporalio/admin-tools:1.7.0 -ep host.docker.internal -u temporal --pw temporal create --db temporal_visibility

docker run --entrypoint "/usr/local/bin/temporal-sql-tool" temporalio/admin-tools:1.7.0 -ep host.docker.internal -u temporal --pw temporal --db temporal_visibility setup-schema -v 0.0

docker run --entrypoint "/usr/local/bin/temporal-sql-tool" temporalio/admin-tools:1.7.0 -ep host.docker.internal -u temporal --pw temporal --db temporal_visibility update-schema -d ./schema/mysql/v57/visibility/versioned
9

I got this error
error creating database:Error 1193: Unknown system variable ‘transaction_isolation’

10

I need to add --ca tx_isolation=READ-COMMITTED

11

HI, I got the same issue when trying to create the temporal db from the temporal-admintools container.
The SQL DB is based on AWS Aurora MySQL instance

This is the command I’m running within the container:

bash-5.0# /usr/local/bin/temporal-sql-tool create-database -database temporal
2021/04/26 10:01:39 error creating database:Error 1193: Unknown system variable ‘transaction_isolation’

12

@tal You need to add --ca tx_isolation=READ-COMMITTED when running the commands. AWS Aurora MySQL using older MySQL version

13

I tried to use the --ca flag but /usr/local/bin/temporal-sql-tool failed while adding this extra flag.

14

Update from me: I’m using the service docker image to setup the schema. And this is the exact image and version I’m using to run the service so no mis-match problem at all. I don’t trust setup schema from temporalio/admin-tools:x.y.z will work for temporalio/server:x.y.z. So here is what I’m running and hope it helps:
Using temporalio/server:x.y.z

temporal-sql-tool --ca tx_isolation=READ-COMMITTED -ep $MYSQL_HOST -u $MYSQL_USER -pw $MYSQL_PASSWORD create --db temporal
temporal-sql-tool --ca tx_isolation=READ-COMMITTED -ep $MYSQL_HOST -u $MYSQL_USER -pw $MYSQL_PASSWORD --db temporal setup-schema -v 0.0 
temporal-sql-tool --ca tx_isolation=READ-COMMITTED -ep $MYSQL_HOST -u $MYSQL_USER -pw $MYSQL_PASSWORD --db temporal update-schema -d ./schema/mysql/v57/temporal/versioned
temporal-sql-tool --ca tx_isolation=READ-COMMITTED -ep $MYSQL_HOST -u $MYSQL_USER -pw $MYSQL_PASSWORD create --db temporal_visibility
temporal-sql-tool --ca tx_isolation=READ-COMMITTED -ep $MYSQL_HOST -u $MYSQL_USER -pw $MYSQL_PASSWORD --db temporal_visibility setup-schema -v 0.0
temporal-sql-tool --ca tx_isolation=READ-COMMITTED -ep $MYSQL_HOST -u $MYSQL_USER -pw $MYSQL_PASSWORD --db temporal_visibility update-schema -d ./schema/mysql/v57/visibility/versioned

And I’m not running this manually. I put this into a K8s job and run it once when deploy. So if I updated the image this happens automatically. This might be a bad idea.

15

@Gordon_Sun Thank you for your reply.