Clarifying Issues in Partitioned Table Creation

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

Original topic: 分区表创建问题解惑

| username: zhimadi

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed to cause the issue
[Encountered Issue: Problem Phenomenon and Impact]
CREATE TABLE IF NOT EXISTS opt_log (
id BIGINT(20) UNSIGNED NOT NULL AUTO_RANDOM,
mod_id BIGINT(20) UNSIGNED NOT NULL DEFAULT ‘0’,
act_id BIGINT(20) UNSIGNED NOT NULL DEFAULT ‘0’,
apkid TINYINT(1) UNSIGNED NOT NULL DEFAULT ‘0’,
deviceid TINYINT(1) UNSIGNED NOT NULL DEFAULT ‘0’,
content TEXT COMMENT ‘Log Content’,
user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT ‘0’,
create_time INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,
PRIMARY KEY (id,create_time)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT ‘Operation Behavior Log Partition Table’
PARTITION BY RANGE (create_time)
(PARTITION p202307 VALUES LESS THAN (UNIX_TIMESTAMP(‘2023-08-01 00:00:00’)),
PARTITION p202308 VALUES LESS THAN (UNIX_TIMESTAMP(‘2023-09-01 00:00:00’)),
PARTITION p202309 VALUES LESS THAN (UNIX_TIMESTAMP(‘2023-10-01 00:00:00’)),
PARTITION p202310 VALUES LESS THAN (UNIX_TIMESTAMP(‘2023-11-01 00:00:00’)),
PARTITION p202311 VALUES LESS THAN (UNIX_TIMESTAMP(‘2023-12-01 00:00:00’)),
PARTITION p202312 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-01-01 00:00:00’)),
PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-02-01 00:00:00’)),
PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-03-01 00:00:00’)),
PARTITION p202403 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-04-01 00:00:00’)),
PARTITION p202404 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-05-01 00:00:00’)),
PARTITION p202405 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-06-01 00:00:00’)),
PARTITION p202406 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-07-01 00:00:00’)),
PARTITION p202407 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-08-01 00:00:00’)),
PARTITION p202408 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-09-01 00:00:00’)),
PARTITION p202409 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-10-01 00:00:00’)),
PARTITION p202410 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-11-01 00:00:00’)),
PARTITION p202411 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-12-01 00:00:00’)),
PARTITION pmax VALUES LESS THAN (MAXVALUE));

Creating partition table error 8216 - Invalid auto random: column id is not the integer primary key, or the primary key is nonclustered
Does anyone use partition tables? When creating a partition table, this error occurs. How should it be adjusted?
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

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

Change PRIMARY KEY (id, create_time) to PRIMARY KEY (id, create_time) CLUSTERED

| username: zhanggame1 | Original post link

Test creation successful

| username: zhimadi | Original post link

What is the principle of adding CLUSTERED? Also, does the PRIMARY KEY have to include create_time?

| username: xfworld | Original post link

The difference between clustered and non-clustered indexes is significant in terms of efficiency and performance, and the official documentation provides a detailed description.

Because there are scenarios that require partitioning, the keys involved in partitioning must all be primary keys.
This is based on the standard definition from MySQL, and you can refer to the principles of MySQL partitioning settings.

| username: zhimadi | Original post link

May I ask which version you are using? Why is mine not successful?

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

For clustered indexes, refer here: 聚簇索引 | PingCAP 文档中心
For partitioned table primary keys and unique keys, refer here: 分区表 | PingCAP 文档中心

| username: zhimadi | Original post link

8216 - Invalid auto random: column id is not the integer primary key, or the primary key is nonclustered
Adding PRIMARY KEY (id, create_time) CLUSTERED doesn’t work either, it reports the above error.
But isn’t it supposed to be /*T![clustered_index] CLUSTERED */ by default?

| username: zhanggame1 | Original post link

Version 7.1 of TiDB, with default configuration, has clustered as the default setting for new versions of TiDB, so there’s no need to specify it explicitly.

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

It seems that version 5.4 only supports auto_random on bigint type primary key columns.

I have no problem creating it with version 6.6.

| username: zhimadi | Original post link

It’s a primary key column of type bigint, and it doesn’t work either.

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

No, from the description of 5.4, it seems that it cannot be a composite primary key. It must be a primary key column of type bigint…

| username: zhimadi | Original post link

If it is not a composite key, PRIMARY KEY (id) will result in the following error:
1503 - A PRIMARY KEY must include all columns in the table’s partitioning function

| username: 像风一样的男子 | Original post link

Learned.

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

This is a limitation of partitioned tables. The partition key of a partitioned table must be part of the primary key. If you remove all the partitions and treat it as a regular table, can it be created?

| username: redgame | Original post link

Can create…

| username: zhimadi | Original post link

Is it version v5.4.2?

| username: knull | Original post link

There are mainly two things here:

  1. Auto random has certain usage restrictions. For example, it needs to be an int: AUTO_RANDOM | PingCAP 文档中心
  2. Partition also has certain usage restrictions, such as the partition key must be part of the primary key (unique key). 分区表 | PingCAP 文档中心