TiFlash UNION ALL Query Error

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tiflash UNION ALL查询报错

| username: wenyi

Two large data queries execute separately without issues, but when combined using UNION ALL, an error occurs.

  1. Separate queries without UNION ALL


    Querying the first statement is quick and efficient, with the execution plan as follows:

    Querying the second statement, with the execution plan as follows:

  2. Combining the two statements using UNION ALL results in an error


    The execution plan is as follows:


Error log:
tiflash_error.log (89.8 KB)

| username: xfworld | Original post link

It looks like the tunnel was closed, and no data was read…
Not sure if it’s a bug… :rofl:

| username: zhanggame1 | Original post link

Does TiDB have error logs?

| username: wenyi | Original post link

TiDB Node Logs
[2023/06/02 17:41:46.971 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895854263762945] [ID=13] [QueryTs=1685698906965726703] [LocalQueryId=10] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{RightHashJoin{Recv(8, )->Recv(11, )}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Projection->Recv(12, )}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Limit->Send(-1, )”] [mpp-version=1] [exchange-compression-mode=NONE]
[2023/06/02 17:41:51.599 +08:00] [WARN] [mpp.go:490] [“other error”] [txnStartTS=441895854263762945] [storeAddr=10.201.14.7:3930] [mpp-version=1] [error=“other error for mpp stream: From MPP<query:<query_ts:1685698906965726703, local_query_id:10, server_id:1506433, start_ts:441895854263762945>,task_id:4>: Code: 0, e.displayText() = DB::Exception: write to tunnel tunnel4+10 which is already closed, , e.what() = DB::Exception,”]
[2023/06/02 17:41:51.624 +08:00] [INFO] [executor.go:1513] [“limit executor close takes a long time”] [elapsed=24.47549ms]
[2023/06/02 17:41:51.625 +08:00] [INFO] [conn.go:1184] [“command dispatched failed”] [conn=3312681199930966419] [connInfo=“id:3312681199930966419, addr:10.203.3.95:59161 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“SELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n b.patient_id,\r\n b.id,\r\n b.id temp_id,\r\n b.inp_number visit_number,\r\n b.patient_name,\r\n b.order_project_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.start_date ttime,\r\n b.issued_dept_id dept_id,\r\n b.issued_dept_name dept_name,\r\n b.issued_doctor_name doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.orders execCombiSeq,\r\n ‘’ exec_id,\r\n ‘inp’ businesstype,\r\n ‘住院’ type,\r\n b.eye_type_name,\r\n b.recipe_kind_code AS recipe_kind,\r\n b.apply_number,\r\n NULL treatment_results,\r\n b.remarks\r\n FROM his.t_inp_order b\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON b.patient_id = pi.id\r\n WHERE b.order_state IN (7,9,10)\r\n AND EXISTS\r\n (SELECT 1\r\n FROM his.t_inp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_time >= STR_TO_DATE(‘2022-01-01 00:00:00’,‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_time < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\nUNION ALL\r\nSELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n a.patient_id,\r\n b.id,\r\n b.temp_id,\r\n a.reg_number visit_number,\r\n a.patient_name,\r\n b.item_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.billing_time ttime,\r\n b.dept_id,\r\n b.dept_name,\r\n b.doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.exec_combi_seq execCombiSeq,\r\n ‘’ exec_id,\r\n ‘outp’ businesstype,\r\n ‘门诊’ type,\r\n b.eye_type_name,\r\n b.recipe_kind,\r\n b.apply_number,\r\n r.treatment_results,\r\n b.remarks\r\n FROM his.t_outp_order a\r\n LEFT JOIN his.t_outp_order_detail b\r\n ON a.id = b.t_outp_order_id\r\n AND a.hosp_id = b.hosp_id\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON a.patient_id = pi.id\r\n LEFT JOIN his.t_outp_order_exec_record r\r\n ON b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n WHERE EXISTS\r\n (SELECT 1\r\n FROM his.t_outp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_date >= STR_TO_DATE(‘2022-01-01 00:00:00’, ‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_date < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\n AND b.order_state IN (7,9,10)\r\nORDER BY EXEC_TIME DESC limit 100”] [txn_mode=PESSIMISTIC] [timestamp=441895854263762945] [err=“other error for mpp stream: From MPP<query:<query_ts:1685698906965726703, local_query_id:10, server_id:1506433, start_ts:441895854263762945>,task_id:4>: Code: 0, e.displayText() = DB::Exception: write to tunnel tunnel4+10 which is already closed, , e.what() = DB::Exception,\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).handleMPPStreamResponse\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:489\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).establishMPPConns\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:447\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).handleDispatchReq\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:353\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).run.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:194\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1598”]
[2023/06/02 17:42:16.018 +08:00] [INFO] [domain.go:2652] [“refreshServerIDTTL succeed”] [serverID=1506433] [“lease id”=4779887b6101dad6]
[2023/06/02 17:46:45.446 +08:00] [INFO] [fragment.go:120] [“Mpp will generate tasks”] [plan=LeftHashJoin{Recv()->Recv()}(his.t_inp_order.id,his.t_inp_order_exec_record.order_id)(his.t_inp_order.hosp_id,his.t_inp_order_exec_record.hosp_id)->Limit->Send()] [mpp-version=1]
[2023/06/02 17:46:45.446 +08:00] [INFO] [fragment.go:120] [“Mpp will generate tasks”] [plan=RightHashJoin{RightHashJoin{Recv()->Recv()}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Projection->Recv()}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Limit->Send()] [mpp-version=1]
[2023/06/02 17:46:45.447 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.448 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.448 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.448 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.449 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.449 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=1] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_inp_order)->Sel([in(his.t_inp_order.order_state, 7, 9, 10)])->Send(4, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=3] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_dcg_patient_info)->Send(4, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=4] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“LeftHashJoin{Recv(1, )->Recv(3, )}(his.t_inp_order.patient_id,base.t_dcg_patient_info.id)->Send(10, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=9] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_inp_order_exec_record)->Projection->Send(10, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=10] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“LeftHashJoin{Recv(4, )->Recv(9, )}(his.t_inp_order.id,his.t_inp_order_exec_record.order_id)(his.t_inp_order.hosp_id,his.t_inp_order_exec_record.hosp_id)->Limit->Send(-1, )”] [mpp-version=1] [exchange-compression-mode=NONE]
[2023/06/02 17:46:45.449 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.450 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.450 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=2] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order_detail)->Sel([not(isnull(his.t_outp_order_detail.hosp_id))])->Send(6, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.450 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=5] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order)->Send(6, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=6] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{Recv(2, )->Recv(5, )}(his.t_outp_order_detail.t_outp_order_id,his.t_outp_order.id)(his.t_outp_order_detail.hosp_id,his.t_outp_order.hosp_id)->Send(8, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=7] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_dcg_patient_info)->Send(8, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=8] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{Recv(6, )->Recv(7, )}(his.t_outp_order.patient_id,base.t_dcg_patient_info.id)->Send(13, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=11] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order_exec_record)->Sel([not(isnull(his.t_outp_order_exec_record.order_id))])->Send(13, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_g

| username: zhanggame1 | Original post link

The logs don’t show much. You can first try analyzing the related tables, and then adjust the memory parameters. Refer to the best answer in this link:

| username: wenyi | Original post link

I think it is related to the large amount of data. When querying a small amount of data, there is no error, but when querying a large amount of data, an error occurs. Adjusting the memory is a direction to solve the problem. Could you please advise on which specific memory parameters to adjust?

Looking at the official documentation, the memory parameters are already configured quite reasonably.

[profiles.default]
## Whether to use logical splitting for the segment of the storage engine. Using logical splitting can reduce write amplification, but it may cause some delay in disk space reclamation. The default is false.
## In v6.2.0 and later versions, it is strongly recommended to keep the default value false and not change it to true. For details, please refer to the known issue #5576.
# dt_enable_logical_split = false

## Memory limit for intermediate data on a single query
## When set to an integer, the unit is byte, for example, 34359738368 means a memory limit of 32 GiB, 0 means no limit
## When set to a floating point number between [0.0, 1.0), it indicates the proportion of the total memory of the node, for example, 0.8 means 80% of the total memory, 0.0 means no limit
## The default value is 0, which means no limit
## When a query attempts to request more memory than the limit, the query is terminated and an error is reported
max_memory_usage = 0

## Memory limit for intermediate data on all queries
## When set to an integer, the unit is byte, for example, 34359738368 means a memory limit of 32 GiB, 0 means no limit
## When set to a floating point number between [0.0, 1.0), it indicates the proportion of the total memory of the node, for example, 0.8 means 80% of the total memory, 0.0 means no limit
## The default value is 0.8, which means 80% of the total memory
## When a query attempts to request more memory than the limit, the query is terminated and an error is reported
max_memory_usage_for_all_queries = 0.8
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.