Modifying datetime precision causes tiflash query errors

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

Original topic: 修改datetime精度导致tiflash查询报错

| username: xiaofagn

After modifying the datetime precision of the table (previously default datetime), for example: alter table my_table order_date datetime(6);, querying TiFlash resulted in an error. Even after removing the table from TiFlash and rebuilding it, the issue persists. How can I resolve this type of problem?

MySQL [test_db]> select * from information_schema.tiflash_replica;
±----------------------±----------------±---------±--------------±----------------±----------±---------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
±----------------------±----------------±---------±--------------±----------------±----------±---------+
| test_db | my_table | 18560 | 1 | | 1 | 1 |
±----------------------±----------------±---------±--------------±----------------±----------±---------+
1 row in set (0.015 sec)

MySQL [test_db]> desc my_table;
±--------------±------------±-----±-----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------------±------------±-----±-----±--------±------+
| order_date | datetime(6) | YES | | NULL | |
| out_seri | int(11) | NO | | NULL | |
| dept_code | varchar(7) | YES | | NULL | |
| ward_code | varchar(7) | YES | | NULL | |
| section_code | varchar(7) | YES | | NULL | |
| inpatient_no | varchar(12) | NO | MUL | NULL | |
| name | varchar(32) | NO | | NULL | |
| bed_no | varchar(4) | YES | | NULL | |
| charge_code | varchar(6) | NO | MUL | NULL | |
| retprice | double | NO | | NULL | |
| amount | double | NO | | NULL | |
| infant_flag | varchar(1) | YES | | NULL | |
| drawer | varchar(5) | YES | | NULL | |
| keeper | varchar(5) | YES | | NULL | |
| date1 | datetime(6) | YES | | NULL | |
| date2 | datetime(6) | YES | | NULL | |
| always_flag | varchar(1) | NO | | NULL | |
| drug_class | varchar(1) | YES | | NULL | |
| acct_sign | varchar(1) | YES | | NULL | |
| total_flag | varchar(1) | YES | | NULL | |
| admiss_times | smallint(6) | YES | | NULL | |
| supply_code | varchar(1) | YES | | NULL | |
| act_order_no | double | YES | MUL | NULL | |
| page_no | double | YES | | NULL | |
| self_flag | varchar(1) | YES | | NULL | |
| separate_flag | varchar(1) | YES | | NULL | |
| suprice_flag | varchar(1) | YES | | NULL | |
| drug_flag | varchar(1) | YES | | NULL | |
| pay_self | varchar(1) | YES | | NULL | |
| group_no | varchar(2) | YES | | NULL | |
| confirm_time | datetime(6) | YES | MUL | NULL | |
| serial | varchar(2) | YES | | NULL | |
| doctor_name | varchar(5) | YES | | NULL | |
±--------------±------------±-----±-----±--------±------+
33 rows in set (0.001 sec)

MySQL [test_db]> explain select count(0) from my_table where confirm_time >‘2021-01-01’;
±-----------------------------±------------±------------------±----------------------±-----------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-----------------------------±------------±------------------±----------------------±-----------------------------------------------------------------------------------+
| StreamAgg_27 | 1.00 | root | | funcs:count(Column#38)->Column#35 |
| └─TableReader_28 | 1.00 | root | | data:StreamAgg_11 |
| └─StreamAgg_11 | 1.00 | batchCop[tiflash] | | funcs:count(0)->Column#38 |
| └─Selection_26 | 20418375.92 | batchCop[tiflash] | | gt(test_db.my_table.confirm_time, 2021-01-01 00:00:00.000000) |
| └─TableFullScan_25 | 26628350.00 | batchCop[tiflash] | table:my_table | keep order:false |
±-----------------------------±------------±------------------±----------------------±-----------------------------------------------------------------------------------+
5 rows in set (0.028 sec)

MySQL [test_db]> select count(0) from my_table where confirm_time >‘2021-01-01’;
ERROR 1105 (HY000): Check schema[i].type->equals(*storage_schema[i].type) failed, schema[i].name = table_scan_0, schema[i].type->getName() = Nullable(MyDateTime(6)), storage_schema[i].name = confirm_time, storage_schema[i].type->getName() = Nullable(MyDateTime(0))
MySQL [test_db]>

| username: xiaofagn | Original post link

Reply:
When TiFlash initializes, it fetches the table metadata into its own metadata directory. I suspect that the metadata did not update after modifying the datetime(6) precision, which caused the issue. After uninstalling TiFlash and re-adding the table to TiFlash, the query worked normally.

| username: ljluestc | Original post link

The encountered error indicates a schema mismatch between the table definition and the TiFlash storage schema for the column confirm_time. It appears that when the precision of the datetime column was changed from the default datetime to datetime(6), TiFlash still expects the old datetime type.

To resolve this issue, you need to update the TiFlash table’s storage schema to match the new datetime(6) definition. Please follow these steps to resolve the issue:

  1. Check the current storage schema of the TiFlash table using the following command:
SELECT * FROM information_schema.tiflash_replica;
  1. Confirm that the confirm_time column in the storage schema is still defined as datetime(0). This might be the cause of the schema mismatch.

  2. To update the TiFlash storage schema, you can follow these steps:

    a. Disable the TiFlash replica for the affected table:

    ALTER TABLE my_table SET TIFLASH REPLICA 0;
    

    b. Drop the TiFlash replica:

    ALTER TABLE my_table DROP TIFLASH REPLICA;
    

    c. Recreate the TiFlash replica with the updated schema:

    ALTER TABLE my_table SET TIFLASH REPLICA 1;
    

After performing these steps, the TiFlash replica should be recreated with the updated schema, and the error should be resolved. You can then try running the query again to verify if it works as expected.

Please note that modifying the TiFlash storage schema may impact TiFlash performance and data replication, so ensure to perform these changes during a maintenance window or at an appropriate time. Additionally, it is a good practice to back up data before making schema changes.

| username: redgame | Original post link

Learned…

| username: MrSylar | Original post link

Hold on, which DDL operations require this kind of action for TiFlash?

| username: system | Original post link

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