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

'ALTER TABLE mytable DROP FOREIGN KEY IF EXISTS mytable_fk' no viable alternative at input 'ALTER TABLE mytable DROP FOREIGN KEY IF'

XMLWordPrintable

    • False
    • None
    • False

      What Debezium connector do you use and what version?

      mysql v1.9.2

      What is the connector configuration?

      It doesn't matter, the issue is with the parser

      What is the captured database version and mode of deployment?

      It doesn't matter, the issue is with the parser. But it's worth mentioning that the DB where it originally happened is MariaDB hosted on AWS RDS.

      What behaviour do you expect?

      An expression of syntax

      ALTER TABLE mytable DROP FOREIGN KEY IF EXISTS mytable_fk

      is parsed successfully. 

      What behaviour do you see?

      An expression of syntax

      ALTER TABLE mytable DROP FOREIGN KEY IF EXISTS mytable_fk

      causes exception in the parser:

       

      io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'ALTER TABLE mytable DROP FOREIGN KEY IF EXISTS mytable_fk'
no viable alternative at input 'ALTER TABLE mytable DROP FOREIGN KEY IF'    at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:43)
    at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)

       

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

      Confirmed in 1.8.0, 1.9.1 and 1.9.2.

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

       

      io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'ALTER TABLE mytable DROP FOREIGN KEY IF EXISTS mytable_fk'
no viable alternative at input 'ALTER TABLE mytable DROP FOREIGN KEY IF'    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.MySqlAntlrDdlParserTest.shouldSupportDropForeignKeyIfExists(MySqlAntlrDdlParserTest.java:3162)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
    at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
    at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
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)

       

      How to reproduce the issue using our tutorial deployment?

      The fastest way to reproduce the issue is to add to the

      {{ io.debezium.connector.mysql.MySqlAntlrDdlParserTest}}

      the following test:

       

      @Test
@FixFor("DBZ-NONE")
public void shouldSupportDropForeignKeyIfExists() {
    String ddl = "CREATE TABLE mytable (id INT PRIMARY KEY, val1 INT)";
    parser.parse(ddl, tables);
    assertThat(((MySqlAntlrDdlParser) parser).getParsingExceptionsFromWalker().size()).isEqualTo(0);
    assertThat(tables.size()).isEqualTo(1);

    parser.parse("ALTER TABLE mytable DROP FOREIGN KEY IF EXISTS mytable_fk", tables);
    assertThat(((MySqlAntlrDdlParser) parser).getParsingExceptionsFromWalker().size()).isEqualTo(0);
    assertThat(tables.size()).isEqualTo(1);
}

       

       

       

      To my understanding, the problem is that the exiting grammar expects ID after the DROP FOREIGN KEY expression, but in my problematic case the ID follows IF EXISTS fragment.

      Existing grammar (see https://github.com/debezium/debezium/blob/c32561b3cd8445ea8a1b22ddd37820aa142300a0/debezium-ddl-parser/src/main/antlr4/io/debezium/ddl/parser/mysql/generated/MySqlParser.g4#L666 ):

      | DROP FOREIGN KEY uid ifExists?                                #alterByDropForeignKey // ifExists is MariaDB-specific

      Grammar I'd expect to work:

      | DROP FOREIGN KEY ifExists? uid                                #alterByDropForeignKey // when key name is at the end

      According to similar issue https://issues.redhat.com/browse/DBZ-4833 , Debezium (https://github.com/debezium/debezium/pull/3298 ) and ANTLR upstream (https://github.com/antlr/grammars-v4/pull/2542 ) PRs are needed.

      I'd be happy to take care of them.

            Unassigned Unassigned
            ilexicy Alexey Miroshnikov (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: