Common Issues and Solutions for Flink CDC with MySQL

I have previously introduced the principles and practice of Flink CDC in detail.

If you are not familiar with Flink CDC, you can refer to the linked article "Flink CDC Principles and Production Practice".

Below is a collection of common issues I have encountered, found online, or seen in official blogs and the Flink mailing list, along with solutions for reference.

Different Kafka version dependency conflicts

Conflicting Kafka version dependencies can cause CDC errors; see the referenced issue.

2020-11-04 16:39:10.972 [Source: Custom Source -> Sink: Print to Std. Out (1/1)] WARN  org.apache.flink.runtime.taskmanager.Task  - Source: Custom Source -> Sink: Print to Std. Out (1/1) (7c3ccf7686ccfb33254e8cb785cd339d) switched from RUNNING to FAILED.<br/>java.lang.AbstractMethodError: org.apache.kafka.connect.json.JsonSerializer.configure(Ljava/util/Map;Z)V<br/>at org.apache.kafka.connect.json.JsonConverter.configure(JsonConverter.java:300)<br/>...

Source code example:

public class CdcTest {<br/>    public static void main(String[] args) throws Exception {<br/>        SourceFunction<String> sourceFunction = MySQLSource.<String>builder()<br/>                .hostname("localhost")<br/>                .port(3306)<br/>                .databaseList("sohay") // monitor all tables under inventory database<br/>                .username("root")<br/>                .password("123456")<br/>                .deserializer(new StringDebeziumDeserializationSchema()) // converts SourceRecord to String<br/>                .build();<br/><br/>        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();<br/><br/>        env.addSource(sourceFunction).print().setParallelism(1); // use parallelism 1 for sink to keep message ordering<br/><br/>        env.execute();<br/>    }<br/>}<br/>

Removing the conflicting Kafka dependency from the pom resolves the issue.

MySQL CDC source checkpoint timeout

During table scanning, if no recoverable position exists, checkpoints are held, leading to timeout and fail‑over. For large tables, add the following Flink configuration to avoid fail‑over caused by checkpoint timeout:

execution.checkpointing.interval: 10min<br/>execution.checkpointing.tolerable-failed-checkpoints: 100<br/>restart-strategy: fixed-delay<br/>restart-strategy.fixed-delay.attempts: 2147483647<br/>

Database switch, binlog restart, MySQL global lock not released

Switching database environments and restarting binlog causes all jobs to re‑sync full data, keeping the global lock waiting and blocking job execution. Solution: record the checkpoint address, cancel the job, and restart from the checkpoint.

Flink SQL CDC creating a dimension table error

Creating a CDC table with the following DDL fails because the MySQL user lacks required CDC privileges:

CREATE TABLE cdc_test (<br/>    id  STRING,<br/>    ip  STRING,<br/>    url STRING,<br/>    PRIMARY KEY (id) NOT ENFORCED<br/>) WITH (<br/>    'connector' = 'mysql-cdc', <br/>    'hostname' = '127.0.0.1',<br/>    'port' = '3306',<br/>    'database-name' = 'xx',<br/>    'table-name' = 'xx',<br/>    'username' = 'xx',<br/>    'password' = 'xx'<br/>);<br/>

Running SELECT * FROM cdc_test; throws:

User does not have the 'LOCK TABLES' privilege required to obtain a consistent snapshot by preventing concurrent writes to tables.

Reason: the MySQL user lacks necessary CDC permissions. Flink SQL CDC (based on Debezium) acquires a global read lock (FLUSH TABLES WITH READ LOCK) to take a consistent snapshot.

Solution: create a new MySQL user with the required privileges:

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';<br/>mysql> GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user' IDENTIFIED BY 'password';<br/>mysql> FLUSH PRIVILEGES;<br/>

Flink job scanning MySQL full data causes fail‑over

When scanning massive MySQL tables, checkpoints timeout because the source cannot emit offsets during the full‑table scan, leading to fail‑over. Configure Flink to tolerate failed checkpoints and set a fixed‑delay restart strategy as shown above.

Runtime error "no viable alternative at input 'alter table std'"

The error occurs when another table’s schema change generates an ALTER DDL that the CDC source cannot parse. Upgrading to the latest flink‑cdc‑connectors (e.g., version 1.1.0) skips unparsable DDL statements.

Multiple jobs sharing the same source table without changing server‑id cause data loss

CDC sources act as MySQL slaves using a server‑id; duplicate server‑ids cause data corruption. Use a dynamic table hint to set a unique server‑id, e.g.:

FROM bill_info /*+ OPTIONS('server-id'='123456') */ ;<br/>

CDC source cannot insert data into MySQL table during scan

The MySQL user lacks RELOAD permission, preventing acquisition of the global read lock and causing prolonged table‑level locks. Grant RELOAD permission to the user, or set debezium.snapshot.locking.mode='none' if schema changes are not expected during snapshot.