7.7 MySQL timezones

Troubleshooting
1

Upgrade to 7.7 states:

An error may occur on servers using MySQL if the server is set to SYSTEM timezone and doesn’t support named timezones. These errors can be fixed by populating the timezone tables on the server. Please see the docs for more details.

How do I detect wether the server is set to ‘SYSTEM’ timezone or not?

2

Hi TI-Admin,

you can find out by running the following query:

mysql> show variables like "time_zone";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

In this example, the server is running with the SYSTEM timezone (which is the default).
To check if your server does support named timezones, you will also have to run the following query:

mysql> select * from time_zone_name;
Empty set (0.00 sec)

If this query returns nothing (as in my example, which is also the default), then your installation is affected.
To fix that, you can use the mysql_tzinfo_to_sql script which comes bundled with the MySQL server. See the manpage of this command for further details, but usually, all you have to do to make this work is:

# mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -D mysql
Warning: Unable to load '/usr/share/zoneinfo//iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//tzdata.zi' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//zone1970.tab' as time zone. Skipping it.

This command will populate the available timezones on your system into the mysql server for internal named use.

3

Thanks. I updated the tables accordingly.

4

We have also provided a bug fix for this now in v7.7.1 dot release.