[MDEV-26093] Explicitly set session time_zone shows UTC time Created: 2021-07-06  Updated: 2021-07-06  Resolved: 2021-07-06

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Zoltán Böszörményi Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

This occurs with both 10.5.9 and 10.5.11 built under Yocto 3.3, both with "genericx86-64" and "intel-corei7-64" CPU tuning builds.

The timezone database from /usr/share/zoneinfo is loaded.

Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 35
 
Server version: 10.5.11-MariaDB-log MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [dtd]> select @@global.time_zone, @@session.time_zone, now(), localtime(), utc_timestamp();
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
| @@global.time_zone | @@session.time_zone | now()               | localtime()         | utc_timestamp()     |
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
| SYSTEM             | SYSTEM              | 2021-07-06 07:55:08 | 2021-07-06 07:55:08 | 2021-07-06 05:55:08 |
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [dtd]> set time_zone = "US/Eastern";
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [dtd]> select @@global.time_zone, @@session.time_zone, now(), localtime(), utc_timestamp();
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
| @@global.time_zone | @@session.time_zone | now()               | localtime()         | utc_timestamp()     |
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
| SYSTEM             | US/Eastern          | 2021-07-06 05:55:25 | 2021-07-06 05:55:25 | 2021-07-06 05:55:25 |
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
1 row in set (0.001 sec)

Can it be a configuration problem or an issue with the particular build?

 Comments   
Comment by Sergei Golubchik [ 2021-07-06 ]

The server simply asks OS for the time. It's likely some local configuration issue. What do you get from

date
 
env TZ=US/Eastern date
 
env TZ= date
 
env -u TZ date

here, of course, it's important to have the same environment as for the mysqld process. If unsure, check /proc/<mysqld_pid>/environ

Comment by Zoltán Böszörményi [ 2021-07-06 ]

mariadb is run under systemd with a quite limited set of environment variables. This is the closest I can get:

[root@dtd root]# exec env --ignore-environment /bin/bash
 
dircolors: no SHELL environment variable, and no shell type option given
 
bash-5.1#

This way the commands give these results:

bash-5.1# while IFS= read -rd '' var; do declare +x "$var"; done </proc/60571/environ
 
bash-5.1# date
 
Tue Jul  6 11:47:11 CEST 2021
 
bash-5.1# env TZ=US/Eastern date
 
Tue Jul  6 05:47:16 EDT 2021
 
bash-5.1# env TZ= date
 
Tue Jul  6 09:47:21 UTC 2021
 
bash-5.1# env -u TZ date
 
Tue Jul  6 11:47:25 CEST 2021

Comment by Sergei Golubchik [ 2021-07-06 ]

Are your timezone tables populated properly? I see 236 rows in

select * from mysql.time_zone_transition join mysql.time_zone_name using(Time_zone_id) where name='US/Eastern';

and 5 rows in

select * from mysql.time_zone_transition_type join mysql.time_zone_name using(Time_zone_id) where name='US/Eastern';

My tables were freshly generated from timezone-data-2021a

Comment by Zoltán Böszörményi [ 2021-07-06 ]

Interesting. 0 rows for the first join query, 1 row for the second:

MariaDB [mysql]> select * from mysql.time_zone_transition_type join mysql.time_zone_name using(Time_zone_id) where name='US/Eastern';
 
+--------------+--------------------+--------+--------+--------------+------------+
 
| Time_zone_id | Transition_type_id | Offset | Is_DST | Abbreviation | Name       |
 
+--------------+--------------------+--------+--------+--------------+------------+
 
|          582 |                  0 |      0 |      0 |              | US/Eastern |
 
+--------------+--------------------+--------+--------+--------------+------------+
 
1 row in set (0.003 sec)

The number of rows in the timezone tables:

MariaDB [mysql]> select count(*) from time_zone;
 
+----------+
 
| count(*) |
 
+----------+
 
|     1779 |
 
+----------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [mysql]> select count(*) from time_zone_leap_second;
 
+----------+
 
| count(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [mysql]> select count(*) from time_zone_name;
 
+----------+
 
| count(*) |
 
+----------+
 
|     1779 |
 
+----------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [mysql]> select count(*) from time_zone_transition;
 
+----------+
 
| count(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [mysql]> select count(*) from time_zone_transition_type;
 
+----------+
 
| count(*) |
 
+----------+
 
|     1779 |
 
+----------+
 
1 row in set (0.001 sec)

tzdata 2021a here, too.

Comment by Zoltán Böszörményi [ 2021-07-06 ]

mysql_tzinfo_to_sql doesn't generate rows for time_zone_transition:

[root@dtd ~]# mysql_tzinfo_to_sql /usr/share/zoneinfo 2>/dev/null | grep -i time_zone_transition | grep -iv time_zone_transition_type
 
ALTER TABLE time_zone_transition ENGINE=InnoDB;
 
TRUNCATE TABLE time_zone_transition;
 
ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
 
ALTER TABLE time_zone_transition ENGINE=Aria;

Comment by Zoltán Böszörményi [ 2021-07-06 ]

Okay, it turned out to be a packaging issue with tzdata. The "zic" command in tzcode 2020b changed the default format from "-b fat" to "-b slim".
After repackaging tzdata with zic -b fat and reloading the timezone database into MariaDB:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 366
 
Server version: 10.5.11-MariaDB-log MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [mysql]> select count(*) from mysql.time_zone_transition join mysql.time_zone_name using(Time_zone_id) where name='US/Eastern';
 
+----------+
 
| count(*) |
 
+----------+
 
|      236 |
 
+----------+
 
1 row in set (0.003 sec)
 
 
 
MariaDB [mysql]> select count(*) from mysql.time_zone_transition_type join mysql.time_zone_name using(Time_zone_id) where name='US/Eastern';
 
+----------+
 
| count(*) |
 
+----------+
 
|        5 |
 
+----------+
 
1 row in set (0.002 sec)

and setting the timezone works:

MariaDB [mysql]> select @@global.time_zone, @@session.time_zone, now(), localtime(), utc_timestamp();
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
| @@global.time_zone | @@session.time_zone | now()               | localtime()         | utc_timestamp()     |
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
| SYSTEM             | SYSTEM              | 2021-07-06 16:51:03 | 2021-07-06 16:51:03 | 2021-07-06 14:51:03 |
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [mysql]> set time_zone = "US/Eastern";
 
Query OK, 0 rows affected (0.004 sec)
 
 
 
MariaDB [mysql]> select @@global.time_zone, @@session.time_zone, now(), localtime(), utc_timestamp();
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
| @@global.time_zone | @@session.time_zone | now()               | localtime()         | utc_timestamp()     |
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
| SYSTEM             | US/Eastern          | 2021-07-06 10:51:19 | 2021-07-06 10:51:19 | 2021-07-06 14:51:19 |
 
+--------------------+---------------------+---------------------+---------------------+---------------------+
 
1 row in set (0.001 sec)

So it was a configuration issue, just not in MariaDB.

Generated at Thu Feb 08 09:42:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.