Some tables in information_schema doesn't present times in correct timezone.

[MyonKeminta]

Bug Report

1. Minimal reproduce step (Required)

• Start some transaction, or construct some deadlock errors
• select * from information_schema.tidb_trx or select * from information_schema.deadlocks

2. What did you expect to see? (Required)

For the Timestamp typed fields, it shows the time according to the timezone in the current session.

3. What did you see instead (Required)

It always shows a fixed timezone.

4. What is your TiDB version? (Required)

master

This issue is initially found in Lock View while it's in development. But later it's found that this issue also exists in some other system tables.

Example (statements_summary):

mysql> select SUMMARY_BEGIN_TIME, DIGEST from information_schema.statements_summary where DIGEST = "e5796985ccafe2f71126ed6c
0ac939ffa015a8c0744a24b7aee6d587103fd2f7";                                                                                  
+---------------------+------------------------------------------------------------------+                                  
| SUMMARY_BEGIN_TIME  | DIGEST                                                           |                                  
+---------------------+------------------------------------------------------------------+                                  
| 2021-05-28 05:30:00 | e5796985ccafe2f71126ed6c0ac939ffa015a8c0744a24b7aee6d587103fd2f7 |                                  
+---------------------+------------------------------------------------------------------+                                  
1 row in set (0.01 sec)                                                                                                     
                                                                                                                            
mysql> set @@time_zone = "+08:00";                                                                                          
Query OK, 0 rows affected (0.01 sec)                                                                                        
                                                                                                                            
mysql> select SUMMARY_BEGIN_TIME, DIGEST from information_schema.statements_summary where DIGEST = "e5796985ccafe2f71126ed6c
0ac939ffa015a8c0744a24b7aee6d587103fd2f7";                                                                                  
+---------------------+------------------------------------------------------------------+                                  
| SUMMARY_BEGIN_TIME  | DIGEST                                                           |                                  
+---------------------+------------------------------------------------------------------+                                  
| 2021-05-28 05:30:00 | e5796985ccafe2f71126ed6c0ac939ffa015a8c0744a24b7aee6d587103fd2f7 |                                  
+---------------------+------------------------------------------------------------------+                                  
1 row in set (0.00 sec)                                                                                                     

In normal user-created tables (create table t (id int primary key, t timestamp)):

mysql> set @@time_zone = "+00:00";       
Query OK, 0 rows affected (0.00 sec)     
                                         
mysql> select * from t;                  
+----+---------------------+             
| id | t                   |             
+----+---------------------+             
|  1 | 2021-05-28 05:38:59 |             
+----+---------------------+             
1 row in set (0.00 sec)                  
                                         
mysql> set @@time_zone = "+08:00";       
Query OK, 0 rows affected (0.01 sec)     
                                         
mysql> select * from t;                  
+----+---------------------+             
| id | t                   |             
+----+---------------------+             
|  1 | 2021-05-28 13:38:59 |             
+----+---------------------+             
1 row in set (0.01 sec)                                                        

It's also found that the implementation of statements_summary uses time.Unix directly without any timezone conversion:

tidb/util/stmtsummary/statement_summary.go

Lines 871 to 873 in 348abf0

	return types.MakeDatums(
	types.NewTime(types.FromGoTime(time.Unix(ssElement.beginTime, 0)), mysql.TypeTimestamp, 0),
	types.NewTime(types.FromGoTime(time.Unix(ssElement.endTime, 0)), mysql.TypeTimestamp, 0),

As I understood it, it will cause the query result depends on the system timezone of the host of that TiDB process.

[dveeden]

Looks like this was fixed at least for this table/column.

sql> select SUMMARY_BEGIN_TIME, DIGEST from information_schema.statements_summary where DIGEST = "94bc1c9a284ad16760e858cabb1ed6e607a87eaaed878476679ef2e1b923fa04";
+---------------------+------------------------------------------------------------------+
| SUMMARY_BEGIN_TIME  | DIGEST                                                           |
+---------------------+------------------------------------------------------------------+
| 2023-11-17 11:00:00 | 94bc1c9a284ad16760e858cabb1ed6e607a87eaaed878476679ef2e1b923fa04 |
+---------------------+------------------------------------------------------------------+
1 row in set (0.0020 sec)

sql> set @@time_zone = "+08:00";
Query OK, 0 rows affected (0.0004 sec)

sql> select SUMMARY_BEGIN_TIME, DIGEST from information_schema.statements_summary where DIGEST = "94bc1c9a284ad16760e858cabb1ed6e607a87eaaed878476679ef2e1b923fa04";
+---------------------+------------------------------------------------------------------+
| SUMMARY_BEGIN_TIME  | DIGEST                                                           |
+---------------------+------------------------------------------------------------------+
| 2023-11-17 18:00:00 | 94bc1c9a284ad16760e858cabb1ed6e607a87eaaed878476679ef2e1b923fa04 |
+---------------------+------------------------------------------------------------------+
1 row in set (0.0018 sec)

sql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.6.0-alpha-218-g5a2d7da5c6
Edition: Community
Git Commit Hash: 5a2d7da5c62d244e273cc4d93351e0bc5774da5c
Git Branch: master
UTC Build Time: 2023-11-17 10:09:22
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: unistore
1 row in set (0.0011 sec)

[dveeden]

This might be a data type issue.

For these columns that hold a date and time to work correctly they should be a datetime or timestamp. For varchar things like timezones and functions like DATE_ADD(), etc might not work as expected.

sql> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='INFORMATION_SCHEMA' AND COLUMN_NAME LIKE '%END\_TIME' or COLUMN_NAME LIKE '%START\_TIME' ORDER BY TABLE_NAME, COLUMN_NAME;
+------------------------------------+--------------------+-----------+
| TABLE_NAME                         | COLUMN_NAME        | DATA_TYPE |
+------------------------------------+--------------------+-----------+
| ANALYZE_STATUS                     | END_TIME           | datetime  |
| ANALYZE_STATUS                     | START_TIME         | datetime  |
| CLUSTER_INFO                       | START_TIME         | varchar   |
| CLUSTER_STATEMENTS_SUMMARY         | SUMMARY_END_TIME   | timestamp |
| CLUSTER_STATEMENTS_SUMMARY_EVICTED | END_TIME           | timestamp |
| CLUSTER_STATEMENTS_SUMMARY_HISTORY | SUMMARY_END_TIME   | timestamp |
| CLUSTER_TIDB_TRX                   | START_TIME         | timestamp |
| CLUSTER_TIDB_TRX                   | WAITING_START_TIME | timestamp |
| DDL_JOBS                           | END_TIME           | datetime  |
| DDL_JOBS                           | START_TIME         | datetime  |
| RUNAWAY_WATCHES                    | END_TIME           | varchar   |
| RUNAWAY_WATCHES                    | START_TIME         | varchar   |
| STATEMENTS_SUMMARY                 | SUMMARY_END_TIME   | timestamp |
| STATEMENTS_SUMMARY_EVICTED         | END_TIME           | timestamp |
| STATEMENTS_SUMMARY_HISTORY         | SUMMARY_END_TIME   | timestamp |
| TIDB_TRX                           | START_TIME         | timestamp |
| TIDB_TRX                           | WAITING_START_TIME | timestamp |
+------------------------------------+--------------------+-----------+
17 rows in set (0.0742 sec)

An example of this with mysqlsh --column-type-info ...:

sql> SELECT START_TIME FROM information_schema.CLUSTER_INFO WHERE TYPE='tidb';
Field 1
Name:      `START_TIME`
Org_name:  `START_TIME`
Catalog:   `def`
Database:  `information_schema`
Table:     `CLUSTER_INFO`
Org_table: `CLUSTER_INFO`
Type:      String
DbType:    VAR_STRING
Collation: utf8mb4_bin (46)
Length:    128
Decimals:  0
Flags:     

+---------------------------+
| START_TIME                |
+---------------------------+
| 2023-11-17T11:28:00+01:00 |
+---------------------------+
1 row in set (0.0020 sec)

sql> SELECT SUMMARY_BEGIN_TIME FROM information_schema.statements_summary LIMIT 1;
Field 1
Name:      `SUMMARY_BEGIN_TIME`
Org_name:  `SUMMARY_BEGIN_TIME`
Catalog:   `def`
Database:  `information_schema`
Table:     `STATEMENTS_SUMMARY`
Org_table: `STATEMENTS_SUMMARY`
Type:      DateTime
DbType:    TIMESTAMP
Collation: binary (63)
Length:    26
Decimals:  0
Flags:     NOT_NULL 

+---------------------+
| SUMMARY_BEGIN_TIME  |
+---------------------+
| 2023-11-17 11:00:00 |
+---------------------+
1 row in set (0.0017 sec)

Related: #29771