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

ParsingException: DDL statement couldn't be parsed (index hints)

XMLWordPrintable

      Bug report

      What Debezium connector do you use and what version?

      Debezium 1.9.6.Final

      What is the connector configuration?

      Omitted because the error can be reproduced in a unit test in debezium-ddl-parser and debezium-connector-mysql.

      What is the captured database version and mode of depoyment?

      AWS RDS MariaDB 10.5 (10.5.17)

      What behaviour do you expect?

      The following DDL statement should be correctly parsed:

      CREATE DEFINER=`my-user`@`%` PROCEDURE `my_table`()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE minID INT DEFAULT 0;
  DECLARE maxID INT DEFAULT 0;
  DECLARE limitSize INT DEFAULT 1000;
  DECLARE total_rows INT DEFAULT 0;
  SET total_rows = (SELECT count(*) FROM db.my_table a);
  WHILE i <= total_rows DO
      SET minID = (select min(a.id) FROM db.my_table a);
      SET maxID = (select max(id) from (select a.id FROM db.my_table a  USE INDEX(PRIMARY) order by a.id asc limit limitSize) top);
      START TRANSACTION;
        DELETE db.my_table FROM db.my_table USE INDEX(PRIMARY) WHERE db.my_table.id >= minId AND db.my_table.id <= maxID ;
      COMMIT;
    do SLEEP(1);
    SET i = i + limitSize;
  END WHILE;
END

      What behaviour do you see?

      The given DDL statement cannot be parsed:

      io.debezium.text.ParsingException: DDL statement couldn't be parsed.
[...]
Caused by: org.antlr.v4.runtime.NoViableAltException
[...]
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.

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

      Yes, the issue can be reproduced in Debezium 1.9.6.Final and on the 1.9 branch in the Debezium repository.

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

       

      [2022-10-13 05:40:28,435] ERROR [my-connector-name|task-0] Producer failure (io.debezium.pipeline.ErrorHandler:35)
io.debezium.DebeziumException: Error processing binlog event
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:369)
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$25(MySqlStreamingChangeEventSource.java:869)
    at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1125)
    at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:973)
    at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:599)
    at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:857)
    at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'CREATE DEFINER=`my-user`@`%` PROCEDURE `delete_my_db`()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE minID INT DEFAULT 0;
  DECLARE maxID INT DEFAULT 0;
  DECLARE limitSize INT DEFAULT 1000;
  DECLARE total_rows INT DEFAULT 0;
  SET total_rows = (SELECT count(*) FROM db.my_db a);
  WHILE i <= total_rows DO
      SET minID = (select min(a.id) FROM db.my_db a);
      SET maxID = (select max(id) from (select a.id FROM db.my_db a  USE INDEX(PRIMARY) order by a.id asc limit limitSize) top);
      START TRANSACTION;
        DELETE db.my_db FROM db.my_db USE INDEX(PRIMARY) WHERE db.my_db.id >= minId AND db.my_db.id <= maxID ;
      COMMIT;
    do SLEEP(1);
    SET i = i + limitSize;
  END WHILE;
END'
no viable alternative at input 'CREATE DEFINER=`my-user`@`%` PROCEDURE `delete_my_db`()\nBEGIN\n  DECLARE i INT DEFAULT 0;\n  DECLARE minID INT DEFAULT 0;\n  DECLARE maxID INT DEFAULT 0;\n  DECLARE limitSize INT DEFAULT 1000;\n  DECLARE total_rows INT DEFAULT 0;\n\n  SET total_rows = (SELECT count(*) FROM db.my_db a);\n\n  WHILE i <= total_rows DO\n      SET minID = (select min(a.id) FROM db.my_db a);\n      SET maxID = (select max(id) from (select a.id FROM db.my_db a  USE INDEX(PRIMARY'
    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:1228)
    at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:950)
    at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:73)
    at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:45)
    at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:82)
    at io.debezium.connector.mysql.MySqlDatabaseSchema.parseDdl(MySqlDatabaseSchema.java:224)
    at io.debezium.connector.mysql.MySqlDatabaseSchema.parseStreamingDdl(MySqlDatabaseSchema.java:210)
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleQueryEvent(MySqlStreamingChangeEventSource.java:566)
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$14(MySqlStreamingChangeEventSource.java:841)
    at io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:349)
    ... 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:1026)
    ... 15 more
[2022-10-13 05:40:28,436] INFO [my-connector-name|task-0] 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.MySqlStreamingChangeEventSource:374)

       

      How to reproduce the issue using our tutorial deployment?

      Create the following stored procedure:

      CREATE DEFINER=`my-user`@`%` PROCEDURE `my_table`()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE minID INT DEFAULT 0;
  DECLARE maxID INT DEFAULT 0;
  DECLARE limitSize INT DEFAULT 1000;
  DECLARE total_rows INT DEFAULT 0;
  SET total_rows = (SELECT count(*) FROM db.my_table a);
  WHILE i <= total_rows DO
      SET minID = (select min(a.id) FROM db.my_table a);
      SET maxID = (select max(id) from (select a.id FROM db.my_table a  USE INDEX(PRIMARY) order by a.id asc limit limitSize) top);
      START TRANSACTION;
        DELETE db.my_table FROM db.my_table USE INDEX(PRIMARY) WHERE db.my_table.id >= minId AND db.my_table.id <= maxID ;
      COMMIT;
    do SLEEP(1);
    SET i = i + limitSize;
  END WHILE;
END

            Unassigned Unassigned
            jochen-redhat Jochen Schalanda (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: