TRUNCATE TABLE time_zone_transition_type To give some insights, the following SQL script is generated when running the mysql_tzinfo_to_sql command with just the “Japan” time zone in the folder: mysql_tzinfo_to_sql /usr/share/zoneinfo This can be of concern when running the command on a different host OS due to reasons like disaster recovery, database migration, and/or after kernel upgrades. Because the first few SQL commands clear the tables, any time zones not in the target folder are lost. The mysql_tzinfo_to_sql command generates a SQL script that first truncates the time zone tables, and then inserts the new time zone information into them. “Asia/Singapore”), as long as they exist in the time_zone_name table.īonus Question: Does the mysql_tzinfo_to_sql command retain changes/differences to the time zone table between runs? If/Once these tables are populated, your database will support time zones, and you can reference them by their names (e.g. You might see some warnings for unsupported files, and that’s okay.

Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Simply populate these tables by running the following command on the database host OS: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -D mysql -u root -p mysql If these tables are empty, that means your MySQL database does not support it yet. time_zone_transition, time_zone_transition_type: Time zone descriptions.time_zone_name: Mappings between time zone IDs and names.time_zone_leap_second: When leap seconds occur.time_zone: Time zone IDs and whether they use leap seconds.Time zone information is stored in several tables within the mysql system schema: The Docker container takes some time to initialize, so if the connection command does not work, wait a while before trying again.Not specifying it, or using localhost, will default to using the local mysqld.sock unix socket, which will fail as there is no locally running MySQL daemon. 127.0.0.1 is used instead of localhost so as to make the connection over TCP with the default 3306 port.To connect to the MySQL database from within the container after SSH: mysql -h127.0.0.1 -uroot -p $ docker exec -it mysql-container /bin/sh $ docker run -dt -name mysql-container -hostname mysql-container -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 mysql:5.7 MySQL CLI Client: Ver 14.14 Distrib 5.7.29ĭocker commands for demo MySQL container: // Starting the container:.Docker Client & Server Version: 19.03.6.I am using the mysql:5.7 Docker image with the MySQL CLI Client: It just so happens that the time offset of UTC from GMT is exactly zero This post by Devney Hamilton explains it well.We can think of time zones as human friendly references to time offsets from GMT. Here is my scratchpad: Background Knowledge A while ago, I dived deep into t ime zones on MySQL.

Tab sql scratchpad