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

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

    XMLWordPrintable

Details

    • False
    • False
    • Undefined
    • 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

            jpechane Jiri Pechanec
            epirealer Anders Engström (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: