Error When Changing AUTO_INCREMENT Auto-increment ID to AUTO_RANDOM

Chinese Translated
1

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

Original topic: AUTO_INCREMENT 自增id 改为 AUTO_RANDOM 报错

| username: TiDBer_lVKhiLUB

[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

2
| username: 我是咖啡哥 | Original post link

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.

3
| username: 我是咖啡哥 | Original post link 
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)
4
| username: TiDBer_lVKhiLUB | Original post link

It’s a cluster, and even after setting tidb_allow_remove_auto_inc to on, it still can’t be changed.

5
| username: TiDBer_lVKhiLUB | Original post link

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’ |
±---------±----------------------------------------------------------------------------------------------------

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

Try executing the following:

ALTER TABLE approval MODIFY id BIGINT UNSIGNED NOT NULL AUTO_RANDOM COMMENT 'Auto-increment primary key';
7
| username: TiDBer_lVKhiLUB | Original post link

It works.

8
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.