Error in Setting Data Placement Policy in TiDB

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

Original topic: Tidb 设置数据分离放置策略报错

| username: 最强王者

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
TIDB 6.5.2
[Reproduction Path] What operations were performed to cause the issue
Executed statement
alter table employees_test partition p1 placement policy storeonhdd;
Table creation statement
CREATE TABLE employees_test (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT ‘1970-01-01’,
separated DATE DEFAULT ‘9999-12-31’,
job_code INT,
store_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![placement] PLACEMENT POLICY=storeonssd */
PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page

[Attachment: Screenshot/Log/Monitoring]

| username: yiduoyunQ | Original post link

| username: 有猫万事足 | Original post link

After binding the placement policy to the partition, you can change the placement policy of the specified partition. An example is as follows:

ALTER TABLE t1 PARTITION pEurope PLACEMENT POLICY=p1;

Should it be written as
alter table employees_test partition p1 placement policy=storeonhdd;

From the syntax diagram, PlacementPolicyOption
is also connected through EqOpt here.

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

Execute SHOW placement labels; to see if there are corresponding labels. If not, there will be an error when binding.

  • When creating a placement rule using CREATE PLACEMENT POLICY, TiDB does not check if the labels exist, but it does check when binding the table.
| username: Jolyne | Original post link

Check if the corresponding label tags (show placement labels;) and policies have been created:
Create policy:
Specify data to be stored only on SSD: CREATE PLACEMENT POLICY storeonfastssd CONSTRAINTS=“[+disk=ssd]”;
Specify data to be stored only on HDD: CREATE PLACEMENT POLICY storeonfastssd CONSTRAINTS=“[+disk=hdd]”;

| username: 最强王者 | Original post link

There is an error when specifying the change policy. It looks like there is an issue with the label modification in PD.

| username: 最强王者 | Original post link

Yes, I get an error when binding.
ERROR 8243 (HY000): “[PD:placement:ErrRuleContent]invalid rule content, rule ‘partition_rule_102_0’ from rule group ‘TiDB_DDL_102’ can not match any store”

| username: 最强王者 | Original post link

According to the binding method you provided, it was successful on my side.

| username: Jolyne | Original post link

So, have you successfully bound it before? If so, try your alter statement again after binding it successfully.

| username: Fly-bird | Original post link

Is it a rule issue?

| username: 最强王者 | Original post link

Yes, the rule had an issue causing the creation to fail. Changing it to CREATE PLACEMENT POLICY storeonfastssd CONSTRAINTS=“[+disk=ssd]”; works.