已经support 建表"engine=innodb"

大海上大 · 2022 年3 月 25 日 10:27

【产品名称】OceanBase DataBase

【产品版本】3.1.2 CE

【问题描述】迁移数据时创建表分区失败，脚本如附件所示，该脚本在mysql下执行成功，在ob下执行失败

和顺 · 2022 年3 月 25 日 11:11

CREATE TABLE `asset_survey_info_td`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  `region_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '库区类型',
  `cycle_time` datetime NOT NULL COMMENT '记录时间',
  `table_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表名',
  `instance_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物理库区',
  `data_volume` bigint(20) NULL DEFAULT NULL COMMENT '数据总量 单位:条',
  `data_incre_volume` bigint(20) NULL DEFAULT NULL COMMENT '当日数据增量 单位:条',
  `data_store` bigint(20) NULL DEFAULT NULL COMMENT '数据存储总量 单位:Byte',
  `data_incre_store` bigint(20) NULL DEFAULT NULL COMMENT '当日存储增量 单位:Byte',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `is_able` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否有效，1，有效，0，无效',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`, `cycle_time`) USING BTREE
) AUTO_INCREMENT = 2886 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '资产概况数据量天表'
ROW_FORMAT = Dynamic PARTITION BY RANGE (YEAR(cycle_time))
SUBPARTITION BY HASH (MONTH(cycle_time))
SUBPARTITIONS 12
(PARTITION `p19` VALUES LESS THAN (2020)   (SUBPARTITION `p19sp0`  ,
SUBPARTITION `p19sp1`  ,
SUBPARTITION `p19sp2`  ,
SUBPARTITION `p19sp3`  ,
SUBPARTITION `p19sp4`  ,
SUBPARTITION `p19sp5`  ,
SUBPARTITION `p19sp6`  ,
SUBPARTITION `p19sp7`  ,
SUBPARTITION `p19sp8`  ,
SUBPARTITION `p19sp9`  ,
SUBPARTITION `p19sp10`  ,
SUBPARTITION `p19sp11`  ),
PARTITION `p20` VALUES LESS THAN (2021)   (SUBPARTITION `p20sp0`  ,
SUBPARTITION `p20sp1`  ,
SUBPARTITION `p20sp2`  ,
SUBPARTITION `p20sp3`  ,
SUBPARTITION `p20sp4`  ,
SUBPARTITION `p20sp5`  ,
SUBPARTITION `p20sp6`  ,
SUBPARTITION `p20sp7`  ,
SUBPARTITION `p20sp8`  ,
SUBPARTITION `p20sp9`  ,
SUBPARTITION `p20sp10`  ,
SUBPARTITION `p20sp11`  ),
PARTITION `p21` VALUES LESS THAN (2022)   (SUBPARTITION `p21sp0`  ,
SUBPARTITION `p21sp1`  ,
SUBPARTITION `p21sp2`  ,
SUBPARTITION `p21sp3`  ,
SUBPARTITION `p21sp4`  ,
SUBPARTITION `p21sp5`  ,
SUBPARTITION `p21sp6`  ,
SUBPARTITION `p21sp7`  ,
SUBPARTITION `p21sp8`  ,
SUBPARTITION `p21sp9`  ,
SUBPARTITION `p21sp10`  ,
SUBPARTITION `p21sp11`  ),
PARTITION `p22` VALUES LESS THAN (2023)   (SUBPARTITION `p22sp0`  ,
SUBPARTITION `p22sp1`  ,
SUBPARTITION `p22sp2`  ,
SUBPARTITION `p22sp3`  ,
SUBPARTITION `p22sp4`  ,
SUBPARTITION `p22sp5`  ,
SUBPARTITION `p22sp6`  ,
SUBPARTITION `p22sp7`  ,
SUBPARTITION `p22sp8`  ,
SUBPARTITION `p22sp9`  ,
SUBPARTITION `p22sp10`  ,
SUBPARTITION `p22sp11`  ),
PARTITION `p23` VALUES LESS THAN (2024)   (SUBPARTITION `p23sp0`  ,
SUBPARTITION `p23sp1`  ,
SUBPARTITION `p23sp2`  ,
SUBPARTITION `p23sp3`  ,
SUBPARTITION `p23sp4`  ,
SUBPARTITION `p23sp5`  ,
SUBPARTITION `p23sp6`  ,
SUBPARTITION `p23sp7`  ,
SUBPARTITION `p23sp8`  ,
SUBPARTITION `p23sp9`  ,
SUBPARTITION `p23sp10`  ,
SUBPARTITION `p23sp11`  ),
PARTITION `p24` VALUES LESS THAN (2025)   (SUBPARTITION `p24sp0`  ,
SUBPARTITION `p24sp1`  ,
SUBPARTITION `p24sp2`  ,
SUBPARTITION `p24sp3`  ,
SUBPARTITION `p24sp4`  ,
SUBPARTITION `p24sp5`  ,
SUBPARTITION `p24sp6`  ,
SUBPARTITION `p24sp7`  ,
SUBPARTITION `p24sp8`  ,
SUBPARTITION `p24sp9`  ,
SUBPARTITION `p24sp10`  ,
SUBPARTITION `p24sp11`  ),
PARTITION `p25` VALUES LESS THAN (2026)   (SUBPARTITION `p25sp0`  ,
SUBPARTITION `p25sp1`  ,
SUBPARTITION `p25sp2`  ,
SUBPARTITION `p25sp3`  ,
SUBPARTITION `p25sp4`  ,
SUBPARTITION `p25sp5`  ,
SUBPARTITION `p25sp6`  ,
SUBPARTITION `p25sp7`  ,
SUBPARTITION `p25sp8`  ,
SUBPARTITION `p25sp9`  ,
SUBPARTITION `p25sp10`  ,
SUBPARTITION `p25sp11`  ),
PARTITION `p26` VALUES LESS THAN (2027)   (SUBPARTITION `p26sp0`  ,
SUBPARTITION `p26sp1`  ,
SUBPARTITION `p26sp2`  ,
SUBPARTITION `p26sp3`  ,
SUBPARTITION `p26sp4`  ,
SUBPARTITION `p26sp5`  ,
SUBPARTITION `p26sp6`  ,
SUBPARTITION `p26sp7`  ,
SUBPARTITION `p26sp8`  ,
SUBPARTITION `p26sp9`  ,
SUBPARTITION `p26sp10`  ,
SUBPARTITION `p26sp11`  ),
PARTITION `p27` VALUES LESS THAN (2028)   (SUBPARTITION `p27sp0`  ,
SUBPARTITION `p27sp1`  ,
SUBPARTITION `p27sp2`  ,
SUBPARTITION `p27sp3`  ,
SUBPARTITION `p27sp4`  ,
SUBPARTITION `p27sp5`  ,
SUBPARTITION `p27sp6`  ,
SUBPARTITION `p27sp7`  ,
SUBPARTITION `p27sp8`  ,
SUBPARTITION `p27sp9`  ,
SUBPARTITION `p27sp10`  ,
SUBPARTITION `p27sp11`  ),
PARTITION `p28` VALUES LESS THAN (2029)   (SUBPARTITION `p28sp0`  ,
SUBPARTITION `p28sp1`  ,
SUBPARTITION `p28sp2`  ,
SUBPARTITION `p28sp3`  ,
SUBPARTITION `p28sp4`  ,
SUBPARTITION `p28sp5`  ,
SUBPARTITION `p28sp6`  ,
SUBPARTITION `p28sp7`  ,
SUBPARTITION `p28sp8`  ,
SUBPARTITION `p28sp9`  ,
SUBPARTITION `p28sp10`  ,
SUBPARTITION `p28sp11`  ),
PARTITION `p29` VALUES LESS THAN (2030)   (SUBPARTITION `p29sp0`  ,
SUBPARTITION `p29sp1`  ,
SUBPARTITION `p29sp2`  ,
SUBPARTITION `p29sp3`  ,
SUBPARTITION `p29sp4`  ,
SUBPARTITION `p29sp5`  ,
SUBPARTITION `p29sp6`  ,
SUBPARTITION `p29sp7`  ,
SUBPARTITION `p29sp8`  ,
SUBPARTITION `p29sp9`  ,
SUBPARTITION `p29sp10`  ,
SUBPARTITION `p29sp11`  ),
PARTITION `plast` VALUES LESS THAN (9999)   (SUBPARTITION `plastsp0`  ,
SUBPARTITION `plastsp1`  ,
SUBPARTITION `plastsp2`  ,
SUBPARTITION `plastsp3`  ,
SUBPARTITION `plastsp4`  ,
SUBPARTITION `plastsp5`  ,
SUBPARTITION `plastsp6`  ,
SUBPARTITION `plastsp7`  ,
SUBPARTITION `plastsp8`  ,
SUBPARTITION `plastsp9`  ,
SUBPARTITION `plastsp10`  ,
SUBPARTITION `plastsp11`  ))
;

和顺 · 2022 年3 月 25 日 11:23

有些语法不兼容，需要批量删除了：

engine=innodb,

MAX_ROWS = 0 MIN_ROWS = 0

还有2级分区时候，一级分区不用写分区数

墨康 · 2022 年3 月 25 日 11:52

-- ----------------------------
-- Table structure for asset_survey_info_td
-- ----------------------------
DROP TABLE IF EXISTS `asset_survey_info_td`;
CREATE TABLE `asset_survey_info_td`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  `region_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '库区类型',
  `cycle_time` datetime NOT NULL COMMENT '记录时间',
  `table_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表名',
  `instance_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物理库区',
  `data_volume` bigint(20) NULL DEFAULT NULL COMMENT '数据总量 单位:条',
  `data_incre_volume` bigint(20) NULL DEFAULT NULL COMMENT '当日数据增量 单位:条',
  `data_store` bigint(20) NULL DEFAULT NULL COMMENT '数据存储总量 单位:Byte',
  `data_incre_store` bigint(20) NULL DEFAULT NULL COMMENT '当日存储增量 单位:Byte',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `is_able` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否有效，1，有效，0，无效',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`, `cycle_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2886 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '资产概况数据量天表' ROW_FORMAT = Dynamic 
PARTITION BY RANGE (YEAR(cycle_time))
SUBPARTITION BY HASH (MONTH(cycle_time)) subpartitions 12
( PARTITION `p19` VALUES LESS THAN (2020),
  PARTITION `p20` VALUES LESS THAN (2021),
  PARTITION `p21` VALUES LESS THAN (2022),
  PARTITION `p22` VALUES LESS THAN (2023),
  PARTITION `p23` VALUES LESS THAN (2024),
  PARTITION `p24` VALUES LESS THAN (2025),
  PARTITION `p25` VALUES LESS THAN (2026),
  PARTITION `p26` VALUES LESS THAN (2027),
  PARTITION `p27` VALUES LESS THAN (2028),
  PARTITION `p28` VALUES LESS THAN (2029),
  PARTITION `p29` VALUES LESS THAN (2030),
  PARTITION `plast` VALUES LESS THAN (9999)
);

you can try execute sql above

https://open.oceanbase.com/docs/observer-cn/V3.1.2/10000000000015789#title-w8l-20g-zu8

snowofsummer · 2022 年3 月 29 日 08:27

语法不兼容。

longda · 2022 年11 月 18 日 15:01

mysql> create table t1(id int primary key, c int) engine=innodb;
Query OK, 0 rows affected, 1 warning (0.09 sec)

engine=innodb 已经ok了