insert into be 频繁挂掉

[cooldwind]

#7634

• 系统版本：0.15.1-rc09
• 报错信息：
  通过insert into select 更新表，只执行select也会导致be挂掉。

• 执行了什么操作导致的报错：
  *通过调度任务跑 sql 脚本 ，数据量不大，但是be频发挂掉。
  insert into select 操作。be挂掉几率为 90%， fe节点正常，但是be节点会无故崩溃挂掉，经常是挂掉1个，2个be，当be挂掉后重启挂掉的be，有时反复启动挂掉的不同be后，sql偶尔可执行成功。
• 操作系统：centOS 7
• 机器配置：CPU核数、内存、磁盘
  生产环境：3台fe节点，16核CPU，32G内存，300G HDD，3台be节点，16核CPU，32G内存，500G HDD。共5台，其中一台 FE，BE 混部。exec_mem_limit = 8G, mem_limit = 90%。
• 平台：x86
• 是否能复现以及复现步骤
  可以，重新执行sql调度任务。
• 具体表现：
  调度器执行sql:
  insert into ads_trade_stat
  (
  biz_date
  ,app_name
  ,spu_id
  ,spu_title
  ,spu_kind
  ,cate_id
  ,cate_name1
  ,cate_name2
  ,order_user_cnt
  ,order_cnt
  ,order_amt
  ,pay_user_cnt
  ,pay_cnt
  ,pay_amt
  ,refund_user_cnt
  ,refund_cnt
  ,refund_amt
  ,create_time
  ,udpate_time
  )
  SELECT
  DATE(a.biz_create_time) AS biz_date,
  c.app_name,
  a.spu_id,
  b.spu_title,
  b.spu_kind,
  b.cate_id,
  f.cate_name1,
  f.cate_name2,
  COUNT(DISTINCT a.user_id) AS order_user_cnt,
  COUNT(DISTINCT a.oid) AS order_cnt,
  SUM(a.total_amount) AS order_amt,
  COUNT(DISTINCT d.user_id) AS pay_user_cnt,
  COUNT(DISTINCT d.oid) AS pay_cnt,
  IFNULL(SUM(d.pay_amount),0) AS pay_amt,
  COUNT(DISTINCT e.user_id) AS refund_user_cnt,
  COUNT(DISTINCT e.oid) AS refund_cnt,
  IFNULL(SUM(e.amount),0)AS refund_amt,
  current_timestamp() as create_time,
  current_timestamp() as update_time
  FROM
  dwd_trade_order_detail a
  LEFT JOIN dwd_trade_pay d ON a.oid=d.oid and d.pay_status='paid'
  LEFT JOIN dwd_trade_refund_detail e ON a.oid=e.oid and a.iid=e.iid and e.refund_status in ('return_completed','refund_completed')
  LEFT JOIN dim_sku b ON a.spu_id = b.spu_id AND a.spu_spec_id = b.spu_spec_id and b.deleted='N'
  LEFT JOIN dim_item_category f ON b.cate_id=f.id and f.deleted='N'
  LEFT JOIN dim_support_app c ON a.app_id = c.id
  WHERE
  a.order_deleted='N' AND a.order_item_deleted='N'
  GROUP BY
  DATE(a.biz_create_time),
  c.app_name,
  a.spu_id,
  b.spu_title,
  b.spu_kind,
  b.cate_id,
  f.cate_name1,
  f.cate_name2;

数据量：
select count(1) from dwd_trade_order_detail 73071
select count(1) from dwd_trade_pay 237612
select count(1) from dwd_trade_refund_detail 5384
select count(1) from dim_sku 4474
select count(1) from dim_item_category 9
select count(1) from dim_support_app 11

插入的数据也就是 1000 条左右;

建立表语句：
CREATE TABLE ads_trade_stat (
biz_date date NULL COMMENT "",
app_name text NULL COMMENT "",
spu_id bigint(20) NULL COMMENT "",
spu_title text NULL COMMENT "",
spu_kind text NULL COMMENT "",
cate_id bigint(20) NULL COMMENT "",
cate_name1 text NULL COMMENT "",
cate_name2 text NULL COMMENT "",
order_user_cnt bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
order_cnt bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
order_amt bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_user_cnt bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_cnt bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_amt bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_user_cnt bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_cnt bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_amt bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
create_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
udpate_time datetime REPLACE_IF_NOT_NULL NULL COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(biz_date, app_name, spu_id, spu_title, spu_kind, cate_id, cate_name1, cate_name2)
COMMENT "订单全链路统计"
DISTRIBUTED BY HASH(biz_date) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"in_memory" = "false",
"storage_format" = "V2"
);

CREATE TABLE dwd_trade_order_detail (
id bigint(20) NOT NULL COMMENT "",
oid bigint(20) NULL COMMENT "",
iid bigint(20) NULL COMMENT "",
biz_create_time datetime NULL COMMENT "",
app_id bigint(20) NULL COMMENT "",
store_id bigint(20) NULL COMMENT "",
user_id bigint(20) NULL COMMENT "",
spu_id bigint(20) NULL COMMENT "",
spu_spec_id bigint(20) NULL COMMENT "",
coupon_id bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
marketing_coupon_id bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
oid_status text REPLACE_IF_NOT_NULL NULL COMMENT "",
iid_status text REPLACE_IF_NOT_NULL NULL COMMENT "",
create_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
order_type text REPLACE_IF_NOT_NULL NULL COMMENT "",
order_source text REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
ship_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
complete_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
deadline_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
cancel_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
cost_price bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
price bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
num bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
total_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
total_discount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_num bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
shipped_num bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
delivery_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
city text REPLACE_IF_NOT_NULL NULL COMMENT "",
detail_address text REPLACE_IF_NOT_NULL NULL COMMENT "",
region text REPLACE_IF_NOT_NULL NULL COMMENT "",
area_code bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
province text REPLACE_IF_NOT_NULL NULL COMMENT "",
receiver_name text REPLACE_IF_NOT_NULL NULL COMMENT "",
receiver_phone text REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_invoice text REPLACE_IF_NOT_NULL NULL COMMENT "",
points_send bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
points_limit bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
coupon_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
coupon_name text REPLACE_IF_NOT_NULL NULL COMMENT "",
order_archive text REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_kind text REPLACE_IF_NOT_NULL NULL COMMENT "",
item_system text REPLACE_IF_NOT_NULL NULL COMMENT "",
member_contact_name text REPLACE_IF_NOT_NULL NULL COMMENT "",
member_contact_phone text REPLACE_IF_NOT_NULL NULL COMMENT "",
note text REPLACE_IF_NOT_NULL NULL COMMENT "",
note2 text REPLACE_IF_NOT_NULL NULL COMMENT "",
ship_Ids text REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_ids text REPLACE_IF_NOT_NULL NULL COMMENT "",
gift_num bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
gift_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
order_update_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
order_item_update_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
order_deleted text REPLACE_IF_NOT_NULL NULL COMMENT "",
order_item_deleted text REPLACE_IF_NOT_NULL NULL COMMENT "",
update_time datetime REPLACE_IF_NOT_NULL NULL COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(id, oid, iid, biz_create_time, app_id, store_id, user_id, spu_id, spu_spec_id)
COMMENT "交易订单事实表"
PARTITION BY RANGE(biz_create_time)()
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "16",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "400",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"in_memory" = "false",
"storage_format" = "V2"
);

CREATE TABLE dwd_trade_pay (
id bigint(20) NULL COMMENT "",
oid bigint(20) NULL COMMENT "",
app_id bigint(20) NULL COMMENT "",
store_id bigint(20) NULL COMMENT "",
user_id bigint(20) NULL COMMENT "",
pay_create_time datetime NULL COMMENT "",
pay_invoice text REPLACE_IF_NOT_NULL NULL COMMENT "",
token text REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_channel text REPLACE_IF_NOT_NULL NULL COMMENT "",
channel_pay_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_points bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_plat text REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_resource text REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_status text REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_update_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
deleted text REPLACE_IF_NOT_NULL NULL COMMENT "",
note text REPLACE_IF_NOT_NULL NULL COMMENT "",
create_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
update_time datetime REPLACE_IF_NOT_NULL NULL COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(id, oid, app_id, store_id, user_id, pay_create_time)
COMMENT "支付事实表"
PARTITION BY RANGE(pay_create_time)()
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "16",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "400",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"in_memory" = "false",
"storage_format" = "V2"
);

CREATE TABLE dwd_trade_refund_detail (
refund_id bigint(20) NULL COMMENT "",
refund_create_time datetime NULL COMMENT "",
oid bigint(20) NULL COMMENT "",
iid bigint(20) NULL COMMENT "",
app_id bigint(20) NULL COMMENT "",
store_id bigint(20) NULL COMMENT "",
user_id bigint(20) NULL COMMENT "",
invoice text REPLACE_IF_NOT_NULL NULL COMMENT "",
operator_id bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
ship_fee bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
num bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
cash_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
points_add bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
points_reduce bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
used_redride bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
pay_plat text REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_reason text REPLACE_IF_NOT_NULL NULL COMMENT "",
note text REPLACE_IF_NOT_NULL NULL COMMENT "",
ship_company text REPLACE_IF_NOT_NULL NULL COMMENT "",
ship_no text REPLACE_IF_NOT_NULL NULL COMMENT "",
ship_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
receive_type text REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_type text REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_kind text REPLACE_IF_NOT_NULL NULL COMMENT "",
initiate text REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_success_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_status text REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_update_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
deleted text REPLACE_IF_NOT_NULL NULL COMMENT "",
create_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
update_time datetime REPLACE_IF_NOT_NULL NULL COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(refund_id, refund_create_time, oid, iid, app_id, store_id, user_id)
COMMENT "退款"
DISTRIBUTED BY HASH(refund_id) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"in_memory" = "false",
"storage_format" = "V2"
);

CREATE TABLE dim_sku (
id bigint(20) NOT NULL COMMENT "",
spu_id bigint(20) NULL COMMENT "",
spu_spec_id bigint(20) NULL COMMENT "",
spu_create_time datetime NULL COMMENT "",
key1 text REPLACE_IF_NOT_NULL NULL COMMENT "",
value1 text REPLACE_IF_NOT_NULL NULL COMMENT "",
key2 text REPLACE_IF_NOT_NULL NULL COMMENT "",
value2 text REPLACE_IF_NOT_NULL NULL COMMENT "",
key3 text REPLACE_IF_NOT_NULL NULL COMMENT "",
value3 text REPLACE_IF_NOT_NULL NULL COMMENT "",
spu_spec_code text REPLACE_IF_NOT_NULL NULL COMMENT "",
price bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
market_price bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
cost_price bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
points_limit bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
points_send bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
weight bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
gift_ride_id bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
gift_ride_name text REPLACE_IF_NOT_NULL NULL COMMENT "",
gift_ride_amount bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
gift_ride_content text REPLACE_IF_NOT_NULL NULL COMMENT "",
app_ids text REPLACE_IF_NOT_NULL NULL COMMENT "",
app_names text REPLACE_IF_NOT_NULL NULL COMMENT "",
store_id bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
brand_id bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
brand_name text REPLACE_IF_NOT_NULL NULL COMMENT "",
brand_type text REPLACE_IF_NOT_NULL NULL COMMENT "",
cate_id bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
group_ids text REPLACE_IF_NOT_NULL NULL COMMENT "",
group_names text REPLACE_IF_NOT_NULL NULL COMMENT "",
supplier_id bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
supplier_name text REPLACE_IF_NOT_NULL NULL COMMENT "",
spu_kind text REPLACE_IF_NOT_NULL NULL COMMENT "",
channel text REPLACE_IF_NOT_NULL NULL COMMENT "",
spu_title text REPLACE_IF_NOT_NULL NULL COMMENT "",
spu_sub_title text REPLACE_IF_NOT_NULL NULL COMMENT "",
spu_share_title text REPLACE_IF_NOT_NULL NULL COMMENT "",
start_num int(11) REPLACE_IF_NOT_NULL NULL COMMENT "",
limit_channel text REPLACE_IF_NOT_NULL NULL COMMENT "",
limit_menthod text REPLACE_IF_NOT_NULL NULL COMMENT "",
limit_num int(11) REPLACE_IF_NOT_NULL NULL COMMENT "",
delivery_method text REPLACE_IF_NOT_NULL NULL COMMENT "",
spu_system text REPLACE_IF_NOT_NULL NULL COMMENT "",
labels text REPLACE_IF_NOT_NULL NULL COMMENT "",
settle_method text REPLACE_IF_NOT_NULL NULL COMMENT "",
include_all_shops text REPLACE_IF_NOT_NULL NULL COMMENT "",
exclude_shops text REPLACE_IF_NOT_NULL NULL COMMENT "",
refund_kind text REPLACE_IF_NOT_NULL NULL COMMENT "",
spu_status text REPLACE_IF_NOT_NULL NULL COMMENT "",
up_date datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
down_date datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
last_up_date datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
last_down_date datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
effect_start_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
effect_end_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
take_effect_hours bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
effect_days bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
spu_update_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
deleted text REPLACE_IF_NOT_NULL NULL COMMENT "",
create_time datetime REPLACE_IF_NOT_NULL NULL COMMENT "",
update_time datetime REPLACE_IF_NOT_NULL NULL COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(id, spu_id, spu_spec_id, spu_create_time)
COMMENT "SKU维度表"
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"in_memory" = "false",
"storage_format" = "V2"
);

[cooldwind]

String 类型问题，string 不用做 where 条件和 join on 条件。string换成 varchar类型解决。