Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 把一张id主键为auto_increament的表，用命令改成auto_random，提示Invalid auto random: max allowed auto_random shard bits is 0, but got 5 on column id。
[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.1
[Reproduction Path] Create a table with a primary key id set to auto_increment, insert some data, and then use a command to change it to auto_random. There is a certain probability that the change will fail.
[Encountered Problem: Problem Phenomenon and Impact]
The modify and change commands fail to execute successfully, reporting “Invalid auto random: max allowed auto_random shard bits is 0, but got 5 on column id.”
[Resource Configuration]
Confirm the shard bits: Check the shard bits setting of the auto-random column ID in the table structure. Ensure that the shard bits do not exceed the allowed maximum value.
Reproduction steps:
id_value.sql (7.2 MB)
CREATE TABLE t1 (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘Primary Key ID’,
name VARCHAR(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘Name’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
– After creating the table, import the id values from the attachment, then execute the following statement to reproduce:
SET @@tidb_allow_remove_auto_inc = 1;
ALTER TABLE t1 MODIFY id BIGINT(20) UNSIGNED NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */ COMMENT ‘Primary Key ID’;
Based on the provided information, you want to change the column type of a table with an auto-increment primary key to auto-random. However, when doing so, an error occurs: “Invalid auto random: max allowed auto_random shard bits is 0, but got 5 on column id.”
The issue lies in setting the shard bits for auto_random, which is used for automatic sharding in TiDB. In your table, the shard bits for auto_random are set to 5, but the storage engine does not support this value, leading to the error.
To resolve this issue, follow these steps:
-
Confirm TiDB Version: Ensure that the TiDB version you are using supports the auto_random feature.
-
Confirm Storage Engine: In TiDB, only when using TiFlash as the storage engine can the shard bits for auto_random columns be supported. Therefore, if you want to set the shard bits for auto_random, you need to enable TiFlash and store the table data in TiFlash.
-
Remove auto_random Attribute: If you do not need the auto_random feature, you can remove the auto_random attribute from the column and revert it to an auto_increment column. Execute the following ALTER TABLE statement:
ALTER TABLE your_table MODIFY COLUMN id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary Key ID';
- Enable TiFlash and Change to auto_random: If you need to use the auto_random feature and have enabled TiFlash, you can follow these steps to set the correct shard bits for auto_random:
-- First, enable TiFlash and store the data in TiFlash
-- Then, modify the column type to auto_random and specify the correct shard bits
ALTER TABLE your_table MODIFY COLUMN id BIGINT(20) UNSIGNED NOT NULL /*T![auto_rand] AUTO_RANDOM(5)*/ COMMENT 'Primary Key ID';
Please note that enabling TiFlash and making changes may involve migrating table data and configuring the storage engine. Therefore, be sure to back up your data before performing these operations to prevent data loss.
It is possible to change auto_increment to auto_random without enabling TiFlash. This has already been tested, and the official documentation does not state that TiFlash must be enabled. Additionally, this issue is triggered only after inserting some ID values into the table, and it cannot be triggered under normal circumstances.
|
username: TiGod
|
username: 
|
username: 
|
username: