Require LOCK TABLES privilege in MySQL CDC connector

[QuantumBear]

Is your feature request related to a problem? Please describe.

When I try to create MySQL CDC source table, I got such error in connector node.

2023-08-28 02:01:28,011 WARN [grpc-default-executor-0] source.SourceValidateHandler:48 - Source validation failed io.grpc.StatusRuntimeException: INVALID_ARGUMENT: MySQL user does not have privilege LOCK TABLES, which is needed for debezium connector at io.grpc.Status.asRuntimeException(Status.java:526) ~[grpc-api-1.49.0.jar:1.49.0] at com.risingwave.connector.source.common.ValidatorUtils.invalidArgument(ValidatorUtils.java:36) ~[risingwave-connector-service-1.0-SNAPSHOT.jar:?] at com.risingwave.connector.source.common.MySqlValidator.validatePrivileges(MySqlValidator.java:186) ~[risingwave-connector-service-1.0-SNAPSHOT.jar:?] at com.risingwave.connector.source.common.MySqlValidator.validateUserPrivilege(MySqlValidator.java:97) ~[risingwave-connector-service-1.0-SNAPSHOT.jar:?] at com.risingwave.connector.source.common.DatabaseValidator.validateAll(DatabaseValidator.java:21) ~[risingwave-connector-service-1.0-SNAPSHOT.jar:?] at com.risingwave.connector.source.SourceValidateHandler.validateSource(SourceValidateHandler.java:120) ~[risingwave-connector-service-1.0-SNAPSHOT.jar:?] at com.risingwave.connector.source.SourceValidateHandler.handle(SourceValidateHandler.java:41) [risingwave-connector-service-1.0-SNAPSHOT.jar:?] at com.risingwave.connector.ConnectorServiceImpl.validateSource(ConnectorServiceImpl.java:49) [risingwave-connector-service-1.0-SNAPSHOT.jar:?] at com.risingwave.proto.ConnectorServiceGrpc$MethodHandlers.invoke(ConnectorServiceGrpc.java:398) [proto-1.0-SNAPSHOT.jar:?] at io.grpc.stub.ServerCalls$UnaryServerCallHandler$UnaryServerCallListener.onHalfClose(ServerCalls.java:182) [grpc-stub-1.49.0.jar:1.49.0] at io.grpc.internal.ServerCallImpl$ServerStreamListenerImpl.halfClosed(ServerCallImpl.java:354) [grpc-core-1.49.0.jar:1.49.0] at io.grpc.internal.ServerImpl$JumpToApplicationThreadServerStreamListener$1HalfClosed.runInContext(ServerImpl.java:866) [grpc-core-1.49.0.jar:1.49.0] at io.grpc.internal.ContextRunnable.run(ContextRunnable.java:37) [grpc-core-1.49.0.jar:1.49.0] at io.grpc.internal.SerializingExecutor.run(SerializingExecutor.java:133) [grpc-core-1.49.0.jar:1.49.0] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?] at java.lang.Thread.run(Thread.java:829) [?:?]

Describe the solution you'd like

Flink CDC source connector is lock free. Plz check this article:
https://netflixtechblog.com/dblog-a-generic-change-data-capture-framework-69351fb9099b

Describe alternatives you've considered

No response

Additional context

No response

[StrikeW]

Thanks for your suggestion. Right now the snapshot phase of CDC still require locking, but we are working on the lock-free and incremental snapshot feature. And you can refer to the design doc for technical details risingwavelabs/rfcs#63.

[fuyufjh]

I think for now we should document the privilege requirement in docs?

[StrikeW]

I already submited a pr (https://github.com/risingwavelabs/risingwave-docs/pull/1214) to fix the doc, but it only reflects in the 1.2 (dev) version, I'll contact doc team to also update previous version.

[StrikeW]

Hi @QuantumBear, we have implemented CDC backfill feature (#11707) for mysql-cdc connector, which doesn't require the LOCK TABLES to ingest data. You can try this feature with our nightly docker image or build from the main source code.
Make sure execute set cdc_backfill='true' before the CREATE TABLE to enable this feature.