Re: procedure created has broken debezium

[jiri.p...@gmail.com]

Hi,

please create a Jira issue so we can fix the grammar.

Thanks

On Monday, March 22, 2021 at 3:25:10 PM UTC+1 joel.salm...@manomano.com wrote:

Upon deploying a procedure to limit a large dml, the debezium source has choked while trying to parse:

org.apache.kafka.connect.errors.ConnectException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'CREATE DEFINER=`ci`@`%` PROCEDURE `COM251`(IN p_platform_id CHAR(2), IN p_sleep INT)

BEGIN

    declare cnt integer default 0; 

    DECLARE finished INTEGER DEFAULT 0;

    DECLARE transactionId int(11);

    DECLARE curTransaction

        CURSOR FOR 

        SELECT cto.id 

        FROM colibri_transaction_order cto

        JOIN colibri_transaction ct 

          ON cto.transaction_id = ct.id

        JOIN xcart_orders xo 

          ON xo.order_cart_id = ct.cart_id

        WHERE ct.platform_id = p_platform_id

          AND cto.shipping_price_vat_rate = 0

          AND xo.is_mmf = 1;

    DECLARE CONTINUE HANDLER 

        FOR NOT FOUND SET finished = 1;

    OPEN curTransaction;

    START TRANSACTION;

    getTransaction: LOOP

        FETCH curTransaction INTO transactionId;

        IF finished = 1 THEN 

            LEAVE getTransaction;

        END IF;

        SET cnt = cnt + 1;

        UPDATE colibri_transaction_order

        SET shipping_price_vat_rate = 21

        WHERE id = transactionId;

        if mod(cnt,25000) = 0

            then commit; 

                select cnt, now();

                select sleep(p_sleep);

                START TRANSACTION; 

        end if;

    END LOOP getTransaction;

    CLOSE curTransaction;

    commit; 

END'

no viable alternative at input 'CREATE DEFINER=`ci`@`%` PROCEDURE `COM251`(IN p_platform_id CHAR(2), IN p_sleep INT)\nBEGIN\n    declare cnt integer default 0; \n    DECLARE finished INTEGER DEFAULT 0;\n    DECLARE transactionId int(11);\n    DECLARE curTransaction\n        CURSOR FOR \n        SELECT cto.id \n        FROM colibri_transaction_order cto\n        JOIN colibri_transaction ct \n          ON cto.transaction_id = ct.id\n        JOIN xcart_orders xo \n          ON xo.order_cart_id = ct.cart_id\n        WHERE ct.platform_id = p_platform_id\n          AND cto.shipping_price_vat_rate = 0\n          AND xo.is_mmf = 1;\n    DECLARE CONTINUE HANDLER \n        FOR NOT FOUND SET finished = 1;\n    OPEN curTransaction;\n    START TRANSACTION;\n    getTransaction: LOOP\n        FETCH curTransaction INTO transactionId;\n        IF finished = 1 THEN \n            LEAVE getTransaction;\n        END IF;\n        SET cnt = cnt + 1;\n        UPDATE colibri_transaction_order\n        SET shipping_price_vat_rate = 21\n        WHERE id = transactionId;\n        if mod('

at io.debezium.connector.mysql.AbstractReader.wrap(AbstractReader.java:241)

at io.debezium.connector.mysql.AbstractReader.failed(AbstractReader.java:218)

at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:607)

at com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1104)

at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:955)

at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:595)

at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:839)

at java.base/java.lang.Thread.run(Thread.java:834)

Caused by: io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'CREATE DEFINER=`ci`@`%` PROCEDURE `COM251`(IN p_platform_id CHAR(2), IN p_sleep INT)

BEGIN

    declare cnt integer default 0; 

    DECLARE finished INTEGER DEFAULT 0;

    DECLARE transactionId int(11);

    DECLARE curTransaction

        CURSOR FOR 

        SELECT cto.id 

        FROM colibri_transaction_order cto

        JOIN colibri_transaction ct 

          ON cto.transaction_id = ct.id

        JOIN xcart_orders xo 

          ON xo.order_cart_id = ct.cart_id

        WHERE ct.platform_id = p_platform_id

          AND cto.shipping_price_vat_rate = 0

          AND xo.is_mmf = 1;

    DECLARE CONTINUE HANDLER 

        FOR NOT FOUND SET finished = 1;

    OPEN curTransaction;

    START TRANSACTION;

    getTransaction: LOOP

        FETCH curTransaction INTO transactionId;

        IF finished = 1 THEN 

            LEAVE getTransaction;

        END IF;

        SET cnt = cnt + 1;

        UPDATE colibri_transaction_order

        SET shipping_price_vat_rate = 21

        WHERE id = transactionId;

        if mod(cnt,25000) = 0

            then commit; 

                select cnt, now();

                select sleep(p_sleep);

                START TRANSACTION; 

        end if;

    END LOOP getTransaction;

    CLOSE curTransaction;

    commit; 

END'

no viable alternative at input 'CREATE DEFINER=`ci`@`%` PROCEDURE `COM251`(IN p_platform_id CHAR(2), IN p_sleep INT)\nBEGIN\n    declare cnt integer default 0; \n    DECLARE finished INTEGER DEFAULT 0;\n    DECLARE transactionId int(11);\n    DECLARE curTransaction\n        CURSOR FOR \n        SELECT cto.id \n        FROM colibri_transaction_order cto\n        JOIN colibri_transaction ct \n          ON cto.transaction_id = ct.id\n        JOIN xcart_orders xo \n          ON xo.order_cart_id = ct.cart_id\n        WHERE ct.platform_id = p_platform_id\n          AND cto.shipping_price_vat_rate = 0\n          AND xo.is_mmf = 1;\n    DECLARE CONTINUE HANDLER \n        FOR NOT FOUND SET finished = 1;\n    OPEN curTransaction;\n    START TRANSACTION;\n    getTransaction: LOOP\n        FETCH curTransaction INTO transactionId;\n        IF finished = 1 THEN \n            LEAVE getTransaction;\n        END IF;\n        SET cnt = cnt + 1;\n        UPDATE colibri_transaction_order\n        SET shipping_price_vat_rate = 21\n        WHERE id = transactionId;\n        if mod('

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:1157)

at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:885)

at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:72)

at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:45)

at io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:80)

at io.debezium.connector.mysql.MySqlSchema.applyDdl(MySqlSchema.java:326)

at io.debezium.connector.mysql.BinlogReader.handleQueryEvent(BinlogReader.java:810)

at io.debezium.connector.mysql.BinlogReader.handleEvent(BinlogReader.java:590)

... 5 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:959)

... 12 more

Any ideas on how to get past this?

[Joel SALMERON VIVER]

Done; https://issues.redhat.com/browse/DBZ-3333

Thanks!

El dia dilluns, 22 de març de 2021 a les 15:32:59 UTC+1, jiri.p...@gmail.com va escriure: