search

apache / doris

Apache Doris is an easy-to-use, high performance and unified analytics database.
https://doris.apache.org
Apache License 2.0
12.53k stars 3.24k forks source link

[Bug] #7637

Closed cooldwind closed 2 years ago

cooldwind commented 2 years ago

Search before asking

Version

0.15.1-rc09

What's Wrong?

报错信息： 通过insert into select 更新表，只执行select也会导致be挂掉。 B41B31DA-F095-4e65-9080-3FDB00C26AE9

执行了什么操作导致的报错： *通过调度任务跑 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?

Code of Conduct

morningman commented 2 years ago

Could you provide some sample data to reproduce this bug?

cooldwind commented 2 years ago

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