Error 1298 hy000 unknown or incorrect time zone europe moscow

VM4.2 Ошибка SET LOCAL time_zone=’Europe/Moscow’ в кор.портале

Имеем установленную виртуальную машину BitrixVM4.2 60 Гб
Разворачиваю кор.портал из бекапа и получаю DB query error
Проверяю базы и пользователей, пароли — результат нулевой.

Включаю $DBDebug = true и вижу MySQL Query Error: SET LOCAL time_zone=’Europe/Moscow’ [Unknown or incorrect time zone: ‘Europe/Moscow’]

1. date_default_timezone_set(«Europe/Moscow» 😉 — в dbconn.php ни на что не влияет, проверил.
2. $DB->Query(«SET LOCAL time_zone=’Etc/GMT-4′» 😉 — не помогает, помогает запустить портал только полное удаление строки SET LOCAL time_zone в after_connect.php

Возможно я в чем-то не разобрался, объясните пожалуйста, коллеги!
Не исключаю, что ошибка воспроизводится/повторяется у некоторых ваших клиентов, тех кто использует VM Битрикс.

замените эту строку на

Лeчше загрузить информацию по временным зонам в mysql

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

я пробовал так сделать, получил

Warning: Unable to load ‘/usr/share/zoneinfo/iso3166.tab’ as time zone. Skipping it.
Warning: Unable to load ‘/usr/share/zoneinfo/zone.tab’ as time zone. Skipping it.

чтобы это значило?

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Где мне вводить эту команду в веб-окружении Битрикс?

Уважаемые разработчики, кто более опытен, поделитесь информацией куда вводить данный запрос mysql ? Тоже столкнулась с такой проблемой ..сайт развернула на виртуальной машине mvware установила тайм-зону через родную консоль , но mysql ругается на отсутствие тайм-зоны.

Источник

Error 1298 hy000 unknown or incorrect time zone europe moscow

MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

5.1.15В MySQL Server Time Zone Support

This section describes the time zone settings maintained by MySQL, how to load the system tables required for named time support, how to stay current with time zone changes, and how to enable leap-second support.

Beginning with MySQL 8.0.19, time zone offsets are also supported for inserted datetime values; see Section 11.2.2, “The DATE, DATETIME, and TIMESTAMP Types”, for more information.

Time Zone Variables

MySQL Server maintains several time zone settings:

The server system time zone. When the server starts, it attempts to determine the time zone of the host machine and uses it to set the system_time_zone system variable.

To explicitly specify the system time zone for MySQL Server at startup, set the TZ environment variable before you start mysqld . If you start the server using mysqld_safe , its —timezone option provides another way to set the system time zone. The permissible values for TZ and —timezone are system dependent. Consult your operating system documentation to see what values are acceptable.

The server current time zone. The global time_zone system variable indicates the time zone the server currently is operating in. The initial time_zone value is ‘SYSTEM’ , which indicates that the server time zone is the same as the system time zone.

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.

The initial global server time zone value can be specified explicitly at startup with the —default-time-zone option on the command line, or you can use the following line in an option file:

If you have the SYSTEM_VARIABLES_ADMIN privilege (or the deprecated SUPER privilege), you can set the global server time zone value at runtime with this statement:

Per-session time zones. Each client that connects has its own session time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

The session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME() , and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.

The session time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE , TIME , or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE , TIME , or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

The current global and session time zone values can be retrieved like this:

timezone values can be given in several formats, none of which are case-sensitive:

As the value ‘SYSTEM’ , indicating that the server time zone is the same as the system time zone.

As a string indicating an offset from UTC of the form [ H ] H : MM , prefixed with a + or — , such as ‘+10:00’ , ‘-6:00’ , or ‘+05:30’ . A leading zero can optionally be used for hours values less than 10; MySQL prepends a leading zero when storing and retrieving the value in such cases. MySQL converts ‘-00:00’ or ‘-0:00’ to ‘+00:00’ .

Prior to MySQL 8.0.19, this value had to be in the range ‘-12:59’ to ‘+13:00’ , inclusive; beginning with MySQL 8.0.19, the permitted range is ‘-13:59’ to ‘+14:00’ , inclusive.

As a named time zone, such as ‘Europe/Helsinki’ , ‘US/Eastern’ , ‘MET’ , or ‘UTC’ .

Named time zones can be used only if the time zone information tables in the mysql database have been created and populated. Otherwise, use of a named time zone results in an error:

Populating the Time Zone Tables

Several tables in the mysql system schema exist to store time zone information (see Section 5.3, “The mysql System Schema”). The MySQL installation procedure creates the time zone tables, but does not load them. To do so manually, use the following instructions.

Loading the time zone information is not necessarily a one-time operation because the information changes occasionally. When such changes occur, applications that use the old rules become out of date and you may find it necessary to reload the time zone tables to keep the information used by your MySQL server current. See Staying Current with Time Zone Changes.

If your system has its own zoneinfo database (the set of files describing time zones), use the mysql_tzinfo_to_sql program to load the time zone tables. Examples of such systems are Linux, macOS, FreeBSD, and Solaris. One likely location for these files is the /usr/share/zoneinfo directory. If your system has no zoneinfo database, you can use a downloadable package, as described later in this section.

To load the time zone tables from the command line, pass the zoneinfo directory path name to mysql_tzinfo_to_sql and send the output into the mysql program. For example:

The mysql command shown here assumes that you connect to the server using an account such as root that has privileges for modifying tables in the mysql system schema. Adjust the connection parameters as required.

mysql_tzinfo_to_sql reads your system’s time zone files and generates SQL statements from them. mysql processes those statements to load the time zone tables.

mysql_tzinfo_to_sql also can be used to load a single time zone file or generate leap second information:

To load a single time zone file tz_file that corresponds to a time zone name tz_name , invoke mysql_tzinfo_to_sql like this:

With this approach, you must execute a separate command to load the time zone file for each named zone that the server needs to know about.

If your time zone must account for leap seconds, initialize leap second information like this, where tz_file is the name of your time zone file:

After running mysql_tzinfo_to_sql , restart the server so that it does not continue to use any previously cached time zone data.

If your system has no zoneinfo database (for example, Windows), you can use a package containing SQL statements that is available for download at the MySQL Developer Zone:

Do not use a downloadable time zone package if your system has a zoneinfo database. Use the mysql_tzinfo_to_sql utility instead. Otherwise, you may cause a difference in datetime handling between MySQL and other applications on your system.

To use an SQL-statement time zone package that you have downloaded, unpack it, then load the unpacked file contents into the time zone tables:

Then restart the server.

Do not use a downloadable time zone package that contains MyISAM tables. That is intended for older MySQL versions. MySQL now uses InnoDB for the time zone tables. Trying to replace them with MyISAM tables causes problems.

Staying Current with Time Zone Changes

When time zone rules change, applications that use the old rules become out of date. To stay current, it is necessary to make sure that your system uses current time zone information is used. For MySQL, there are multiple factors to consider in staying current:

The operating system time affects the value that the MySQL server uses for times if its time zone is set to SYSTEM . Make sure that your operating system is using the latest time zone information. For most operating systems, the latest update or service pack prepares your system for the time changes. Check the website for your operating system vendor for an update that addresses the time changes.

If you replace the system’s /etc/localtime time zone file with a version that uses rules differing from those in effect at mysqld startup, restart mysqld so that it uses the updated rules. Otherwise, mysqld might not notice when the system changes its time.

If you use named time zones with MySQL, make sure that the time zone tables in the mysql database are up to date:

If your system has its own zoneinfo database, reload the MySQL time zone tables whenever the zoneinfo database is updated.

For systems that do not have their own zoneinfo database, check the MySQL Developer Zone for updates. When a new update is available, download it and use it to replace the content of your current time zone tables.

For instructions for both methods, see Populating the Time Zone Tables. mysqld caches time zone information that it looks up, so after updating the time zone tables, restart mysqld to make sure that it does not continue to serve outdated time zone data.

If you are uncertain whether named time zones are available, for use either as the server’s time zone setting or by clients that set their own time zone, check whether your time zone tables are empty. The following query determines whether the table that contains time zone names has any rows:

A count of zero indicates that the table is empty. In this case, no applications currently are using named time zones, and you need not update the tables (unless you want to enable named time zone support). A count greater than zero indicates that the table is not empty and that its contents are available to be used for named time zone support. In this case, be sure to reload your time zone tables so that applications that use named time zones can obtain correct query results.

To check whether your MySQL installation is updated properly for a change in Daylight Saving Time rules, use a test like the one following. The example uses values that are appropriate for the 2007 DST 1-hour change that occurs in the United States on March 11 at 2 a.m.

The test uses this query:

The two time values indicate the times at which the DST change occurs, and the use of named time zones requires that the time zone tables be used. The desired result is that both queries return the same result (the input time, converted to the equivalent value in the ‘US/Central’ time zone).

Before updating the time zone tables, you see an incorrect result like this:

After updating the tables, you should see the correct result:

Time Zone Leap Second Support

Leap second values are returned with a time part that ends with :59:59 . This means that a function such as NOW() can return the same value for two or three consecutive seconds during the leap second. It remains true that literal temporal values having a time part that ends with :59:60 or :59:61 are considered invalid.

If it is necessary to search for TIMESTAMP values one second before the leap second, anomalous results may be obtained if you use a comparison with ‘ YYYY-MM-DD hh:mm:ss ‘ values. The following example demonstrates this. It changes the session time zone to UTC so there is no difference between internal TIMESTAMP values (which are in UTC) and displayed values (which have time zone correction applied).

To work around this, you can use a comparison based on the UTC value actually stored in the column, which has the leap second correction applied:

Источник

SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: ‘UTC’ ? #86

SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: ‘UTC’

here my country is VietNam!

The text was updated successfully, but these errors were encountered:

Simply run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

Hope your issue is now fixed. This is server related, not project related.
Feel free to open this issue again if you still need any help.

Yes, I had not had that problem in 3 other environments, so I suspected MAMP. However, I did not expect a quick reply at that time, so I dug into the MAMP directory structure and found the utility program that led to the fix.

That did fix my problem. I’m sorry I bothered you.

I had the same issue. it seems your time zone table from mysql is empty. to resolve this , follow the instructions below:

Stop mysql server (if running)

Download timezone list from: here

Extract the file.

if your’re using xampp/wamp server , go to c drive> xamp/wamp> mysql> data> mysql
and paste the extracted files here. (replace the files if necessary).

(N.B: if you’re not using xampp/wamp servers, you can also follow the same
procedure to replace the files.)

Finally , start mysql server again and check phpmyadmin>mysql> time_zone_name
table.
[Hope that will resolve your problem]

open a file config/database.php

I had the same issue. it seems your time zone table from mysql is empty. to resolve this , follow the instructions below:

1. Stop mysql server (if running)
2. Download timezone list from: here
3. Extract the file.
4. if your’re using xampp/wamp server , go to c drive> xamp/wamp> mysql> data> mysql
   and paste the extracted files here. (replace the files if necessary).
   (N.B: if you’re not using xampp/wamp servers, you can also follow the same
   procedure to replace the files.)
5. Finally , start mysql server again and check phpmyadmin>mysql> time_zone_name
   table.
   [Hope that will resolve your problem]

my savior 🙆🏻‍♂️👌🏻 thank you

© 2023 GitHub, Inc.

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

Источник, как всегда авторам благодарность.

Чтобы проверить текущую временную зону, нужно выполнить команду:

Чтобы посмотреть текущее время сервера MySQL:

Прописать в конфигурационном файле timezone можно следующим способом (в таком случае потребуется перезагрузка):

/etc/my.cnf

Можно поменять время и без перезагрузки, для этого сначала перенесем системные тайм зоны в MySQL следующим способом:

Далее, мы уже можем обновить временную зону без появления ошибок типа:

Выполним обновление time_zone:

В MySQL также можно использовать системное время, это наверное даже
лучше. Чтобы изменить текущее системное время на сервере, нужно сделать:

Чтобы использовалось системное время, в MySQL нужно выполнить

topology:
Upstream TiDB: 172.16.6.206
Downstream: MySQL 5.6.46

1. Version Info

[tidb@localhost tidb-ansible]$ /data1/tidb/deploy/bin/tidb-server -V
Release Version: v4.0.0-alpha-516-g5466a3c31
Git Commit Hash: 5466a3c31bf4b93fb3a2c595dd6aeac46aca7b8e
Git Branch: master
UTC Build Time: 2019-10-14 03:55:02
GoVersion: go version go1.13 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[tidb@localhost tidb-ansible]$ /data1/tidb/deploy/bin/tikv-server -V
TiKV
Release Version:   4.0.0-alpha
Git Commit Hash:   56dc6d63ade182289c4ab1e37996746040bc07d6
Git Commit Branch: cdc
UTC Build Time:    2019-11-06 03:29:57
Rust Version:      rustc 1.39.0-nightly (c6e9c76c5 2019-09-04)

[tidb@localhost tidb-ansible]$ /data1/tidb/deploy/bin/pd-server -V
Release Version: v4.0.0-alpha-191-g7811255c
Git Commit Hash: 7811255c7345503ed5f44afb981bbf9712fd25c6
Git Branch: master
UTC Build Time:  2019-12-06 05:07:33

2. Reproduce steps

mysql -h 127.0.0.1 -P 4000 -u root

CREATE table test.simple2(id int primary key, val int);
CREATE table test.simple2(id int primary key, val int);

## start_ts=$(($(date +%s%N | cut -b1-13)<<18)) => 413114580074496000

INSERT INTO test.simple1(id, val) VALUES (1, 1);
INSERT INTO test.simple1(id, val) VALUES (2, 2);
INSERT INTO test.simple1(id, val) VALUES (3, 3);
UPDATE test.simple1 set val = 22 where id = 2;
DELETE from test.simple1 where id = 3

mysql -h 127.0.0.1 -P 3306 -u root -e ‘create database test’

nohup /home/tidb/cdc server --pd-endpoints http://172.16.6.206:2379 &

/home/tidb/cdc cli --pd-addr http://172.16.6.206:2379 --start-ts=413114580074496000 --sink-uri 'root@tcp(127.0.0.1:3306)/test'

3. Expected and Got

cdc.log:

[2019/12/09 21:20:10.843 -05:00] [DEBUG] [storage.go:302] ["handle job: "] ["sql query"="CREATE TABLE if not exists mysql.stats_top_n (ntttable_id bigint(64) NOT NULL,nttis_index tinyint(2) NOT NULL,ntthist_id bigint(64) NOT NULL,nttvalue longblob,nttcount bigint(64) UNSIGNED NOT NULL,nttindex tbl(table_id, is_index, hist_id)nt);"] [job="ID:38, Type:create table, State:synced, SchemaState:public, SchemaID:3, TableID:37, RowCount:0, ArgLen:0, start time: 2019-12-09 21:14:55.003 -0500 EST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[2019/12/09 21:20:10.843 -05:00] [DEBUG] [storage.go:221] ["create table success"] [name=mysql.stats_top_n] [id=37]
[2019/12/09 21:20:10.843 -05:00] [DEBUG] [storage.go:302] ["handle job: "] ["sql query"="CREATE TABLE IF NOT EXISTS mysql.expr_pushdown_blacklist (nttname char(100) NOT NULLnt);"] [job="ID:40, Type:create table, State:synced, SchemaState:public, SchemaID:3, TableID:39, RowCount:0, ArgLen:0, start time: 2019-12-09 21:14:55.103 -0500 EST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[2019/12/09 21:20:10.843 -05:00] [DEBUG] [storage.go:221] ["create table success"] [name=mysql.expr_pushdown_blacklist] [id=39]
[2019/12/09 21:20:10.844 -05:00] [DEBUG] [storage.go:302] ["handle job: "] ["sql query"="CREATE TABLE IF NOT EXISTS mysql.opt_rule_blacklist (nttname char(100) NOT NULLnt);"] [job="ID:42, Type:create table, State:synced, SchemaState:public, SchemaID:3, TableID:41, RowCount:0, ArgLen:0, start time: 2019-12-09 21:14:55.153 -0500 EST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[2019/12/09 21:20:10.844 -05:00] [DEBUG] [storage.go:221] ["create table success"] [name=mysql.opt_rule_blacklist] [id=41]
[2019/12/09 21:20:10.844 -05:00] [DEBUG] [storage.go:302] ["handle job: "] ["sql query"="CREATE table test.simple1(id int primary key, val int)"] [job="ID:44, Type:create table, State:synced, SchemaState:public, SchemaID:1, TableID:43, RowCount:0, ArgLen:0, start time: 2019-12-09 21:17:06.003 -0500 EST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[2019/12/09 21:20:10.844 -05:00] [DEBUG] [storage.go:221] ["create table success"] [name=test.simple1] [id=43]
[2019/12/09 21:20:10.844 -05:00] [DEBUG] [storage.go:302] ["handle job: "] ["sql query"="CREATE table test.simple2(id int primary key, val int)"] [job="ID:46, Type:create table, State:synced, SchemaState:public, SchemaID:1, TableID:45, RowCount:0, ArgLen:0, start time: 2019-12-09 21:17:12.253 -0500 EST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[2019/12/09 21:20:10.844 -05:00] [DEBUG] [storage.go:221] ["create table success"] [name=test.simple2] [id=45]
[2019/12/09 21:20:10.845 -05:00] [DEBUG] [client.go:228] ["singleEventFeed quit"]
[2019/12/09 21:20:10.845 -05:00] [INFO] [processor.go:353] ["Checkpoint worker exited"]
[2019/12/09 21:20:10.845 -05:00] [INFO] [client.go:235] ["EventFeed disconnected"] [span="{"Start":"bURETEpvYkxp/3N0AAAAAAAA+QAAAAAAAABs","End":"bURETEpvYkxp/3N0AAAAAAAA+QAAAAAAAABt"}"] [checkpoint=413124368270098433] [error="rpc error: code = Canceled desc = context canceled"] [errorVerbose="rpc error: code = Canceled desc = context canceledngithub.com/pingcap/errors.AddStackntgithub.com/pingcap/errors@v0.11.4/errors.go:174ngithub.com/pingcap/errors.Tracentgithub.com/pingcap/errors@v0.11.4/juju_adaptor.go:15ngithub.com/pingcap/ticdc/cdc/kv.(*CDCClient).singleEventFeedntgithub.com/pingcap/ticdc@/cdc/kv/client.go:408ngithub.com/pingcap/ticdc/cdc/kv.(*CDCClient).partialRegionFeed.func1ntgithub.com/pingcap/ticdc@/cdc/kv/client.go:227ngithub.com/pingcap/ticdc/pkg/retry.Run.func1ntgithub.com/pingcap/ticdc@/pkg/retry/retry.go:31ngithub.com/cenkalti/backoff.RetryNotifyntgithub.com/cenkalti/backoff@v2.2.1+incompatible/retry.go:37ngithub.com/cenkalti/backoff.Retryntgithub.com/cenkalti/backoff@v2.2.1+incompatible/retry.go:24ngithub.com/pingcap/ticdc/pkg/retry.Runntgithub.com/pingcap/ticdc@/pkg/retry/retry.go:30ngithub.com/pingcap/ticdc/cdc/kv.(*CDCClient).partialRegionFeedntgithub.com/pingcap/ticdc@/cdc/kv/client.go:215ngithub.com/pingcap/ticdc/cdc/kv.(*CDCClient).EventFeed.func1.1ntgithub.com/pingcap/ticdc@/cdc/kv/client.go:188ngolang.org/x/sync/errgroup.(*Group).Go.func1ntgolang.org/x/sync@v0.0.0-20190423024810-112230192c58/errgroup/errgroup.go:57nruntime.goexitntruntime/asm_amd64.s:1357"]
[2019/12/09 21:20:10.845 -05:00] [INFO] [scheduler.go:313] ["stop to run processor"] ["changefeed id"=245b6079-015f-4707-9f18-78bca094b6cf]
[2019/12/09 21:20:10.846 -05:00] [DEBUG] [client.go:228] ["singleEventFeed quit"]
[2019/12/09 21:20:10.846 -05:00] [ERROR] [server.go:80] ["run server"] [error="Error 1298: Unknown or incorrect time zone: 'UTC'ngithub.com/pingcap/errors.AddStackntgithub.com/pingcap/errors@v0.11.4/errors.go:174ngithub.com/pingcap/errors.Tracentgithub.com/pingcap/errors@v0.11.4/juju_adaptor.go:15ngithub.com/pingcap/ticdc/cdc/sink.(*mysqlSink).Emitntgithub.com/pingcap/ticdc@/cdc/sink/mysql.go:141ngithub.com/pingcap/ticdc/cdc.(*processor).syncResolvedntgithub.com/pingcap/ticdc@/cdc/processor.go:587ngithub.com/pingcap/ticdc/cdc.(*processor).Run.func3ntgithub.com/pingcap/ticdc@/cdc/processor.go:283ngolang.org/x/sync/errgroup.(*Group).Go.func1ntgolang.org/x/sync@v0.0.0-20190423024810-112230192c58/errgroup/errgroup.go:57nruntime.goexitntruntime/asm_amd64.s:1357"]
[2019/12/09 21:20:10.846 -05:00] [INFO] [client.go:235] ["EventFeed disconnected"] [span="{"Start":"bURETEpvYkxp/3N0AAAAAAAA+QAAAAAAAABs","End":"bURETEpvYkxp/3N0AAAAAAAA+QAAAAAAAABt"}"] [checkpoint=413124368270098433] [error="rpc error: code = Canceled desc = context canceled"] [errorVerbose="rpc error: code = Canceled desc = context canceledngithub.com/pingcap/errors.AddStackntgithub.com/pingcap/errors@v0.11.4/errors.go:174ngithub.com/pingcap/errors.Tracentgithub.com/pingcap/errors@v0.11.4/juju_adaptor.go:15ngithub.com/pingcap/ticdc/cdc/kv.(*CDCClient).singleEventFeedntgithub.com/pingcap/ticdc@/cdc/kv/client.go:408ngithub.com/pingcap/ticdc/cdc/kv.(*CDCClient).partialRegionFeed.func1ntgithub.com/pingcap/ticdc@/cdc/kv/client.go:227ngithub.com/pingcap/ticdc/pkg/retry.Run.func1ntgithub.com/pingcap/ticdc@/pkg/retry/retry.go:31ngithub.com/cenkalti/backoff.RetryNotifyntgithub.com/cenkalti/backoff@v2.2.1+incompatible/retry.go:37ngithub.com/cenkalti/backoff.Retryntgithub.com/cenkalti/backoff@v2.2.1+incompatible/retry.go:24ngithub.com/pingcap/ticdc/pkg/retry.Runntgithub.com/pingcap/ticdc@/pkg/retry/retry.go:30ngithub.com/pingcap/ticdc/cdc/kv.(*CDCClient).partialRegionFeedntgithub.com/pingcap/ticdc@/cdc/kv/client.go:215ngithub.com/pingcap/ticdc/cdc/kv.(*CDCClient).EventFeed.func1.1ntgithub.com/pingcap/ticdc@/cdc/kv/client.go:188ngolang.org/x/sync/errgroup.(*Group).Go.func1ntgolang.org/x/sync@v0.0.0-20190423024810-112230192c58/errgroup/errgroup.go:57nruntime.goexitntruntime/asm_amd64.s:1357"]
[2019/12/09 21:20:10.846 -05:00] [DEBUG] [capture_info.go:128] ["watchC from etcd close normally"]
[2019/12/09 21:20:10.846 -05:00] [INFO] [owner.go:372] ["handleWatchCapture quit"]
[2019/12/09 21:20:10.846 -05:00] [DEBUG] [etcd.go:205] ["update subchangefeed info success"] ["changefeed id"=6cdfb9e6-e0ec-4933-bd77-b269946cd685] ["capture id"=a3d0a077-497e-4b4a-a7c3-cb186e9e110d] [modRevision=232] [info="{"checkpoint-ts":0,"resolved-ts":413124368270098433,"table-infos":[{"id":45,"start-ts":413124328229699584}],"table-p-lock":null,"table-c-lock":null}"]
[2019/12/09 21:20:10.846 -05:00] [INFO] [processor.go:330] ["Local resolved worker exited"]

A while ago, I dived deep into time zones on MySQL. Here is my scratchpad:

Background Knowledge

• Time zones != Time offsets. We can think of time zones as human friendly references to time offsets from GMT.
  • This post by Devney Hamilton explains it well
• UTC is a timezone, GMT is an offset. It just so happens that the time offset of UTC from GMT is exactly zero
  • e.g. UTC = GMT+0 and Asia/Singapore = GMT+8

Set Up

I am using the mysql:5.7 Docker image with the MySQL CLI Client:

• Host OS: Ubuntu Server 18.04
• Docker Client & Server Version: 19.03.6
• MySQL Image: mysql:5.7
• MySQL CLI Client: Ver 14.14 Distrib 5.7.29

Docker commands for demo MySQL container:

// Starting the container: 
$ docker run -dt --name mysql-container --hostname mysql-container -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 mysql:5.7

// SSH into the container: 
$ docker exec -it mysql-container /bin/sh

To connect to the MySQL database from within the container after SSH:

root@mysql-container:/# mysql -h127.0.0.1 -uroot -p

Note that:

• 127.0.0.1 is used instead of localhost so as to make the connection over TCP with the default 3306 port. 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.
• The Docker container takes some time to initialize, so if the connection command does not work, wait a while before trying again.

Part 1: Supporting Time Zones in MySQL

Time zone information is stored in several tables within the mysql system schema:

• time_zone: Time zone IDs and whether they use leap seconds.
• time_zone_leap_second: When leap seconds occur.
• time_zone_name: Mappings between time zone IDs and names.
• time_zone_transition, time_zone_transition_type: Time zone descriptions.

(Documentation)

If these tables are empty, that means your MySQL database does not support it yet. Simply populate these tables by running the following command on the database host OS:

root@mysql-container:/# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -D mysql -u root -p mysql
Enter password: 
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/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

You might see some warnings for unsupported files, and that’s okay. Read more here.

If/Once these tables are populated, your database will support time zones, and you can reference them by their names (e.g. “Asia/Singapore”), as long as they exist in the time_zone_name table.

Bonus Question: Does the mysql_tzinfo_to_sql command retain changes/differences to the time zone table between runs?

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. Because the first few SQL commands clear the tables, any time zones not in the target folder are lost. 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.

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:

root@mysql-container:/# mysql_tzinfo_to_sql /usr/share/zoneinfo
TRUNCATE TABLE time_zone;
TRUNCATE TABLE time_zone_name;
TRUNCATE TABLE time_zone_transition;
TRUNCATE TABLE time_zone_transition_type;
START TRANSACTION;
INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Japan', @time_zone_id);
INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
 (@time_zone_id, -2147483648, 3)
,(@time_zone_id, -683802000, 1)
,(@time_zone_id, -672310800, 2)
,(@time_zone_id, -654771600, 1)
,(@time_zone_id, -640861200, 2)
,(@time_zone_id, -620298000, 1)
,(@time_zone_id, -609411600, 2)
,(@time_zone_id, -588848400, 1)
,(@time_zone_id, -577962000, 2)
;
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, 33539, 0, 'LMT')
,(@time_zone_id, 1, 36000, 1, 'JDT')
,(@time_zone_id, 2, 32400, 0, 'JST')
,(@time_zone_id, 3, 32400, 0, 'JST')
;
COMMIT;

Part 2: MySQL Has 2 Time Zones

There are two time zones settings that MySQL has – a global and session time zone.

• Global Time Zone: The time zone that the database is in. Usually this is set to SYSTEM to take the host’s time zone. This setting is persisted across connections.
• Session Time Zone: The time zone that the connecting session is in. If undefined during connection, this takes the same value the global time zone. This does not persist across connections.

Part 2a: Set the Time Zone of a Running MySQL Database

To set the global time zone setting that will be the default for all connections unless otherwise specified:

mysql> SET GLOBAL time_zone = "America/New_York";
Query OK, 0 rows affected (0.00 sec)

To set the session time zone setting that will only last for the current session/connection:

mysql> SET time_zone = "Asia/Tokyo";
Query OK, 0 rows affected (0.00 sec)

To see either/both time zone settings:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| America/New_York   | Asia/Tokyo          |
+--------------------+---------------------+
1 row in set (0.00 sec)

If you disconnect and reconnect to the database, the @@session.time_zone will default to the @@global.time_zone value:

mysql> exit
Bye
$ mysql -h 127.0.0.1 -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.7.29 MySQL Community Server (GPL)
...

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

Part 2b: Set the Time Zone during MySQL Database Startup

A more persistent way to set the global time zone is via the mysqld.cnf file. The location of this file varies based on your setup – use the find command on the database host if you are not sure where it is located:

root@mysql-container:/# find / -name "mysqld.cnf"
/etc/mysql/mysql.conf.d/mysqld.cnf

Set the default-time-zone variable to the time zone you want:

root@mysql-container:/# echo "default-time-zone='Asia/Singapore'" >> /etc/mysql/mysql.conf.d/mysqld.cnf

Restart the database (in my case, the Docker container) and check that it worked:

$ docker restart mysql-container
mysql-container

$ mysql -h 127.0.0.1 -uroot -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or g.
...

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

If you selected an incorrect time zone, MySQL will refuse to start, with the following error:

[ERROR] Fatal error: Illegal or unknown default time zone 'Asia/Luppeng'

(yes, for demonstration purposes I had declared myself as a time zone)

Part 2c: Set the Session Time Zone via the JDBC Connection String

When connecting to a MySQL database via the JDBC driver, it uses a connection URL that allows session variables to be set. In the connection URL below, I am setting the connection’s session time_zone variable to be Australia/Sydney:

Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mysql?user=root&password=password&sessionVariables=time_zone='Australia/Sydney'");

You can use the returned Connection object to execute the following query to get the time_zone parameter values: SELECT @@global.time_zone, @@session.time_zone. You should see that the @@session.time_zone is set to “Australia/Sydney”.

Part 3: Time Zones Affect Date and Time Operations

In case it was not obvious what the time zone affects – lets see how the NOW() function reacts when the time zone is set to UTC (GMT+0) vs Asia/Hong_Kong (GMT+8):

mysql> SET time_zone = "UTC";
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-03-15 22:25:12 |
+---------------------+
1 row in set (0.00 sec)

mysql> SET time_zone = "Asia/Hong_Kong";
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-03-16 06:25:20 |
+---------------------+
1 row in set (0.00 sec)

Part 4: Referenced Time Zones are Cached While Database Process is Running

If you have used a time zone in a query, and subsequently delete it from the mysql system schema table, it will still be valid for as long the database process is not stopped.

mysql> SET time_zone = "Europe/London";
Query OK, 0 rows affected (0.01 sec)

mysql> DELETE FROM mysql.time_zone_name WHERE Name LIKE "Europe%";
Query OK, 63 rows affected (0.00 sec)

mysql> SET time_zone = "Europe/Paris";
ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Paris'

mysql> exit
Bye

$ mysql -h 127.0.0.1 -uroot -p

mysql> SET time_zone = "Europe/London";
Query OK, 0 rows affected (0.00 sec)

However, once the database process is restarted, the time zone is no longer cached and it will be impossible to reference it. To illustrate, lets try restarting our database container and referencing the Europe/London time zone again:

$ docker restart mysql-container
mysql-container

$ mysql -h 127.0.0.1 -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)
...

mysql> SET time_zone = "Europe/London";
ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/London'

Part 5: The Misleading serverTimezone Parameter of the JDBC Connection String

TL;DR: The serverTimezone parameter tells MySQL engine that any date/time that was stored in the database was inserted on the specified time zone. So when returning such values, it applies the difference between the @@global.time_zone and serverTimezone. It does not change any time_zone parameter values, be it global or session. 

Based on the JDBC driver documentation, serverTimezone overrides the detection/mapping of time zone, but only when time zone from server doesn’t map to Java time zone. But I feel that its actually more confusing than that.

To demonstrate, I created a simple table with one timestamp column within the mysql system schema:

$ mysql -h 127.0.0.1 -uroot -Dmysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
...

mysql> CREATE TABLE time (time TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

With the global time zone being UTC, I inserted a the current timestamp into the table:

mysql> SET time_zone = UTC;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL time_zone = UTC;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO time VALUES ('2020-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql>  SELECT * FROM time;
+---------------------+
| time                |
+---------------------+
| 2020-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Using the mysql-connector-java-8.0.19 JDBC driver, I wrote a simple Java program to SELECT * FROM time; that uses the following connection string:

jdbc:mysql://127.0.0.1:3306?user=root&password=password&serverTimezone=UTC

When @@global.time_zone == UTC, and

• when serverTimezone=UTC, the output was: 2020-01-01 00:00:00.0
• when serverTimezone=Asia/Singapore (i.e. GMT+8), the output was: 2019-12-31 16:00:00.0

When @@global.time_zone == Asia/Singapore, and

• when serverTimezone=UTC, the output was: 2020-01-01 08:00:00.0
• when serverTimezone=Asia/Singapore (i.e. GMT+8), the output was: 2020-01-01 00:00:00.0

What happened is that the serverTimezone parameter tells MySQL engine that any date/time that was stored in the database was inserted using the specified time zone. So when returning such values, it applies the difference between the @@global.time_zone and serverTimezone.

For example, when @@global.time_zone == UTC and serverTimezone == Asia/Singapore, we are telling the MySQL engine that the ‘2020-01-01 00:00:00’ value we inserted earlier was done so with the GMT+8 time offset. Thus, before returning the timestamp value, it should apply the difference between the @@global.time_zone (GMT+0) and serverTimezone (GMT+8). This results in a negative 8 hour difference, which led to the returned value of “2019-12-31 16:00:00.0”.

Part 5a: Acceptable Values of serverTimezone

The accepted values for the serverTimezone parameter is based on your Java version Time Zone IDs. Time zones rarely change, and you can find a comprehensive list of Time Zones IDs in Gary’s 2013 blog post. Or refer to your Java version documentation.

Will add if I learn more nuances about time zones and MySQL.

For now, its EOF.