airbyte icon indicating copy to clipboard operation
airbyte copied to clipboard
verified publisher icon airbytehq

Unable to use MariaDB with CDC featire - Debezium cannot handle DDL’s

Open KacpiW opened this issue 3 years ago • 3 comments

Environment

  • Airbyte version: example is 0.39.14-alpha
  • OS Version / Instance: Ubuntu 18.04, GCP n2. , AWS EC2
  • Deployment: Docker on Debian 4.19.171-2
  • Source Connector and version: MySQL 0.5.15
  • Destination Connector and version: BigQuery 1.1.11
  • Step where error happened: Process of replication

Current Behavior

I’m keep trying to setup my MariaDB using “deduped history” feature, but also keep getting different errors on that. After last database config changes I get now error that debezium cannot handle my DDL’s. I saw on stack overflow that there were already issues like that solved by debezium in their library, but cannot tell how it works with your tool. I’m sending logs for a closer look. On top of that he’s not handling DDL of a table that I’m not even trying to replicate, a table that in fact is currently empty im my db.

Logs

logs.txt

KacpiW avatar Jul 15 '22 10:07 KacpiW

@KacpiW can you share the Stack Overflow discussions you found? Airbyte uses Debezium to sync using CDC. Maybe updating the library version of changing some parameters can help.

marcosmarxm avatar Jul 27 '22 17:07 marcosmarxm

Error message:

2022-07-07 12:09:17 [43mdestination[0m > 2022-07-07 12:09:17 [32mINFO[m i.a.i.d.s.S3StorageOperations(createBucketObjectIfNotExists):97 - Storage Object airbyte_gcs_database_name/cdc_test/cdc_test_users/2022/07/07/12/3cf5fad4-b78c-4f6d-b92b-3e01d7a6a49f/ does not exist in bucket; creating...
2022-07-07 12:09:17 [44msource[0m > 2022-07-07 12:09:17 [32mINFO[m i.d.c.m.SnapshotReader(execute):754 - Step 7: rolling back transaction after abort
2022-07-07 12:09:17 [44msource[0m > 2022-07-07 12:09:17 [1;31mERROR[m i.d.c.m.AbstractReader(failed):219 - Failed due to error: Aborting snapshot due to error when last running 'SHOW CREATE TABLE `database_name`.`user_leaderboard_match_stats`': DDL statement couldn't be parsed. Please open a Jira issue with the statement 'CREATE TABLE `user_leaderboard_match_stats` (
2022-07-07 12:09:17 [44msource[0m >   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
2022-07-07 12:09:17 [44msource[0m >   `user_id` bigint(20) unsigned NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   `user_leaderboard_match_stat_id` bigint(20) unsigned NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   `value` int(11) NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   `inserted_date` date NOT NULL DEFAULT curdate(),
2022-07-07 12:09:17 [44msource[0m >   `match_counter` int(11) NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   PRIMARY KEY (`id`),
2022-07-07 12:09:17 [44msource[0m >   KEY `user_leaderboard_match_stat_ibfk_1` (`user_id`),
2022-07-07 12:09:17 [44msource[0m >   KEY `user_leaderboard_match_stat_ibfk_2` (`user_leaderboard_match_stat_id`),
2022-07-07 12:09:17 [44msource[0m >   CONSTRAINT `user_leaderboard_match_stat_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
2022-07-07 12:09:17 [44msource[0m >   CONSTRAINT `user_leaderboard_match_stat_ibfk_2` FOREIGN KEY (`user_leaderboard_match_stat_id`) REFERENCES `user_leaderboard_match_stat` (`id`)
2022-07-07 12:09:17 [44msource[0m > ) ENGINE=InnoDB DEFAULT CHARSET=utf8'
2022-07-07 12:09:17 [44msource[0m > no viable alternative at input 'CREATE TABLE `user_leaderboard_match_stats` (\n  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n  `user_id` bigint(20) unsigned NOT NULL,\n  `user_leaderboard_match_stat_id` bigint(20) unsigned NOT NULL,\n  `value` int(11) NOT NULL,\n  `inserted_date` date NOT NULL DEFAULT curdate'
2022-07-07 12:09:17 [44msource[0m > org.apache.kafka.connect.errors.ConnectException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'CREATE TABLE `user_leaderboard_match_stats` (
2022-07-07 12:09:17 [44msource[0m >   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
2022-07-07 12:09:17 [44msource[0m >   `user_id` bigint(20) unsigned NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   `user_leaderboard_match_stat_id` bigint(20) unsigned NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   `value` int(11) NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   `inserted_date` date NOT NULL DEFAULT curdate(),
2022-07-07 12:09:17 [44msource[0m >   `match_counter` int(11) NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   PRIMARY KEY (`id`),
2022-07-07 12:09:17 [44msource[0m >   KEY `user_leaderboard_match_stat_ibfk_1` (`user_id`),
2022-07-07 12:09:17 [44msource[0m >   KEY `user_leaderboard_match_stat_ibfk_2` (`user_leaderboard_match_stat_id`),
2022-07-07 12:09:17 [44msource[0m >   CONSTRAINT `user_leaderboard_match_stat_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
2022-07-07 12:09:17 [44msource[0m >   CONSTRAINT `user_leaderboard_match_stat_ibfk_2` FOREIGN KEY (`user_leaderboard_match_stat_id`) REFERENCES `user_leaderboard_match_stat` (`id`)
2022-07-07 12:09:17 [44msource[0m > ) ENGINE=InnoDB DEFAULT CHARSET=utf8'
2022-07-07 12:09:17 [44msource[0m > no viable alternative at input 'CREATE TABLE `user_leaderboard_match_stats` (\n  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n  `user_id` bigint(20) unsigned NOT NULL,\n  `user_leaderboard_match_stat_id` bigint(20) unsigned NOT NULL,\n  `value` int(11) NOT NULL,\n  `inserted_date` date NOT NULL DEFAULT curdate'
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:241) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:218) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:846) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
2022-07-07 12:09:17 [44msource[0m > 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
2022-07-07 12:09:17 [44msource[0m > 	at java.lang.Thread.run(Thread.java:833) [?:?]
2022-07-07 12:09:17 [44msource[0m > Caused by: io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'CREATE TABLE `user_leaderboard_match_stats` (
2022-07-07 12:09:17 [44msource[0m >   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
2022-07-07 12:09:17 [44msource[0m >   `user_id` bigint(20) unsigned NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   `user_leaderboard_match_stat_id` bigint(20) unsigned NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   `value` int(11) NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   `inserted_date` date NOT NULL DEFAULT curdate(),
2022-07-07 12:09:17 [44msource[0m >   `match_counter` int(11) NOT NULL,
2022-07-07 12:09:17 [44msource[0m >   PRIMARY KEY (`id`),
2022-07-07 12:09:17 [44msource[0m >   KEY `user_leaderboard_match_stat_ibfk_1` (`user_id`),
2022-07-07 12:09:17 [44msource[0m >   KEY `user_leaderboard_match_stat_ibfk_2` (`user_leaderboard_match_stat_id`),
2022-07-07 12:09:17 [44msource[0m >   CONSTRAINT `user_leaderboard_match_stat_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
2022-07-07 12:09:17 [44msource[0m >   CONSTRAINT `user_leaderboard_match_stat_ibfk_2` FOREIGN KEY (`user_leaderboard_match_stat_id`) REFERENCES `user_leaderboard_match_stat` (`id`)
2022-07-07 12:09:17 [44msource[0m > ) ENGINE=InnoDB DEFAULT CHARSET=utf8'
2022-07-07 12:09:17 [44msource[0m > no viable alternative at input 'CREATE TABLE `user_leaderboard_match_stats` (\n  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n  `user_id` bigint(20) unsigned NOT NULL,\n  `user_leaderboard_match_stat_id` bigint(20) unsigned NOT NULL,\n  `value` int(11) NOT NULL,\n  `inserted_date` date NOT NULL DEFAULT curdate'
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:43) ~[debezium-ddl-parser-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) ~[antlr4-runtime-4.7.2.jar:4.7.2]
2022-07-07 12:09:17 [44msource[0m > 	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544) ~[antlr4-runtime-4.7.2.jar:4.7.2]
2022-07-07 12:09:17 [44msource[0m > 	at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) ~[antlr4-runtime-4.7.2.jar:4.7.2]
2022-07-07 12:09:17 [44msource[0m > 	at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) ~[antlr4-runtime-4.7.2.jar:4.7.2]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1157) ~[debezium-ddl-parser-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:885) ~[debezium-ddl-parser-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:72) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:45) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:80) ~[debezium-ddl-parser-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.MySqlSchema.applyDdl(MySqlSchema.java:326) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.SnapshotReader.lambda$readTableSchema$17(SnapshotReader.java:879) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:556) ~[debezium-core-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:497) ~[debezium-core-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.SnapshotReader.readTableSchema(SnapshotReader.java:877) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:539) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	... 3 more
2022-07-07 12:09:17 [44msource[0m > Caused by: org.antlr.v4.runtime.NoViableAltException
2022-07-07 12:09:17 [44msource[0m > 	at org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:2026) ~[antlr4-runtime-4.7.2.jar:4.7.2]
2022-07-07 12:09:17 [44msource[0m > 	at org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:467) ~[antlr4-runtime-4.7.2.jar:4.7.2]
2022-07-07 12:09:17 [44msource[0m > 	at org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:393) ~[antlr4-runtime-4.7.2.jar:4.7.2]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:959) ~[debezium-ddl-parser-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:885) ~[debezium-ddl-parser-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:72) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:45) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:80) ~[debezium-ddl-parser-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.MySqlSchema.applyDdl(MySqlSchema.java:326) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.SnapshotReader.lambda$readTableSchema$17(SnapshotReader.java:879) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:556) ~[debezium-core-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.jdbc.JdbcConnection.query(JdbcConnection.java:497) ~[debezium-core-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.SnapshotReader.readTableSchema(SnapshotReader.java:877) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	at io.debezium.connector.mysql.SnapshotReader.execute(SnapshotReader.java:539) ~[debezium-connector-mysql-1.4.2.Final.jar:1.4.2.Final]
2022-07-07 12:09:17 [44msource[0m > 	... 3 more

marcosmarxm avatar Jul 27 '22 17:07 marcosmarxm

@marcosmarxm Sure, I think this is an issue that is similar to mine and seems to be resolved. So updating debezium, sounds like a possible solution.

https://stackoverflow.com/questions/64372783/no-viable-alternative-at-input-create-table https://issues.redhat.com/browse/DBZ-2671

This one might be helpful also: https://issues.redhat.com/browse/DBZ-3333

KacpiW avatar Jul 28 '22 09:07 KacpiW

@KacpiW do you mind upgrading MySQL connector to latest version? The latest version is using the updated version of Debezium, check here https://github.com/airbytehq/airbyte/commit/83b0f81982b338ba939f9bca516be5fd8e5d80dc

marcosmarxm avatar Aug 01 '22 12:08 marcosmarxm

Same "DDL statement couldn't be parsed" on 0.40.17 with the latest mysql connector (as of Nov 2, 2022).

fcatanzaro avatar Nov 02 '22 17:11 fcatanzaro

same error on 0.40.14 with latest mysql connector (1.0.11, as of Nov 9, 2022).

shmf avatar Nov 09 '22 12:11 shmf

@shmf @fcatanzaro @KacpiW the issue is in database team backlog but doesn't have an estimated time to conclusion.

marcosmarxm avatar Nov 21 '22 20:11 marcosmarxm