AUTO_INCREMENT 自增id 改为 AUTO_RANDOM 报错

TiDBer_lVKhiLUB · 2023 年6 月 30 日 04:03

【 TiDB 使用环境】生产环境
【遇到的问题：问题现象及影响】 ALTER TABLE business_bucket_file_node MODIFY id BIGINT(11) not null AUTO_RANDOM; 执行会报错， message 8200 - Unsupported modify column: this column has primary key flag, Time: 0.022000s

我是咖啡哥 · 2023 年6 月 30 日 04:49

auto_random can only be converted from auto_increment clustered primary key

你的表不是cluster的吧。
聚簇表，并且设置了set tidb_allow_remove_auto_inc=on;参数才能修改。

我是咖啡哥 · 2023 年6 月 30 日 04:52


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)

TiDBer_lVKhiLUB · 2023 年6 月 30 日 06:09

是集群，同样也把tidb_allow_remove_auto_inc 设置了on, 也改不了

TiDBer_lVKhiLUB · 2023 年6 月 30 日 06:13

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 ‘自增主键’,
uuid varchar(32) NOT NULL DEFAULT ‘’ COMMENT ‘uuid’,
name varchar(32) NOT NULL DEFAULT ‘’ COMMENT ‘工单名称’,
status tinyint(4) NOT NULL COMMENT ‘1-待审核；2-通过；3-驳回；4-撤销’,
level tinyint(4) NOT NULL COMMENT ‘当前节点’,
approval_module_uuid varchar(32) NOT NULL COMMENT ‘审批模板uuid’,
approval_module_code varchar(32) NOT NULL DEFAULT ‘’ COMMENT ‘模板审批类型’,
approval_module_config text DEFAULT NULL COMMENT ‘审批配置模板快照’,
create_user_id bigint(20) NOT NULL COMMENT ‘创建人id’,
business_content text NOT NULL COMMENT ‘审批内容 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=‘审批主表’ |
±---------±----------------------------------------------------------------------------------------------------

tidb菜鸟一只 · 2023 年6 月 30 日 06:22

ALTER TABLE approval MODIFY id BIGINT UNSIGNED NOT NULL AUTO_RANDOM COMMENT ‘自增主键’ ;
执行下看看

TiDBer_lVKhiLUB · 2023 年6 月 30 日 06:25

可以了

system · 2023 年8 月 29 日 06:26

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。