Docker compose files for temporal.io with external MySQL databases for temporal and temporal_visibility tables (using PlanetScale as example)

README.md

Docker compose files for temporal.io with external MySQL databases for temporal and temporal_visibility tables (using PlanetScale as example)

As the docker-compose files 👇 are using PlanetScale's MySQL-compatible Vitess database as an example, each database (temporal and temporal_internal) use different keyspaces and connection strings. Unfortunately, temporalio/auto-setup does not seem to support multiple connection strings for database creation and schema updates (using temporal-sql-tool), so the following commands would need to be run manually before starting up docker-compose:

./temporal-sql-tool --ep $TEMPORAL_PSCALE_HOSTSTRING --user $TEMPORAL_PSCALE_USER --tls  --password $TEMPORAL_PASSWORD-p 3306 --plugin mysql --db temporal setup-schema -v 0.0
./temporal-sql-tool --ep $TEMPORAL_PSCALE_HOSTSTRING --user $TEMPORAL_PSCALE_USER --tls  --password $TEMPORAL_PASSWORD-p 3306 --plugin mysql --db temporal update-schema -d ./schema/mysql/v57/temporal/versioned
./temporal-sql-tool --ep $TEMPORAL_VISBILITY_PSCALE_HOSTSTRING --user $TEMPORAL_VISBILITY_PSCALE_USER --tls  --password $TEMPORAL_VISBILITY_PASSWORD-p 3306 --plugin mysql --db temporal_visibility setup-schema -v 0.0
./temporal-sql-tool --ep $TEMPORAL_VISBILITY_PSCALE_HOSTSTRING --user $TEMPORAL_VISBILITY_PSCALE_USER --tls  --password $TEMPORAL_VISBILITY_PASSWORD-p 3306 --plugin mysql --db temporal_visibility update-schema -d ./schema/mysql/v57/visibility/versioned

After successful initialization, you can run temporal like this:

cd docker-compose/
docker-compose -f docker-compose-mysql.yml up
# or, if you like advanced visibility
# docker-compose -f docker-compose-mysql-es.yml up

docker-compose-mysql-es.yml

version: "3.5"
services:
  elasticsearch:
    container_name: temporal-elasticsearch
    environment:
      - cluster.routing.allocation.disk.threshold_enabled=true
      - cluster.routing.allocation.disk.watermark.low=512mb
      - cluster.routing.allocation.disk.watermark.high=256mb
      - cluster.routing.allocation.disk.watermark.flood_stage=128mb
      - discovery.type=single-node
      - ES_JAVA_OPTS=-Xms256m -Xmx256m
      - xpack.security.enabled=false
    image: elasticsearch:${ELASTICSEARCH_VERSION}
    networks:
      - temporal-network
    ports:
      - 9200:9200
  temporal:
    container_name: temporal
    depends_on:
      - elasticsearch
    environment:
      - DB=mysql
      - MYSQL_USER=${TEMPORAL_USER}
      - MYSQL_PWD=${TEMPORAL_PASSWORD}
      - MYSQL_SEEDS=${TEMPORAL_PSCALE_HOSTSTRING}
      - VISIBILITY_MYSQL_USER=${TEMPORAL_VISIBILITY_USER}
      - VISIBILITY_MYSQL_PWD=${TEMPORAL_VISIBILITY_PASSWORD}
      - VISIBILITY_MYSQL_SEEDS=${TEMPORAL_VISIBILITY_PSCALE_HOSTSTRING}
      - SQL_TLS=true
      - SKIP_DB_CREATE=true
      - SKIP_SCHEMA_SETUP=true
      - SQL_TLS_ENABLED=true
      - DYNAMIC_CONFIG_FILE_PATH=config/dynamicconfig/development-sql.yaml
      - ENABLE_ES=true
      - ES_SEEDS=elasticsearch
      - ES_VERSION=v7
    image: temporalio/auto-setup:${TEMPORAL_VERSION}
    networks:
      - temporal-network
    ports:
      - 7233:7233
    volumes:
      - ./dynamicconfig:/etc/temporal/config/dynamicconfig
  temporal-admin-tools:
    container_name: temporal-admin-tools
    depends_on:
      - temporal
    environment:
      - TEMPORAL_CLI_ADDRESS=temporal:7233
    image: temporalio/admin-tools:${TEMPORAL_VERSION}
    networks:
      - temporal-network
    stdin_open: true
    tty: true
  temporal-web:
    container_name: temporal-web
    depends_on:
      - temporal
    environment:
      - TEMPORAL_GRPC_ENDPOINT=temporal:7233
    image: temporalio/web:${TEMPORAL_WEB_VERSION}
    networks:
      - temporal-network
    ports:
      - 8088:8088
networks:
  temporal-network:
    driver: bridge
    name: temporal-network

docker-compose-mysql.yml

version: "3.5"
services:
  temporal:
    container_name: temporal
    environment:
      - DB=mysql
      - DB_PORT=3306
      - VISIBILITY_MYSQL_USER=${TEMPORAL_VISIBILITY_USER}
      - VISIBILITY_MYSQL_PWD=${TEMPORAL_VISIBILITY_PASSWORD}
      - VISIBILITY_MYSQL_SEEDS=${TEMPORAL_VISIBILITY_PSCALE_HOSTSTRING}
      - MYSQL_USER=${TEMPORAL_USER}
      - MYSQL_PWD=${TEMPORAL_PASSWORD}
      - MYSQL_SEEDS=${TEMPORAL_PSCALE_HOSTSTRING}
      - SQL_TLS=true
      - SKIP_DB_CREATE=true
      - SKIP_SCHEMA_SETUP=true
      - SQL_TLS_ENABLED=true
      - DYNAMIC_CONFIG_FILE_PATH=config/dynamicconfig/development-sql.yaml
    image: temporalio/auto-setup:${TEMPORAL_VERSION}
    networks:
      - temporal-network
    ports:
      - 7233:7233
    volumes:
      - ./dynamicconfig:/etc/temporal/config/dynamicconfig
  temporal-admin-tools:
    container_name: temporal-admin-tools
    depends_on:
      - temporal
    environment:
      - TEMPORAL_CLI_ADDRESS=temporal:7233
    image: temporalio/admin-tools:${TEMPORAL_VERSION}
    networks:
      - temporal-network
    stdin_open: true
    tty: true
  temporal-web:
    container_name: temporal-web
    depends_on:
      - temporal
    environment:
      - TEMPORAL_GRPC_ENDPOINT=temporal:7233
      - TEMPORAL_PERMIT_WRITE_API=true
    image: temporalio/web:${TEMPORAL_WEB_VERSION}
    networks:
      - temporal-network
    ports:
      - 8088:8088
networks:
  temporal-network:
    driver: bridge
    name: temporal-network

Hi @jonico
I'm curious if you've successfully tried running a cluster that is connected to planetscale locally. I found this gist while searching for pointers. I've set up the temporal and temporal_visibility dbs with the temporal-sql-tool. But when I try to run docker-compose command, I get constant context-deadline-exceeded errors being logged. Any help is musch appreciated. Thanks!

@Mayowa-Ojo: I tested it successfully by the steps ☝️

• created the PlanetScale DBs for temporal and temporal_visibility and
• populated the necessary environment variables from the PlanetScale connection dialog
  (TEMPORAL_VISIBILITY_USER, TEMPORAL_VISIBILITY_PASSWORD, TEMPORAL_VISIBILITY_PSCALE_HOSTSTRING, TEMPORAL_USER, TEMPORAL_PASSWORD, TEMPORAL_PSCALE_HOSTSTRING)
• ran the sql schema tool as outlined in this gist
• cloned https://github.com/planetscale/docker-compose into directory where I cloned https://github.com/temporalio/temporal
• ran cd docker-compose/ && docker-compose -f docker-compose-mysql.yml up

I have seen context deadline exceptions quite frequently when another instance of docker-compose / temporal was using the same databases in parallel (as temporal does a lot of locking SQL operations), but once I made sure my temporal is the only one operating on the databases, the errors went away.

@jonico
Thanks for your response. It seems I just can't get it to work. I tried the steps again as you mentioned but the constant errors are still getting logged and I'm certain nothing else is connected to the databases except the current temporal sever. I already tried running a cluster with a mysql and postgres database and everything worked fine. Which makes this case weird.

It's worth noting that I created the two dbs (temporal and temporal_visibility) in two separate organizations as the free tier only allows one database per organization but I don't think that should be an issue.

Can you share your docker compose file and log output in a different gist? @Mayowa-Ojo

Also, can you look into Insights which exact queries are timing out?

I shared my docker-compose and log output in this gist

Also, I took a look at the insights for temporal db and saw a query pattern that just says error: unknown database 'temporal'

@Mayowa-Ojo: I will continue commenting in your gist