Bug report

Hi, I've encountered a parser problem of the MySQL connector recently that stopped the pipeline of consuming Binlog entries from source MySQL instance.

The Debezium engine showed the error message like "DDL statement couldn't be parsed. Please open a Jira issue with the statement" to direct me to create this issue.

The initial statement that failed to be parsed is "create table t (id bigint references tt (tt_id) ON DELETE SET DEFAULT)". But later on, as I've tested all the possible syntax of the DDL 'CREATE TABLE' that MySQL 8.0 supports(Described in this page: https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-indexes-keys ), more statements that the debezium-connector-mysql could not parse were found. 

The list of failed to be parsed DDL statements:

CREATE TABLE t (c NATIONAL CHAR);
CREATE TABLE t (c GEOMETRY SRID 0);
CREATE TABLE t (c POINT SRID 0);
CREATE TABLE t (c LINESTRING SRID 0);
CREATE TABLE t (c POLYGON SRID 0);
CREATE TABLE t (c MULTIPOINT SRID 0);
CREATE TABLE t (c MULTILINESTRING SRID 0);
CREATE TABLE t (c MULTIPOLYGON SRID 0);
CREATE TABLE t (c GEOMETRYCOLLECTION SRID 0);
CREATE TABLE t (id BIGINT, s VARCHAR(100), i INT, CONSTRAINT abc CHECK (id < 5) ENFORCED);
CREATE TABLE t (id BIGINT REFERENCES tt (tt_id) ON DELETE SET DEFAULT);
CREATE TABLE t (id BIGINT REFERENCES tt (tt_id) ON UPDATE SET DEFAULT);

ALTER TABLE t ADD FOREIGN KEY ( i )  REFERENCES tt ( i ) ON DELETE SET DEFAULT;
ALTER TABLE t ADD FOREIGN KEY ( i ) REFERENCES tt ( i ) ON UPDATE SET DEFAULT;
ALTER TABLE t ADD CHECK (id + 6 > 10) ENFORCED;
ALTER TABLE t ADD CHECK (id + 6 > 10) NOT ENFORCED;
ALTER TABLE t ALTER CHECK c_cons ENFORCED;
ALTER TABLE t ALTER CHECK c_cons NOT ENFORCED;
ALTER TABLE t ALTER i SET VISIBLE;
ALTER TABLE t ALTER i SET INVISIBLE;

What Debezium connector do you use and what version?

debezium-connector-mysql:2.2.0.Alpha2

What is the connector configuration?

props.put("name", "abc");
props.put("connector.class", "io.debezium.connector.mysql.MySqlConnector");
props.put("database.hostname", "127.0.0.1");
props.put("database.port", "53306");
props.put("database.user", "root");
props.put("database.password", "password");
props.put("database.server.id", "100000");
props.put("database.server.name", "abc");
props.put("database.allowPublicKeyRetrieval", "true");

What is the captured database version and mode of depoyment?

(E.g. on-premises, with a specific cloud provider, etc.)

On-prem, 8.0.30 MySQL Community Server

What behaviour do you expect?

Statement should be parsed and handled by the Debezium engine.

What behaviour do you see?

Debezium engine stopped the Binlog pipelining since it failed to parse the statement.

Do you see the same behaviour using the latest relesead Debezium version?

This report is based on the latest version recorded in the Maven Central Repository.

Do you have the connector logs, ideally from start till finish?

19:28:39.330 [blc-127.0.0.1:53306] INFO io.debezium.connector.mysql.MySqlStreamingChangeEventSource - Stopped reading binlog after 0 events, last recorded offset: {transaction_id=null, ts_sec=1676531166, file=binlog.000007, pos=338212, server_id=1, event=1}
19:28:39.330 [debezium-mysqlconnector-MyConn2-change-event-source-coordinator] INFO io.debezium.pipeline.ChangeEventSourceCoordinator - Finished streaming
19:28:39.341 [pool-60-thread-1] INFO io.debezium.jdbc.JdbcConnection - Connection gracefully closed
19:28:39.342 [pool-57-thread-1] INFO com.foxlake.debezium.S3OffsetBackingStore - Stopped S3OffsetBackingStore
19:28:39.342 [pool-57-thread-1] ERROR io.debezium.embedded.EmbeddedEngine - Error while trying to run connector class 'io.debezium.connector.mysql.MySqlConnector'
org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.
    at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:53)
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:392)
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$27(MySqlStreamingChangeEventSource.java:922)
    at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1246)
    at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:1072)
    at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:631)
    at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:932)
    at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: io.debezium.DebeziumException: Error processing binlog event
    ... 7 common frames omitted
Caused by: io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement '/*+ ENGINE = columnar@abc */ create table t (id bigint, s varchar(100), i int, foreign key (id) references tt (tt_id) ON DELETE SET DEFAULT)'
no viable alternative at input 'create table t (id bigint, s varchar(100), i int, foreign key (id) references tt (tt_id) ON DELETE SET DEFAULT'
    at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:43)
    at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
    at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)
    at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1245)
    at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:967)
    at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:74)
    at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:46)
    at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:78)
    at io.debezium.connector.mysql.MySqlDatabaseSchema.parseDdl(MySqlDatabaseSchema.java:213)
    at io.debezium.connector.mysql.MySqlDatabaseSchema.parseStreamingDdl(MySqlDatabaseSchema.java:199)
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleQueryEvent(MySqlStreamingChangeEventSource.java:590)
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$15(MySqlStreamingChangeEventSource.java:893)
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:372)
    ... 6 common frames omitted
Caused by: org.antlr.v4.runtime.NoViableAltException: null
    at org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:2026)
    at org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:467)
    at org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:393)
    at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1043)
    ... 15 common frames omitted

How to reproduce the issue using our tutorial deployment?

1. Run these DDL statements on a MySQL instance:

drop table if exists t;
drop table if exists tt;
create table tt (tt_id bigint, key (tt_id));
create table t (id bigint references tt (tt_id) ON DELETE SET DEFAULT);

2. Start a pipeline to consume the Binlog entries of this MySQL instance.

3. The pipeline will stop because the connector fails to parse this DDL statement.