SELECT statement reports "[table:1526]Table has no partition for value from column_list"

translator_bot · June 22, 2024, 10:08pm

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

Original topic: SELECT语句报"[table:1526]Table has no partition for value from column_list"

| username: dba-kit

This error generally indicates that the data to be inserted has a partition key that is not within the existing partition range. Theoretically, if there is no error during insertion, there should not be an error during selection. (The query is a large SQL involving a multi-table join with 3 partitioned tables, and it is still unclear which partitioned table is causing the issue.)

[table:1526]Table has no partition for value from column_list
github.com/pingcap/errors.AddStack
	/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20211224045212-9687c2b0f87c/errors.go:174
github.com/pingcap/errors.(*Error).GenWithStackByArgs
	/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20211224045212-9687c2b0f87c/normalize.go:164
github.com/pingcap/tidb/table/tables.(*partitionedTable).locateRangeColumnPartition
	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/table/tables/partition.go:1029
github.com/pingcap/tidb/table/tables.(*partitionedTable).locatePartition
	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/table/tables/partition.go:976
github.com/pingcap/tidb/table/tables.(*partitionedTable).GetPartitionByRow
	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/table/tables/partition.go:1147
github.com/pingcap/tidb/executor.(*dataReaderBuilder).buildTableReaderForIndexJoin
	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/builder.go:4025
github.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoinInternal
	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/builder.go:3924
github.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoin
	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/builder.go:3917
github.com/pingcap/tidb/executor.(*innerWorker).fetchInnerResults
	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:685
github.com/pingcap/tidb/executor.(*innerWorker).handleTask
	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:524
github.com/pingcap/tidb/executor.(*innerWorker).run
	/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:498
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1571

translator_bot · June 22, 2024, 10:08pm

| username: dba-kit | Original post link

I suspect that it is caused by dirty data because there are two pieces of data written into the 9999-12-31 partition in one of the partitioned tables. After communicating with the business team, we backed up and deleted the two pieces of dirty data. It is possible that there are still related data in other tables that have not been cleaned up. I am currently confirming this with the business team. However, theoretically, even if such data exists, it should not cause an error during the select operation.

translator_bot · June 22, 2024, 10:08pm

| username: dba-kit | Original post link

After deleting the associated dirty data, the query still reports the same error. Is it possible to identify which table’s partition has the query issue? Does [table:1526] refer to the table ID? I couldn’t find which table it is in information_schema.tables.

translator_bot · June 22, 2024, 10:08pm

| username: buddyyuan | Original post link

What version are you using? There is a similar issue.

github.com/pingcap/tidb

query use join + for update in transaction，return error "Table has no partition for value from column_list"

opened 11:17AM - 10 Jan 22 UTC

closed 06:50AM - 17 Jan 22 UTC

vivid392845427

type/bug sig/sql-infra severity/major affects-5.0 component/tablepartition

## Bug Report Please answer these questions before submitting your issue. Tha…nks! ### 1. Minimal reproduce step (Required) ``` set @@session.tidb_enable_list_partition = ON; drop table if exists t1,t2; create table t1 (c_int int, c_str varchar(40) , c_datetime datetime, c_timestamp timestamp, c_double double, c_decimal decimal(12, 6), c_enum enum('blue','green','red','yellow','white','orange','purple'), primary key (c_int, c_str) , key(c_str(19)) , key(c_datetime) , key(c_timestamp)) partition by list columns(c_int) ( partition p0 values IN (1, 5, 9, 13, 17, 21, 25, 29, 33, 37), partition p1 values IN (2, 6, 10, 14, 18, 22, 26, 30, 34, 38), partition p2 values IN (3, 7, 11, 15, 19, 23, 27, 31, 35, 39), partition p3 values IN (4, 8, 12, 16, 20, 24, 28, 32, 36, 40)) ; create table t2 (c_int int, c_str varchar(40) , c_datetime datetime, c_timestamp timestamp, c_double double, c_decimal decimal(12, 6), c_enum enum('blue','green','red','yellow','white','orange','purple') , key(c_int) , unique key(c_str) , unique key(c_decimal) ); insert into t1 values(1,'bold curran','2020-06-12 19:53:45','2020-05-06 20:15:34',19.828653,7.139000,'blue'); insert into t2 values(1,'laughing satoshi','2020-05-06 14:45:01','2020-05-30 07:16:53',26.577031,6.584000,'white'); select * from t1 right join t2 on t1.c_enum > t2.c_enum for update; begin; select * from t1 right join t2 on t1.c_enum > t2.c_enum for update; ``` ### 2. What did you expect to see? (Required) ``` mysql> select * from t1 right join t2 on t1.c_enum > t2.c_enum for update; +-------+-------+------------+-------------+----------+-----------+--------+-------+------------------+---------------------+---------------------+-----------+-----------+--------+ | c_int | c_str | c_datetime | c_timestamp | c_double | c_decimal | c_enum | c_int | c_str | c_datetime | c_timestamp | c_double | c_decimal | c_enum | +-------+-------+------------+-------------+----------+-----------+--------+-------+------------------+---------------------+---------------------+-----------+-----------+--------+ | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | laughing satoshi | 2020-05-06 14:45:01 | 2020-05-30 07:16:53 | 26.577031 | 6.584000 | white | +-------+-------+------------+-------------+----------+-----------+--------+-------+------------------+---------------------+---------------------+-----------+-----------+--------+ 1 row in set (0.00 sec) ``` ### 3. What did you see instead (Required) ``` mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 right join t2 on t1.c_enum > t2.c_enum for update; ERROR 1526 (HY000): Table has no partition for value from column_list ``` ### 4. What is your TiDB version? (Required) ``` Release Version: v5.0.0-nightly Edition: Community Git Commit Hash: 6416f8d601472892d245b950dfd5547e857a1a33 Git Branch: heads/refs/tags/v5.0.0-nightly UTC Build Time: 2022-01-10 05:41:28 GoVersion: go1.13 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false ```

translator_bot · June 22, 2024, 10:08pm

| username: dba-kit | Original post link

It’s 6.1.2. My scenario is different from this one; it’s purely a multi-table join select without adding for update.

translator_bot · June 22, 2024, 10:08pm

| username: dba-kit | Original post link

For partitioned tables, I added a fallback pfuture partition, and the issue was resolved. It seems that the intermediate data during the scan was inconsistent, causing the partition key used when scanning other tables to exceed the limits of the original table (in fact, these data can be directly treated as unmatched since the original table definitely doesn’t have these data). The SQL to add the fallback partition is:

alter table test.t1 ADD PARTITION (PARTITION pfuture VALUES LESS THAN (MAXVALUE));

PS: Since TiDB 6.1 does not yet support the REORGANIZE PARTITION operation, to enable periodic partition creation, only the partition for the next year was created, and the fallback pfuture partition was not created.

translator_bot · June 22, 2024, 10:08pm

| username: Lucien-卢西恩 | Original post link

Hello, after adding the pfuture partition, did the error get bypassed? Could you provide the reproduction steps so we can try to reproduce it and see if it can be fixed in the future?

translator_bot · June 22, 2024, 10:08pm

| username: system | Original post link

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