Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: AUTO_INCREMENT 自增id 改为 AUTO_RANDOM 报错
[TiDB Usage Environment] Production Environment
[Encountered Problem: Problem Phenomenon and Impact] Executing ALTER TABLE business_bucket_file_node MODIFY id BIGINT(11) not null AUTO_RANDOM;
results in an error, message 8200 - Unsupported modify column: this column has primary key flag, Time: 0.022000s
Your table isn’t clustered, right?
You need a clustered table and set the parameter set tidb_allow_remove_auto_inc=on;
to make the modification.
root@[test]>CREATE TABLE t100 (a BIGINT PRIMARY KEY CLUSTERED auto_increment, b VARCHAR(255));
Query OK, 0 rows affected (0.10 sec)
root@[test]>show create table t100\G
*************************** 1. row ***************************
Table: t100
Create Table: CREATE TABLE `t100` (
`a` bigint(20) NOT NULL AUTO_INCREMENT,
`b` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)
root@[test]>alter table t100 modify a BIGINT AUTO_RANDOM;
ERROR 8200 (HY000): Unsupported modify column: can't remove auto_increment without @@tidb_allow_remove_auto_inc enabled
root@[test]>
root@[test]>show variables like 'tidb_allow_remove_auto_inc';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| tidb_allow_remove_auto_inc | OFF |
+----------------------------+-------+
1 row in set (0.01 sec)
root@[test]>set global tidb_allow_remove_auto_inc=on;
ERROR 1228 (HY000): Variable 'tidb_allow_remove_auto_inc' is a SESSION variable and can't be used with SET GLOBAL
root@[test]>set tidb_allow_remove_auto_inc=on;
Query OK, 0 rows affected (0.01 sec)
root@[test]>alter table t100 modify a BIGINT AUTO_RANDOM;
Query OK, 0 rows affected (0.11 sec)
root@[test]>show create table t100\G
*************************** 1. row ***************************
Table: t100
Create Table: CREATE TABLE `t100` (
`a` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
`b` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=2 */
1 row in set (0.05 sec)
It’s a cluster, and even after setting tidb_allow_remove_auto_inc to on, it still can’t be changed.
mysql> show variables like ‘tidb_allow_remove_auto_inc’;
±---------------------------±------+
| Variable_name | Value |
±---------------------------±------+
| tidb_allow_remove_auto_inc | ON |
±---------------------------±------+
1 row in set (0.02 sec)
mysql> alter table approval modify id BIGINT AUTO_RANDOM
→ ;
8200 - Unsupported modify column: this column has primary key flag
mysql> show variables like ‘tidb_allow_remove_auto_inc’;
±---------------------------±------+
| Variable_name | Value |
±---------------------------±------+
| tidb_allow_remove_auto_inc | ON |
±---------------------------±------+
1 row in set (0.03 sec)
mysql> show create table approval;
±---------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±---------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| approval | CREATE TABLE approval
(
id
bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘auto-increment primary key’,
uuid
varchar(32) NOT NULL DEFAULT ‘’ COMMENT ‘uuid’,
name
varchar(32) NOT NULL DEFAULT ‘’ COMMENT ‘work order name’,
status
tinyint(4) NOT NULL COMMENT ‘1-pending review; 2-approved; 3-rejected; 4-revoked’,
level
tinyint(4) NOT NULL COMMENT ‘current node’,
approval_module_uuid
varchar(32) NOT NULL COMMENT ‘approval template uuid’,
approval_module_code
varchar(32) NOT NULL DEFAULT ‘’ COMMENT ‘template approval type’,
approval_module_config
text DEFAULT NULL COMMENT ‘approval configuration template snapshot’,
create_user_id
bigint(20) NOT NULL COMMENT ‘creator id’,
business_content
text NOT NULL COMMENT ‘approval content json’,
create_time
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
execute_content
text DEFAULT NULL,
PRIMARY KEY (id
) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY uk_uuid
(uuid
),
KEY idx_create_user_id
(create_user_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001 COMMENT=‘approval main table’ |
±---------±----------------------------------------------------------------------------------------------------
Try executing the following:
ALTER TABLE approval MODIFY id BIGINT UNSIGNED NOT NULL AUTO_RANDOM COMMENT 'Auto-increment primary key';
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.