Online Partition Table Modification in TiDB Cluster

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

Original topic: tidb 集群在线更改分区表

| username: TiDBer_lsgVPNzX

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 7.5.1
[Reproduction Path] Partitioning a table with tens of millions of rows online
Table structure
CREATE TABLE test (
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL,
UNIQUE KEY (col1, col2),
UNIQUE KEY (col1, col3)
);

Alter statement
ALTER TABLE test PARTITION BY KEY(col1) PARTITIONS 4;
[Encountered Problem: Problem Phenomenon and Impact]
The online modification process gets interrupted
Error reported: ERROR 1062 (23000) at line 1: Duplicate entry ‘col1_value–col12_value’ for key ‘test.col1’

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots / Logs / Monitoring]

| username: GreenGuan | Original post link

Have you checked the data with errors in the table?

| username: TiDBer_小阿飞 | Original post link

Is this a primary key conflict error?

| username: xfworld | Original post link

He set a composite primary key, so there must be conflicts with single-column information… The data is duplicated…

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

I didn’t see the documentation mention that partition fields cannot be duplicated,

| username: TiDBer_lsgVPNzX | Original post link

Checked, there is only one.

| username: TiDBer_lsgVPNzX | Original post link

As long as the multiple columns are unique, it’s fine. Moreover, I am just partitioning the table and not modifying the primary key.

| username: 小龙虾爱大龙虾 | Original post link

There is a similar bug, handle ddl event failed while reorganize partition table · Issue #48838 · pingcap/tidb · GitHub, but it has been fixed in version 7.5.1.

| username: TiDBer_lsgVPNzX | Original post link

Nor did it modify the unique index.

| username: TiDBer_lsgVPNzX | Original post link

I’m using version 7.5.1. I’m not sure if the Duplicate of online data insertion caused the sharding error. I didn’t stop the machine to maintain the sharding. I tried other tables before, and they could be sharded normally, but this one doesn’t work.

| username: 小龙虾爱大龙虾 | Original post link

You can first check whether there is any inconsistency between the data and the index in this table: ADMIN CHECK [TABLE|INDEX] | PingCAP 文档中心

| username: TiDBer_lsgVPNzX | Original post link

Tried exactly the same. I haven’t modified the unique index of this online table.

| username: 小龙虾爱大龙虾 | Original post link

Go submit a bug.

| username: TiDBer_rvITcue9 | Original post link

Check in

| username: 健康的腰间盘 | Original post link

Wait for the version upgrade. There are indeed many areas where TiDB is not very user-friendly.

| username: TiDBer_HErMeXDz | Original post link

Duplicate entry… The problem is here.

| username: Hacker_QGgM2nks | Original post link

Isn’t this redundant?

| username: changpeng75 | Original post link

Are the other tables also key partitioned, and is the partition field also an int?