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

MySQL singleDeleteStatement parser does not support table alias

    XMLWordPrintable

Details

    Description

      What Debezium connector do you use and what version?

      debezium-connector-mysql 2.1.2.Final

      What is the connector configuration?

      Irrelevant

      What is the captured database version and mode of depoyment?

      Google CloudSQL

      What behaviour do you expect?

      Debezium parses DDL

      What behaviour do you see?

      2023-03-22 10:19:47.903  WARN [debezium-export,,] 64751 --- [connect-tasks-2] c.g.h.s.c.services.ConnectorRestarter    : Failing connector: [gaming3 @ 10.90.16.168:40414] RUNNING (id=0,worker=10.90.16.168:40414,state=FAILED,trace=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 more
      Caused by: io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'CREATE DEFINER=`gamingservice`@`10.90.16.%` PROCEDURE `DeleteAggregateJackpotData`()
      BEGIN
      				DECLARE EXIT HANDLER FOR SQLEXCEPTION
      				BEGIN
      						SHOW ERRORS;
      						ROLLBACK;
      				END;
      
      				START TRANSACTION;
      				DELETE FROM jackpot_data_aggregate JDA WHERE JDA.jackpot_date <= DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY);
      
      				COMMIT;
      			END'
      no viable alternative at input 'CREATE DEFINER=`gamingservice`@`10.90.16.%` PROCEDURE `DeleteAggregateJackpotData`()\nBEGIN\n\t\t\t\tDECLARE EXIT HANDLER FOR SQLEXCEPTION\n\t\t\t\tBEGIN\n\t\t\t\t\t\tSHOW ERRORS;\n\t\t\t\t\t\tROLLBACK;\n\t\t\t\tEND;\n\n\t\t\t\tSTART TRANSACTION;\n\t\t\t\tDELETE FROM jackpot_data_aggregate JDA'
      	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:1250)
      	at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:972)
      	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 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:1048)
      	... 15 more
      )
      

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

      (Ideally, also verify with latest Alpha/Beta/CR version)

      Yes, i tried adding the following statement to debezium-ddl-parser/src/main/antlr4/io/debezium/ddl/parser/mysql/generated/MySqlParser.g4 in master branch;

      delete from t1 as alias_t1 where alias_t1.col1 > 0;
      

      Which made the build fail.

      Note that the statement in the log is a bit more complex, but the parse failure boils down to single delete with a table alias.

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

      See above.

      How to reproduce the issue using our tutorial deployment?

      Not needed, it is easily reproducable in maven build/test.

      Implementation ideas (optional)

      Modify the simpleDeleteStatement in MySqlParser.g4 like this to allow table alias;

      singleDeleteStatement
          : DELETE priority=LOW_PRIORITY? QUICK? IGNORE?
          FROM tableName (AS? uid)?
            (PARTITION '(' uidList ')' )?
            (WHERE expression)?
            orderByClause? (LIMIT limitClauseAtom)?
          ;
      

      Attachments

        Activity

          People

            rh-ee-mvitale Mario Fiore Vitale
            tommy.karlsson Tommy Karlsson (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: