QAN Export Button

[gordan-bobic]

QAN page should have an EXPORT button. When clicked, it should encode the following data into a JSON blob and push it for download:

• CREATE TABLE statements for all identified tables
• CREATE VIEW statements for any views involved, including their underlying tables
• Query
• EXPLAIN in JSON format
• Index stats for all of the tables involved (SHOW INDEX FROM $table)

This is to provide the user with an easy way to extract all available data relating to a query they deem problem and forward it to a specialist for advice.

[oblitorum]

The Export button is going to be added like this, at the bottom of the QAN page

And the json file looks like this:

Table

{
    "Query": "SELECT * FROM t1 JOIN t2 ON t1.name = t2.name WHERE t1.name = 'Tom'",
    "Explain": [
        {
            "Id": 1,
            "SelectType": "SIMPLE",
            "Table": "t1",
            "Partitions": null,
            "CreateTable": null,
            "Type": "ref",
            "PossibleKeys": "t1_name",
            "Key": "t1_name",
            "KeyLen": "1022",
            "Ref": "const",
            "Rows": 1,
            "Filtered": 100,
            "Extra": null
        },
        {
            "Id": 1,
            "SelectType": "SIMPLE",
            "Table": "t2",
            "Partitions": null,
            "CreateTable": null,
            "Type": "ALL",
            "PossibleKeys": null,
            "Key": null,
            "KeyLen": null,
            "Ref": null,
            "Rows": 1,
            "Filtered": 100,
            "Extra": "Using where; Using join buffer (hash join)"
        }
    ],
    "Tables": [
        {
            "Db": "test",
            "Table": "t1",
            "Create": "CREATE TABLE `t1` (\n  `id` int NOT NULL,\n  `name` varchar(255) NOT NULL,\n  KEY `t1_name` (`name`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci",
            "Index": {
                "t1_name": [
                    {
                        "Table": "t1",
                        "NonUnique": true,
                        "KeyName": "t1_name",
                        "SeqInIndex": 1,
                        "ColumnName": "name",
                        "Collation": "A",
                        "Cardinality": 2,
                        "SubPart": null,
                        "Packed": null,
                        "Null": "",
                        "IndexType": "BTREE",
                        "Comment": "",
                        "IndexComment": "",
                        "Visible": "YES"
                    }
                ]
            }
        },
        {
            "Db": "test",
            "Table": "t2",
            "Create": "CREATE TABLE `t2` (\n  `id` int NOT NULL,\n  `name` varchar(255) NOT NULL,\n  KEY `t2_id` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci",
            "Index": {
                "t2_id": [
                    {
                        "Table": "t2",
                        "NonUnique": true,
                        "KeyName": "t2_id",
                        "SeqInIndex": 1,
                        "ColumnName": "id",
                        "Collation": "A",
                        "Cardinality": 1,
                        "SubPart": null,
                        "Packed": null,
                        "Null": "",
                        "IndexType": "BTREE",
                        "Comment": "",
                        "IndexComment": "",
                        "Visible": "YES"
                    }
                ]
            }
        }
    ],
    "Views": []
}

View

{
    "Query": "SELECT  * FROM t1_t2_view",
    "Explain": [
        {
            "Id": 1,
            "SelectType": "SIMPLE",
            "Table": "t1",
            "Partitions": null,
            "CreateTable": null,
            "Type": "ref",
            "PossibleKeys": "t1_name",
            "Key": "t1_name",
            "KeyLen": "1022",
            "Ref": "const",
            "Rows": 1,
            "Filtered": 100,
            "Extra": null
        },
        {
            "Id": 1,
            "SelectType": "SIMPLE",
            "Table": "t2",
            "Partitions": null,
            "CreateTable": null,
            "Type": "ALL",
            "PossibleKeys": null,
            "Key": null,
            "KeyLen": null,
            "Ref": null,
            "Rows": 1,
            "Filtered": 100,
            "Extra": "Using where; Using join buffer (hash join)"
        }
    ],
    "Tables": [
        {
            "Db": "test",
            "Table": "t1",
            "Create": "CREATE TABLE `t1` (\n  `id` int NOT NULL,\n  `name` varchar(255) NOT NULL,\n  KEY `t1_name` (`name`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci",
            "Index": {
                "t1_name": [
                    {
                        "Table": "t1",
                        "NonUnique": true,
                        "KeyName": "t1_name",
                        "SeqInIndex": 1,
                        "ColumnName": "name",
                        "Collation": "A",
                        "Cardinality": 2,
                        "SubPart": null,
                        "Packed": null,
                        "Null": "",
                        "IndexType": "BTREE",
                        "Comment": "",
                        "IndexComment": "",
                        "Visible": "YES"
                    }
                ]
            }
        },
        {
            "Db": "test",
            "Table": "t2",
            "Create": "CREATE TABLE `t2` (\n  `id` int NOT NULL,\n  `name` varchar(255) NOT NULL,\n  KEY `t2_id` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci",
            "Index": {
                "t2_id": [
                    {
                        "Table": "t2",
                        "NonUnique": true,
                        "KeyName": "t2_id",
                        "SeqInIndex": 1,
                        "ColumnName": "id",
                        "Collation": "A",
                        "Cardinality": 1,
                        "SubPart": null,
                        "Packed": null,
                        "Null": "",
                        "IndexType": "BTREE",
                        "Comment": "",
                        "IndexComment": "",
                        "Visible": "YES"
                    }
                ]
            }
        }
    ],
    "Views": [
        {
            "Db": "test",
            "Table": "t1_t2_view",
            "Create": "CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`t1_t2_view` AS select `test`.`t1`.`id` AS `id1`,`test`.`t1`.`name` AS `name1`,`test`.`t2`.`id` AS `id2`,`test`.`t2`.`name` AS `name2` from (`test`.`t1` join `test`.`t2` on((`test`.`t1`.`name` = `test`.`t2`.`name`))) where (`test`.`t1`.`name` = 'Tom')"
        }
    ]
}

Procedure

{
    "Query": "CALL t1_t2_procedure('Tom')",
    "Explain": "",
    "Prodecures": [
        {
            "Db": "test",
            "Name": "t1_t2_procedure",
            "Create": "CREATE DEFINER=`root`@`localhost` PROCEDURE `t1_t2_procedure`(IN name VARCHAR(255))\nBEGIN \n    SELECT \n        *\n    FROM t1\n    JOIN t2 ON t1.name = t2.name\n    WHERE t1.name = name;\nEND"
        }
    ]
}

(speaking of this, the underlying tables of procedure seems not parsed currently, should we try to get the underlying tables like view?)

Filename is in format ssm-${monitored_server_name}-${date}-query-${query_id}-report.json, e.g. ssm-599a338a347c-2024-04-01T03_01_34-query-EC3862F9C7994C5E-report.json

Are all these ideal?

[gordan-bobic]

If it is not overwhelmingly difficult to parse the procedure and get table names out of it, then yes, let's include those tables in the export.

[oblitorum]

Unfortunately, I just checked the package we used to parse the SQL -> https://github.com/vitessio/vitess , it seems that it's NOT able to parse a CREATE PROCEDURE statement. And it's quite difficult to parse it ourself, maybe we should just skip it for now.

[gordan-bobic]

Yes, I suspected that would be the case. Skip it.