Debezium MySQL Connector failed due to DDL binlog of table not included in table.include.list

34 views
Skip to first unread message

shani.kim

unread,
Apr 28, 2021, 9:46:29 PMApr 28
to debezium
Hello,!

My Debezium MySQL Connector failed due to DDL binlog of table not included in table.include.list.
I think it is inefficient for the Connector to be affected by changes of tables that are not included in the table.include.list.

I have two questions.

1. Can i skip DDL binlog of tables that are not included in table.include.list?
As intended in the config below, I would like to CDC test.test_outbox table only.

2. Why did Debezium connector fail to parse the problematic DDL?

I'd really appreciate it if you could answer my question.
I'll attach the relevant information.

Regards,
shani.kim


Debezium version used:1.3

Connector config:
{
  "name": "shopper-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "tasks.max": "1",
    "database.hostname": "...",
    "database.port": "...",
    "database.user": "...",
    "database.password": "...",
    "database.server.id": "5555",
    "database.server.name": "mysql_real",
    "database.include.list": "test",
    "table.include.list": "test.test_outbox",
    "tombstones.on.delete": false,
    "skipped.operations": "d,u",
    "value.converter": "io.debezium.converters.CloudEventsConverter",
    "message.key.columns": "test.test_outbox:aggregate_id",
    "database.history.kafka.bootstrap.servers": "...",
    "database.history.kafka.topic": "schema.test"
  }
}

Problematic DDL
'CREATE TABLE separated_shopper_sns (  \nshpr_id_no varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', \nsns_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nsns_nick_name varchar(255) NULL DEFAULT NULL, \nsns_key varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nreg_date_time datetime(6) NOT NULL, \nsns_id varchar(50) NULL DEFAULT NULL, \nid bigint(20) unsigned unsigned'

error log:
2021-04-12 18:15:03,569 ERROR  MySQL|mysql_real|binlog  Error during binlog processing. Last offset stored = {ts_sec=1618215502, file=mysql-bin.000001, pos=8233223, row=1, server_id=7297, event=8}, binlog reader near position = mysql-bin.000001/8239665   [io.debezium.connector.mysql.BinlogReader]
2021-04-12 18:15:03,570 ERROR  MySQL|mysql_real|binlog  Failed due to error: Error processing binlog event   [io.debezium.connector.mysql.BinlogReader]
org.apache.kafka.connect.errors.ConnectException: no viable alternative at input 'CREATE TABLE separated_shopper_sns (  \nshpr_id_no varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', \nsns_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nsns_nick_name varchar(255) NULL DEFAULT NULL, \nsns_key varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nreg_date_time datetime(6) NOT NULL, \nsns_id varchar(50) NULL DEFAULT NULL, \nid bigint(20) unsigned unsigned'
        at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230)
        at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207)
        at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:604)
        at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1100)
        at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:951)
        at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:594)
        at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:838)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: io.debezium.text.ParsingException: no viable alternative at input 'CREATE TABLE separated_shopper_sns (  \nshpr_id_no varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', \nsns_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nsns_nick_name varchar(255) NULL DEFAULT NULL, \nsns_key varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nreg_date_time datetime(6) NOT NULL, \nsns_id varchar(50) NULL DEFAULT NULL, \nid bigint(20) unsigned unsigned'
        at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:40)
        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:1154)
        at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:882)
        at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:68)
        at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:41)
        at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:80)
        at io.debezium.connector.mysql.MySqlSchema.applyDdl(MySqlSchema.java:326)
        at io.debezium.connector.mysql.BinlogReader.handleQueryEvent(BinlogReader.java:807)
        at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:587)
        ... 5 more
Caused by: org.antlr.v4.runtime.NoViableAltException
        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:956)
        ... 12 more
2021-04-12 18:15:03,574 INFO   MySQL|mysql_real|binlog  Error processing binlog event, and propagating to Kafka Connect so it stops this connector. Future binlog events read before connector is shutdown will be ignored.   [io.debezium.connector.mysql.BinlogReader]
2021-04-12 18:15:03,939 INFO   ||  WorkerSourceTask{id=shopper-connector-0} Committing offsets   [org.apache.kafka.connect.runtime.WorkerSourceTask]
2021-04-12 18:15:03,939 INFO   ||  WorkerSourceTask{id=shopper-connector-0} flushing 0 outstanding messages for offset commit   [org.apache.kafka.connect.runtime.WorkerSourceTask]
2021-04-12 18:15:03,939 ERROR  ||  WorkerSourceTask{id=shopper-connector-0} Task threw an uncaught and unrecoverable exception   [org.apache.kafka.connect.runtime.WorkerTask]
org.apache.kafka.connect.errors.ConnectException: no viable alternative at input 'CREATE TABLE separated_shopper_sns (  \nshpr_id_no varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', \nsns_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nsns_nick_name varchar(255) NULL DEFAULT NULL, \nsns_key varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nreg_date_time datetime(6) NOT NULL, \nsns_id varchar(50) NULL DEFAULT NULL, \nid bigint(20) unsigned unsigned'
        at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:230)
        at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:207)
        at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:604)
        at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1100)
        at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:951)
        at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:594)
        at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:838)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: io.debezium.text.ParsingException: no viable alternative at input 'CREATE TABLE separated_shopper_sns (  \nshpr_id_no varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', \nsns_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nsns_nick_name varchar(255) NULL DEFAULT NULL, \nsns_key varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', \nreg_date_time datetime(6) NOT NULL, \nsns_id varchar(50) NULL DEFAULT NULL, \nid bigint(20) unsigned unsigned'
        at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:40)
        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:1154)
        at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:882)
        at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:68)
        at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:41)
        at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:80)
        at io.debezium.connector.mysql.MySqlSchema.applyDdl(MySqlSchema.java:326)
        at io.debezium.connector.mysql.BinlogReader.handleQueryEvent(BinlogReader.java:807)
        at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:587)
        ... 5 more
Caused by: org.antlr.v4.runtime.NoViableAltException
        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:956)
        ... 12 more
2021-04-12 18:15:03,940 ERROR  ||  WorkerSourceTask{id=shopper-connector-0} Task is being killed and will not recover until manually restarted   [org.apache.kafka.connect.runtime.WorkerTask]
2021-04-12 18:15:03,940 INFO   ||  Stopping down connector   [io.debezium.connector.common.BaseSourceTask]
2021-04-12 18:15:03,940 INFO   MySQL|mysql_real|task  Stopping MySQL connector task   [io.debezium.connector.mysql.MySqlConnectorTask]
2021-04-12 18:15:03,940 INFO   MySQL|mysql_real|task  ChainedReader: Stopping the binlog reader   [io.debezium.connector.mysql.ChainedReader]
2021-04-12 18:15:03,940 INFO   MySQL|mysql_real|task  Discarding 1 unsent record(s) due to the connector shutting down   [io.debezium.connector.mysql.BinlogReader]
2021-04-12 18:15:03,941 INFO   MySQL|mysql_real|task  Discarding 0 unsent record(s) due to the connector shutting down   [io.debezium.connector.mysql.BinlogReader]
2021-04-12 18:15:03,941 INFO   MySQL|mysql_real|task  [Producer clientId=shopper-connector-dbhistory] Closing the Kafka producer with timeoutMillis = 9223372036854775807 ms.   [org.apache.kafka.clients.producer.KafkaProducer]
2021-04-12 18:15:03,941 INFO   MySQL|mysql_real|binlog  Stopped reading binlog after 1269 events, last recorded offset: {ts_sec=1618215502, file=mysql-bin.000001, pos=8233223, row=1, server_id=7297, event=8}   [io.debezium.connector.mysql.BinlogReader]
2021-04-12 18:15:03,949 INFO   MySQL|mysql_real|task  Connector task finished all work and is now shutdown   [io.debezium.connector.mysql.MySqlConnectorTask]
2021-04-12 18:15:03,950 INFO   ||  [Producer clientId=connector-producer-shopper-connector-0] Closing the Kafka producer with timeoutMillis = 30000 ms.   [org.apache.kafka.clients.producer.KafkaProducer]  

jiri.p...@gmail.com

unread,
Apr 29, 2021, 1:05:44 AMApr 29
to debezium
Hi,

what does mean id bigint(20) unsigned unsigned? Could you please share the full DDL? Does parsing work for Debezium 1.5.0?

J.

shani.kim

unread,
Apr 29, 2021, 7:00:17 AMApr 29
to debezium
Hi J,
Thanks for your reply.

Double 'unsigned' marks are a mistake.
When I put one unsigned in and tested it, the connector worked fine.
I would check if it works well with Debezium 1.5.0.


This is a full DDL,

CREATE TABLE separated_shopper_sns (  
shpr_id_no varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', 
sns_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', 
sns_nick_name varchar(255) NULL DEFAULT NULL, 
sns_key varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', 
reg_date_time datetime(6) NOT NULL, 
sns_id varchar(50) NULL DEFAULT NULL, 
id bigint(20) unsigned unsigned NOT NULL, 
PRIMARY KEY (shpr_id_no, sns_code), 
UNIQUE INDEX id(id)) ENGINE=InnoDB;

But what I really want to know is if there's a way to skip ddl binlog from a table that's not in the table.include.list
I think DDL binlog parsing error can happen at any time.

Have a nice day.
shani.kim
2021년 4월 29일 목요일 오후 2시 5분 44초 UTC+9에 jiri.p...@gmail.com님이 작성:

jiri.p...@gmail.com

unread,
May 5, 2021, 7:41:24 AMMay 5
to debezium
Reply all
Reply to author
Forward
0 new messages