TiCDC changefeed stucks "Can't create more than max_prepared_stmt_count statements (current value: 16382)"

[fubinzh]

What did you do?

1. create changefeed for MySQL sink
2. run workload

   sysbench --db-driver=mysql --mysql-host=upstream-tidb.cdc-testbed-tps-1651903-1-86 --mysql-port=4000 --mysql-user=root --mysql-db=workload --tables=100 --table-size=500000 --create_secondary=off --time=3600 --threads=100 oltp_update_non_index prepare
   sysbench --db-driver=mysql --mysql-host=upstream-tidb.cdc-testbed-tps-1651903-1-86 --mysql-port=4000 --mysql-user=root --mysql-db=workload --tables=100 --table-size=500000 --create_secondary=off --time=3600 --threads=100 oltp_update_non_index run

What did you expect to see?

PITR changefeed should advance normal

What did you see instead?

PITR changefeed stucks.

In cdc log, we can see MySQL txn error: Error 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 16382)

[2023/02/26 10:16:37.314 +00:00] [ERROR] [mysql.go:184] ["execute DMLs failed"] [error="[CDC:ErrReachMaxTry]reach maximu
m try: 8, error: [CDC:ErrMySQLTxnError]MySQL txn error: Error 1461 (42000): Can't create more than max_prepared_stmt_cou
nt statements (current value: 16382): [CDC:ErrMySQLTxnError]MySQL txn error: Error 1461 (42000): Can't create more than
max_prepared_stmt_count statements (current value: 16382)"] [errorVerbose="[CDC:ErrReachMaxTry]reach maximum try: 8, err
or: [CDC:ErrMySQLTxnError]MySQL txn error: Error 1461 (42000): Can't create more than max_prepared_stmt_count statements
 (current value: 16382): [CDC:ErrMySQLTxnError]MySQL txn error: Error 1461 (42000): Can't create more than max_prepared_
stmt_count statements (current value: 16382)\[ngithub.com/pingcap/errors.AddStack\n\tgithub.com/pingcap/errors@v0.11.5-0](http://ngithub.com/pingcap/errors.AddStack/n/tgithub.com/pingcap/errors@v0.11.5-0).
20221009092201-b66cddb77c32/errors.go:174\[ngithub.com/pingcap/errors.(*Error).GenWithStackByArgs\n\tgithub.com/pingcap/e](http://ngithub.com/pingcap/errors.(*Error).GenWithStackByArgs/n/tgithub.com/pingcap/e)
rrors@v0.11.5-0.20221009092201-b66cddb77c32/normalize.go:164\[ngithub.com/pingcap/tiflow/pkg/retry.run\n\tgithub.com/ping](http://ngithub.com/pingcap/tiflow/pkg/retry.run/n/tgithub.com/ping)
cap/tiflow/pkg/retry/retry_with_opt.go:69\[ngithub.com/pingcap/tiflow/pkg/retry.Do\n\tgithub.com/pingcap/tiflow/pkg/retry](http://ngithub.com/pingcap/tiflow/pkg/retry.Do/n/tgithub.com/pingcap/tiflow/pkg/retry)
/retry_with_opt.go:34\[ngithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/mysql.(*mysqlBackend).execDMLWithMaxRetries\n\tgit](http://ngithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/mysql.(*mysqlBackend).execDMLWithMaxRetries/n/tgit)
[hub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/mysql/mysql.go:582\ngithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/mysql](http://hub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/mysql/mysql.go:582/ngithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/mysql).(*m
ysqlBackend).Flush\n\[tgithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/mysql/mysql.go:182\ngithub.com/pingcap/tiflow/cdc/s](http://tgithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/mysql/mysql.go:182/ngithub.com/pingcap/tiflow/cdc/s)
ink/dmlsink/txn.(*worker).doFlush\n\[tgithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/worker.go:214\ngithub.com/pingcap/ti](http://tgithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/worker.go:214/ngithub.com/pingcap/ti)
flow/cdc/sink/dmlsink/txn.(*worker).runBackgroundLoop.func1\n\[tgithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/worker.go](http://tgithub.com/pingcap/tiflow/cdc/sink/dmlsink/txn/worker.go):
163\nruntime.goexit\n\truntime/asm_amd64.s:1598"]

Versions of the cluster

CDC version: [2023/02/26 10:06:56.361 +00:00] [INFO] [version.go:47] ["Welcome to Change Data Capture (CDC)"] [release-version=v6.7.0 -alpha] [git-hash=00bfd0d0580d59977adf91ea9c1e237f787d0b6c] [git-branch=heads/refs/tags/v6.7.0-alpha] [utc-build-time="2 023-02-25 11:32:40"] [go-version="go version go1.20.1 linux/amd64"] [failpoint-build=false]

[fubinzh]

/severity major

[fubinzh]

/found automation

[dbsid]

/assign @dbsid