Data inconsistency after adding index in ingest mode

[jackysp]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> SET time_zone = '-06:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone, @@global.system_time_zone;
+--------------------+---------------------+---------------------------+
| @@global.time_zone | @@session.time_zone | @@global.system_time_zone |
+--------------------+---------------------+---------------------------+
| SYSTEM             | -06:00              | Asia/Shanghai             |
+--------------------+---------------------+---------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists  timetest;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table timetest (`src` varchar(48),`t` timestamp,`timezone` varchar(100));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into timetest values('2023-07-29 23:15:30 -0600','2023-07-29 23:15:30 -0600','-6:00');
Query OK, 1 row affected (0.01 sec)

mysql> create index idx on timetest(t);

Query OK, 0 rows affected (2.95 sec)

mysql> admin check table timetest;
ERROR 8223 (HY000): data inconsistency in table: timetest, index: idx, handle: 1, index-values:"handle: 1, values: [KindMysqlTime 2023-07-30 19:15:30]" != record-values:"handle: 1, values: [KindMysqlTime 2023-07-30 13:15:30]"
mysql> admin show ddl jobs;
+--------+---------+-------------------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME | TABLE_NAME              | JOB_TYPE               | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME         | START_TIME          | END_TIME            | STATE  |
+--------+---------+-------------------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
|    100 | test    | timetest                | add index /* ingest */ | public       |         2 |       98 |         1 | 2023-08-13 05:36:22 | 2023-08-13 05:36:22 | 2023-08-13 05:36:25 | synced |
|     99 | test    | timetest                | create table           | public       |         2 |       98 |         0 | 2023-08-13 05:36:22 | 2023-08-13 05:36:22 | 2023-08-13 05:36:22 | synced |
|     97 | mysql   | tidb_runaway_watch_done | create table           | public       |         1 |       96 |         0 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | synced |
|     95 | mysql   | tidb_timers             | create table           | public       |         1 |       94 |         0 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | synced |
|     93 | mysql   | tidb_runaway_queries    | create table           | public       |         1 |       92 |         0 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | synced |
|     91 | mysql   | tidb_runaway_watch      | create table           | public       |         1 |       90 |         0 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | synced |
|     89 | mysql   | tidb_import_jobs        | create table           | public       |         1 |       88 |         0 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | synced |
|     87 | mysql   | load_data_jobs          | create table           | public       |         1 |       86 |         0 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | synced |
|     85 | mysql   | tidb_global_task        | create table           | public       |         1 |       84 |         0 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | 2023-08-11 08:01:31 | synced |
|     83 | mysql   | tidb_ttl_job_history    | create table           | public       |         1 |       82 |         0 | 2023-08-11 08:01:30 | 2023-08-11 08:01:30 | 2023-08-11 08:01:31 | synced |
+--------+---------+-------------------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
10 rows in set (0.01 sec)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.4.0-alpha
Edition: Community
Git Commit Hash: c7c7000165aba353eb7b39a6c11371e1adc7b6b1
Git Branch: heads/refs/tags/v7.4.0-alpha
UTC Build Time: 2023-08-10 14:55:04
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

No error of admin check statement.

3. What did you see instead (Required)

error, timestamp column set the wrong timezone in index.

4. What is your TiDB version? (Required)

c7c7000

[lance6716]

lightning always use its owner session to encode KV pairs. We should set all related session variables to it, or use kernel's session

tidb/br/pkg/lightning/backend/kv/session.go

Lines 263 to 266 in 61ecb59

	// Session is a trimmed down Session type which only wraps our own trimmed-down
	// transaction type and provides the session variables to the TiDB library
	// optimized for Lightning.
	type Session struct {

[jackysp]

So, what you mean is that lightning didn't get the timezone from TiDB connection?

[lance6716]

So, what you mean is that lightning didn't get the timezone from TiDB connection?

Yes, just a guess. Will take a look later.

[lance6716]

So, what you mean is that lightning didn't get the timezone from TiDB connection?

Sorry I just checked DDL worker will do the encoding work rather than lightning. Seems it's a issue of DDL

[jackysp]

@tangenta filed a PR to fix it #46055