MySQL Shell 8.0.16: Built-in Reports

Readers of my blog know that I like how MySQL Shell allows you tocustomize it and use it’s Python and JavaScript support to createcustom libraries with tools that help with your daily tasks and even creating auto-refreshing reports . Lefred has even taken this a step further and started to port Innotop to MySQL Shell .

One disadvantage of my example of auto-refreshing reports and the Innotop port is they both rely on the curses Python module to refresh the screen. While avoiding to reinvent the wheel is usually a good thing, and the curses library is both powerful and easy to use, it is not well supported on Microsoft Windows. The good news is that in MySQL 8.0.16 and later, you can also get auto-refreshing reports with a new built-in reporting framework in MySQL Shell. This blog shows how this framework works.

Built-In Features

The great thing with the built-in framework is that you can start using it even without coding as it comes with a pre-configured report. The framework consists of three parts:

• \show : This is the most basic command which runs a report once and displays the result.
• \watch : This is similar to the watch command on Linux, where a command (report in this case) is repeatedly executed with the screen refreshed to show the new result.
• shell.registerReport() : This method can be used to register custom reports. The details of custom reports will be saved for a later blog.

The \show command is a good place to start.

The \show Command

You can get more information about how the \show command works and reports in general using thebuilt-in help system:

mysql-js> \h \show
NAME
      \show - Executes the given report with provided options and arguments.

SYNTAX
      \show <report_name> [options] [arguments]

DESCRIPTION
      The report name accepted by the \show command is case-insensitive, '-'
      and '_' characters can be used interchangeably.

      Common options:

      - --help - Display help of the given report.
      - --vertical, -E - For 'list' type reports, display records vertically.

      The output format of \show command depends on the type of report:

      - 'list' - displays records in tabular form (or vertically, if --vertical
        is used),
      - 'report' - displays a YAML text report,
      - 'print' - does not display anything, report is responsible for text
        output.

      If executed without the report name, lists available reports.

      Note: user-defined reports can be registered with shell.registerReport()
      method.

EXAMPLES
      \show
            Lists available reports, both built-in and user-defined.

      \show query show session status like 'Uptime%'
            Executes 'query' report with the provided SQL statement.

      \show query --vertical show session status like 'Uptime%'
            As above, but results are displayed in vertical form.

      \show query --help
            Displays help for the 'query' report.

SEE ALSO

Additional entries were found matching \show

The following topics were found at the SQL Syntax category:

- SHOW

For help on a specific topic use: \? <topic>

e.g.: \? SHOW

This already gives a lot of information, not only about the \show command, but also about reports. Reports can be in one of three formats (more on that in a later blog), if they are using the list format (which the query report discussed below uses), you can get the output in tabular format (the default) or vertical using the --vertical or -e option. And finally, you can get more information about known reports by running the report with the --help option, and you can get a list of known reports running \show without arguments:

mysql-js> \show
Available reports: query.

Let’s take a closer look at the query report.

The Query Report

The query report is a very simple report that take a query and runs it. You can get the help text for it by executing \show query --help :

mysql-js> \show query --help
query - Executes the SQL statement given as arguments.

Usage:
       \show query [OPTIONS] [ARGUMENTS]
       \watch query [OPTIONS] [ARGUMENTS]

Options:
  --help                        Display this help and exit.
  --vertical, -E                Display records vertically.

Arguments:
  This report accepts 1-* arguments.

So, to run it, you simply provide the query as an argument – you can do this either just providing the query as is or as a quoted string. Let’s say you want to use the following query for the report:

SELECT conn_id,
       sys.format_statement(current_statement) AS statement,
       format_pico_time(statement_latency) AS latency
  FROM sys.x$session
 ORDER BY statement_latency DESC
 LIMIT 10

This will show the longest running queries limited to 10 queries. Note that is uses the new format_pico_time() function that replaces the sys.format_time() function in MySQL 8.0.16. Newlines are not allowed in the query when generating the report, so the command becomes:

mysql-js> \show query SELECT conn_id, sys.format_statement(current_statement) AS statement, format_pico_time(statement_latency) AS latency FROM sys.x$session ORDER BY statement_latency DESC LIMIT 10
+---------+-------------------------------------------------------------------+----------+
| conn_id | statement                                                         | latency  |
+---------+-------------------------------------------------------------------+----------+
| 8       | SELECT conn_id, sys.format_sta ... tatement_latency DESC LIMIT 10 | 33.34 ms |
| 4       | NULL                                                              |   0 ps   |
+---------+-------------------------------------------------------------------+----------+

Granted, this is not particular useful – you could just have executed the query on its own. However, if you consider the \watch command instead, it become more useful.

Tip

The \show command is more useful for more complex reports that does more than just execute a single query or executes a complex query where the report functions as a stored query.

The \watch Command

The \watch the command supports two additional arguments on its own:

--interval=float, -i float
--nocls

Report may also add options of their own. The query report for example accepts one argument, which is the query to execute. Other reports may accept other arguments.

Otherwise, you start the report the same way as when using \show . For example, to run the query every five seconds:

mysql-js> \watch query --interval=5 SELECT conn_id, sys.format_statement(current_statement) AS statement, format_pico_time(statement_latency) AS latency FROM sys.x$session ORDER BY statement_latency DESC LIMIT 10

That’s it. If you want to stop the report again, use CTRL+c and the report will stop after the next refresh.

Conclusion

The report framework in MySQL Shell 8.0.16 gives a nice starting point for generating reports. The built-in query function may not be the most fancy you can think of, but it is very easy way to quickly make a query run repeatedly at set intervals. However, the real power of the report framework is that you now have a framework to create cross-platform custom reports. That will be the topic of a later blog.