How can I use a MySql case-sensitive column with a debezium

144 views

Skip to first unread message

shani.kim

unread,

Nov 16, 2020, 2:32:44 AM11/16/20

to debezium

Hello,

I have a problem using MySqlConnector.

Case-sensitive was required for a particular column.

So I changed the column by usin below sql. (set binary option to change collation )

ALTER TABLE `test` CHANGE `case_insensitive` `case_sensitive` VARCHAR(10) CHARACTER SET utf8mb4 BINARY NULL DEFAULT NULL;

And then, spitting out this log, the connector stopped.

trace: "org.apache.kafka.connect.errors.ConnectException: mismatched input 'BINARY' expecting {<EOF>, '--'} 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: mismatched input 'BINARY' expecting {<EOF>, '--'} 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.reportInputMismatch(DefaultErrorStrategy.java:327) at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:139) at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:902) 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.InputMismatchException at org.antlr.v4.runtime.DefaultErrorStrategy.sync(DefaultErrorStrategy.java:270) at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:887) ... 11 more "

It is asumed that the ddl parser of debezium failed to process the column's collation change.

My connector just died.

The settings of the column have been restored to their original state, but the connector is stopped.

What should I do in this case?

And how can I use a case-sensitive column with a debezium?

For your information, I attach my debezium MySqlConnector settings.

{

"name": "test-connector",

"config": {

"connector.class": "io.debezium.connector.mysql.MySqlConnector",

"tasks.max": "1",

"database.hostname": "...",

"database.port": "13306",

"database.user": "...",

"database.password": "...",

"database.server.id": "5555",

"database.server.name": "mysql_dev",

"database.include.list": "my_db",

"table.include.list": "my_db.my_table",

"tombstones.on.delete": false,

"database.history.kafka.bootstrap.servers": "...",

"database.history.kafka.topic": "schema.changes.my_db",

"skipped.operations": "d",

"value.converter": "io.debezium.converters.CloudEventsConverter",

"message.key.columns": "my_db.my_table:aggregate_id"

}

jiri.p...@gmail.com

unread,

Nov 16, 2020, 2:44:14 AM11/16/20

to debezium

Hi,

the real error is most probably BINARY NULL. Could you please point me the docs about this MySQL statement so we would update the grammar?

Thanks a lot

shani.kim

unread,

Nov 16, 2020, 11:01:02 PM11/16/20

to debezium

Thank you! J.

I've tested it, and I think there's a problem with parsing the 'Binary attribute'.

docs about this MySQL statement is here.

https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html

The BINARY and VARBINARY data types are distinct from the CHAR BINARY and VARCHAR BINARY data types. For the latter types, the BINARY attribute does not cause the column to be treated as a binary string column. Instead, it causes the binary (_bin) collation for the column character set (or the table default character set if no column character set is specified) to be used, and the column itself stores nonbinary character strings rather than binary byte strings. For example, if the default character set is utf8mb4, CHAR(5) BINARY is treated as CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin. This differs from BINARY(5), which stores 5-byte binary strings that have the binary character set and collation. For information about the differences between the binary collation of the binary character set and the _bin collations of nonbinary character sets, see Section 10.8.5, “The binary Collation Compared to _bin Collations”.

The connector worked well when I used this query.

ALTER TABLE `test` CHANGE `id` `id` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

But this query had the same problem as before.

ALTER TABLE `test` CHANGE `id` `id` VARCHAR(10) CHARACTER SET utf8mb4 BINARY NOT NULL;

Actually, I didn't mean to use a query with a Binary attribute.
It was an automatically generated query when the column was modified in the UI of the SequelPro. (https://www.sequelpro.com/)

I'd appreciate it if you could fix it so that Debezium could parse the Binary attribute.

---

I have an additional question.
What should I do if the DDL parsing fails like this case?

In this case, to get the connector back to normal status,
I deleted Offset topic and recreated it.
The connector took a new snapshot, and it worked again.

Is this the best way?
Do you have any recommendations for restoring connectors?

Regards,

shani.kim

2020년 11월 16일 월요일 오후 4시 44분 14초 UTC+9에 jiri.p...@gmail.com님이 작성:

jiri.p...@gmail.com

unread,

Nov 18, 2020, 4:14:32 AM11/18/20

to debezium

Hi,

thats for the report the fix is available at https://issues.redhat.com/browse/DBZ-2771

Yes, the procedure you've followed is correct. other option is just removing the database history topic and using schme_only_snapshot.

Then it is not necessary to reexecute data snapshot.

shani.kim

unread,

Nov 20, 2020, 3:22:33 AM11/20/20

to debezium

Hi, J.

Thank you so much for fixing this problem quickly.

I look forward to version 1.4!

I have a question about recovery procedure.

When i used first solution that remove offset topic and restart connector, the connector make snapshot.

What concerns me is to publish duplicate events.

So, i like second solution you told me.

>other option is just removing the database history topic and using schme_only_snapshot.

But, this solution doesn't work well.

Did you mean using "snapshot.mode": "schema_only" option, when you said 'schme_only_snapshot'?

I did these steps.

1. remove db history topic

2. create db new history topic

3. change connector config

curl -X PUT -H "Content-Type: application/json" --data '{

"connector.class": "io.debezium.connector.mysql.MySqlConnector",

"snapshot.mode": "schema_only",

"tasks.max": "1",

"database.hostname": "...",

"database.port": "...",

"database.user": "...",

"database.password": "...",

"database.server.id": "5555",

"database.server.name": "mysql_dev",

"database.include.list": "test",

"table.include.list": "test_outbox",

"tombstones.on.delete": false,

"database.history.kafka.bootstrap.servers": "...",

"database.history.kafka.topic": "...",

"skipped.operations": "d",

"value.converter": "io.debezium.converters.CloudEventsConverter",

"message.key.columns": "test.test_outbox:aggregate_id"

}' http://domain:8083/connectors/test-connector/config

I tested it, and there was an error like this.

and then, i also tested "snapshot.mode": "schema_only_recovery".

That's a failure, too..

Is there any problem with my recovery test?

Regards,

shani.kim

2020년 11월 18일 수요일 오후 6시 14분 32초 UTC+9에 jiri.p...@gmail.com님이 작성:

jiri.p...@gmail.com

unread,

Nov 23, 2020, 3:26:09 AM11/23/20

to debezium

Hi,

I am sorry for the confusion I meant schema_only_recovery snapshot mode - just somehow I mentally melted the words together. What error do you see with schema_only_recovery mode? In this case you need to keep the offsets but remove the history.

shani.kim

unread,

Nov 24, 2020, 3:11:14 AM11/24/20

to debezium

Hi,

Thank you for your continued care.

I've tried twice.

#. First try:

1. remove db history topic

2. create db new history topic

3. change connector config

curl -X PUT -H "Content-Type: application/json" --data '{

"connector.class": "io.debezium.connector.mysql.MySqlConnector",

"snapshot.mode": "schema_only_recovery",

"tasks.max": "1",

"database.hostname": "...",

"database.port": "...",

"database.user": "...",

"database.password": "...",

"database.server.id": "5555",

"database.server.name": "mysql_dev",

"database.include.list": "test",

"table.include.list": "test_outbox",

"tombstones.on.delete": false,

"database.history.kafka.bootstrap.servers": "...",

"database.history.kafka.topic": "...",

"skipped.operations": "d",

"value.converter": "io.debezium.converters.CloudEventsConverter",

"message.key.columns": "test.test_outbox:aggregate_id"

}' http://domain:8083/connectors/test-connector/config

4. The problem still exists. so I restarted the connector. (But it didn't work.)

curl -X POST http://domain:8083/connectors/test-connector/restart

- Error log:

2020-11-24 14:41:34,208 ERROR MySQL|mysql_dev|binlog Failed due to error: Error processing binlog event [io.debezium.connector.mysql.BinlogReader]

org.apache.kafka.connect.errors.ConnectException: mismatched input 'BINARY' expecting {, '--'}

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: mismatched input 'BINARY' expecting {, '--'}

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.reportInputMismatch(DefaultErrorStrategy.java:327)

at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:139)

at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:902)

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.InputMismatchException

at org.antlr.v4.runtime.DefaultErrorStrategy.sync(DefaultErrorStrategy.java:270)

at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:887)

... 11 more

2020-11-24 14:41:34,209 INFO MySQL|mysql_dev|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]

2020-11-24 14:41:34,297 INFO MySQL|mysql_dev|task Keepalive thread is running [io.debezium.connector.mysql.BinlogReader]

2020-11-24 14:41:34,397 INFO || WorkerSourceTask{id=shopper-connector-0} Committing offsets [org.apache.kafka.connect.runtime.WorkerSourceTask]

2020-11-24 14:41:34,397 INFO || WorkerSourceTask{id=shopper-connector-0} flushing 0 outstanding messages for offset commit [org.apache.kafka.connect.runtime.WorkerSourceTask]

2020-11-24 14:41:34,401 INFO || WorkerSourceTask{id=shopper-connector-0} Finished commitOffsets successfully in 4 ms [org.apache.kafka.connect.runtime.WorkerSourceTask]

2020-11-24 14:41:34,401 ERROR || WorkerSourceTask{id=shopper-connector-0} Task threw an uncaught and unrecoverable exception [org.apache.kafka.connect.runtime.WorkerTask]