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

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

XMLWordPrintable

    • 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.

      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).

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

              Created:
              Updated:
              Resolved: