lightning will fail for "exceeding the allowed memory limit for a single SQL query" when conflict rows is huge and duplicate-resolution=replace

[seiya-annie]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set duplicate-resolution=replace
import data with 4540384 rows conflict

2. What did you expect to see? (Required)

import successfully

3. What did you see instead (Required)

import fail

2024/03/12 15:30:08.075 +00:00] [ERROR] [duplicate.go:998] ["[resolve-dupe] resolve duplicate rows failed"] [table=`IFRS9`.`ECL_RETAIL_LOAN_BIZ_INFO`] [takeTime=3h51m13.910246842s] [error="delete additionally inserted rows for conflict detection 'replace' mode failed: Error 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=868222532]"]
[2024/03/12 15:30:08.075 +00:00] [ERROR] [table_import.go:1064] ["resolve remote duplicate keys failed"] [table=`IFRS9`.`ECL_RETAIL_LOAN_BIZ_INFO`] [error="delete additionally inserted rows for conflict detection 'replace' mode failed: Error 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=868222532]"]
[2024/03/12 15:30:08.075 +00:00] [ERROR] [import.go:1678] ["restore all tables data failed"] [takeTime=3h54m42.029893354s] [error="delete additionally inserted rows for conflict detection 'replace' mode failed: Error 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=868222532]"]
[2024/03/12 15:30:08.075 +00:00] [INFO] [import.go:1273] ["everything imported, stopping periodic actions"]
[2024/03/12 15:30:08.077 +00:00] [ERROR] [import.go:568] ["run failed"] [step=4] [error="delete additionally inserted rows for conflict detection 'replace' mode failed: Error 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=868222532]"]
[2024/03/12 15:30:08.077 +00:00] [ERROR] [import.go:578] ["the whole procedure failed"] [takeTime=3h54m42.965670694s] [error="delete additionally inserted rows for conflict detection 'replace' mode failed: Error 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=868222532]"]
[2024/03/12 15:30:08.077 +00:00] [WARN] [errormanager.go:980] ["Detect 4540384 conflict errors in total, please refer to table `lightning_task_info`.`conflict_error_v2` for more details"]
[2024/03/12 15:30:08.167 +00:00] [WARN] [engine_mgr.go:547] ["remove local db file failed"] [error="unlinkat /tmp/sorted-kv-dir: device or resource busy"]
[2024/03/12 15:30:08.169 +00:00] [INFO] [client.go:319] ["[pd] http client closed"] [source=lightning]
[2024/03/12 15:30:08.169 +00:00] [ERROR] [main.go:105] ["tidb lightning encountered error stack info"] [error="delete additionally inserted rows for conflict detection 'replace' mode failed: Error 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=868222532]"] [errorVerbose="Error 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=868222532]\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/[email protected]/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/[email protected]/juju_adaptor.go:15\ngithub.com/pingcap/tidb/br/pkg/lightning/errormanager.(*ErrorManager).ReplaceConflictKeys.func1.3\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/errormanager/errormanager.go:761\ngithub.com/pingcap/tidb/br/pkg/lightning/common.SQLWithRetry.Transact.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:266\ngithub.com/pingcap/tidb/br/pkg/lightning/common.Retry\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:190\ngithub.com/pingcap/tidb/br/pkg/lightning/common.SQLWithRetry.perform\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:175\ngithub.com/pingcap/tidb/br/pkg/lightning/common.SQLWithRetry.Transact\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/common/util.go:260\ngithub.com/pingcap/tidb/br/pkg/lightning/errormanager.(*ErrorManager).ReplaceConflictKeys.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/lightning/errormanager/errormanager.go:753\ngithub.com/pingcap/tidb/br/pkg/utils.(*WorkerPool).ApplyOnErrorGroup.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/br/br/pkg/utils/worker.go:76\ngolang.org/x/sync/errgroup.(*Group).Go.func1\n\t/go/pkg/mod/golang.org/x/[email protected]/errgroup/errgroup.go:78\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1650\ndelete additionally inserted rows for conflict detection 'r

4. What is your TiDB version? (Required)

master

[lyzx2001]

Currently when the data conflict portion is too high (in this case is 40%), conflict detection replace mode may result in this error due to potential select query that may take up too much memory. This issue will be solved by performance optimization, which is planned to be conducted in v8.1.

[lyzx2001]

In v8.0 we encountered the same issue, where we set conflict.strategy = "replace" and conflict.precheck-conflict-before-import = false

[lyzx2001]

solved by #52656