Schema check failed for MyDateTime(3) with MyDateTime(0)

[JaySon-Huang]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

For 6.5/7.1 versions

CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
);
insert into t(id) values (10);
alter table t set tiflash replica 1;

set tidb_isolation_read_engines='tiflash';
select * from t;

alter table t modify column create_time datetime(3);
select * from t;

For v6.1 or earlier versions

CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
);
insert into t(id) values (10);
alter table t set tiflash replica 1;
alter table t compact tiflash replica; -- generate a DMFile

set tidb_isolation_read_engines='tiflash';
select * from t;

alter table t modify column create_time datetime(3) not null; -- nullable to not nullable
-- when tiflash try to read the column from dmfile, it happens to this bug
select * from t;

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

The query success

3. What did you see instead (Required)

The query fail with error message

TiDB [email protected]:test> select * from inventory_record;
(1105, 'other error for mpp stream: From MPP<query:<query_ts:1689327674311914000, local_query_id:6, server_id:987888, start_ts:442847113852485633>,task_id:1>: Code: 49, e.displayText() = DB::Exception: Check schema[i].type->equals(*storage_schema[i].type) failed, schema[i].name = table_scan_1, schema[i].type->getName() = Nullable(MyDateTime(3)), storage_schema[i].name = create_time, storage_schema[i].type->getName() = Nullable(MyDateTime(0)), e.what() = DB::Exception,')

4. What is your TiFlash version? (Required)

v6.5.x, v7.1.x, nightly, v6.1.x

[JaySon-Huang]

Seems the nightly version is not affected by this bug

[JaySon-Huang]

[2023/07/14 17:51:02.571 +08:00] [DEBUG] [DAGStorageInterpreter.cpp:985] ["not OK, syncing schemas."] [source="MPP<query:<query_ts:1689328262567407000, local_query_id:3, server_id:3801577, start_ts:442847268056334348>,task_id:1>"] [thread_id=34]
[2023/07/14 17:51:02.572 +08:00] [INFO] [TiDBSchemaSyncer.h:149] ["Start to sync schemas. current version is: 46 and try to sync schema version to: 47"] [source="keyspace=4294967295"] [thread_id=34]
[2023/07/14 17:51:02.572 +08:00] [DEBUG] [TiDBSchemaSyncer.h:206] ["Try load schema diffs."] [source="keyspace=4294967295"] [thread_id=34]
[2023/07/14 17:51:02.572 +08:00] [DEBUG] [TiDBSchemaSyncer.h:217] ["End load schema diffs with total 1 entries."] [source="keyspace=4294967295"] [thread_id=34]
[2023/07/14 17:51:02.572 +08:00] [DEBUG] [SchemaGetter.cpp:250] ["Get DB Info from TiKV : {\"id\":2,\"db_name\":{\"O\":\"test\",\"L\":\"test\"},\"charset\":\"utf8mb4\",\"collate\":\"utf8mb4_bin\",\"state\":5,\"policy_ref_info\":null}"] [thread_id=34]
[2023/07/14 17:51:02.572 +08:00] [DEBUG] [SchemaGetter.cpp:266] ["Get Table Info from TiKV : {\"id\":88,\"name\":{\"O\":\"t\",\"L\":\"t\"},\"charset\":\"utf8mb4\",\"collate\":\"utf8mb4_bin\",\"cols\":[{\"id\":1,\"name\":{\"O\":\"id\",\"L\":\"id\"},\"offset\":0,\"origin_default\":null,\"origin_default_bit\":null,\"default\":null,\"default_bit\":null,\"default_is_expr\":false,\"generated_expr_string\":\"\",\"generated_stored\":false,\"dependences\":null,\"type\":{\"Tp\":8,\"Flag\":515,\"Flen\":20,\"Decimal\":0,\"Charset\":\"binary\",\"Collate\":\"binary\",\"Elems\":null,\"ElemsIsBinaryLit\":null,\"Array\":false},\"state\":5,\"comment\":\"\",\"hidden\":false,\"change_state_info\":null,\"version\":2},{\"id\":2,\"name\":{\"O\":\"create_time\",\"L\":\"create_time\"},\"offset\":1,\"origin_default\":null,\"origin_default_bit\":null,\"default\":null,\"default_bit\":null,\"default_is_expr\":false,\"generated_expr_string\":\"\",\"generated_stored\":false,\"dependences\":null,\"type\":{\"Tp\":12,\"Flag\":128,\"Flen\":23,\"Decimal\":3,\"Charset\":\"binary\",\"Collate\":\"binary\",\"Elems\":null,\"ElemsIsBinaryLit\":null,\"Array\":false},\"state\":5,\"comment\":\"\",\"hidden\":false,\"change_state_info\":null,\"version\":2}],\"index_info\":null,\"constraint_info\":null,\"fk_info\":null,\"state\":5,\"pk_is_handle\":true,\"is_common_handle\":false,\"common_handle_version\":0,\"comment\":\"\",\"auto_inc_id\":0,\"auto_id_cache\":0,\"auto_rand_id\":0,\"max_col_id\":2,\"max_idx_id\":0,\"max_fk_id\":0,\"max_cst_id\":0,\"update_timestamp\":442847268043227142,\"ShardRowIDBits\":0,\"max_shard_row_id_bits\":0,\"auto_random_bits\":0,\"auto_random_range_bits\":0,\"pre_split_regions\":0,\"partition\":null,\"compression\":\"\",\"view\":null,\"sequence\":null,\"Lock\":null,\"version\":5,\"tiflash_replica\":{\"Count\":1,\"LocationLabels\":[],\"Available\":true,\"AvailablePartitionIDs\":null},\"is_columnar\":false,\"temp_table_type\":0,\"cache_table_status\":0,\"policy_ref_info\":null,\"stats_options\":null,\"exchange_partition_info\":null,\"ttl_info\":null}"] [thread_id=34]
[2023/07/14 17:51:02.572 +08:00] [INFO] [SchemaBuilder.cpp:327] ["Altering table test(2).t(88)"] [source="keyspace=4294967295"] [thread_id=34]
[2023/07/14 17:51:02.572 +08:00] [INFO] [SchemaBuilder.cpp:334] ["No schema change detected for table test(2).t(88), not altering"] [source="keyspace=4294967295"] [thread_id=34]
[2023/07/14 17:51:02.572 +08:00] [INFO] [TiDBSchemaSyncer.h:170] ["End sync schema, version has been updated to 46(latest diff is empty)"] [source="keyspace=4294967295"] [thread_id=34]

[JaySon-Huang]

The root cause is that TiFlash does not detect the datatype change from mydatetime(0) to mydatetime(3) or timestamp(0) to timestamp(3) or time(0) to time(3).

tiflash/dbms/src/TiDB/Schema/SchemaBuilder.cpp

Lines 124 to 144 in 338634a

	bool typeDiffers(const TiDB::ColumnInfo & a, const TiDB::ColumnInfo & b)
	{
	if (a.tp != b.tp || a.hasNotNullFlag() != b.hasNotNullFlag() || a.hasUnsignedFlag() != b.hasUnsignedFlag())
	return true;
	if (a.tp == TypeEnum || a.tp == TypeSet)
	{
	if (a.elems.size() != b.elems.size())
	return true;
	for (size_t i = 0; i < a.elems.size(); i++)
	{
	if (a.elems[i].first != b.elems[i].first)
	return true;
	}
	return false;
	}
	else if (a.tp == TypeNewDecimal)
	{
	return a.flen != b.flen || a.decimal != b.decimal;
	}
	return false;
	}

Enlarging the fsp of mydatetime/timestamp/time is a lossless change, TiFlash should detect and change the data type in place. Narrowing down the fsp of mydatetime/timestamp/time is a lossy change, TiDB will add a temporary column and reorganize the column data as other lossy type change.

Seems the nightly version is not affected by this bug

The version after v7.2.0 is not affected by this bug, because #7437 change the way of handling column type change
The nightly version is also affected, in another way

(1105, 'other error for mpp stream: From MPP<gather_id:<gather_id:1, query_ts:1689587648738941000, local_query_id:4, server_id:835035, start_ts:442915264584220674>,task_id:1>: Code: 48, e.displayText() = DB::Exception: Reading mismatch data type pack. Cast from Nullable(MyDateTime(0)) to Nullable(MyDateTime(3)) is NOT supported, column_id=2: (while reading from DTFile: /Users/jayson/.tiup/data/TkG8bcE/tiflash-0/data/data/t_96/stable/dmf_4), e.what() = DB::Exception,')

[JaySon-Huang]

Affected LTS versions:

• v6.5.0/v6.5.1/v6.5.2/v6.5.3
• v7.1.0/v7.1.1

Workaround (choose 1 action from the below):

1. Disable the Planner Interpreter in TiFlash by following config. Disabling this doesn't matter for those affected version

[profiles]
[profiles.default]
enable_planner = false

2. Create a new column with the correct column type; copy the values from old column to the new column using UPDATE table_name SET ... statement; drop the old column
3. Create a new table with the correct columns type; copy the rows from old table to the new table; drop the old table

[JaySon-Huang]

The strict check was introduced by f470b46 since v6.3.0. So this bug does not effects 5.x/6.1.x versions

[JaySon-Huang]

In the v6.1.x or previous version, it may raise exception message like:

• Reading mismatch data type pack. Cast from Nullable(MyDateTime(0)) to Nullable(MyDateTime(3)) is NOT supported)
• Storage engine DeltaMerge doesn't support lossy data type modification. Try to modify column ... from Nullable(MyDateTime(0)) to MyDateTime(3)

Minimal reproduce step for v6.1 or earlier versions

CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
);
insert into t(id) values (10);
alter table t set tiflash replica 1;
alter table t compact tiflash replica; -- generate a DMFile

set tidb_isolation_read_engines='tiflash';
select * from t;

alter table t modify column create_time datetime(3) not null; -- nullable to not nullable
-- when tiflash try to read the column from dmfile, it happens to this bug
select * from t;

[seiya-annie]

/found customer

[seiya-annie]

/found community