为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:dm8
【操作系统】:windows11
【CPU】: i5
【问题描述】*:
使用dts迁移mysql到dm部分表迁移失败:临时表、分区表不能包含自增列
错误号: -2738
错误消息: 第30 行附近出现错误:
临时表,分区表[tr_order_log_202210]不能包含自增列
CREATE TABLE "szebzhst_stcpdb"."tr_order_log_202210"
(
"log_id" BIGINT IDENTITY(1,1) NOT NULL,
"create_id" BIGINT NULL,
"create_name" VARCHAR(150) NULL,
"create_time" TIMESTAMP(0) NULL,
"order_no" VARCHAR(30) NULL,
"order_date" DATE DEFAULT '0001-01-01'
NOT NULL,
"log_date" DATE NULL,
"log_content" CLOB NULL,
"is_deleted" TINYINT NULL,
"buyer_code" CHAR(16) NULL,
"seller_code" CHAR(16) NULL,
"modify_id" BIGINT NULL,
"last_modified_time" TIMESTAMP(0) NULL
)PARTITION BY HASH("MONTH(order_date)")
(PARTITION "p0"
,PARTITION "p1"
,PARTITION "p2"
,PARTITION "p3"
,PARTITION "p4"
,PARTITION "p5"
,PARTITION "p6"
,PARTITION "p7"
,PARTITION "p8"
,PARTITION "p9"
,PARTITION "p10"
,PARTITION "p11"
)
用序列绕过
drop table USERCERTIFICATE;
CREATE SEQUENCE "SYSDBA"."SEQ_TEST" INCREMENT BY 1 START WITH 5 MAXVALUE 999999999 MINVALUE 1 CYCLE ORDER;
CREATE TABLE "USERCERTIFICATE"
(
"ID" INT DEFAULT SEQ_TEST.nextval ,
"SERIALNUMBER" VARCHAR(50) NOT NULL,
"USERID" VARCHAR(50) NOT NULL,
"ISSUERCN" VARCHAR(128) NULL
)PARTITION BY RANGE("ID")
(PARTITION "p0" VALUES LESS THAN (200000)
,PARTITION "p1" VALUES LESS THAN (400000)
,PARTITION "p3" VALUES LESS THAN (600000)
,PARTITION "p4" VALUES LESS THAN (800000)
,PARTITION "p5" VALUES LESS THAN (1000000)
,PARTITION "p6" VALUES LESS THAN (MAXVALUE)
);
迁移前先将需要保持自增列的表设置一下:
SET IDENTITY_INSERT 表名称 ON
目前临时表、列存储表、水平分区表确实不支持使用自增列。需要手动修改语句再进行建表。