Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10339

Invalid JSON Output FROM ANALYZE FORMAT=JSON

    XMLWordPrintable

Details

    Description

      The output of ANALYZE FORMAT=JSON can be invalid JSON by having multiple table keys in a single object. In the example below, the temporary_table object and the query_block object under subqueries both have multiple table keys. I'm not sure what's being used to create the output JSON, but it might be best to put the tables in a query block group in a tables array instead of in the main object for the query block.

      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 1484.3,
          "const_condition": "1",
          "filesort": {
            "r_loops": 1,
            "r_total_time_ms": 0.7623,
            "r_used_priority_queue": false,
            "r_output_rows": 409,
            "r_buffer_size": "218Kb",
            "temporary_table": {
              "table": {
                "table_name": "dpgc",
                "access_type": "index",
                "key": "PRIMARY",
                "key_length": "4",
                "used_key_parts": ["id"],
                "r_loops": 1,
                "rows": 406,
                "r_rows": 409,
                "r_total_time_ms": 0.6227,
                "filtered": 100,
                "r_filtered": 100
              },
              "table": {
                "table_name": "dpgct1",
                "access_type": "ref",
                "possible_keys": ["creditor_id"],
                "key": "creditor_id",
                "key_length": "5",
                "used_key_parts": ["creditor_id"],
                "ref": ["db.dpgc.id"],
                "r_loops": 409,
                "rows": 274,
                "r_rows": 497.52,
                "r_total_time_ms": 64.339,
                "filtered": 100,
                "r_filtered": 100,
                "using_index": true
              },
              "table": {
                "table_name": "t2",
                "access_type": "eq_ref",
                "possible_keys": ["PRIMARY", "process_date"],
                "key": "PRIMARY",
                "key_length": "4",
                "used_key_parts": ["id"],
                "ref": ["db.dpgct1.trans_id"],
                "r_loops": 203538,
                "rows": 1,
                "r_rows": 0.9983,
                "r_total_time_ms": 602.47,
                "filtered": 50,
                "r_filtered": 29.737,
                "attached_condition": "trigcond(((t2.process_date >= 2016-07-06) and trigcond((dpgct1.trans_id is not null))))"
              },
              "subqueries": [
                {
                  "query_block": {
                    "select_id": 2,
                    "r_loops": 1,
                    "r_total_time_ms": 675.79,
                    "table": {
                      "table_name": "dpgct2",
                      "access_type": "index",
                      "possible_keys": ["PRIMARY"],
                      "key": "creditor_id",
                      "key_length": "5",
                      "used_key_parts": ["creditor_id"],
                      "r_loops": 1,
                      "rows": 179946,
                      "r_rows": 203488,
                      "r_total_time_ms": 44.784,
                      "filtered": 100,
                      "r_filtered": 100,
                      "using_index": true
                    },
                    "table": {
                      "table_name": "t",
                      "access_type": "eq_ref",
                      "possible_keys": ["PRIMARY", "process_date"],
                      "key": "PRIMARY",
                      "key_length": "4",
                      "used_key_parts": ["id"],
                      "ref": ["db.dpgct2.trans_id"],
                      "r_loops": 203488,
                      "rows": 1,
                      "r_rows": 0.9985,
                      "r_total_time_ms": 565.77,
                      "filtered": 50,
                      "r_filtered": 29.736,
                      "attached_condition": "(t.process_date >= 2016-07-06)"
                    }
                  }
                }
              ]
            }
          }
        }
      }
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            bradjorgensen Brad Jorgensen
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.