MySQL Bugs: #70675: MEM suggests loading timezone data: make the MySQL installer do this...

Bug #70675	MEM suggests loading timezone data: make the MySQL installer do this...
Submitted:	21 Oct 2013 15:20
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S4 (Feature request)
Version:	MEM 3.0.2.2901 / MySQL 5.6+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MEM3 suggests:

Category: Administration
Event: Time Zone Data Not Loaded
Problem Description

The MySQL server supports multiple time zones and provides various date and time functions, including a function that converts a datetime value from one time zone to another (CONVERT_TZ). However, while the MySQL installation procedure creates the time zone tables in the mysql database, it does not load them; you must do so manually after installation. If the time zone tables are not loaded, certain time zone functions such as CONVERT_TZ will not work.

Make our life easier and make the MySQL installer, or any packages built by Oracle, do this for us automatically. Then the process won't need to be done manually.

How to repeat:
see above.

Suggested fix:
Oracle packages MySQL packages, so make the installer do this for us on installation and upgrades.
That might mean that the DBA has to do this manually when newer timezone packages become available (if that's important) but it would mean that most of our work will be done, and also developers might consider using this facility more if it's already installed into the server.

For those people really not wanting to install this information then provide an option to stop this.
Maybe something to consider for 5.7 which is the DEV version now?

+1 to improve the workflow on autoloading timezone data. Let me add a story (demonstrating why this matters) and a few ways to mitigate.

## Story

I work on an open-source web application that has been using the `SET time_zone` and `TIMESTAMP` functionality for some number of years. We recently realized that the app was systemically and subtly mis-representing timestamps.

Specifically, instead of using well-formed time-zones (eg `SET time_zone='America/New_York'` or `SET time_zone='US/Eastern'`), it would pass the current user's offset (eg `SET time_zone='-04:00'`). This gives an approximation of correct new behavior -- `NOW()` and `CURRENT_TIMESTAMP` always appear correct. Recently logged activity also appear correct, and a lot of data (derived from `CURRENT_TIMESTAMP`) is stored correctly. But... if you live in a timezone that practices DST, then (loosely speaking) half your timestamps will appear incorrect (+/- 1 hour). (The exact errors flip back/forth whenever the end-user's timezone changes its offset - eg DST).)

Strictly speaking, that is an application bug (*passing a faulty `time_zone`*). But why does that bug exist? Apparently... because time-zone data is unreliable across MySQL deployments. So:

* Developer Alice reads MySQL docs and gets things to work with `TIMESTAMP`, `SET time_zone='Some/Name'`, etc.
* User Bob deploys the app. He's on a well-managed MySQL deployment, so it works fine.
* User Carol deploys the app. Her system lacks TZ data and raises, so she asks Developer Dave about how to fix it.
* Developer Dave sees that TZ names don't work, but TZs names just correspond to offsets, so he tries `SET time_zone='-04:00'`.
* The offset seems to work with all the data that's immediately available. Alice, Bob, Carol, and Dave may each feel a bit uncomfortable (*they're not experts in global time handling*), but this seems to fix a clear-and-present problem with no intuitive drawback.

The challenge is exacerbated in an open-source context because there are multiple developers and multiple runtime environments. Some environments provide tzdata by default; some omit tzdata but can be fixed; some omit tzdata and cannot be fixed. The net-result is a strong disincentive for open-source apps to use MySQL's timezone functionality -- because (in the big picture) the ecosystem has flaky support.

## Proposed Mitigation

Autoload tzdata during startup:

1. Define a configuration option `autoload_timezones=<BOOL>`.
2. When starting `mysqld`, check `autoload_timezones`. If enabled, use `TZDIR` or `/usr/share/zoneinfo` to populate the `mysql.time_zone*` tables.
3. Enable `autoload_timezones=1` by default on new deployments (ie new `my.cnf` files).

Note that:

- TZs would work by default.
- This wouldn't require any major change to MySQL runtime or data-storage.
- Whenever tzdata changes, the system would self-correct on the next start.
- Due to ^^^, it's more sustainable than tapping into the installer (eg `mysqld --initialize` or `mysql_install_db`).
- The docs already encourage restarting. ("[A]fter updating the time zone tables, restart mysqld to make sure that it does not continue to serve outdated time zone data." - https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-upgrades)
- "Restart" is a well-known quantity for sysadmins / distributors / package maintainers.
- You can opt-out (eg if it adds 500ms to mysqld start-up time and if that's a problem -- then downstream can opt-out.

(Of course, it could be even better for `mysqld` to read `zoneinfo` files directly... Maybe link to a C library that can read these files. Or maybe lazy-load TZs to an internal `MEMORY` data-store... Either of these would be more intrusive changes, but they would make the TZ data more reliable.)