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

Function MOD is missing from MySQL grammar

    XMLWordPrintable

Details

    Description

       

      A procedure created to throttle DML has not been to the liking of the debezium we have on the prod db:

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

       

      Code of the proc attached.

      dbz conf:

      { "name" : "app_common_mysql_monechelle2", "config" : { "connector.class" : "io.debezium.connector.mysql.MySqlConnector", "database.history.kafka.bootstrap.servers" : "b-2.prd-infra-kafka-msk.ayzah0.c4.kafka.eu-west-3.amazonaws.com:9092,b-3.prd-infra-kafka-msk.ayzah0.c4.kafka.eu-west-3.amazonaws.com:9092,b-1.prd-infra-kafka-msk.ayzah0.c4.kafka.eu-west-3.amazonaws.com:9092", "database.history.kafka.recovery.poll.interval.ms" : "200000", "database.history.kafka.topic" : "app_common_mysql_monechelle.dbhistory", "database.history​.skip.unparseable​.ddl" : "yes", "database.hostname" : "prd-app-common-db.manomano.tech", "database.password" : "", "database.port" : "3306", "database.server.id" : "3423068", "database.server.name" : "app_common_mysql", "database.serverTimezone" : "Europe/Paris", "database.user" : "dbz_monechelle", "database.whitelist" : "monechelle", "decimal.handling.mode" : "double", "key.converter" : "org.apache.kafka.connect.storage.StringConverter", "key.converter.schema.registry.url" : "http://infra-kafka-framework.prd.manomano.com:8081", "max.batch.size" : "20480", "max.queue.size" : "81290", "max.request.size" : "10485760", "name" : "app_common_mysql_monechelle2", "producer.compression_type" : "lz4", "snapshot.locking.mode" : "none", "snapshot.mode" : "initial", "table.blacklist" : "monechelle.colibri_contract,monechelle.colibri_adyen_log,monechelle.colibri_fraud_result,monechelle.colibri_transaction_message,catalog_attribute.log_entry,monechelle.colibri_adyen_log,monechelle.colibri_catalog_import_reporting,monechelle.colibri_catalog_update_reporting,monechelle.colibri_fraud_tmp_transaction,monechelle.colibri_password_reminders,monechelle.colibri_product_business_score,monechelle.colibri_product_categories_prediction,monechelle.colibri_product_categories_prediction_api,monechelle.colibri_product_categories_prediction_api_history,monechelle.colibri_product_categories_prediction_manomano_api_history,monechelle.colibri_transaction_log,monechelle.colibri_ws_logs,monechelle.colibri_zendesk_log,monechelle.rememberme_token,monechelle.tmp_tb_3549", "tasks.max" : "1", "tombstones.on.delete" : "false", "value.converter" : "io.confluent.connect.avro.AvroConverter", "value.converter.schema.registry.url" : "http://infra-kafka-framework.prd.manomano.com:8081" } }
      

       

       

       

      Attachments

        1. COM251.sql
          1 kB
        2. x.sql
          1 kB

        Activity

          People

            jpechane Jiri Pechanec
            joel.salmeronviver Joel Salmeron Viver (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: