Statements that can be executed in TiDB result in errors when executed in TiFlash

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

Original topic: 可以在tidb执行的语句,在tiflash执行报错

| username: zzw6776

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.1

Table Structure

CREATE TABLE `inventory_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key ID',
  `site_id` bigint(20) DEFAULT NULL COMMENT 'Sorting Center ID',
  `provider_id` bigint(20) DEFAULT NULL COMMENT 'Supplier ID',
  `project_id` varchar(36) DEFAULT NULL COMMENT 'Project ID',
  `business_no` varchar(255) DEFAULT NULL COMMENT 'In/Out Warehouse Order Number',
  `business_id` bigint(20) DEFAULT NULL COMMENT 'Business ID, if it is an inbound warehouse then it is the ID of recycle_record, if it is an outbound warehouse then it is the ID of sell_record table, if it is a transfer outbound or inbound then it is the ID of allocation_item table',
  `inventory_type` tinyint(4) DEFAULT NULL COMMENT 'Operation Type 1: Inbound 2: Outbound',
  `trade_time` datetime(3) DEFAULT NULL COMMENT 'Transaction Time',
  `trade_type` tinyint(4) DEFAULT NULL COMMENT 'Transaction Type 1: Raw Material Inbound 2: Semi-finished Product Inbound 3: Finished Product Inbound',
  `product_code` varchar(50) DEFAULT NULL COMMENT 'Product Category ID',
  `product_name` varchar(255) DEFAULT NULL COMMENT 'Product Category Name',
  `product_type` int(4) DEFAULT NULL COMMENT 'Product Category Type',
  `weight_before` decimal(12,2) DEFAULT NULL COMMENT 'Weight Before Change',
  `weight` decimal(12,3) DEFAULT NULL COMMENT 'Net Weight, Unit: Kilogram',
  `weight_after` decimal(12,2) DEFAULT NULL COMMENT 'Weight After Change',
  `price` decimal(10,3) DEFAULT NULL COMMENT 'Unit Price (Unit: Yuan)',
  `amount_before` decimal(12,2) DEFAULT NULL COMMENT 'Amount Before Change',
  `amount` decimal(12,2) DEFAULT NULL COMMENT 'Amount',
  `amount_after` decimal(12,2) DEFAULT NULL COMMENT 'Amount After Change',
  `inventory_id` bigint(20) DEFAULT NULL COMMENT 'Inventory ID',
  `deleted` tinyint(4) DEFAULT '0' COMMENT 'Is Deleted 0: Normal 1: Deleted',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `modify_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creator` varchar(255) DEFAULT NULL COMMENT 'Creator',
  `statistics_year_month` int(11) NOT NULL COMMENT 'Statistics Year Month',
  `statistics_year_month_day` int(11) NOT NULL COMMENT 'Statistics Year Month Day',
  `provider_id_index` bigint(20) DEFAULT NULL COMMENT 'Supplier ID Statistics',
  `inventory_channel` tinyint(4) DEFAULT NULL COMMENT 'Inventory Type 1: Mixed 2: Self-operated 3: Purchased',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `uniq_business_id_trade_type` (`business_id`,`trade_type`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_modify_time` (`modify_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=630001 COMMENT='Inventory Detail Table';

Executing SQL

SELECT
	site_id,
	inventory_channel,
	product_code,
	product_type,
	statistics_year_month_day,
	SUBSTRING_INDEX( GROUP_CONCAT( weight_before ORDER BY create_time ASC ), ',', 1 ) AS weight_before,
	sum( CASE WHEN inventory_type = 1 THEN weight ELSE 0 END ) AS inWeight,
	sum( CASE WHEN inventory_type = 2 THEN weight ELSE 0 END ) AS outWeight,
	sum( CASE WHEN inventory_type = 3 THEN weight ELSE 0 END ) AS inventoryCountingWeight,
	SUBSTRING_INDEX( GROUP_CONCAT( weight_after ORDER BY create_time DESC ), ',', 1 ) AS weight_after,
	SUBSTRING_INDEX( GROUP_CONCAT( amount_before ORDER BY create_time ASC ), ',', 1 ) AS amount_before,
	sum( CASE WHEN inventory_type = 1 THEN amount ELSE 0 END ) AS inAmount,
	sum( CASE WHEN inventory_type = 2 THEN amount ELSE 0 END ) AS outAmount,
	sum( CASE WHEN inventory_type = 3 THEN amount ELSE 0 END ) AS inventoryCountingAmount,
	SUBSTRING_INDEX( GROUP_CONCAT( amount_after ORDER BY create_time DESC ), ',', 1 ) AS amount_after 
FROM
	inventory_record 
WHERE
	statistics_year_month_day >= '20230701' 
	AND statistics_year_month_day <= '20230731' 
GROUP BY
	site_id,
	inventory_channel,
	product_code,
	product_type,
	statistics_year_month_day 
ORDER BY
	statistics_year_month_day DESC,
	site_id DESC,
	product_type ASC 
	LIMIT 20

When using TiDB, the query can be executed, but after executing

ALTER TABLE inventory_record SET TIFLASH REPLICA 2;

an error occurs when querying again

1105 - other error for mpp stream: From MPP<query:442844490967547945,task:3>: Code: 49, e.displayText() = DB::Exception: Check schema[i].type->equals(*storage_schema[i].type) failed, schema[i].name = table_scan_10, 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,

Tried executing

select * from inventory_record limit 1

and it also reports an error

| username: h5n1 | Original post link

After altering the table, did you query directly? Check if the replica synchronization is completed in the information_schema.tiflash_replica table.

| username: zzw6776 | Original post link

Synchronization is complete. This table doesn’t have much data, it only took a few seconds.

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

Export your data into insert statements so I can test it.

| username: h5n1 | Original post link

It seems like the error is related to this column. Refer to this: 使用tiflash执行sql语句 - #3,来自 zhanggame1 - TiDB 的问答社区

| username: zzw6776 | Original post link

…I checked the specific error in the backend, and it really is this issue. I’m hesitant to perform uninstall and reinstall operations in the production environment - - It seems there are no other solutions mentioned in the thread either.

| username: h5n1 | Original post link

Your environment was also upgraded, right?

| username: zzw6776 | Original post link

Yes, upgraded directly from 5.4 to 6.5.1. I also found a solution: creating a new database works fine.

| username: h5n1 | Original post link

The official confirmation is a bug:
Here is the issue: Schema check failed for Int64 with Int32 · Issue #6806 · pingcap/tiflash · GitHub
The fix PR has just been merged into the master branch, and it will be fixed in the next LTS minor version: Improve error message for tidb TableScan schema check by SeaRise · Pull Request #7637 · pingcap/tiflash · GitHub

| username: JaySon-Huang | Original post link

It has been confirmed that modifying the precision (fsp) of a DATETIME/TIMESTAMP/TIME type column through a DDL statement may cause TiFlash query errors. Since this check was only added in v6.5, upgrading from older versions to the current 6.5 and 7.1 versions may also be affected, causing SQL that previously ran to report errors in the new version.

For now, you can use the following workarounds:

  1. Create a new column with the correct type on the original table; use the UPDATE table_name SET ... statement to copy the values from the old column to the new column; then drop the old column.
  2. Create a new table with the correct type; insert the rows from the old table into the new table; then drop the old table.

Alternatively, you can wait for the next patch version that includes the fix and upgrade to the new version.

| username: zzw6776 | Original post link

Thank you, master. Master 66666666.

| username: xiaofagn | Original post link

During TiFlash initialization, the table metadata is fetched into its own metadata directory. It is suspected that the metadata was not updated after modifying the datetime(3) precision, causing the issue. After uninstalling TiFlash and re-adding the table to TiFlash, the query worked normally.

| username: cy6301567 | Original post link

I have encountered this as well.

| username: system | Original post link

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