admin check table with tiflash: Can't find a proper physical plan for this query #21784

marsishandsome · 2020-12-15T04:12:08Z

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t ( c_int int, c_str varchar(40), c_datetime datetime, c_timestamp timestamp, c_double double, c_decimal decimal(12, 6) , primary key(c_int, c_str) , key(c_int)  , key(c_decimal)  , key(c_timestamp) );

alter table t set tiflash replica 1;

insert into t values (1, 'goofy bouman', '2020-02-28 18:00:01', '2020-01-24 08:37:28', 48.393304, 6.687), (2, 'kind brown', '2020-04-23 22:53:50', '2020-04-30 01:48:07', 46.996454, 6.119), (3, 'elated einstein', '2020-01-13 04:10:18', '2020-05-08 18:12:11', 60.203135, 2.364), (4, 'quizzical curran', '2020-05-08 20:03:17', '2020-04-02 03:05:59', 53.356897, 6.295), (5, 'determined roentgen', '2020-04-23 22:24:33', '2020-05-05 22:33:28', 15.524128, 3.658)","insert into t values (6, 'reverent rosalind', '2020-05-22 12:55:58', '2020-02-16 21:05:00', 6.013712, 1.148), (7, 'stoic black', '2020-02-24 17:30:04', '2020-01-12 02:20:09', 54.459788, 3.733), (8, 'sharp lovelace', '2020-03-28 14:12:57', '2020-05-14 19:29:23', 86.801050, 6.093), (9, 'admiring tereshkova', '2020-05-12 00:37:19', '2020-03-10 21:42:51', 86.542053, 5.454), (10, 'musing feynman', '2020-04-28 21:06:39', '2020-04-04 15:00:37', 89.056129, 7.489)","insert into t values (11, 'inspiring noyce', '2020-01-30 05:00:10', '2020-06-28 00:33:15', 95.090046, 2.401), (12, 'nostalgic sinoussi', '2020-06-12 16:27:42', '2020-02-19 17:08:13', 78.271141, 2.033), (13, 'confident swanson', '2020-03-09 16:08:37', '2020-06-28 12:17:12', 17.864788, 5.669), (14, 'crazy shannon', '2020-01-29 06:30:29', '2020-02-03 22:03:13', 39.566887, 6.704), (15, 'gallant nightingale', '2020-06-08 09:23:04', '2020-05-13 05:10:31', 67.926363, 7.079);

admin check table t;

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

MySQL [test]> admin check table t;
Query OK, 0 rows affected (0.14 sec)

3. What did you see instead (Required)

MySQL [test]> admin check table t;
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query

4. What is your TiDB version? (Required)

Release Version: v4.0.0-beta.2-1804-g06cd92e05
Edition: Community
Git Commit Hash: 06cd92e05f0dfff1a139c1e5baca2ee24fb387b2
Git Branch: master
UTC Build Time: 2020-12-12 13:49:05
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

The text was updated successfully, but these errors were encountered:

LittleFall · 2020-12-16T09:33:43Z

@marsishandsome What is the TiFlash version? I checkout to this TiDB version but I can't get the error. Following is my sql.

mysql> create table t (c_int int, c_str varchar(40), c_datetime datetime, c_timestamp timestamp, c_double double, c_decimal decimal(12, 6) , primary key(c_int, c_str) , key(c_int)  , key(c_decimal)  , key(c_timestamp) );
Query OK, 0 rows affected (1.05 sec)

mysql>
mysql> alter table t set tiflash replica 1;
Query OK, 0 rows affected (0.53 sec)

mysql> insert into t values
    -> (1, 'goofy bouman', '2020-02-28 18:00:01', '2020-01-24 08:37:28', 48.393304, 6.687),
    -> (2, 'kind brown', '2020-04-23 22:53:50', '2020-04-30 01:48:07', 46.996454, 6.119),
    -> (3, 'elated einstein', '2020-01-13 04:10:18', '2020-05-08 18:12:11', 60.203135, 2.364),
    -> (4, 'quizzical curran', '2020-05-08 20:03:17', '2020-04-02 03:05:59', 53.356897, 6.295),
    -> (5, 'determined roentgen', '2020-04-23 22:24:33', '2020-05-05 22:33:28', 15.524128, 3.658),
    -> (6, 'reverent rosalind', '2020-05-22 12:55:58', '2020-02-16 21:05:00', 6.013712, 1.148),
    -> (7, 'stoic black', '2020-02-24 17:30:04', '2020-01-12 02:20:09', 54.459788, 3.733),
    -> (8, 'sharp lovelace', '2020-03-28 14:12:57', '2020-05-14 19:29:23', 86.801050, 6.093),
    -> (9, 'admiring tereshkova', '2020-05-12 00:37:19', '2020-03-10 21:42:51', 86.542053, 5.454),
    -> (10, 'musing feynman', '2020-04-28 21:06:39', '2020-04-04 15:00:37', 89.056129, 7.489),
    -> (11, 'inspiring noyce', '2020-01-30 05:00:10', '2020-06-28 00:33:15', 95.090046, 2.401),
    -> (12, 'nostalgic sinoussi', '2020-06-12 16:27:42', '2020-02-19 17:08:13', 78.271141, 2.033),
    -> (13, 'confident swanson', '2020-03-09 16:08:37', '2020-06-28 12:17:12', 17.864788, 5.669),
    -> (14, 'crazy shannon', '2020-01-29 06:30:29', '2020-02-03 22:03:13', 39.566887, 6.704),
    -> (15, 'gallant nightingale', '2020-06-08 09:23:04', '2020-05-13 05:10:31', 67.926363, 7.079);
Query OK, 15 rows affected (0.03 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> admin check table t;
Query OK, 0 rows affected (0.13 sec)

mysql> select * from information_schema.tiflash_replica;
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test         | t          |       79 |             1 |                 |         0 |        0 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.01 sec)

mysql> select * from information_schema.tiflash_replica;
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test         | t          |       79 |             1 |                 |         1 |        1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.00 sec)

mysql> admin check table t;
Query OK, 0 rows affected (0.13 sec)

mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                          |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-1804-g06cd92e05
Edition: Community
Git Commit Hash: 06cd92e05f0dfff1a139c1e5baca2ee24fb387b2
Git Branch: HEAD
UTC Build Time: 2020-12-16 09:23:16
GoVersion: go1.15
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

marsishandsome · 2020-12-18T07:52:22Z

cannot reproduce in the latest version of branch-4.0 and master (maybe a bug already fixed)

ichn-hu · 2020-12-21T10:41:26Z

What about use git bisect to find out the fix?

LittleFall · 2020-12-23T06:44:31Z

I use scripts to check tiup playground each version from v4.0.3 to nightly, none version reoccurs this bug.

Maybe some pr import it and another pr fix it timely.

Could you help me to close this issue? @ichn-hu

Following is my script.

use test;

drop table if exists t;

create table t (c_int int, c_str varchar(40), c_datetime datetime, c_timestamp timestamp, c_double double, c_decimal decimal(12, 6) , primary key(c_int, c_str) , key(c_int)  , key(c_decimal)  , key(c_timestamp) );

alter table t set tiflash replica 1;

insert into t values
(1, 'goofy bouman', '2020-02-28 18:00:01', '2020-01-24 08:37:28', 48.393304, 6.687),
(2, 'kind brown', '2020-04-23 22:53:50', '2020-04-30 01:48:07', 46.996454, 6.119),
(3, 'elated einstein', '2020-01-13 04:10:18', '2020-05-08 18:12:11', 60.203135, 2.364),
(4, 'quizzical curran', '2020-05-08 20:03:17', '2020-04-02 03:05:59', 53.356897, 6.295),
(5, 'determined roentgen', '2020-04-23 22:24:33', '2020-05-05 22:33:28', 15.524128, 3.658),
(6, 'reverent rosalind', '2020-05-22 12:55:58', '2020-02-16 21:05:00', 6.013712, 1.148),
(7, 'stoic black', '2020-02-24 17:30:04', '2020-01-12 02:20:09', 54.459788, 3.733),
(8, 'sharp lovelace', '2020-03-28 14:12:57', '2020-05-14 19:29:23', 86.801050, 6.093),
(9, 'admiring tereshkova', '2020-05-12 00:37:19', '2020-03-10 21:42:51', 86.542053, 5.454),
(10, 'musing feynman', '2020-04-28 21:06:39', '2020-04-04 15:00:37', 89.056129, 7.489),
(11, 'inspiring noyce', '2020-01-30 05:00:10', '2020-06-28 00:33:15', 95.090046, 2.401),
(12, 'nostalgic sinoussi', '2020-06-12 16:27:42', '2020-02-19 17:08:13', 78.271141, 2.033),
(13, 'confident swanson', '2020-03-09 16:08:37', '2020-06-28 12:17:12', 17.864788, 5.669),
(14, 'crazy shannon', '2020-01-29 06:30:29', '2020-02-03 22:03:13', 39.566887, 6.704),
(15, 'gallant nightingale', '2020-06-08 09:23:04', '2020-05-13 05:10:31', 67.926363, 7.079);

select sleep(10);

select * from information_schema.tiflash_replica;

admin check table t;

ichn-hu · 2020-12-23T09:07:22Z

@LittleFall see if the scripts I commented in #20027 will help you to find the fix?

ichn-hu · 2020-12-23T09:08:10Z

BTW I am good to mark this issue as closed for can't reproduce, but I don't have the priviledge to do so, you might want to ask @lzmhhh123 for help.

LittleFall · 2020-12-23T09:18:14Z

@LittleFall see if the scripts I commented in #20027 will help you to find the fix?

Sorry I can't find any base version to reoccur it, so that I can't use bisect to find the pr fixing it.

Thanks for your experience and script, I will mark it and maybe use in another situation.

@lzmhhh123 Could you help me to close this issue? Thanks a lot.

ti-srebot · 2020-12-24T05:33:36Z

Please edit this comment or add a new comment to complete the following information

Not a bug

Remove the 'type/bug' label
Add notes to indicate why it is not a bug

Duplicate bug

Add the 'type/duplicate' label
Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

marsishandsome added the type/bug The issue is confirmed as a bug. label Dec 15, 2020

zanmato1984 assigned LittleFall Dec 15, 2020

ChenPeng2013 added severity/moderate sig/planner SIG: Planner labels Dec 16, 2020

ichn-hu mentioned this issue Dec 16, 2020

Welcome to contribute #20804

Closed

lzmhhh123 closed this as completed Dec 24, 2020

ti-srebot added the need-more-info label Dec 24, 2020

wjhuang2016 mentioned this issue Feb 25, 2021

admin check table with tiflash: Can't find a proper physical plan for this query #22947

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

admin check table with tiflash: Can't find a proper physical plan for this query #21784

admin check table with tiflash: Can't find a proper physical plan for this query #21784

marsishandsome commented Dec 15, 2020

LittleFall commented Dec 16, 2020

marsishandsome commented Dec 18, 2020

ichn-hu commented Dec 21, 2020

LittleFall commented Dec 23, 2020

ichn-hu commented Dec 23, 2020

ichn-hu commented Dec 23, 2020

LittleFall commented Dec 23, 2020 •

edited

Loading

ti-srebot commented Dec 24, 2020

admin check table with tiflash: Can't find a proper physical plan for this query #21784

admin check table with tiflash: Can't find a proper physical plan for this query #21784

Comments

marsishandsome commented Dec 15, 2020

Bug Report

1. Minimal reproduce step (Required)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

LittleFall commented Dec 16, 2020

marsishandsome commented Dec 18, 2020

ichn-hu commented Dec 21, 2020

LittleFall commented Dec 23, 2020

ichn-hu commented Dec 23, 2020

ichn-hu commented Dec 23, 2020

LittleFall commented Dec 23, 2020 • edited Loading

ti-srebot commented Dec 24, 2020

Please edit this comment or add a new comment to complete the following information

Not a bug

Duplicate bug

Bug

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

LittleFall commented Dec 23, 2020 •

edited

Loading