TiDB Data Written to Partitioned Table Falls into Wrong Partition Due to Time Zone Issue

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb数据写入到分区表因为时区问题落错分区

| username: TI表弟

[TiDB Usage Environment] Test
[TiDB Version] v5.4.0
[Reproduction Path] Create table + Insert data + Query
Note that my SYSTEM time zone is Shanghai, the time_zone environment variable is UTC, and the timestamps stored in the database are also UTC.

CREATE TABLE `history_wti_alarm` (
  `id` bigint not null,
  `alarm_id` varchar(64) NOT NULL  COMMENT 'unique alarm id',
  `start_time` timestamp(3) NOT NULL,
  `end_time` timestamp(3) NULL DEFAULT NULL COMMENT 'Last sample timestamp',
  PRIMARY KEY (`alarm_id`,`start_time`),
  UNIQUE KEY `uniq_alarm_id` (`alarm_id`,`start_time`),
  KEY `idx_start_time` (`start_time`)
) PARTITION BY RANGE(floor(UNIX_TIMESTAMP(start_time))) (
    PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')),
    PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-10 00:00:00')),
    PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-20 00:00:00')),
    PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-31 00:00:00')),
    PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-01 00:00:00')),
    PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-10 00:00:00')),
    PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-20 00:00:00')),
    PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-28 00:00:00')),
    PARTITION p9 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-01 00:00:00')),
    PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-10 00:00:00')),
    PARTITION p11 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-20 00:00:00')),
    PARTITION p12 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-31 00:00:00')),
    PARTITION p13 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-01 00:00:00')),
    PARTITION p14 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-10 00:00:00')),
    PARTITION p15 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-20 00:00:00')),
    PARTITION p16 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-30 00:00:00')),
    PARTITION p17 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-01 00:00:00')),
    PARTITION p18 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-10 00:00:00')),
    PARTITION p19 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-20 00:00:00')),
    PARTITION p20 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-31 00:00:00')),
    PARTITION p21 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-01 00:00:00')),
    PARTITION p22 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-10 00:00:00')),
    PARTITION p23 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-20 00:00:00')),
    PARTITION p24 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-30 00:00:00')),
    PARTITION p25 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-01 00:00:00')),
    PARTITION p26 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-10 00:00:00')),
    PARTITION p27 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-20 00:00:00')),
    PARTITION p28 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-31 00:00:00')),
    PARTITION p29 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-01 00:00:00')),
    PARTITION p30 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-10 00:00:00')),
    PARTITION p31 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-20 00:00:00')),
    PARTITION p32 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-31 00:00:00')),
    PARTITION p33 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-01 00:00:00')),
    PARTITION p34 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-10 00:00:00')),
    PARTITION p35 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-20 00:00:00')),
    PARTITION p36 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-30 00:00:00')),
    PARTITION p37 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-01 00:00:00')),
    PARTITION p38 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-10 00:00:00')),
    PARTITION p39 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-20 00:00:00')),
    PARTITION p40 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-31 00:00:00')),
    PARTITION p41 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-01 00:00:00')),  
    PARTITION p42 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-10 00:00:00')),
    PARTITION p43 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-20 00:00:00')),
    PARTITION p44 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-30 00:00:00')),
    PARTITION p45 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-01 00:00:00')),
    PARTITION p46 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-10 00:00:00')),
    PARTITION p47 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-20 00:00:00')),
    PARTITION p48 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-31 00:00:00')),
     PARTITION p49 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')),
     PARTITION p50 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-10 00:00:00')),
     PARTITION p51 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-20 00:00:00')),
     PARTITION p52 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-31 00:00:00')),
     PARTITION p53 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-01 00:00:00')),
     PARTITION p54 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-10 00:00:00')),
     PARTITION p55 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-20 00:00:00')),
     PARTITION p56 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-28 00:00:00')),
     PARTITION p57 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-01 00:00:00')),
     PARTITION p58 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-10 00:00:00')),
     PARTITION p59 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-20 00:00:00')),
     PARTITION p60 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-31 00:00:00')),
     PARTITION p61 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-01 00:00:00')),
     PARTITION p62 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-10 00:00:00')),
     PARTITION p63 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-20 00:00:00')),
     PARTITION p64 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-30 00:00:00')),
     PARTITION p65 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-01 00:00:00')),
     PARTITION p66 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-10 00:00:00')),
     PARTITION p67 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-20 00:00:00')),
     PARTITION p68 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-31 00:00:00')),
     PARTITION p69 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-01 00:00:00')),
     PARTITION p70 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-10 00:00:00')),
     PARTITION p71 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-20 00:00:00')),
     PARTITION p72 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-30 00:00:00')),
     PARTITION p73 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01 00:00:00')),
     PARTITION p74 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-10 00:00:00')),
     PARTITION p75 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-20 00:00:00')),
     PARTITION p76 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-31 00:00:00')),
     PARTITION p77 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-01 00:00:00')),
     PARTITION p78 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-10 00:00:00')),
     PARTITION p79 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-20 00:00:00')),
     PARTITION p80 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-31 00:00:00')),
     PARTITION p81 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-01 00:00:00')),
     PARTITION p82 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-10 00:00:00')),
     PARTITION p83 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-20 00:00:00')),
     PARTITION p84 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-30 00:00:00')),
     PARTITION p85 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-01 00:00:00')),
     PARTITION p86 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-10 00:00:00')),
     PARTITION p87 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-20 00:00:00')),
     PARTITION p88 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-31 00:00:00')),
     PARTITION p90 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-01 00:00:00')),
     PARTITION p91 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-10 00:00:00')),
     PARTITION p92 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-20 00:00:00')),
     PARTITION p93 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-30 00:00:00')),
     PARTITION p94 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-01 00:00:00')),
     PARTITION p95 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-10 00:00:00')),
     PARTITION p96 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-20 00:00:00')),
     PARTITION p97 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-31 00:00:00')),
     PARTITION p1024 VALUES LESS THAN MAXVALUE
);

Insert

insert1:   insert into history_wti_alarm set id = 2,start_time = "2022-11-10 00:58:59",alarm_id = "123";
insert2:   insert into history_wti_alarm set id = 2,start_time = "2022-11-10 07:58:59",alarm_id = "123";
insert3:   insert into history_wti_alarm set id = 2,start_time = "2022-11-10 08:58:59",alarm_id = "123";

Query

select * from history_wti_alarm where start_time > "2022-11-10 00:00:00";

[Encountered Issues]:

  1. After querying, only the data from insert3 can be seen, while the data from insert1 and insert2 cannot be seen. Theoretically, all three pieces of data should fall into partition p43, but insert1 and insert2 fell into partition p42.
  2. This issue was not found in a newly built cluster with version 6.5.0, so I upgraded the historical cluster to 6.5.0, but the issue still exists.

[Resource Configuration]

image
image1380×634 61 KB

image
image1380×172 31.5 KB

2
| username: vcdog | Original post link

I happen to have a cluster here that was recently upgraded online from version v5.4.0 to v6.50. I used your test SQL statements directly, created the table, inserted data, and checked the execution plan. Everything is normal when checking the partition range.

mysql> explain select * from history_wti_alarm where start_time > "2022-11-10 00:00:00" and start_time < "2022-11-12
00:
+-------------------------+---------+-----------+----------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| id                      | estRows | task      | access object                          | operator info                                                                                                                            |
+-------------------------+---------+-----------+----------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_9           | 0.07    | root      |                                        | data:Selection_8                                                                                                                         |
| └─Selection_8           | 0.07    | cop[tikv] |                                        | gt(test.history_wti_alarm.start_time, 2022-11-10 00:00:00.000000), lt(test.history_wti_alarm.start_time, 2022-11-12 00:00:00.000000) |
|   └─TableFullScan_7     | 3.00    | cop[tikv] | table:history_wti_alarm, partition:p43 | keep order:false, stats:pseudo                                                                                                           |
+-------------------------+---------+-----------+----------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> 

mysql> explain select * from history_wti_alarm where start_time >="2022-11-10 00:00:00" and start_time<="2022-11-12 00:
+-------------------------+---------+-----------+----------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| id                      | estRows | task      | access object                          | operator info                                                                                                                            |
+-------------------------+---------+-----------+----------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_9           | 0.07    | root      |                                        | data:Selection_8                                                                                                                         |
| └─Selection_8           | 0.07    | cop[tikv] |                                        | ge(test.history_wti_alarm.start_time, 2022-11-10 00:00:00.000000), le(test.history_wti_alarm.start_time, 2022-11-12 00:00:00.000000) |
|   └─TableFullScan_7     | 3.00    | cop[tikv] | table:history_wti_alarm, partition:p43 | keep order:false, stats:pseudo                                                                                                           |
+-------------------------+---------+-----------+----------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
3
| username: dba-kit | Original post link

Could you please output the values of system_time_zone and time_zone?

MySQL []> show variables like '%zone%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| system_time_zone | Asia/Shanghai |
| time_zone        | SYSTEM        |
+------------------+---------------+
2 rows in set (0.001 sec)
4
| username: TI表弟 | Original post link

Is it possible to set the time zone to UTC?

5
| username: TI表弟 | Original post link

Zhang Zhenren, the result is like this

image
image740×312 25.4 KB

6
| username: TI表弟 | Original post link

There might be some potential bugs in TiDB. When I was setting up the cluster, I used the default Shanghai time zone. Since it involves overseas data, I created partitioned tables. During the insert operation, TiDB used the Shanghai time zone to calculate the partitions, but during table creation and querying, it used the UTC time zone. This caused some data to not be retrieved during partition pruning. After modifying the system_zone value (the documentation says it cannot be modified, but I forcibly updated it in mysql.tidb), and finally restarting the cluster, the issue was perfectly resolved.

7
| username: TI表弟 | Original post link

Proposal: Infer the System Timezone of a TiDB cluster via TZ environment variable

  • Author(s): [Zhexuan Yang](file:/Users/ji.wu/Downloads/tidb-5.4.0/docs/design/www.github.com/zhexuany)
  • Last updated: 2018/09/09
  • Discussion at: Not applicable

Abstract

When it comes to time-related calculation, it is hard for the distributed system. This proposal tries to resolve two problems: 1. timezone may be inconsistent across multiple TiDB instances, 2. performance degradation caused by pushing System down to TiKV. The impact of this proposal is changing the way of TiDB inferring system’s timezone name. Before this proposal, the default timezone name pushed down to TiKV is System when session’s timezone is not set. After this, TiDB evaluates system’s timezone name via TZ environment variable and the path of the soft link of /etc/localtime. If both of them are failed, TiDB then push UTC to TiKV.

Background

After we solved the daylight saving time issue, we found the performance degradation of TiKV side. Thanks for the investigation done by engineers from TiKV. The root cause of such performance degradation is that TiKV infers System timezone name via a third party lib, which calls a syscall and costs a lot. In our internal benchmark system, after this PR, our codebase is 1000 times slower than before. We have to address this.

Another problem needs also to be addressed is the potentially inconsistent timezone name across multiple TiDB instances. TiDB instances may reside at different timezone which could cause incorrect calculation when it comes to time-related calculation. Just getting TiDB’s system timezone could be broken. We need find a way to ensure the uniqueness of global timezone name across multiple TiDB’s timezone name and also to leverage to resolve the performance degradation.

Proposal

Firstly, we need to introduce the TZ environment. In POSIX system, the value of TZ variable can be one of the following three formats. A detailed description can be found in this link

* std offset
* std offset dst [offset], start[/time], end[/time]
* :characters

The std means the IANA timezone name; the offset means timezone offset; the dst indicates the leading timezone having daylight saving time.

In our case, which means both TiDB and TiKV, we need care the first and third formats. For answering why we do not need the second format, we need to review how Golang evaluates timezone. In time package, the method LoadLocation reads tzData from pre-specified sources(directories may contain tzData) and then builds time.Location from such tzData which already contains daylight saving time information.

In this proposal, we suggest setting TZ to a valid IANA timezone name which can be read from TiDB later. If TiDB can’t get TZ or the supply of TZ is invalid, TiDB just falls back to evaluate the path of the soft link of /etc/localtime. In addition, a warning message telling the user you should set TZ properly will be printed. Setting TZ can be done in our tidb-ansible project, it is also can be done at user side by export TZ="Asia/Shanghai". If both of them are failed, TiDB will use UTC as timezone name.

The positive side of this change is resolving performance degradation issue and ensuring the uniqueness of global timezone name in multiple TiDB instances.

The negative side is just adding a config item which is a very small matter and the user probably does not care it if we can take care of it and more importantly guarantee the correctness.

Rationale

We tried to read system timezone name by checking the path of the soft link of /etc/localtime but, sadly, failed at a corner case. The failed case is docker. In docker image, it copies the real timezone file and links to /usr/share/zoneinfo/utc. The timezone data is correct but the path is not. Regarding of UTC, Golang just returns UTC instance and will not further read tzdata from sources. This leads to a fallback solution. When we cannot evaluate from the path, we fall back to UTC.

Compatibility

It does not have compatibility issue as long as the user deploys by tidb-ansible. We may mention this in our release-node and the message printed before tidb quits, which must be easy to understand.

The upgrading process need to be handled in particular. TZ environment variable has to be set before we start new TiDB binary. In this way, the following bootstrap process can benefit from this and avoid any hazard happening.

Implementation

The implementation is relatively easy. We just get TZ environment from system and check whether it is valid or not. If it is invalid, TiDB evaluates the path of soft link of /etc/localtime. In addition, a warning message needs to be printed indicating user has to set TZ variable properly. For example, if /etc/localtime links to /usr/share/zoneinfo/Asia/Shanghai, then timezone name TiDB gets should be Asia/Shanghai.

In order to ensure the uniqueness of global timezone across multiple TiDB instances, we need to write timezone name into variable_value with variable name system_tz in mysql.tidb. This cached value can be read once TiDB finishes its bootstrap stage. A method loadLocalStr can do this job.

Open issues (if applicable)

PR of this proposal: *: write system timezone into mysql.tidb in bootstrap stage. by zhexuany · Pull Request #7638 · pingcap/tidb · GitHub PR of change TZ loading logic of golang: time: support colon at start of TZ value by BusyJay · Pull Request #27570 · golang/go · GitHub

8
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.