Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-4980

CTE statements aren't parsed by MySQL connector

XMLWordPrintable

    • False
    • None
    • False

      Bug report

      Debezium

      Database

      MySQL 8 deployed in docker (mysql:8)

      What behaviour do you expect?

      Correctly parsed DDL or at least streamed event to Kafka for a statement:

       

      CREATE VIEW view_that_doesnt_work
      AS
      WITH cte1 AS (
          SELECT column_1 AS a, column_2 AS b
          FROM table1
      )
      SELECT a, b
      FROM cte1; 

       

      What behaviour do you see?

       

      io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_that_doesnt_work` AS WITH cte1 AS (2022-04-12T06:19:29.578799700Z     SELECT column_1 AS a, column_2 AS b2022-04-12T06:19:29.578870200Z     FROM table12022-04-12T06:19:29.578894300Z )2022-04-12T06:19:29.578940900Z SELECT a, b2022-04-12T06:19:29.578970900Z FROM cte1'2022-04-12T06:19:29.578993000Z no viable alternative at input 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_that_doesnt_work` AS WITH'2022-04-12T06:19:29.579018700Z 	at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:43)2022-04-12T06:19:29.579050000Z 	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)2022-04-12T06:19:29.579077700Z 	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)2022-04-12T06:19:29.579104400Z 	at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)2022-04-12T06:19:29.579186200Z 	at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)2022-04-12T06:19:29.579216200Z 	at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1226)2022-04-12T06:19:29.579240100Z 	at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:948)2022-04-12T06:19:29.579271200Z 	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:73)2022-04-12T06:19:29.579304700Z 	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:45)2022-04-12T06:19:29.579337500Z 	at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:82)2022-04-12T06:19:29.579398600Z 	at io.debezium.connector.mysql.MySqlDatabaseSchema.parseDdl(MySqlDatabaseSchema.java:224)2022-04-12T06:19:29.579430200Z 	at io.debezium.connector.mysql.MySqlDatabaseSchema.parseStreamingDdl(MySqlDatabaseSchema.java:210)2022-04-12T06:19:29.579462600Z 	at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleQueryEvent(MySqlStreamingChangeEventSource.java:566)2022-04-12T06:19:29.579496400Z 	at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$14(MySqlStreamingChangeEventSource.java:841)2022-04-12T06:19:29.579527800Z 	at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:349)2022-04-12T06:19:29.579560600Z 	at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$25(MySqlStreamingChangeEventSource.java:869)2022-04-12T06:19:29.579667800Z 	at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1125)2022-04-12T06:19:29.579700100Z 	at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:973)2022-04-12T06:19:29.579728600Z 	at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:599)2022-04-12T06:19:29.579761800Z 	at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:857)2022-04-12T06:19:29.579789200Z 	at java.base/java.lang.Thread.run(Thread.java:829)2022-04-12T06:19:29.579819700Z Caused by: org.antlr.v4.runtime.NoViableAltException2022-04-12T06:19:29.579851400Z 	at org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:2026)2022-04-12T06:19:29.579876400Z 	at org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:467)2022-04-12T06:19:29.579899900Z 	at org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:393)2022-04-12T06:19:29.579941600Z 	at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1024)2022-04-12T06:19:29.579979800Z 	... 15 more 

       

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

      Yes.

      How to reproduce the issue using our tutorial deployment?

      Connector configuration

       

      {
        "name": "my-db-connector",
        "config": {
          "connector.class": "io.debezium.connector.mysql.MySqlConnector",
          "database.user": "***",
          "database.hostname": "hostname",
          "tasks.max": "1",
          "database.password": "***",
          "database.history.kafka.bootstrap.servers": "my-kafka:9092",
          "database.history.kafka.topic": "schema-changes.hostname-3306",
          "name": "my-db-connector",
          "database.server.name": "my-db",
          "database.history.skip.unparseable.ddl": "true",
          "database.port": "3306"
        }
      } 

      Then, create a database and run the query above.

       

            anmohant Anisha Mohanty
            nikolai14 Nikolai Lebedev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: