10.1.5 Running MySQL Shell Reports

Built-in reports and user-defined reports that have been registered with MySQL Shell can be run in any interactive MySQL Shell mode (JavaScript, Python, or SQL) using the \show or \watch command, or called using the shell.reports object from JavaScript or Python scripts. The \show command or \watch command with no parameters list all the available built-in and user-defined reports.

Using the Show and Watch Commands

To use the \show and \watch commands, an active MySQL session must be available.

The \show command runs the named report, which can be either a built-in MySQL Shell report or a user-defined report that has been registered with MySQL Shell. You can specify any options or additional arguments that the report supports. For example, the following command runs the built-in report query, which takes as an argument a single SQL statement:

\show query show session status

The report name is case-insensitive, and the dash and underscore characters are treated as the same.

The \show command also provides the following standard options:

• --vertical (or -E) displays the results from a report that returns a list in vertical format, instead of table format.

• --help displays any provided help for the named report. (Alternatively, you can use the \help command with the name of the report, which displays help for the report function.)

Standard options and report-specific options are given before the arguments. For example, the following command runs the built-in report query and returns the results in vertical format:

\show query --vertical show session status

The \watch command runs a report in the same way as the \show command, but then refreshes the results at regular intervals until you cancel the command using Ctrl + C. The \watch command has additional standard options to control the refresh behavior, as follows:

• --interval=float (or -i float) specifies a number of seconds to wait between refreshes. The default is 2 seconds. Fractional seconds can be specified, with a minimum interval of 0.1 second, and the interval can be set up to a maximum of 86400 seconds (24 hours).

  --nocls specifies that the screen is not cleared before refreshes, so previous results can still be seen.

For example, the following command uses the built-in report query to display the statement counter variables and refresh the results every 0.5 seconds:

\watch query --interval=0.5 show global status like 'Com%'

Quotes are interpreted by the command handler rather than directly by the server, so if they are used in a query, they must be escaped by preceding them with a backslash (\).

Using the shell.reports Object

Built-in MySQL Shell reports and user-defined reports that have been registered with MySQL Shell can also be accessed as API functions in the shell.reports object. The shell.reports object is available in JavaScript and Python mode, and uses the report name supplied during the registration as the function name. The function has the following signature:

Dict report(Session session, List argv, Dict options);

Where:

• session is a MySQL Shell session object that is to be used to execute the report.

• argv is a list containing string values of additional arguments that are passed to the report.

• options is a dictionary with key names and values that correspond to any report-specific options and their values. The short form of the options cannot be used with the shell.reports object.

The return value is a dictionary with the key report, and a list of JSON objects containing the report. For the List type of report, there is an element for each list, for the Report type there is a single element, and for the Print type there are no elements.

With the shell.reports object, if a dictionary of options is present, the argv list is required even if there are no additional arguments. Use the \help report_name command to display the help for the report function and check whether the report requires any arguments or options.

For example, the following code runs a user-defined report named sessions which shows the sessions that currently exist. A MySQL Shell session object is created to execute the report. A report-specific option is used to limit the number of rows returned to 10. There are no additional arguments, so the argv list is present but empty.

report = shell.reports.sessions(shell.getSession(), [], {'limit':10});