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

Unparseable DDL: Using 'trigger' as table alias in view creation

    XMLWordPrintable

    Details

    • Steps to Reproduce:
      Hide

      Create a database and a table (with a Debezium connector active):

      create table test (num int primary key);
      create view test_v as select * from test;
      create view `trigger` as select * from test;
      

      This works fine.

      create view `test_view_1` as select `trigger`.num from test `trigger`;
      

      This also works.

      create view `test_view_2` as select trigger.num from test `trigger`;
      

      This fails with a parsing exception.

      Show
      Create a database and a table (with a Debezium connector active): create table test (num int primary key ); create view test_v as select * from test; create view ` trigger ` as select * from test; This works fine. create view `test_view_1` as select ` trigger `.num from test ` trigger `; This also works. create view `test_view_2` as select trigger .num from test ` trigger `; This fails with a parsing exception.

      Description

      When creating a VIEW using "trigger" as the name for a field, the ANTLR parser fails with:

      io.debezium.text.ParsingException: no viable alternative at input '<SQL>' 
      	at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:40) ~[debezium-ddl-parser-1.1.2.Final.jar!/:1.1.2.Final]
      	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) ~[antlr4-runtime-4.7.2.jar!/:4.7.2]
      	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544) ~[antlr4-runtime-4.7.2.jar!/:4.7.2]
      	at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) ~[antlr4-runtime-4.7.2.jar!/:4.7.2]
      	at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) ~[antlr4-runtime-4.7.2.jar!/:4.7.2]
      	at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1121) ~[debezium-ddl-parser-1.1.2.Final.jar!/:1.1.2.Final]
      	at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:849) ~[debezium-ddl-parser-1.1.2.Final.jar!/:1.1.2.Final]
      	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:68) ~[debezium-connector-mysql-1.1.2.Final.jar!/:1.1.2.Final]
      	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:41) ~[debezium-connector-mysql-1.1.2.Final.jar!/:1.1.2.Final]
      	at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:80) ~[debezium-ddl-parser-1.1.2.Final.jar!/:1.1.2.Final]
      	at io.debezium.connector.mysql.MySqlSchema.applyDdl(MySqlSchema.java:308) ~[debezium-connector-mysql-1.1.2.Final.jar!/:1.1.2.Final]
      	at io.debezium.connector.mysql.BinlogReader.handleQueryEvent(BinlogReader.java:729) [debezium-connector-mysql-1.1.2.Final.jar!/:1.1.2.Final]
      	at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:519) [debezium-connector-mysql-1.1.2.Final.jar!/:1.1.2.Final]
      	at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1095) ~[mysql-binlog-connector-java-0.19.1.jar!/:0.19.1]
      	at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:943) ~[mysql-binlog-connector-java-0.19.1.jar!/:0.19.1]
      	at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:580) ~[mysql-binlog-connector-java-0.19.1.jar!/:0.19.1]
      	at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:825) ~[mysql-binlog-connector-java-0.19.1.jar!/:0.19.1]
      	at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_252]
      Caused by: org.antlr.v4.runtime.NoViableAltException: null
      	at org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:2026) ~[antlr4-runtime-4.7.2.jar!/:4.7.2]
      	at org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:467) ~[antlr4-runtime-4.7.2.jar!/:4.7.2]
      	at org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:393) ~[antlr4-runtime-4.7.2.jar!/:4.7.2]
      	at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:923) ~[debezium-ddl-parser-1.1.2.Final.jar!/:1.1.2.Final]
      	... 12 common frames omitted
      
      

      This is from Debezium 1.1.2.Final - see 'Steps to Reproduce' for details.

      We're guessing that the reason is that `trigger` is a keyword in MySQL - however the statement is accepted by MySQL (8.0.19) without errors, and the ANTLR parser should be able to handle it as well.

      We've managed to work around the issue by setting `database.history.skip.unparseable.ddl: true`. Then redefine the view in MySQL (without using "trigger"), and then set  `snapshot.mode: schema_only_recovery` to be able to drop the history topic and re-create it (to avoid Debezium to read the "old" view definition).

        Attachments

          Activity

            People

            Assignee:
            jpechanec Jiri Pechanec
            Reporter:
            epirealer Anders Engström
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: