【已解决】新建自动分区表，insert数据时be节点全部挂掉重启

Viewed 92
2.1 upgrade

version:2.1.1(从2.0.5升级）
建表SQL

CREATE TABLE `imp_base_target_unique_part_auto` (
  `pk_id` VARCHAR(64) NOT NULL,
  `collect_date` DATE NOT NULL COMMENT '采集日期',
  `collect_timestamp` DATETIME NULL COMMENT '采集时间戳',
  `create_ts` DATETIME NULL,
  `sys_type` VARCHAR(8) NULL COMMENT '系统类型',
  `source_type` VARCHAR(8) NULL COMMENT '来源系统',
  `message_type` VARCHAR(8) NULL COMMENT '消息类型',
  `id` BIGINT NOT NULL COMMENT '项目主键',
  `code` VARCHAR(128) NOT NULL COMMENT '项目编码',
  `room_id` BIGINT NOT NULL COMMENT '站点主键',
  `room_code` VARCHAR(128) NOT NULL COMMENT '站点编码',
  `room_name` VARCHAR(256) NULL COMMENT '站点名称',
  `item_id` BIGINT NULL COMMENT '回路主键',
  `item_code` VARCHAR(128) NOT NULL COMMENT '回路编码',
  `item_name` VARCHAR(256) NULL COMMENT '回路名称',
  `target_code` VARCHAR(68) NOT NULL COMMENT '指标编码',
  `target_type_code` VARCHAR(68) NULL COMMENT '指标类型编码',
  `target_group` VARCHAR(68) NULL COMMENT '指标分组编码',
  `first_name` VARCHAR(68) NULL,
  `second_name` VARCHAR(68) NULL,
  `bm_name` VARCHAR(68) NULL,
  `reason` VARCHAR(8) NULL,
  `tag_value` DECIMAL(20, 4) NULL COMMENT '值',
  `collect_ts` VARCHAR(32) NULL COMMENT '采集时间戳',
  `collect_time` VARCHAR(32) NULL COMMENT '采集时间',
  `collect_hour` VARCHAR(32) NOT NULL COMMENT '采集小时',
  `collect_minute` VARCHAR(32) NOT NULL COMMENT '采集分钟',
  `room_status_code` VARCHAR(64) NULL COMMENT '站点允许状态',
  `single_id` VARCHAR(64) NULL COMMENT '物联点',
  `is_miss` VARCHAR(32) NULL
) ENGINE=OLAP
UNIQUE KEY(`pk_id`, `collect_date`)
COMMENT 'OLAP'
auto PARTITION BY RANGE(date_trunc(`collect_date`,'day'))()
DISTRIBUTED BY HASH(`pk_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"enable_unique_key_merge_on_write" = "true"
);

insert

insert into imp_db.imp_base_target_unique_part_auto (pk_id,create_ts, sys_type, source_type,
        message_type, id, code,
        room_id, room_code, room_name,
        item_id, item_code, item_name,
        target_code, target_type_code, target_group,tag_value, collect_timestamp,
        collect_ts, collect_time, collect_hour,
        collect_minute, collect_date, room_status_code,single_id,reason,is_miss)
        values
          
            ('52f479e763cb5ebd7e0','2024-04-10', '0',
            '0',
            '1', 9945417181, 'HXLIVE',
            99454255, 'HXZP', '冰上',
            null, '4217-2', null,
            'UBC', 'target_type2',
            'target_group_03', 392.69,
            '2024-04-10',
            '2024-04-10 16:17:46', '16:20:00',
            '16',
            '20', '2024-04-10',
            null,'c727199892161af584df71','1','0');

image.png

zhangjj77
3 Answers

【问题状态】已复现
【问题处理】问题已经解决，2.1.2版本会修改
这块你建表的时候可以先使用这个
CREATE TABLE imp_base_target_unique_part_auto4 (
pk_id VARCHAR(64) NOT NULL,
collect_date DATE NOT NULL COMMENT '采集日期',
collect_timestamp DATETIME NULL COMMENT '采集时间戳',
create_ts DATETIME NULL,
sys_type VARCHAR(8) NULL COMMENT '系统类型',
source_type VARCHAR(8) NULL COMMENT '来源系统',
message_type VARCHAR(8) NULL COMMENT '消息类型',
id BIGINT NOT NULL COMMENT '项目主键',
code VARCHAR(128) NOT NULL COMMENT '项目编码',
room_id BIGINT NOT NULL COMMENT '站点主键',
room_code VARCHAR(128) NOT NULL COMMENT '站点编码',
room_name VARCHAR(256) NULL COMMENT '站点名称',
item_id BIGINT NULL COMMENT '回路主键',
item_code VARCHAR(128) NOT NULL COMMENT '回路编码',
item_name VARCHAR(256) NULL COMMENT '回路名称',
target_code VARCHAR(68) NOT NULL COMMENT '指标编码',
target_type_code VARCHAR(68) NULL COMMENT '指标类型编码',
target_group VARCHAR(68) NULL COMMENT '指标分组编码',
first_name VARCHAR(68) NULL,
second_name VARCHAR(68) NULL,
bm_name VARCHAR(68) NULL,
reason VARCHAR(8) NULL,
tag_value DECIMAL(20, 4) NULL COMMENT '值',
collect_ts VARCHAR(32) NULL COMMENT '采集时间戳',
collect_time VARCHAR(32) NULL COMMENT '采集时间',
collect_hour VARCHAR(32) NOT NULL COMMENT '采集小时',
collect_minute VARCHAR(32) NOT NULL COMMENT '采集分钟',
room_status_code VARCHAR(64) NULL COMMENT '站点允许状态',
single_id VARCHAR(64) NULL COMMENT '物联点',
is_miss VARCHAR(32) NULL
) ENGINE=OLAP
UNIQUE KEY(pk_id, collect_date)
COMMENT 'OLAP'
AUTO PARTITION BY RANGE date_trunc(collect_date, 'day')
()
DISTRIBUTED BY HASH(pk_id) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

徐振超@SelectDB6326

CREATE TABLE imp_base_target_unique_part_auto2 (
pk_id VARCHAR(64) NOT NULL,
collect_date DATE NOT NULL COMMENT '采集日期',
collect_timestamp DATETIME NULL COMMENT '采集时间戳',
create_ts DATETIME NULL,
sys_type VARCHAR(8) NULL COMMENT '系统类型',
source_type VARCHAR(8) NULL COMMENT '来源系统',
message_type VARCHAR(8) NULL COMMENT '消息类型',
id BIGINT NOT NULL COMMENT '项目主键',
code VARCHAR(128) NOT NULL COMMENT '项目编码',
room_id BIGINT NOT NULL COMMENT '站点主键',
room_code VARCHAR(128) NOT NULL COMMENT '站点编码',
room_name VARCHAR(256) NULL COMMENT '站点名称',
item_id BIGINT NULL COMMENT '回路主键',
item_code VARCHAR(128) NOT NULL COMMENT '回路编码',
item_name VARCHAR(256) NULL COMMENT '回路名称',
target_code VARCHAR(68) NOT NULL COMMENT '指标编码',
target_type_code VARCHAR(68) NULL COMMENT '指标类型编码',
target_group VARCHAR(68) NULL COMMENT '指标分组编码',
first_name VARCHAR(68) NULL,
second_name VARCHAR(68) NULL,
bm_name VARCHAR(68) NULL,
reason VARCHAR(8) NULL,
tag_value DECIMAL(20, 4) NULL COMMENT '值',
collect_ts VARCHAR(32) NULL COMMENT '采集时间戳',
collect_time VARCHAR(32) NULL COMMENT '采集时间',
collect_hour VARCHAR(32) NOT NULL COMMENT '采集小时',
collect_minute VARCHAR(32) NOT NULL COMMENT '采集分钟',
room_status_code VARCHAR(64) NULL COMMENT '站点允许状态',
single_id VARCHAR(64) NULL COMMENT '物联点',
is_miss VARCHAR(32) NULL
) ENGINE=OLAP
UNIQUE KEY(pk_id, collect_date)
COMMENT 'OLAP'
auto PARTITION BY RANGE date_trunc(collect_date,'day')()
DISTRIBUTED BY HASH(pk_id) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"enable_unique_key_merge_on_write" = "true"
);
但是show create table 的结果是一样的，range后边的date_trunc没有在括号里边
image.png
image.png

zhangjj77

这是一个已知问题，仅在2.1.1出现，我们在2.1.2版本修复了。可以等待最新版本

zclllyybb406