server: fixed system_time_zone value in TiDB

[bb7133]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

tidb> show variables like 'system_time_zone';

2. What did you expect to see?
   From MySQL document:

The system time zone. When the server starts, it attempts to determine the time zone of the host machine automatically and uses it to set the system_time_zone system variable. The value does not change thereafter.

3. What did you see instead?
   For now TiDB use fixed CST value.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   This bug exists in almost all TiDB versions.
   TiDB v4.0.0 beta

[bb7133]

One applicable solution is that we always use system_tz recorded in mysql.tidb as system_time_zone. The semantics of this variable sound quite similar to system_time_zone:

tidb> select * from mysql.tidb where VARIABLE_NAME='system_tz';
+---------------+-----------------+------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE  | COMMENT                      |
+---------------+-----------------+------------------------------+
| system_tz     | America/Chicago | TiDB Global System Timezone. |
+---------------+-----------------+------------------------------+
1 row in set (0.00 sec)

The value of this variable is inferred for the initial bootstrapping process of TiDB, it will never change later. And this is the time_zone used by TiDB when time_zone system variable is set to "SYSTEM". Please see the design document for more details.

Please also notice that if we use this, TiDB still have different behavior in some scenarios, for example:

1. Assume that the local timezone is America/Chicago, we initialize/bootstrap MySQL Server and TiDB and we have the following timezone variables:

(TiDB)
tidb> show variables like '%time_zone%';
+------------------+-----------------+
| Variable_name    | Value           |
+------------------+-----------------+
| time_zone        | SYSTEM          |
| system_time_zone | America/Chicago |
+------------------+-----------------+
2 rows in set (0.02 sec)

(MySQL)
mysql> show variables like '%time_zone%';
+------------------+-----------------+
| Variable_name    | Value           |
+------------------+-----------------+
| time_zone        | SYSTEM          |
| system_time_zone | America/Chicago |
+------------------+-----------------+
2 rows in set (0.02 sec)

2. We set TZ environment variable to another timezone, for example export TZ='Asia/Shanghai' and restart MySQL and TiDB server, then query the timezone variables again:

(TiDB)
tidb> show variables like '%time_zone%';
+------------------+-----------------+
| Variable_name    | Value           |
+------------------+-----------------+
| time_zone        | SYSTEM          |
| system_time_zone | America/Chicago |
+------------------+-----------------+
2 rows in set (0.02 sec)

(MySQL)
mysql> show variables like '%time_zone%';
+------------------+-----------------+
| Variable_name    | Value           |
+------------------+-----------------+
| time_zone        | SYSTEM          |
| system_time_zone | America/Chicago |
+------------------+-----------------+
2 rows in set (0.02 sec)

As shown above, system_time_zone does not change. But the 'real' system timezone used by TiDB and MySQL is different:

(TiDB)
tidb> select from_unixtime(0);
+---------------------+
| from_unixtime(0)    |
+---------------------+
| 1969-12-31 18:00:00 |
+---------------------+
1 row in set (0.03 sec)

(MySQL)
mysql> select from_unixtime(0);
+---------------------+
| from_unixtime(0)    |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+
1 row in set (0.00 sec)

In the case above, TiDB will use system_time_zone(America/Chicago, GMT-6), while MySQL is actually using the timezone specified by TZ environment variable(Asia/Shanghai, GMT+8). The reason for this different behavior is that MySQL doesn't use system_time_zone when time_zone is set to "SYSTEM":

If set to SYSTEM, every MySQL function call that requires a time zone calculation makes a system library call to determine the current system time zone. This call may be protected by a global mutex, resulting in contention.

(https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html)

Sounds weird.