Unable to query TiDB data at a specific time point within the given time range

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] v6.1.2
[Encountered Issue: Problem Phenomenon and Impact]
On November 10, 2022, at 00:59:59.996, there was a data entry, but querying the field with > “2022-11-10 00:00:00.000” did not return any results. Both the database timezone and the local timezone are set to UTC.

mysql> SELECT start_time FROM history_wti_alarm WHERE start_time>='2022-11-09 00:00:00.000' and alarm_id='xxx-1668041999996-FCTA-1-WTI' \G;
*************************** 1. row ***************************
start_time: 2022-11-10 00:59:59.996
1 row in set (0.04 sec)

ERROR:
No query specified

mysql> SELECT start_time FROM history_wti_alarm WHERE start_time>='2022-11-10 00:00:00.000' and alarm_id='xxx-1668041999996-FCTA-1-WTI' \G;
Empty set (0.03 sec)

ERROR:
No query specified

mysql> select UNIX_TIMESTAMP("2022-11-10 00:59:59.996");
+-------------------------------------------+
| UNIX_TIMESTAMP("2022-11-10 00:59:59.996") |
+-------------------------------------------+
|                            1668041999.996 |
+-------------------------------------------+
1 row in set (0.03 sec)

mysql> select UNIX_TIMESTAMP("2022-11-10 00:00:00.000");
+-------------------------------------------+
| UNIX_TIMESTAMP("2022-11-10 00:00:00.000") |
+-------------------------------------------+
|                            1668038400.000 |
+-------------------------------------------+
1 row in set (0.03 sec)

mysql>

We have performed range partitioning on start_time

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`),
  KEY `idx_wti_st` (`wti_code`,`start_time`),
  KEY `idx_end_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
);

Execution Plan

mysql> EXPLAIN analyze SELECT count(*) FROM history_wti_alarm WHERE start_time>='2022-11-10 00:00:00' and start_time<'2022-11-10 01:00:00';
+-----------------------------+-------------+---------+-----------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+-----------+------+
| id                          | estRows     | actRows | task      | access object                                             | execution info                                                                                                                                                                                                                          | operator info                                                                           | memory    | disk |
+-----------------------------+-------------+---------+-----------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_34                | 1.00        | 1       | root      |                                                           | time:2.82ms, loops:2                                                                                                                                                                                                                    | funcs:count(Column#48)->Column#45                                                       | 388 Bytes | N/A  |
| └─IndexReader_35            | 1.00        | 0       | root      | partition:p43                                             | time:2.82ms, loops:1, cop_task: {num: 1, max: 2.78ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.77ms, copr_cache_hit_ratio: 0.00}                                                                                                           | index:StreamAgg_10                                                                      | 206 Bytes | N/A  |
|   └─StreamAgg_10            | 1.00        | 0       | cop[tikv] |                                                           | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 0, total_process_keys_size: 0, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 9, read_count: 1, read_byte: 64.0 KB}}} | funcs:count(1)->Column#48                                                               | N/A       | N/A  |
|     └─IndexRangeScan_32     | 44906239.45 | 0       | cop[tikv] | table:history_wti_alarm, index:idx_start_time(start_time) | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                            | range:[2022-11-10 00:00:00.000,2022-11-10 01:00:00.000), keep order:false, stats:pseudo | N/A       | N/A  |
+-----------------------------+-------------+---------+-----------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+-----------+------+
4 rows in set (0.04 sec)

The most surprising part is the following:

mysql> SELECT start_time FROM history_wti_alarm WHERE start_time<='2022-11-10 00:00:00.000' and alarm_id='xxx-1668041999996-FCTA-1-WTI' \G;
Empty set (0.03 sec)

ERROR:
No query specified

mysql> SELECT start_time FROM history_wti_alarm WHERE start_time>='2022-11-10 00:00:00.000' and alarm_id='xxx-1668041999996-FCTA-1-WTI' \G;
Empty set (0.06 sec)

ERROR:
No query specified

image
image1380×350 41.6 KB

[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

2
| username: 我是咖啡哥 | Original post link

Could you provide the insert statement for the abnormal data that you mentioned cannot be queried?

3
| username: xfworld | Original post link

PRIMARY KEY (alarm_id,start_time),
UNIQUE KEY uniq_alarm_id (alarm_id,start_time),

This is redundant, and also
KEY idx_start_time (start_time), will cause index hotspots.

However, this has nothing to do with your question.

What if we read directly from the partition? Can we read it, something like this:
SELECT * FROM employees PARTITION (p1) where …

4
| username: Kongdom | Original post link

Have you ever restored data? The last time I encountered this problem, it was caused by data corruption due to data restoration.

5
| username: TI表弟 | Original post link

Theoretically, the data should fall in partition p43, but in reality, it can only be found in partition p42.
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’)),

Data timestamp: 2022-11-10 00:59:59.996

6
| username: xfworld | Original post link

Two scenarios:

  1. Does this issue occur without partitioning?
  2. What if you use DateTime instead of TIMESTAMP?

I suggest trying it out. If DateTime works, there might be a bug with partitioning…

7
| username: TI表弟 | Original post link

Brother K Coffee, it was synchronized by TiDB DM. insert () values()

8
| username: xfworld | Original post link

Directly create a new structure,
insert into new_table select * from this_table

9
| username: TI表弟 | Original post link
  1. The data volume is huge, at the 2 billion level, and we hope to use partition pruning to improve query performance.
  2. Timestamp is associated with time zones and is more suitable as a partition. We have used datetime for partitioning before, but it does not support partition pruning, which was a pitfall we encountered.
10
| username: xfworld | Original post link

Then I suspect it’s a precision issue.

Here’s the problem, try this:
floor(UNIX_TIMESTAMP(start_time))

11
| username: TI表弟 | Original post link

It looks fine.

12
| username: xfworld | Original post link

The defined time and the time you input are not the same…

So, is it misaligned?

13
| username: TI表弟 | Original post link

*************************** 1. row ***************************
floor(UNIX_TIMESTAMP(start_time)): 1668041999

14
| username: TI表弟 | Original post link

Yes, the data has been placed in the wrong partition.

15
| username: xfworld | Original post link

Then it can’t be found… What a pit.

16
| username: TI表弟 | Original post link

The data is synchronized through TiDB DM. During the data comparison process, it was found that this issue does not occur with all data, only with some of it. :see_no_evil:

The advantage is that TiDB’s distributed nature eliminates the need for sharding, and TiFlash has very high query performance. The downside is that there are quite a few pitfalls at the moment, and I’ve basically had to ask questions here at every step.

17
| username: 裤衩儿飞上天 | Original post link

I’m here to observe and learn how the experts handle such bizarre issues.

18
| username: xfworld | Original post link

Try this:

CEILING()

CEILING(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”))

Give it a shot…

19
| username: TI表弟 | Original post link

mysql> select CEILING(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”));
±---------------------------------------------------+
| CEILING(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”)) |
±---------------------------------------------------+
| 1668042000 |
±---------------------------------------------------+

image
image1380×256 19 KB

20
| username: TI表弟 | Original post link

The impact of floor and ceil is at the second level. My data is already at 00:59:59.996, so no matter how you floor or ceil it, it should fall on p43.