分区交换报错Table has drop column instant, exchange partition not supported

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】4.3.5.4
【问题描述】通过分区交换的形式，将普通表数据转化到分区表中
【复现路径】
1、创建普通表
CREATE TABLE wt_part_change_single1(
RETURN_MONEY_ID varchar(40) COLLATE utf8mb4_bin NOT NULL,
ORDER_ID varchar(80) COLLATE utf8mb4_bin DEFAULT NULL,
AB_TYPE varchar(6) COLLATE utf8mb4_bin DEFAULT NULL ,
ORDER_TYPE decimal(38,0) DEFAULT NULL ,
RETURN_ORDER_STATUS varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
RETURN_FLAG decimal(65,0) DEFAULT NULL,
RETURN_MONEY_DATE datetime DEFAULT NULL ,
RETURN_MONEY decimal(20,2) DEFAULT NULL ,
RETURN_MONEY_FAL decimal(20,2) DEFAULT NULL ,
RETURN_MONEY_REASON varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL ,
RETURN_MONEY_NOTE varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL ,
BUYER_NAME varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
BUYER_BANK_CODE varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
BUYER_ACCOUNT varchar(100) COLLATE utf8mb4_bin DEFAULT NULL ,
OP_ID varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
PAY_TRAN_ID varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
PF_BH varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
RETURN_MONEY_TYPE varchar(4) COLLATE utf8mb4_bin DEFAULT NULL ,
LIQUIDATE_DATE varchar(40) COLLATE utf8mb4_bin DEFAULT NULL ,
BACK_MATERIAL_NO varchar(40) COLLATE utf8mb4_bin DEFAULT NULL ,
create_time datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ,
create_user varchar(50) COLLATE utf8mb4_bin DEFAULT NULL ,
change_time datetime(6) DEFAULT NULL ,
change_user varchar(50) COLLATE utf8mb4_bin DEFAULT NULL ,
remark varchar(100) COLLATE utf8mb4_bin DEFAULT NULL ,
OMS_OBJECT_NUMBER bigint COMMENT ‘Reserved for data migration tasks of OMS’,
OMS_RELATIVE_FNO bigint COMMENT ‘Reserved for data migration tasks of OMS’,
OMS_BLOCK_NUMBER bigint COMMENT ‘Reserved for data migration tasks of OMS’,
OMS_ROW_NUMBER bigint COMMENT ‘Reserved for data migration tasks of OMS’,
UNIQUE KEY UK_DEAL_CHARGE_ORDER_KM_OMS_ROWID (OMS_OBJECT_NUMBER, OMS_RELATIVE_FNO, OMS_BLOCK_NUMBER, OMS_ROW_NUMBER, create_time) LOCAL,
KEY IX_RETRUNMONEY_ID (RETURN_MONEY_ID) BLOCK_SIZE 16384 LOCAL,
KEY IX_RETRUNMONEY_ORDER_ID (ORDER_ID) BLOCK_SIZE 16384 LOCAL,
KEY IX_DEAL_ORDER_RETURN_MONEY1 (RETURN_MONEY_DATE, ORDER_ID, ORDER_TYPE) BLOCK_SIZE 16384 LOCAL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
2、新增数据
INSERT INTO wt_part_change_single1 (
RETURN_MONEY_ID, ORDER_ID, AB_TYPE, ORDER_TYPE, RETURN_ORDER_STATUS,
RETURN_FLAG, RETURN_MONEY_DATE, RETURN_MONEY, RETURN_MONEY_FAL,
RETURN_MONEY_REASON, RETURN_MONEY_NOTE, BUYER_NAME, BUYER_BANK_CODE,
BUYER_ACCOUNT, OP_ID, PAY_TRAN_ID, PF_BH, RETURN_MONEY_TYPE,
LIQUIDATE_DATE, BACK_MATERIAL_NO, create_user, change_time, change_user, remark
) VALUES
– 第1条记录
(‘RM001’, ‘ORD20240115000001’, ‘A1’, 1, ‘COMPLETED’,
1, ‘2024-01-15 10:30:00’, 100.50, 100.50,
‘10’, ‘商品质量问题退货’, ‘张三’, ‘ICBC’,
‘6222021234567890123’, ‘OP001’, ‘PAY001’, ‘PF001’, ‘1’,
‘20240115’, ‘BJWL001’, ‘admin’, NULL, NULL, ‘测试数据1’),

– 第2条记录
(‘RM002’, ‘ORD20240116000002’, ‘A2’, 2, ‘PROCESSING’,
0, ‘2024-01-16 14:20:00’, 200.00, 0.00,
‘12’, ‘客户取消订单’, ‘李四’, ‘CCB’,
‘6227001234567890124’, ‘OP002’, ‘PAY002’, ‘PF001’, ‘1’,
‘20240116’, NULL, ‘admin’, NULL, NULL, ‘测试数据2’),

– 第3条记录
(‘RM003’, ‘ORD20240117000003’, ‘B’, 1, ‘COMPLETED’,
1, ‘2024-01-17 09:15:00’, 150.75, 150.75,
‘15’, ‘客户拒收商品’, ‘王五’, ‘ABC’,
‘955991234567890125’, ‘OP003’, ‘PAY003’, ‘PF002’, ‘1’,
‘20240117’, ‘BJWL003’, ‘admin’, NULL, NULL, ‘测试数据3’),

– 第4条记录
(‘RM004’, ‘ORD20240118000004’, ‘A1’, 3, ‘COMPLETED’,
1, ‘2024-01-18 16:45:00’, 300.25, 300.25,
‘11’, ‘换货完成退款’, ‘赵六’, ‘BOC’,
‘456351234567890126’, ‘OP004’, ‘PAY004’, ‘PF002’, ‘1’,
‘20240118’, NULL, ‘admin’, NULL, NULL, ‘测试数据4’),

– 第5条记录
(‘RM005’, ‘ORD20240119000005’, ‘A2’, 1, ‘REJECTED’,
0, ‘2024-01-19 11:20:00’, 80.00, 0.00,
‘13’, ‘审核失败’, ‘钱七’, ‘CMB’,
‘6225881234567890127’, ‘OP005’, ‘PAY005’, ‘PF001’, ‘1’,
‘20240119’, NULL, ‘admin’, NULL, NULL, ‘测试数据5’),

– 第6条记录
(‘RM006’, ‘ORD20240120000006’, ‘B’, 2, ‘COMPLETED’,
1, ‘2024-01-20 13:30:00’, 250.60, 250.60,
‘14’, ‘普通订单退款’, ‘孙八’, ‘CIB’,
‘6229091234567890128’, ‘OP006’, ‘PAY006’, ‘PF003’, ‘1’,
‘20240120’, NULL, ‘admin’, NULL, NULL, ‘测试数据6’),

– 第7条记录
(‘RM007’, ‘ORD20240121000007’, ‘A1’, 1, ‘COMPLETED’,
1, ‘2024-01-21 15:10:00’, 180.90, 180.90,
‘99’, ‘其他原因退款’, ‘周九’, ‘SPDB’,
‘6225211234567890129’, ‘OP007’, ‘PAY007’, ‘PF003’, ‘2’,
‘20240121’, NULL, ‘admin’, NULL, NULL, ‘重复支付退款’),

– 第8条记录
(‘RM008’, ‘ORD20240122000008’, ‘A2’, 4, ‘PROCESSING’,
0, ‘2024-01-22 08:50:00’, 120.30, 0.00,
‘10’, ‘退货完成退款’, ‘吴十’, ‘CEB’,
‘6226581234567890130’, ‘OP008’, ‘PAY008’, ‘PF001’, ‘1’,
‘20240122’, ‘BJWL008’, ‘admin’, NULL, NULL, ‘测试数据8’),

– 第9条记录
(‘RM009’, ‘ORD20240123000009’, ‘B’, 1, ‘COMPLETED’,
1, ‘2024-01-23 17:25:00’, 95.45, 95.45,
‘15’, ‘拒收订单退款’, ‘郑十一’, ‘HXB’,
‘6226021234567890131’, ‘OP009’, ‘PAY009’, ‘PF002’, ‘1’,
‘20240123’, ‘BJWL009’, ‘admin’, NULL, NULL, ‘测试数据9’),

– 第10条记录
(‘RM010’, ‘ORD20240124000010’, ‘A1’, 2, ‘COMPLETED’,
1, ‘2024-01-24 12:15:00’, 210.80, 210.80,
‘12’, ‘取消订单退款’, ‘王十二’, ‘PSBC’,
‘6221501234567890132’, ‘OP010’, ‘PAY010’, ‘PF003’, ‘1’,
‘20240124’, NULL, ‘admin’, NULL, NULL, ‘测试数据10’);
3、创建分区表
CREATE TABLE wt_part_change_omspartitioned1 (
RETURN_MONEY_ID varchar(40) COLLATE utf8mb4_bin NOT NULL,
ORDER_ID varchar(80) COLLATE utf8mb4_bin DEFAULT NULL ,
AB_TYPE varchar(6) COLLATE utf8mb4_bin DEFAULT NULL ,
ORDER_TYPE decimal(38,0) DEFAULT NULL ,
RETURN_ORDER_STATUS varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
RETURN_FLAG decimal(65,0) DEFAULT NULL,
RETURN_MONEY_DATE datetime DEFAULT NULL ,
RETURN_MONEY decimal(20,2) DEFAULT NULL ,
RETURN_MONEY_FAL decimal(20,2) DEFAULT NULL ,
RETURN_MONEY_REASON varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL ,
RETURN_MONEY_NOTE varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL ,
BUYER_NAME varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
BUYER_BANK_CODE varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
BUYER_ACCOUNT varchar(100) COLLATE utf8mb4_bin DEFAULT NULL ,
OP_ID varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
PAY_TRAN_ID varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
PF_BH varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
RETURN_MONEY_TYPE varchar(4) COLLATE utf8mb4_bin DEFAULT NULL ,
LIQUIDATE_DATE varchar(40) COLLATE utf8mb4_bin DEFAULT NULL ,
BACK_MATERIAL_NO varchar(40) COLLATE utf8mb4_bin DEFAULT NULL ,
create_time datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ,
create_user varchar(50) COLLATE utf8mb4_bin DEFAULT NULL ,
change_time datetime(6) DEFAULT NULL ,
change_user varchar(50) COLLATE utf8mb4_bin DEFAULT NULL ,
remark varchar(100) COLLATE utf8mb4_bin DEFAULT NULL ,
KEY IX_RETRUNMONEY_ID (RETURN_MONEY_ID) BLOCK_SIZE 16384 LOCAL,
KEY IX_RETRUNMONEY_ORDER_ID (ORDER_ID) BLOCK_SIZE 16384 LOCAL,
KEY IX_DEAL_ORDER_RETURN_MONEY1 (RETURN_MONEY_DATE, ORDER_ID, ORDER_TYPE) BLOCK_SIZE 16384 LOCAL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by range columns(create_time)
(partition p0_202509 values less than (‘2025-10-01 00:00:00’),
partition p0_202510 values less than (‘2025-11-01 00:00:00’),
partition p0_202511 values less than (‘2025-12-01 00:00:00’),
partition p0_202512 values less than (‘2026-01-01 00:00:00’),
partition p0_202601 values less than (‘2026-02-01 00:00:00’),
partition p0_202602 values less than (‘2026-03-01 00:00:00’),
partition p0_202603 values less than (‘2026-04-01 00:00:00’),
partition p0_202604 values less than (‘2026-05-01 00:00:00’),
partition p0_202605 values less than (‘2026-06-01 00:00:00’),
partition p0_202606 values less than (‘2026-07-01 00:00:00’),
partition p0_202607 values less than (‘2026-08-01 00:00:00’),
partition p0_202608 values less than (‘2026-09-01 00:00:00’),
partition p0_202609 values less than (‘2026-10-01 00:00:00’),
partition p0_202610 values less than (‘2026-11-01 00:00:00’),
partition p0_202611 values less than (‘2026-12-01 00:00:00’));
4、删除普通表的oms伪列与唯一索引
ALTER TABLE wt_part_change_single1 DROP INDEX UK_DEAL_CHARGE_ORDER_KM_OMS_ROWID;

ALTER TABLE wt_part_change_single1
DROP COLUMN OMS_OBJECT_NUMBER,
DROP COLUMN OMS_RELATIVE_FNO,
DROP COLUMN OMS_BLOCK_NUMBER,
DROP COLUMN OMS_ROW_NUMBER;
5、执行分区交换
ALTER TABLE wt_part_change_omspartitioned1
EXCHANGE PARTITION p0_202509
WITH TABLE wt_part_change_single1
WITHOUT VALIDATION;
报错：
ERROR 1235 (0A000): Table has drop column instant, exchange partition not supported

【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

#3

针对您遇到的问题，建议使用 obdiag 工具进行日志采集，以便我们能够更详细地了解问题的具体情况。以下是具体的命令和步骤：

日志采集场景: SQL执行出错
日志采集命令: obdiag gather scene run --scene=observer.sql_err --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

请替换上述命令中的 IP 地址、端口、用户名、密码和数据库名称，以匹配您的实际情况。同时，确保 trace_id 参数为您执行该 SQL 时生成的跟踪 ID。

为了更好地帮助您解决问题，请提供以下信息：

  1. 您的表结构是什么样的？特别是涉及哪些列被标记为即时删除（drop column instant）？
  2. 您执行的分区交换语句的具体内容是什么？
  3. 报错发生前是否对表进行了其他操作，如删除或修改列？
  4. 您当前使用的 OceanBase 版本是否有任何已知的补丁或更新？

请将采集到的日志压缩包提供给我们，以便进一步分析问题。感谢您的配合！

附上敏捷诊断工具 obdiag 使用帮助链接

#4

原因可能就是因为这个导致的删除普通表的oms伪列.
单独给该表发起合并试试

#5

租户、集群合并都测试过了，还是失败。

#6

image
这边测试成功。
你试试先删伪列再删索引。

#7

好的，我测试下。

#8

mysql> ALTER TABLE wt_part_change_single1 DROP COLUMN OMS_OBJECT_NUMBER, DROP COLUMN OMS_RELATIVE_FNO, DROP COLUMN OMS_BLOCK_NUMBER, DROP COLUMN OMS_ROW_NUMBER;
ERROR 1062 (23000): Duplicated primary key
我先删除伪劣报错

#9

MySQL [test]> CREATE TABLE wt_part_change_omspartitioned1 (
→ RETURN_MONEY_ID varchar(40) COLLATE utf8mb4_bin NOT NULL,
→ ORDER_ID varchar(80) COLLATE utf8mb4_bin DEFAULT NULL ,
→ AB_TYPE varchar(6) COLLATE utf8mb4_bin DEFAULT NULL ,
→ ORDER_TYPE decimal(38,0) DEFAULT NULL ,
→ RETURN_ORDER_STATUS varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
→ RETURN_FLAG decimal(65,0) DEFAULT NULL,
→ RETURN_MONEY_DATE datetime DEFAULT NULL ,
→ RETURN_MONEY decimal(20,2) DEFAULT NULL ,
→ RETURN_MONEY_FAL decimal(20,2) DEFAULT NULL ,
→ RETURN_MONEY_REASON varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL ,
→ RETURN_MONEY_NOTE varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL ,
→ BUYER_NAME varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
→ BUYER_BANK_CODE varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
→ BUYER_ACCOUNT varchar(100) COLLATE utf8mb4_bin DEFAULT NULL ,
→ OP_ID varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
→ PAY_TRAN_ID varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
→ PF_BH varchar(200) COLLATE utf8mb4_bin DEFAULT NULL ,
→ RETURN_MONEY_TYPE varchar(4) COLLATE utf8mb4_bin DEFAULT NULL ,
→ LIQUIDATE_DATE varchar(40) COLLATE utf8mb4_bin DEFAULT NULL ,
→ BACK_MATERIAL_NO varchar(40) COLLATE utf8mb4_bin DEFAULT NULL ,
→ create_time datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ,
→ create_user varchar(50) COLLATE utf8mb4_bin DEFAULT NULL ,
→ change_time datetime(6) DEFAULT NULL ,
→ change_user varchar(50) COLLATE utf8mb4_bin DEFAULT NULL ,
→ remark varchar(100) COLLATE utf8mb4_bin DEFAULT NULL ,
→ KEY IX_RETRUNMONEY_ID (RETURN_MONEY_ID) BLOCK_SIZE 16384 LOCAL,
→ KEY IX_RETRUNMONEY_ORDER_ID (ORDER_ID) BLOCK_SIZE 16384 LOCAL,
→ KEY IX_DEAL_ORDER_RETURN_MONEY1 (RETURN_MONEY_DATE, ORDER_ID, ORDER_TYPE) BLOCK_SIZE 16384 LOCAL
→ ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
→ partition by range columns(create_time)
→ (partition p0_202509 values less than (‘2025-10-01 00:00:00’),
→ partition p0_202510 values less than (‘2025-11-01 00:00:00’),
→ partition p0_202511 values less than (‘2025-12-01 00:00:00’),
→ partition p0_202512 values less than (‘2026-01-01 00:00:00’),
→ partition p0_202601 values less than (‘2026-02-01 00:00:00’),
→ partition p0_202602 values less than (‘2026-03-01 00:00:00’),
→ partition p0_202603 values less than (‘2026-04-01 00:00:00’),
→ partition p0_202604 values less than (‘2026-05-01 00:00:00’),
→ partition p0_202605 values less than (‘2026-06-01 00:00:00’),
→ partition p0_202606 values less than (‘2026-07-01 00:00:00’),
→ partition p0_202607 values less than (‘2026-08-01 00:00:00’),
→ partition p0_202608 values less than (‘2026-09-01 00:00:00’),
→ partition p0_202609 values less than (‘2026-10-01 00:00:00’),
→ partition p0_202610 values less than (‘2026-11-01 00:00:00’),
→ partition p0_202611 values less than (‘2026-12-01 00:00:00’));
Query OK, 0 rows affected (0.275 sec)

MySQL [test]> ALTER TABLE wt_part_change_omspartitioned1
→ EXCHANGE PARTITION p0_202509
→ WITH TABLE wt_part_change_single1
→ WITHOUT VALIDATION;
ERROR 1736 (HY000): Tables have different definitions
MySQL [test]> ALTER TABLE wt_part_change_single1
→ DROP COLUMN OMS_OBJECT_NUMBER,
→ DROP COLUMN OMS_RELATIVE_FNO,
→ DROP COLUMN OMS_BLOCK_NUMBER,
→ DROP COLUMN OMS_ROW_NUMBER;
Query OK, 0 rows affected (2.233 sec)

MySQL [test]> ALTER TABLE wt_part_change_omspartitioned1 EXCHANGE PARTITION p0_202509 WITH TABLE wt_part_change_single1 WITHOUT VALIDATION;
ERROR 1736 (HY000): Tables have different definitions
MySQL [test]> ALTER TABLE wt_part_change_single1 DROP INDEX UK_DEAL_CHARGE_ORDER_KM_OMS_ROWID;
Query OK, 0 rows affected (0.429 sec)

MySQL [test]> ALTER TABLE wt_part_change_omspartitioned1 EXCHANGE PARTITION p0_202509 WITH TABLE wt_part_change_single1 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.301 sec)

这是我的部分执行记录。具体原因这边需要问一下

#10

老师，你有插入测试数据吗？

#11

老师，我找到原因了。
1、是因为原表表结构有问题：
UNIQUE KEY UK_DEAL_CHARGE_ORDER_KM_OMS_ROWID (OMS_OBJECT_NUMBER, OMS_RELATIVE_FNO, OMS_BLOCK_NUMBER, OMS_ROW_NUMBER, create_time) LOCAL
这个唯一索引中不应该有create_time列；需要将这个列删除。
2、删除索引与字段的顺序，还是需要先删除伪列，不然还是会提示
Table has drop column instant, exchange partition not supported

#12

顺带问一句，为什么先删除索引，再删除字段会触发Table has drop column instant, exchange partition not supported，这个是什么原因，是产品问题吗？

#13

测试仅删除伪列也能交换成功

#14

嗯，删除完伪列，唯一索引就消失了

#15

Mysql如果有表带有instant列，交换失败

#16

嗯嗯，发现了。做一下表的数据重整就可以了；
如果想要彻底移除标记删除列，需要执行 ALTER TABLE TABLE_NAME FORCE 命令等待数据重整。