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

support for JSON function in MySQL index

    XMLWordPrintable

Details

    • Hide

      Steps:

      • Create a new table without index.
      CREATE TABLE `orders_json` (  `id` int NOT NULL AUTO_INCREMENT,  `reward` json DEFAULT NULL,  `additional_info` json DEFAULT NULL,  `created_at` timestamp NULL DEFAULT NULL,  `updated_at` timestamp NULL DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB;
      
      • Create a new MySQL connector.
      • Check connector status
      {
        "inventory-connector-json-v3": {
          "status": {
            "name": "inventory-connector-json-v3",
            "connector": {
              "state": "RUNNING",
              "worker_id": "172.18.0.5:8083"
            },
            "tasks": [
              {
                "id": 0,
                "state": "RUNNING",
                "worker_id": "172.18.0.5:8083"
              }
            ],
            "type": "source"
          }
        }
      }
      
      • Create index
      mysql> ALTER TABLE orders_json ADD INDEX `idx_order_codes` ((cast(json_extract(`additional_info`,_utf8mb4'$.order_codes') as char(17) array)));
      
      • Check connector status.
      {
        "inventory-connector-json-v3": {
          "status": {
            "name": "inventory-connector-json-v3",
            "connector": {
              "state": "RUNNING",
              "worker_id": "172.18.0.5:8083"
            },
            "tasks": [
              {
                "id": 0,
                "state": "FAILED",
                "worker_id": "172.18.0.5:8083",
                "trace": "org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.\n\tat io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:42)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:366)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$25(MySqlStreamingChangeEventSource.java:855)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1125)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:973)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:599)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:857)\n\tat java.base/java.lang.Thread.run(Thread.java:829)\nCaused by: io.debezium.DebeziumException: Error processing binlog event\n\t... 7 more\nCaused by: io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'ALTER TABLE orders_json ADD INDEX `idx_order_codes` ((cast(json_extract(`additional_info`,_utf8mb4'$.order_codes') as char(17) array)))'\nno viable alternative at input 'ALTER TABLE orders_json ADD INDEX `idx_order_codes` ((cast(json_extract(`additional_info`,_utf8mb4'$.order_codes') as char(17) array'\n\tat io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:43)\n\tat org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)\n\tat org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)\n\tat org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)\n\tat org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)\n\tat io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1194)\n\tat io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:922)\n\tat io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:72)\n\tat io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:45)\n\tat io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:82)\n\tat io.debezium.connector.mysql.MySqlDatabaseSchema.parseDdl(MySqlDatabaseSchema.java:216)\n\tat io.debezium.connector.mysql.MySqlDatabaseSchema.parseStreamingDdl(MySqlDatabaseSchema.java:202)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleQueryEvent(MySqlStreamingChangeEventSource.java:573)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$14(MySqlStreamingChangeEventSource.java:827)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:349)\n\t... 6 more\nCaused by: org.antlr.v4.runtime.NoViableAltException\n\tat org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:2026)\n\tat org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:467)\n\tat org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:393)\n\tat io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:996)\n\t... 15 more\n"
              }
            ],
            "type": "source"
          }
        }
      }
      
      Show
      Steps: Create a new table without index. CREATE TABLE `orders_json` ( `id` int NOT NULL AUTO_INCREMENT, `reward` json DEFAULT NULL, `additional_info` json DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; Create a new MySQL connector. Check connector status { "inventory-connector-json-v3" : { "status" : { "name" : "inventory-connector-json-v3" , "connector" : { "state" : "RUNNING" , "worker_id" : "172.18.0.5:8083" }, "tasks" : [ { "id" : 0, "state" : "RUNNING" , "worker_id" : "172.18.0.5:8083" } ], "type" : "source" } } } Create index mysql> ALTER TABLE orders_json ADD INDEX `idx_order_codes` (( cast (json_extract(`additional_info`,_utf8mb4 '$.order_codes' ) as char (17) array))); Check connector status. { "inventory-connector-json-v3" : { "status" : { "name" : "inventory-connector-json-v3" , "connector" : { "state" : "RUNNING" , "worker_id" : "172.18.0.5:8083" }, "tasks" : [ { "id" : 0, "state" : "FAILED" , "worker_id" : "172.18.0.5:8083" , "trace" : "org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.\n\tat io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:42)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:366)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$25(MySqlStreamingChangeEventSource.java:855)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1125)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:973)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:599)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:857)\n\tat java.base/java.lang. Thread .run( Thread .java:829)\nCaused by: io.debezium.DebeziumException: Error processing binlog event\n\t... 7 more\nCaused by: io.debezium.text.ParsingException: DDL statement couldn 't be parsed. Please open a Jira issue with the statement ' ALTER TABLE orders_json ADD INDEX `idx_order_codes` (( cast (json_extract(`additional_info`,_utf8mb4 '$.order_codes' ) as char (17) array))) '\nno viable alternative at input ' ALTER TABLE orders_json ADD INDEX `idx_order_codes` (( cast (json_extract(`additional_info`,_utf8mb4 '$.order_codes' ) as char (17) array'\n\tat io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:43)\n\tat org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)\n\tat org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)\n\tat org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)\n\tat org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)\n\tat io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1194)\n\tat io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:922)\n\tat io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:72)\n\tat io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:45)\n\tat io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:82)\n\tat io.debezium.connector.mysql.MySqlDatabaseSchema.parseDdl(MySqlDatabaseSchema.java:216)\n\tat io.debezium.connector.mysql.MySqlDatabaseSchema.parseStreamingDdl(MySqlDatabaseSchema.java:202)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleQueryEvent(MySqlStreamingChangeEventSource.java:573)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$14(MySqlStreamingChangeEventSource.java:827)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:349)\n\t... 6 more\nCaused by: org.antlr.v4.runtime.NoViableAltException\n\tat org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:2026)\n\tat org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:467)\n\tat org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:393)\n\tat io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:996)\n\t... 15 more\n" } ], "type" : "source" } } }

    Description

      Info:

      • MySQL version: 8.0.27
      • Debezium version: 1.7.1.Final

      Error message:

      org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.\n\tat io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:42)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:366)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$25(MySqlStreamingChangeEventSource.java:855)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient.notifyEventListeners(BinaryLogClient.java:1125)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:973)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:599)\n\tat com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:857)\n\tat java.base/java.lang.Thread.run(Thread.java:829)\nCaused by: io.debezium.DebeziumException: Error processing binlog event\n\t... 7 more\nCaused by: io.debezium.text.ParsingException: DDL statement couldn't be parsed. Please open a Jira issue with the statement 'ALTER TABLE orders_json ADD INDEX `idx_order_codes` ((cast(json_extract(`additional_info`,_utf8mb4'$.order_codes') as char(17) array)))'\nno viable alternative at input 'ALTER TABLE orders_json ADD INDEX `idx_order_codes` ((cast(json_extract(`additional_info`,_utf8mb4'$.order_codes') as char(17) array'\n\tat io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:43)\n\tat org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)\n\tat org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)\n\tat org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)\n\tat org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)\n\tat io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1194)\n\tat io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:922)\n\tat io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:72)\n\tat io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:45)\n\tat io.debezium.antlr.AntlrDdlParser.parse(AntlrDdlParser.java:82)\n\tat io.debezium.connector.mysql.MySqlDatabaseSchema.parseDdl(MySqlDatabaseSchema.java:216)\n\tat io.debezium.connector.mysql.MySqlDatabaseSchema.parseStreamingDdl(MySqlDatabaseSchema.java:202)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleQueryEvent(MySqlStreamingChangeEventSource.java:573)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.lambda$execute$14(MySqlStreamingChangeEventSource.java:827)\n\tat io.debezium.connector.mysql.MySqlStreamingChangeEventSource.handleEvent(MySqlStreamingChangeEventSource.java:349)\n\t... 6 more\nCaused by: org.antlr.v4.runtime.NoViableAltException\n\tat org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:2026)\n\tat org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:467)\n\tat org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:393)\n\tat io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:996)\n\t... 15 more\n
      

       

       

       

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              v0112358 Vy Nguyen Tan (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: