cooldwind opened a new issue #7637: URL: https://github.com/apache/incubator-doris/issues/7637
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues. ### Version 0.15.1-rc09 ### What's Wrong? 报错信息： 通过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" ); ### What You Expected? 解决就好 ### How to Reproduce? _No response_ ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
