Using option `--default-time-zone` at first run makes `mysql_install_db` script fail

[yannoff]

When using option --default-time-zone in docker command, the MySQL initialization script fails, because the timezone tables haven't been populated yet when mysql_install_db is invoked.

Here is an example, using the following docker-compose configuration:

#docker-compose.yml
version: '3'
services:
    mysql:
        image: mysql:5.5
        environment:
            MYSQL_DATABASE: acme
            MYSQL_ROOT_PASSWORD: xxxyyy
        command:
             - mysqld
             - "--character-set-server=utf8mb4"
             - "--skip-character-set-client-handshake"
             - "--collation-server=utf8mb4_unicode_ci"
             - "--default-time-zone=Europe/Paris"

And here is the resulting log:

Attaching to webapp_mysql_1
mysql_1        | Initializing database
mysql_1        | 190219 11:01:18 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
mysql_1        | 190219 11:01:18 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.62) starting as process 68 ...
mysql_1        | 190219 11:01:18 [ERROR] Fatal error: Illegal or unknown default time zone 'Europe/Paris'
mysql_1        | 
mysql_1        | Installation of system tables failed!  Examine the logs in
mysql_1        | /var/lib/mysql/ for more information.
mysql_1        | 
mysql_1        | You can try to start the mysqld daemon with:
mysql_1        | 
mysql_1        |     shell> /usr/local/mysql/bin/mysqld --skip-grant &
mysql_1        | 
mysql_1        | and use the command line tool /usr/local/mysql/bin/mysql
mysql_1        | to connect to the mysql database and look at the grant tables:
mysql_1        | 
mysql_1        |     shell> /usr/local/mysql/bin/mysql -u root mysql
mysql_1        |     mysql> show tables
mysql_1        | 
mysql_1        | Try 'mysqld --help' if you have problems with paths.  Using --log
mysql_1        | gives you a log in /var/lib/mysql/ that may be helpful.
mysql_1        | 
mysql_1        | Please consult the MySQL manual section
mysql_1        | 'Problems running mysql_install_db', and the manual section that
mysql_1        | describes problems on your OS.  Another information source are the
mysql_1        | MySQL email archives available at http://lists.mysql.com/.
mysql_1        | 
mysql_1        | Please check all of the above before submitting a bug report
mysql_1        | at http://bugs.mysql.com/
mysql_1        | 

I think this might have been introduced by #358

[wglambert]

As a work around you could use a string indicating an offset from UTC, such as '+10:00' or '-6:00'.

Changing the variable to --default-time-zone=+01:00:

$ env TZ=Europe/Paris date
Tue Feb 26 19:07:08 CET 2019

$ docker exec -it mysql-543_mysql_1_d66a05665422 mysql -pxxxyyy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.62 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT LOCALTIME() FROM DUAL;
+---------------------+
| LOCALTIME()         |
+---------------------+
| 2019-02-26 19:07:35 |
+---------------------+
1 row in set (0.00 sec)

Reference docs https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

[Andor]

@wglambert this workaround will fail every time when the country is switching between winter and summer time.
For example, we have Europe/Helsinki and right now (winter) it is +02:00, but after March it will be +03:00.

[yannoff]

@wglambert Thanks for the reply 😉

But I have to agree with @Andor, this workaround is not reliable because of the winter/summer time we use in some european countries (which is the case in France for example)

[Andor]

@yannoff I just created my own workaround/hack:
part of docker-compose.yaml:

    volumes:
      - mysql-socket:/tmp/run/mysql
      - mysql-data:/var/lib/mysql
      - ./docker/mysql/schema.sql:/docker-entrypoint-initdb.d/schema.sql:ro
      - ./docker/mysql/add-default-timezone.sh:/docker-entrypoint-initdb.d/add-default-timezone.sh:ro
    command:
      - bash
      - -e
      - -c
      - >
        chmod a+rwx /tmp/run/mysql;
        . /entrypoint.sh
      - mysqld
      - --character-set-server=utf8
      - --collation-server=utf8_general_ci
      - --character-set-filesystem=utf8
      - --init-connect='SET NAMES utf8'
      - --init-connect='SET collation_connection = utf8_general_ci'
      - --skip-character-set-client-handshake
      - --skip-name-resolve
      - --bind-address=*
      - --socket=/tmp/run/mysql/socket
      # this is because of bug in docker image related to default-time-zone
      # issue: https://github.com/docker-library/mysql/issues/543
      # you have to leave it commented for mysqld init time
      # - --default-time-zone=Europe/Helsinki

docker/mysql/add-default-timezone.sh:

set -- $@ --default-time-zone=Europe/Helsinki

Not tested yet, but should works.
Scripts /docker-entrypoint-initdb.d/*.sh should be run after mysqld init.

[Andor]

Ok, it doesn't work so I put another hack:

      - bash
      - -e
      - -c
      - >
        chmod a+rwx /tmp/run/mysql;
        touch /etc/mysql/conf.d/timezone.cnf;
        chown mysql /etc/mysql/conf.d/timezone.cnf;
        . /entrypoint.sh
      - mysqld
      - --character-set-server=utf8
      - --collation-server=utf8_general_ci
      - --character-set-filesystem=utf8
      - --init-connect='SET NAMES utf8'
      - --init-connect='SET collation_connection = utf8_general_ci'
      - --skip-character-set-client-handshake
      - --skip-name-resolve
      - --bind-address=*
      - --socket=/tmp/run/mysql/socket

and docker/mysql/add-default-timezone.sh:

#!/bin/bash -e

echo '[mysqld]
default-time-zone=Europe/Helsinki
' > /etc/mysql/conf.d/timezone.cnf

And it finally works:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| Europe/Helsinki    | Europe/Helsinki     |
+--------------------+---------------------+
1 row in set (0.00 sec)

[yannoff]

@Andor Thanks for your contribution, I appreciate 😺

Actually I have already found a much simpler workaround myself, which proved to work very well, by setting the TZ environment variable, as described in the MySQL official documentation.

Here is the docker-compose.yml :

#docker-compose.yml
version: '3'
services:
    mysql:
        image: mysql:5.5
        environment:
            MYSQL_DATABASE: acme
            MYSQL_ROOT_PASSWORD: xxxyyy
            # Set timezone as a global env var so that MySQL server starts with the right tz
            TZ: Europe/Paris
        command:
             - mysqld
             - "--character-set-server=utf8mb4"
             - "--skip-character-set-client-handshake"
             - "--collation-server=utf8mb4_unicode_ci"

The problem of your solution (as well as mine) is that they are, as you said, hacks.

Plus, I think the entrypoint.sh SHOULD be able to run without crashing, whatever option is supplied.

[michaelfig]

I have a patch to entrypoint.sh to make it work: here's my Dockerfile to enable the feature:

FROM mysql:5.7.13
RUN sed -i -e 's/\(\$@" -\)/\1-default-time-zone=SYSTEM -/' /usr/local/bin/docker-entrypoint.sh

All it does is overrides the default time zone when initialising the database, by changing the instances of:

"$@" --verbose --help 2>/dev/null | awk '$1 == "datadir" { print $2; exit }'
                "$@" --initialize-insecure
                "$@" --skip-networking &

to:

"$@" --default-time-zone=SYSTEM --verbose --help 2>/dev/null | awk '$1 == "datadir" { print $2; exit }'
                "$@" --default-time-zone=SYSTEM --initialize-insecure
                "$@" --default-time-zone=SYSTEM --skip-networking &

but leaves the actual final exec untouched.

This works for me, while allowing the user to specify default-time-zone = XXX anywhere in the chain of my.cnf files or on the command line.

Hope this helps,
Michael.

[tianon]

Closing given the rationale in #544 (comment), namely:

... https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html explicitly documents the TZ environment variable as a sane and reasonable way to set the timezone for mysqld ...

The maintenance burden (and fragility aspect) of adding additional behavior to workaround to what amounts to a quirk of mysql_install_db is too much especially when there is a perfectly acceptable and "hack-free" solution provided by MySQL itself (namely the above-documented TZ variable, which is officially documented at least as far back as MySQL 5.5: https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html).

[SambathChea]

https://stackoverflow.com/a/60843288/5563353

[glensc]

I've come up with a solution that overrides entrypoint with code that that extracts --default-time-zone from config, exports TZ with the value, and boots the original entrypoint with --default-time-zone=SYSTEM.

I think this is a more fragile solution than just fix the entry point to use --default-time-zone=SYSTEM when initializing the database. the database init already passes some other options to overcame the cat-mouse game, so why not add a timezone option as well?:

                 echo 'Initializing database'
-                "$@" --initialize-insecure --skip-ssl
+                "$@" --initialize-insecure --skip-ssl --default-time-zone=SYSTEM
                 echo 'Database initialized'

Given that TZ env variable support exists, it's a different config file to initialize, for example, if you version control your mysqld.conf files, now to maintain environment variables, you need to track different a file. and depending on deploy mechanism that varies a lot (docker-compose, kubernetes, shell).

EDIT: I'll try to make PR later, given the arguments above and the simplicity of the fix. Seeing how wild are the hacks around the problem makes me cry.

[yannoff]

@glensc That's an interesting - and much welcome - new approach to address the problem... Sounds great 🎉 👍