Temporal server startup

my requirement : Need to run temoral with mysql running outside the docker-compose
what did I do :

  1. cloned the docker-compose repo and changed the docker-compose as follows
version: "3.5"
services:
  
  temporal:
    container_name: temporal
    extra_hosts:
      - "host.docker.internal:host-gateway"
    environment:
      - DB=mysql
      - DB_PORT=3306
      - MYSQL_USER=TemporalUser
      - MYSQL_PWD=mypsw
      - MYSQL_SEEDS=14.20.202.155
      - DYNAMIC_CONFIG_FILE_PATH=config/dynamicconfig/development-sql.yaml
      - ENABLE_ES=false
      - SKIP_DB_CREATE=true
      - SKIP_SCHEMA_SETUP=true
     
    image: temporalio/auto-setup:${TEMPORAL_VERSION}
    networks:
      - temporal-network
    ports:
      - 7233:7233
    volumes:
      - ./dynamicconfig:/etc/temporal/config/dynamicconfig
  temporal-ui:
    container_name: temporal-ui
    depends_on:
      - temporal
    environment:
      - TEMPORAL_ADDRESS=temporal:7233
      - TEMPORAL_CORS_ORIGINS=http://localhost:3000
    image: temporalio/ui:${TEMPORAL_UI_VERSION}
    networks:
      - temporal-network
    ports:
      - 8080:8080
  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

  1. few important modification I have done in the above docker-compose
    a) included extra host since my mysql is running on the same machine as a standalone server
    b) there is an error with schema mentioned in " /home/shiva/go/src/temporal-master/schema/mysql/v57/temporal/versioned/v1.0 "
    in schema.sql, in the last table which is " cluster_membership " the " session_start " column the default value is giving an error when ever that table is created in mysql 8.0.19,8.0.29,8.0.21
    changing the default value from " 1970-01-01 00:00:01 " to " 1970-01-02 00:00:01 " has fixed the issue
CREATE TABLE cluster_membership
(
    membership_partition INT NOT NULL,
    host_id              BINARY(16) NOT NULL,
    rpc_address          VARCHAR(15) NOT NULL,
    rpc_port             SMALLINT NOT NULL,
    role                 TINYINT NOT NULL,
    session_start        TIMESTAMP DEFAULT '1970-01-02 00:00:01',
    last_heartbeat       TIMESTAMP DEFAULT '1970-01-02 00:00:01',
    record_expiry        TIMESTAMP DEFAULT '1970-01-02 00:00:01',
    INDEX (role, host_id),
    INDEX (role, last_heartbeat),
    INDEX (rpc_address, role),
    INDEX (last_heartbeat),
    INDEX (record_expiry),
    PRIMARY KEY (membership_partition, host_id)
);

c) hence need to run temporal-sql-tool and skip the schema creation in docker-compose
d) so, I mentioned in the docker-compose to skip the DB and SCHEMA creation in the temporal container

  1. making the changes in the default value in " /home/shiva/go/src/temporal-master/schema/mysql/v57/temporal/versioned/v1.0 " and running the command " make install-schema-mysql " ----> worked fine —> few logs are as follows while executing the make command
executing updates for version 1.2
executing updates for version 1.3
executing updates for version 1.4...
executing updates for version 1.8
finally schema updated from 1.0 to 1.1
UpdateSchemaTask done

current error after doing the above task and running the docker-compose :

waiting for temporal server to start
unable to start the server : sql schema version mismatched

kindly let me know if any further clarity is required
and please help me come out of this mess

thanks in advance

changing the default value from " 1970-01-01 00:00:01 " to " 1970-01-02 00:00:01 " has fixed the issue

could you open an issue in Temporal server repo, this does look like a possible bug.

sql schema version mismatched

what is the ${TEMPORAL_VERSION} used in your docker config?
maybe this is version mismatch between server version used and the sql tool version you used to set up the db?

temporal version used in dock-compose : 1.16.2
and I had pulled the same github branch (1.16.2) for running the temporal-sql-tool

According to me whats going wrong is as follows :
I have run temporal-sql-tool by changing the table (cluster_membership) default value from

 " 1970-01-01 00:00:01 " to " 1970-01-02 00:00:01 "

and while running the docker-compose file
even though I have mentioned the variables (to skip DB creation and schema creation)
it might be validating the schema at some place
and there comes the schema mismatch
bcz it is going to use the default value of 1970-01-01 00:00:01 for validating the schema

possible solution might be changing the default value in the temporal github and building new docker image

correct me if im wrong

  1. We are running MySQL 8.0.19 and it does work well with ‘1970-01-01 00:00:01’ as default. I am curious why it doesn’t work for you. May be because of time zone? Can you try `‘1970-01-01 00:00:01+00:00’ as default value?
  2. make install-schema-mysql create database and schema on 127.0.0.1 by default. Unless you modified Makefile also, your database was created on the wrong server. I would suggest you to manually run temporal-sql-tool commands, specifying correct --endpoint and create database on the correct server.