MySQL Shell 8.0.16: User Defined Reports

In my blog yesterday, I wrote about the new reporting framework in MySQL Shell. It is part of the 8.0.16 release. I also noted that it includes the possibility to create your own custom reports and use those with the \show and \watch commands. This blog will explore how you can create a report and register it, so it automatically is available when you start MySQL Shell.

Background

You can write the code that generates the report in either JavaScript or Python. The reports can be used from either language mode – even SQL – irrespective of which language you choose, so go with what you are most comfortable with.

Once you have written your code, you save it in the init.d folder (does not exist by default) inside the user configuration folder. By default this is at the following location depending on whether you are on Microsoft Windows or Linux:

• Microsoft Windows: %AppData%MySQL\mysqlsh
• Linux: ~/.mysqlsh

You can overwrite this path with the MYSQLSH_USER_CONFIG_HOME environment variable.

You are free to choose any file name, but a good rule is to name the file the same as the report. However, it is required that you use .py as the file name extension if you wrote the report in Python and .js if you used JavaScript.

At that point, you need to register the report, so you can use it through the reporting framework. You do that using the shell.registerReport() method from inside the same file that has the report code. It takes four arguments: the name of the report, the report type, the function generating the report (as a function object), and optional a dictionary with the description. I will not go into the details of these argument here beyond providing an example of using it. The manual has a quite detailed section on registering your report including what the arguments are.

One thing that is worth discussing a bit as it influences how the report content should be formatted is the report type. This can have one of three values:

• list: The content of the report is returned as a list of lists constituting the rows of a table. The \show and \watch commands can then show the data either using the familiar tabular format or in vertical. The decision of which display format to use can be made when running the report.
• report: The report content is returned in YAML.
• print: The report code print the output directly.

The report and print types are the more flexible, but the list type works well with query results.

This can all feel very abstract. The best way to actually understand how it works is to write an example report to go through the steps.

Example Custom Report

The custom report, I will create is based on the one in the reference manual, but modified to allow you to choose what to sort by. The example should help make it clearer how to create your own reports.

The example is quite simple and could be generated using the built-in query report, but it serves as a good starting point to understand the mechanics of custom reports, and even simple reports like this provides a way to have your report logic saved in one place and easily accessible from within MySQL Shell. The example is written in Python, but a report generating the same result written in JavaScript would look similar (although not identical).

The Report Function

The first thing is to define the report itself. This report is called sessions, so the function with the code is also called sessions. This is not required, but it is best practice:

SORT_ALLOWED = {
    'thread': 'thd_id',
    'connection': 'conn_id',
    'user': 'user',
    'db': 'db',
    'latency': 'statement_latency',
    'memory': 'current_memory',
}

def sessions(session, args, options):

First a dictionary is defined with the keys specifying the allowed values for the --sort option and the values as what will actually be used for the ordering. Then there is the definition of the reporting function itself. The function takes three arguments:

• session: A MySQL Shell session object. This gives you access to all of the session properties and methods when you create the report.
• args: A list of any additional arguments passed to the the report. This is what the query report uses to get the query that will be executed. This report does not use any such arguments, so anything passed this way will be ignored.
• options: This is a dictionary with named options. This report will support two such named options:
  • --limit or -l which sets the maximum number of rows to retrieve. The option will use the limit key in the dictionary. The default is not to impose any limit.
  • --sort or -s which chooses what to sort by. The option will use the sort key in the dictionary. The report will support ordering by thread, connection, user, db, latency, and memory. The default is to sort by latency.

You can choose different names for the arguments if you prefer.

The next thing is to define the query that will retrieve the result that will be used in the report. You can do this in several ways. If you want to execute an SQL query, you can use session.sql() (where session is the name of the session object in your argument list). However, it is simpler to code the query using the X DevAPI as that makes it trivial to customize the query, for example with the limit option and what to order by.

    sys = session.get_schema('sys')
    session_view = sys.get_table('x$session')
    query = session_view.select(
        'thd_id', 'conn_id', 'user', 'db',
        'sys.format_statement(current_statement) AS statement',
        'sys.format_time(statement_latency) AS latency',
        'format_bytes(current_memory) AS memory')

The statement will query the sys.x$session view. This is the non-formatted version of sys.session. The reason for using this is to allow custom sorting of the result set according to the --sort option. The view is obtained using the session.get_schema() method first to get a schema object for the sys schema, then the get_table() method of the schema object.

The query can then be defined from the table (view in this case) object by using the select() method. The arguments are the columns that should be included in the result. As you can see, it if possible to manipulate the columns and rename them.

The X DevAPI makes it trivial to modify the query with the options the report are invoked with. First handle the --sort option:

    # Set what to sort the rows by (--sort)
    try:
        order_by = options['sort']
    except KeyError:
        order_by = 'latency'

    if order_by in ('latency', 'memory'):
        direction = 'DESC'
    else:
        direction = 'ASC'
    query.order_by('{0} {1}'.format(
        sort_allowed[order_by], direction))

    # If ordering by latency, ignore those statements with a NULL latency
    # (they are not active)
    if order_by == 'latency':
        query.where('statement_latency IS NOT NULL')

If the --sort option is not provided, then a SystemError exception is raised. The first part of the snippet handles this, and ensures that the report default to ordering by the latency. Then, it is checked if the provided value is one of the supported values.

The next step is to decide whether to sort in descending or ascending order. You can of course add another option for this, but here the logic is contained within the report choosing descending when sorting by latency or memory usage; otherwise ascending.

The final step is to tell MySQL what to order by which is done in lines 29-30 by invoking the order_by() method. This is where the programmatic approach of the X DevAPI makes it easier to gradually put the query together compared to working directly with the SQL statement.

This report adds a little extra logic to the query. If the result is ordered by latency, only queries that are currently executing (the latency IS NOT NULL are included). This is one of the advantages of creating a custom report rather than writing the query ad-hoc as you can include logic like that.

The --limit option is handled in a similar way:

    # Set the maximum number of rows to retrieve is --limit is set.
    try:
        limit = options['limit']
    except KeyError:
        limit = 0
    if limit > 0:
        query.limit(limit)

There is not much to note about this code snippet. In line 43 the limit is applied (if the value is greater than 0) by invoking the limit() method. Finally, the query can be executed and the report generated:

    result = query.execute()
    report = [result.get_column_names()]
    for row in result.fetch_all():
        report.append(list(row))

    return {'report': report}

The execute() method is used to tell MySQL that the query can be executed. This returns a result object. The get_column_names() method of the result object can be used to get the column names. Then, the rows are added by iterating over them. As you can see, there is only one report list: the first element is a list with the column headers, the remaining are the row values.

Finally, the result is returned as a dictionary. That is it for generating the report, but it should also be registered.

Registering the Report

The registration of the report is done in the same file as where the report function was defined. You perform the registration by calling the shell.register_report() method:

shell.register_report(
    'sessions',
    'list',
    sessions,
    {
        'brief': 'Shows which sessions exist.',
        'details': ['You need the SELECT privilege on sys.session view and ' +
                    'the underlying tables and functions used by it.'],
        'options': [
            {
                'name': 'limit',
                'brief': 'The maximum number of rows to return.',
                'shortcut': 'l',
                'type': 'integer'
            },
            {
                'name': 'sort',
                'brief': 'The field to sort by.',
                'shortcut': 's',
                'type': 'string',
                'values': list(sort_allowed.keys())
            }
        ],
        'argc': '0'
    }
)

The first argument is the name of the report, ‘sessions', then the report type. The third argument is the function itself. Then comes a dictionary describing the report.

There are two parts to the dictionary: the two first arguments with a description of the report – first a short (brief) description, then more details. Then a list of the options that the report supports. The final argument is the number of additional arguments. Notice that for the --sort option, the element values have been set with the allowed values for the option. This will make MySQL Shell validate the option.

Now, you are ready to test the report.

Testing the Report

First the report must be installed. If you do not already have the init.d directory, create it under %AppData%MySQL\mysqlsh if you are on Microsoft Windows or under ~/.mysqlsh if you are on Linux. Then copy sessions.py into the directory.

Now, start MySQL Shell and the report is ready to be used:

mysql-js> \show
Available reports: query, sessions.

mysql-js> \show sessions --help
sessions - Shows which sessions exist.

You need the SELECT privilege on sys.session view and the underlying tables and
functions used by it.

Usage:
       \show sessions [OPTIONS]
       \watch sessions [OPTIONS]

Options:
  --help                        Display this help and exit.
  --vertical, -E                Display records vertically.
  --limit=integer, -l           The maximum number of rows to return.
  --sort=string, -s             The field to sort by. Allowed values: latency,
                                thread, db, connection, user, memory.

mysql-js> \show sessions
+--------+---------+---------------+------+-------------------------------------------------------------------+----------+------------+
| thd_id | conn_id | user          | db   | statement                                                         | latency  | memory     |
+--------+---------+---------------+------+-------------------------------------------------------------------+----------+------------+
| 65     | 28      | mysqlx/worker | NULL | SELECT `thd_id`,`conn_id`,`use ... ER BY `statement_latency` DESC | 38.09 ms | 965.58 KiB |
+--------+---------+---------------+------+-------------------------------------------------------------------+----------+------------+

mysql-js> \show sessions -E
*************************** 1. row ***************************
   thd_id: 65
  conn_id: 28
     user: mysqlx/worker
       db: NULL
statement: SELECT `thd_id`,`conn_id`,`use ... ER BY `statement_latency` DESC
  latency: 35.49 ms
   memory: 968.88 KiB

Notice how the help text has been generated from the information that was provided when the report was registered, and how the -E option can be used to turn the tabular output format into the vertical format. Note also that the report is invoked from JavaScript mode and still works even though the report is written in Python – MySQL Shell will automatically handle that for you and ensure the report is executed using the correct interpreter.

It is left as an exercise for the reader to add the --sort and --limit options and to use the report with the \watch command.

One related feature that is worth covering before finishing is the shell.reports object.

The shell.reports Object

This far the \show and \watch commands have been used to invoke the reports, but there is a lower level way to do it – using the shell.reports object. It is also a very useful way to explore which reports are available.

Let's start with the latter – exploring reports – as that also shows you how the shell.reports object work. As usual in MySQL Shell, it has built-in help:

mysql-py> shell.reports.help()
NAME
      reports - Gives access to built-in and user-defined reports.

SYNTAX
      shell.reports

DESCRIPTION
      The 'reports' object provides access to built-in reports.

      All user-defined reports registered using the shell.register_report()
      method are also available here.

      The reports are provided as methods of this object, with names
      corresponding to the names of the available reports.

      All methods have the same signature: Dict report(Session session, List
      argv, Dict options), where:

      - session - Session object used by the report to obtain the data.
      - argv (optional) - Array of strings representing additional arguments.
      - options (optional) - Dictionary with values for various report-specific
        options.

      Each report returns a dictionary with the following keys:

      - report (required) - List of JSON objects containing the report. The
        number and types of items in this list depend on type of the report.

      For more information on a report use: shell.reports.help('report_name').

FUNCTIONS
      help([member])
            Provides help about this object and it's members

      query(session, argv)
            Executes the SQL statement given as arguments.

      sessions(session[, argv][, options])
            Shows which sessions exist.

This includes a list of the functions available – and notice that the two reports that exist, query and sessions, are among the functions. You can also use the help() function with the report name as a string argument to get the report specific help.

If you invoke one of the report functions, you execute the report. This is much similar to invoking the report using the \show command, but it will be the raw report result that is returned. Let's try it both for the query and sessions reports:

mysql-py> shell.reports.query(shell.get_session(), ["SELECT NOW()"])
{
    "report": [
        [
            "NOW()"
        ],
        [
            "2019-04-27 15:53:21"
        ]
    ]
}

mysql-py> shell.reports.sessions(shell.get_session(), [], {'limit': 10, 'sort': 'latency'})
{
    "report": [
        [
            "thd_id",
            "conn_id",
            "user",
            "db",
            "statement",
            "latency",
            "memory"
        ],
        [
            66,
            29,
            "mysqlx/worker",
            null,
            "SELECT `thd_id`,`conn_id`,`use ... ment_latency` DESC LIMIT 0, 10",
            "39.76 ms",
            "886.99 KiB"
        ]
    ]
}

It is not often this is needed, but in case you want to manually manipulate the output, it can be useful.

That is all. Now over to you to create your own reports.