Executing SQL Statements with TiFlash

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

Original topic: 使用tiflash执行sql语句

| username: johnwa-CD

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.3
[Reproduction Path] Executing SQL results in an error, the same error persists, but it executes normally after removing TiFlash.

select * from if_user_installreferrer limit 100
> 1105 - other error for mpp stream: From MPP<query:442333193923723265,task:2>: Code: 49, e.displayText() = DB::Exception: Check schema[i].type->equals(*storage_schema[i].type) failed, schema[i].name = table_scan_23, schema[i].type->getName() = Nullable(MyDateTime(3)), storage_schema[i].name = remarketingtime, storage_schema[i].type->getName() = Nullable(MyDateTime(0)), e.what() = DB::Exception,
> Time: 10.03s

[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: tidb菜鸟一只 | Original post link

How many TiFlash nodes do you have, and are all the nodes in a normal state?

| username: zhanggame1 | Original post link

Try removing TiFlash and adding it again.

| username: redgame | Original post link

The type of the “remarketingtime” column is different between the two.

| username: johnwa-CD | Original post link

What is the reason for this? This field is of DateTime type, and we can’t manually configure TiFlash either.

| username: johnwa-CD | Original post link

2 nodes. It doesn’t seem to be a problem. It’s just that after upgrading from 6.1.5 to 6.5.3, this issue was discovered.

| username: johnwa-CD | Original post link

Are you referring to uninstalling TiFlash and then re-adding it, or removing the table from TiFlash and then adding it back?

1105 - other error for mpp stream: From MPPquery:442341727299961673:8,task: Code: 49, e.displayText() = DB::Exception: Check schema[i].type->equals(*storage_schema[i].type) failed, schema[i].name = table_scan_9, schema[i].type->getName() = Nullable(MyDateTime(3)), storage_schema[i].name = remarketingtime, storage_schema[i].type->getName() = Nullable(MyDateTime(0)), e.what() = DB::Exception,

| username: zhanggame1 | Original post link

Uninstalling and re-adding TiFlash is not troublesome.

| username: johnwa-CD | Original post link

I’ll try it right away.

| username: johnwa-CD | Original post link

Thanks, bro. It turns out that I need to uninstall and reinstall TiFlash. This is quite troublesome because I have too many things using TiFlash, and reloading all the TiFlash data took several hours.

| username: zhanggame1 | Original post link

It’s good that it’s settled.

| username: 人如其名 | Original post link

@Billmay Is this considered a BUG? Should we consult an expert?

| username: johnwa-CD | Original post link

Yesterday, the cluster was upgraded from 6.1.5 to 6.5.3. It indicated that the upgrade was successful, but after the upgrade, the cluster basically crashed. Up until now, there have been various issues, which are very troublesome. The cluster data is several tens of terabytes.

Currently, the query performance feels like it’s less than half of what it used to be, but the IO, memory, and CPU of the KV are all normal, with low usage rates. Now, writing is also extremely slow and abnormal in various ways.

As for the TiFlash issue, fortunately, someone reminded me to reinstall it. Initially, I added two nodes, but they were not functioning properly. Now, I only dare to use one TiFlash, which is running relatively normally.

The prompt doesn’t seem to indicate an issue with our environment, and reinstalling actually resolved it. Now, I don’t dare to make any more changes, but at least TiFlash is working.

| username: zhanggame1 | Original post link

Looking at the cases on the forum, upgrading TiDB and TiFlash is quite prone to issues.

| username: linnana | Original post link

Learned.

| username: Hacker_bhcKiBGm | Original post link

Here is a similar issue: Schema check failed for Int64 with Int32 · Issue #6806 · pingcap/tiflash · GitHub
You can try setting profiles.default.enable_planner to 0 in the TiFlash config to see if it resolves the issue.

By the way, has the field type of the error field been changed?

| username: johnwa-CD | Original post link

Reinstalling can solve the problem, so it can be estimated that it is not related to the business.
This is purely a TiFlash issue…

| username: JaySon-Huang | Original post link

Confirmed that modifying the precision (fsp) of a DATETIME/TIMESTAMP/TIME type column via a DDL statement may cause TiFlash query errors. This check was added in v6.5, so upgrading from older versions to the current 6.5 and 7.1 versions may also be affected, causing previously runnable SQL to error in the new version.

Temporary workarounds include:

  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: system | Original post link

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