MySQL 8: Drop Several Stored Events, Procedures, or Functions

Maybe the biggest new feature in MySQL 8 is the new transaction data dictionary that improves the consistency of schema objects among other things. To further protect the data in the data dictionary, the data dictionary tables are hidden and their content only exposed through the Information Schema. (One exception is when you use the debug binary, then it is possible to get direct access to the data dictionary tables. This is not recommended at all on production systems!)

A side effect of the data dictionary tables being hidden is that those that have had a habit of manipulating the tables directly in MySQL 5.7 and earlier (I will not recommend doing that) will no longer be able to do so. Examples of manipulating the tables include dropping several stored events, procedures, and/or functions by deleting the corresponding rows in the mysql.event and mysql.proc tables.

Tip

I will use the word object to cover either an event, procedure, or function.

There are some discussions on Stack Overflow on how to do this task. See for example How to delete multiple events in mysql 8.0 database ? it was working in 5.7 but not in 8.0? and MySQL 8 – Remove All Stored Procedures in a Specific Database.

The obvious thought may be to write a stored procedure that can delete the objects, however that requires using a prepared statement which is not supported for DROP EVENT and similar:

mysql> SET @sql = 'DROP EVENT db1.event1';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt_drop FROM @sql;
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

There is a feature request to implement this in Bug 42337.

So what to do instead? My suggestion is to use MySQL Shell. There you can generate the queries and use session.sql() to execute them without using a prepared statement. However, first some objects are needed.

Deleting events, functions, and procedures in MySQL Shell
Deleting events, functions, and procedures in MySQL Shell

Setup

Before we start looking at the options to drop several events, procedures, or functions, lets create some objects to work with. For the examples there are three events, three procedures, and three functions that can be created as follows:

mysql> CREATE SCHEMA db1;
Query OK, 1 row affected (0.01 sec)

mysql> use db1;
Database changed
mysql> CREATE EVENT event1 ON SCHEDULE EVERY 1 DAY STARTS NOW() DO DO SLEEP(1);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE EVENT event2 ON SCHEDULE EVERY 1 DAY STARTS NOW() DO DO SLEEP(1);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE EVENT `event``3` ON SCHEDULE EVERY 1 DAY STARTS NOW() DO DO SLEEP(1);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE proc1() DO SLEEP(1);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE proc2() DO SLEEP(1);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `proc``3`() DO SLEEP(1);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION func1() RETURNS int DETERMINISTIC RETURN 1;
Query OK, 0 rows affected (0.40 sec)

mysql> CREATE FUNCTION func2() RETURNS int DETERMINISTIC RETURN 1;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE FUNCTION `func``3`() RETURNS int DETERMINISTIC RETURN 1;
Query OK, 0 rows affected (0.30 sec)

mysql> SELECT EVENT_SCHEMA, EVENT_NAME
         FROM information_schema.EVENTS
        WHERE EVENT_SCHEMA = 'db1';
+--------------+------------+
| EVENT_SCHEMA | EVENT_NAME |
+--------------+------------+
| db1          | event1     |
| db1          | event2     |
| db1          | event`3    |
+--------------+------------+
3 rows in set (0.01 sec)

mysql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
         FROM information_schema.ROUTINES
        WHERE ROUTINE_SCHEMA = 'db1';
+----------------+--------------+--------------+
| ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE |
+----------------+--------------+--------------+
| db1            | func1        | FUNCTION     |
| db1            | func2        | FUNCTION     |
| db1            | func`3       | FUNCTION     |
| db1            | proc1        | PROCEDURE    |
| db1            | proc2        | PROCEDURE    |
| db1            | proc`3       | PROCEDURE    |
+----------------+--------------+--------------+
6 rows in set (0.01 sec)

This also shows how the events and routines can be found from the Information Schema.

The third event, procedure, and function are named with a backtick before the digit. This is to ensure our delete method quotes the identifier names correctly. If you are deleting events and routines from a production system, you are encouraged to test carefully that you quote the identifiers correctly.

Now on to delete the objects.

MySQL Shell

MySQL Shell is one of the great recent additions to MySQL. It supports performing DBA and devops tasks using SQL, Python, and JavaScript. Having a full featured programing language available allows you to automate tasks that otherwise would have been cumbersome or impossible.

The Python and JavaScript modes support executing queries either using NoSQL CRUD methods or SQL statements. So, now the task of deleting events, procedures, or functions becomes as simple as executing a query against the Information Schema to find the names of the objects and then generate the queries in a loop. The following will use Python, but it can also be written in JavaScript if you prefer.

I will first go through dropping events, then functions and procedures.

Events – Interactive

It is possible to get the names using the select() CRUD method on the information_schema.EVENTS table:

mysql-py> i_s = session.get_schema("information_schema")

mysql-py> events = i_s.EVENTS \
       ->             .select("sys.quote_identifier(EVENT_SCHEMA) AS EventSchema", "sys.quote_identifier(EVENT_NAME) AS EventName") \
       ->             .where("EVENT_SCHEMA = 'db1'").execute().fetch_all();

mysql-py> events
[
    [
        "`db1`",
        "`event1`"
    ], 
    [
        "`db1`",
        "`event2`"
    ], 
    [
        "`db1`",
        "`event``3`"
    ]
]

First an object of the information_schema is stored in the i_s variable. This allows you to access the Information Schema EVENTS table using i_s.EVENTS. From here the select() CRUD method is used to find all events in the db1 schema.

Tip

If you have a different criteria for which events to delete, edit the argument to the where() method.

The sys.quote_identifier() function is used to ensure that the schema and event names are quoted correctly, so for example the backtick in event`3 is escaped correctly (by adding a second backtick).

Important

The sys.quote_identifier() only works guaranteed to work correctly when the ANSI_QUOTES SQL mode is not enabled. When ANSI quotes are used, double quotes (“) are using to quote identifiers though backticks are still supported.

Now events is a list of the three events that were created earlier. You can easily check which events where found by just “executing” events. With that, you can delete the events one by one in a loop. For this a query template is first defined, then used to add the name of the schema and event. Usually it is not safe to do that using the Python format() function, however here we have ensured the names are quotes appropriately by using sys.quote_identifier().

mysql-py> sql_fmt = "DROP EVENT {0}.{1}"
mysql-py> for event in events:
       ->     print(sql_fmt.format(*event))
       ->     session.sql(sql_fmt.format(*event)).execute()
       ->
DROP EVENT `db1`.`event1`
DROP EVENT `db1`.`event2`
DROP EVENT `db1`.`event``3`
Query OK, 0 rows affected (0.0038 sec)

That’s it. Not exactly a one liner, but not hard either.

This is fine if it is a one off that you need to delete some events, but what if this is a more regular task? MySQL Shell can accommodate for that as well.

Events – Function

I have previously discussed how you can use external libraries in MySQL Shell and even shown an example of using it to have auto-repeating reports. It can also be used to implement a function where you give the schema you want to delete events from as an argument.

Let’s look at how the code looks for that:

from mysqlsh import globals

def events_by_schema(schema, verbose=True):
    """Drops all events in the schema passed as an argument.

    Requires a connection to exist to the MySQL instance where the
    events are to be dropped.

    Optionally specify whether to be verbose (enabled by default) by
    printing each DROP EVENT statement executed.

    Returns the number of events dropped and False if an issue is
    encountered so no attempts are made to drop the events.
    """

    # Get the session and ensure it is connected
    if not globals.session.is_open():
        print("Please create a connection first.")
        return False

    # Define the query to get the events
    i_s = globals.session.get_schema("information_schema")
    stmt = i_s.EVENTS.select(
        "sys.quote_identifier(EVENT_SCHEMA) AS EventSchema",
        "sys.quote_identifier(EVENT_NAME) AS EventName")
    stmt = stmt.where("EVENT_SCHEMA = :schema")

    # Execute the query and check for warnings
    result = stmt.bind("schema", schema).execute()
    events = result.fetch_all()
    if (result.get_warnings_count() > 0):
        # Bail out and print the warnings
        print("Warnings occurred - bailing out:")
        print(result.get_warnings())
        return False

    # Drop the events and check for warnings after each event
    sql_fmt = "DROP EVENT {0}.{1}"
    for event in events:
        sql = sql_fmt.format(*event)
        if verbose:
            print(sql)

        drop_result = globals.session.sql(sql).execute()
        if (drop_result.get_warnings_count() > 0):
            print("Warnings occurred:")
            print(result.get_warnings())

    return len(events)

Yes, this code is a lot longer than the previous example, but in reality it is the same with some extra checks added and obtaining the session (the connection to the MySQL instance) from the mysqlsh.globals module. Since the schema is now provided as an argument to the function, a placeholder and bind() are used to ensure it is safe to use the value in a query.

If you save the code in a file in the Python search path, you can import it into MySQL Shell. Recall from my earlier blog that you can include extra directories in your search path by updating mysqlshrc.py in one of the places where MySQL Shell looks for it; for example in ${HOME}/.mysqlsh on Linux or %APPDATA%\MySQL\mysqlsh on Windows. An example of the mysqlshrc.py file is:

import sys
sys.path.append("D:\MySQL\Shell\Python")

In this example, the code has been saved in the file delete_objects.py, so you can delete all events in the db1 schema like the following example:

mysql-py> import delete_objects
mysql-py> num_delete_events = delete_objects.events_by_schema('db1')
DROP EVENT `db1`.`event1`
DROP EVENT `db1`.`event2`
DROP EVENT `db1`.`event``3`

That completes the example for events. Now let’s turn to functions and procedures.

Procedures and Functions – Interactive

The example of deleting functions and/or procedures is very similar to what have just been seen for events, other than using the information_schema.ROUTINES table instead of information_schema.EVENTS to get the routine names and that your need to set a filter on ROUTINE_TYPE to specify whether you want procedures or functions (or both if the filter is absent). So, I will jump straight into the example by first dropping all functions, then all procedures in the db1 schema:

mysql-py> i_s = session.get_schema("information_schema")

mysql-py> functions = i_s.ROUTINES \
       ->                .select("sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema", "sys.quote_identifier(ROUTINE_NAME) AS RoutineName") \
       ->                .where("ROUTINE_SCHEMA = 'db1' AND ROUTINE_TYPE = 'FUNCTION'").execute().fetch_all()

mysql-py> functions
[
    [
        "`db1`",
        "`func1`"
    ], 
    [
        "`db1`",
        "`func2`"
    ], 
    [
        "`db1`",
        "`func``3`"
    ]
]

mysql-py> for function in functions:
       ->     print(sql_fmt.format(*function))
       ->     session.sql(sql_fmt.format(*function)).execute()
       ->
DROP FUNCTION `db1`.`func1`
DROP FUNCTION `db1`.`func2`
DROP FUNCTION `db1`.`func``3`
Query OK, 0 rows affected (0.0684 sec)

Similar for the procedures:

mysql-py> i_s = session.get_schema("information_schema")

mysql-py> procedures = i_s.ROUTINES \
       ->             .select("sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema", "sys.quote_identifier(ROUTINE_NAME) AS RoutineName") \
       ->             .where("ROUTINE_SCHEMA = 'db1' AND ROUTINE_TYPE = 'PROCEDURE'").execute().fetch_all()

mysql-py> procedures
[
    [
        "`db1`",
        "`proc1`"
    ], 
    [
        "`db1`",
        "`proc2`"
    ], 
    [
        "`db1`",
        "`proc``3`"
    ]
]

mysql-py> sql_fmt = "DROP PROCEDURE {0}.{1}"

mysql-py> for procedure in procedures:
       ->     print(sql_fmt.format(*procedure))
       ->     session.sql(sql_fmt.format(*procedure)).execute()
DROP PROCEDURE `db1`.`proc1`
DROP PROCEDURE `db1`.`proc2`
DROP PROCEDURE `db1`.`proc``3`
Query OK, 0 rows affected (0.0976 sec)

Again, it is possible to create a function, so the code can be re-used.

Functions and Procedures – Function

Since the code for deleting functions and procedures is so similar – it is just the filter and DROP FUNCTION versus DROP PROCEDURE that is the difference – it is simple to use the same function to delete either. In fact, it would not take much to combine it with events_by_schema() from above, but that will be left as an exercise for the reader (there is a hint in the next section using MySQL Connector/Python).

The code for the routines_by_schema() function is:

from mysqlsh import globals

def routines_by_schema(schema, routine_type=None, verbose=True):
    """Drops all routines of a given type in the schema passed as an
    argument. If no type is given, both functions and procedures are
    dropped. The default is to drop both.

    Requires a connection to exist to the MySQL instance where the
    routines are to be dropped.

    Optionally specify whether to be verbose (enabled by default) by
    printing each DROP FUNCTION|PROCEDURE statement executed.

    Returns the number of routines dropped and False if an issue is
    encountered so no attempts are made to drop the routines.
    """

    # Get the session and ensure it is connected
    if not globals.session.is_open():
        print("Please create a connection first.")
        return False

    # Define the query to get the routines
    i_s = globals.session.get_schema("information_schema")
    filters = ["ROUTINE_SCHEMA = :schema"]
    if routine_type is not None:
        filters.append("ROUTINE_TYPE = :type")
    stmt = i_s.ROUTINES.select(
        "sys.quote_identifier(ROUTINE_SCHEMA) AS RoutineSchema",
        "sys.quote_identifier(ROUTINE_NAME) AS RoutineName",
        "ROUTINE_TYPE")
    stmt = stmt.where(" AND ".join(filters))

    # Execute the query and check for warnings
    stmt = stmt.bind("schema", schema)
    if routine_type is not None:
        stmt = stmt.bind("type", routine_type)
    result = stmt.execute()
    routines = result.fetch_all()
    routines = result.fetch_all()
    if (result.get_warnings_count() > 0):
        # Bail out and print the warnings
        print("Warnings occurred - bailing out:")
        print(result.get_warnings())
        return False

    # Drop the routines and check for warnings after each routine
    sql_fmt = "DROP {2} {0}.{1}"
    for routine in routines:
        sql = sql_fmt.format(*routine)
        if verbose:
            print(sql)

        drop_result = globals.session.sql(sql).execute()

        if (drop_result.get_warnings_count() > 0):
            print("Warnings occurred:")
            print(result.get_warnings())

    return len(routines)

The function takes an extra argument compared to events_by_schema(), so it is possible to either delete both functions and procedures (the default) or just one type. The use of the function is also similar to what have been seen before:

mysql-py> num_delete_functions = delete_objects.routines_by_schema('db1', 'FUNCTION')
DROP FUNCTION `db1`.`func1`
DROP FUNCTION `db1`.`func2`
DROP FUNCTION `db1`.`func``3`

mysql-py> num_delete_procedure = delete_objects.routines_by_schema('db1', 'PROCEDURE')
DROP PROCEDURE `db1`.`proc1`
DROP PROCEDURE `db1`.`proc2`
DROP PROCEDURE `db1`.`proc``3`

# Restore the functions and procedures
mysql-py> num_delete_routines = delete_objects.routines_by_schema('db1')
DROP FUNCTION `db1`.`func1`
DROP FUNCTION `db1`.`func2`
DROP FUNCTION `db1`.`func``3`
DROP PROCEDURE `db1`.`proc1`
DROP PROCEDURE `db1`.`proc2`
DROP PROCEDURE `db1`.`proc``3`

The last thing is to look at how the same actions can be done with MySQL Connector/Python.

MySQL Connector/Python

Given how similar using Python in MySQL Shell is with MySQL Connector/Python, it is natural to make the functions available as a command-line tool or function for Connector/Python programs. The following shows an example of how that can be done – combining events_by_schema() and routines_by_schema() into one function. The source code is:

import mysqlx

def objects_by_schema(session, schema, object_type=None, verbose=True):
    """Drops all events, functions, and/or procedures in the schema
    passed as an argument.

    Requires a connection to exist to the MySQL instance where the
    events are to be dropped.

    The object_type can be None (drop all events, functions, and
    procedures - the default), "EVENT", "FUNCTION", or "PROCEDURE".

    Optionally specify whether to be verbose (enabled by default) by
    printing each DROP statement executed.

    Returns the number of events, functions, and procedures dropped
    as a dictionary or False if an issue is encountered so no attempts
    are made to drop the events.
    """

    # Get the session and ensure it is connected
    if not session.is_open():
        print("Please create a connection first.")
        return False

    # Get an object for the Information Schema
    # and whether ANSI quotes are used
    i_s = session.get_schema("information_schema")
    sql_mode = session.sql("SELECT @@session.sql_mode AS sql_mode").execute() \
                      .fetch_one()["sql_mode"]
    sql_modes = sql_mode.split(",")
    ansi_quotes = "ANSI_QUOTES" if "ANSI_QUOTES" in sql_modes else ""

    events = []
    # If dropping events, get a list of all events
    if object_type is None or object_type == "EVENT":
        events_table = i_s.get_table("EVENTS")
        stmt = events_table.select(
            "EVENT_SCHEMA AS ObjectSchema", "EVENT_NAME AS ObjectName",
            "'EVENT' AS ObjectType")
        stmt = stmt.where("EVENT_SCHEMA = :schema")

        # Execute the query and check for warnings
        result = stmt.bind("schema", schema).execute()
        events = result.fetch_all()
        if (result.get_warnings_count() > 0):
            # Bail out and print the warnings
            print("Warnings occurred - bailing out:")
            print(result.get_warnings())
            return False

    routines = []
    if object_type is None or object_type in ("FUNCTION", "PROCEDURE"):
        routines_table = i_s.get_table("ROUTINES")
        filters = ["ROUTINE_SCHEMA = :schema"]
        if object_type is not None:
            filters.append("ROUTINE_TYPE = :type")
        stmt = routines_table.select(
            "ROUTINE_SCHEMA AS ObjectSchema", "ROUTINE_NAME AS ObjectName",
            "ROUTINE_TYPE AS ObjectType")
        stmt = stmt.where(" AND ".join(filters))

        # Execute the query and check for warnings
        stmt = stmt.bind("schema", schema)
        if object_type is not None:
            stmt = stmt.bind("type", object_type)
        result = stmt.execute()
        routines = result.fetch_all()
        if (result.get_warnings_count() > 0):
            # Bail out and print the warnings
            print("Warnings occurred - bailing out:")
            print(result.get_warnings())
            return False

    # Drop the routines and check for warnings after each routine
    objects = events + routines
    sql_fmt = "DROP {0} {1}.{2}"
    objects_deleted = {
        "EVENT": 0,
        "FUNCTION": 0,
        "PROCEDURE": 0,
    }
    for obj in objects:
        objects_deleted[obj[2]] += 1
        sql = sql_fmt.format(obj[2],
                  mysqlx.helpers.quote_identifier(obj[0], ansi_quotes),
                  mysqlx.helpers.quote_identifier(obj[1], ansi_quotes))
        if verbose:
            print(sql)

        drop_result = session.sql(sql).execute()

        if (drop_result.get_warnings_count() > 0):
            print("Warnings occurred:")
            print(result.get_warnings())

    return objects_deleted

if __name__ == "__main__":
    import sys
    import argparse
    import getpass

    # Handle the command line arguments
    parser = argparse.ArgumentParser(
        prog="delete_objects.py",
        description="Delete all events, functions, and/or procedures from a "
                   + "single schema.")

    parser.add_argument("--type", default="ALL",
        choices=["ALL", "EVENT", "FUNCTION", "PROCEDURE"],
        help="The object type to drop.")
    parser.add_argument("--schema", default=None,
        help="The schema to drop objects from.")
    parser.add_argument("--verbose", type=int, default=1,
        help="Whether to produce verbose output.")
    parser.add_argument("--user", default=None,
        help="The user to connect to MySQL as.")
    parser.add_argument("--host", default="127.0.0.1",
        help="The host to connect to MySQL on.")
    parser.add_argument("--port", type=int, default=33060,
        help="The mysqlx port to connect to MySQL on.")

    # Parse the arguments and get the password used when connecting to MySQL
    args = parser.parse_args(sys.argv[1:])
    object_type = args.type
    if args.type == "ALL":
        object_type = None
    verbose = True if args.verbose > 0 else False
    password = getpass.getpass("MySQL Password: ")

    # Connect to MySQL
    session = mysqlx.get_session(user=args.user, password=password,
                                 host=args.host, port=args.port)

    # Drop the objects
    objects_deleted = objects_by_schema(session, args.schema, object_type,
                                        verbose)
    if verbose:
        print(objects_deleted)

While the example is pretty extensive, it is not quite complete. There is only support for TCP connections using the user, password, host, and port options, and the password is always provided interactively (the most secure). Support for a configuration file and other options can be added if needed.

Instead of using the sys.quote_identifier() function, the schema and object names are quoted using the mysqlx.helpers.quote_identifier() function. Otherwise the program should be self explanatory given the previous examples. You can use python delete_objects.py --help to get help with the supported arguments.

Auto-Refreshing Reports in MySQL Shell

MySQL Shell makes it easy to develop tools you can use for example to generate reports. In a previous blog, I showed how to use external modules in MySQL Shell. In this blog, I will take it one step further and use the curses Python module to create auto-refreshing reports. The first example will be kept very simple to show the idea, then a more realistic example will be shown where the top N files sorted by I/O will be returned.

Note

Out of the box, this does not work on Windows as Python does not ship with the curses library.

Basic Example

As a basic example, consider the query SELECT NOW(). This returns the date and time. Let’s query that every second for 10 seconds, then return to the prompt. The easiest is to look at the example and then discuss what it does:

import curses
from time import sleep

def clock():
    # Setup curses
    stdscr = curses.initscr()
    curses.noecho()
    curses.cbreak()
    stdscr.keypad(True)
    
    # Define the query
    query = session.sql("SELECT NOW()")
    
    # Clear screen
    stdscr.clear()
    
    # Run the clock for 10 seconds
    i = 0
    while i < 10:
        i = i + 1
        result = query.execute()
        now = result.fetch_one()
        stdscr.addstr(0, 0, "Now")
        stdscr.addstr(1, 0, "-------------------")
        stdscr.addstr(2, 0, str(now[0]))
        stdscr.refresh()
        sleep(1)
    
    # Reset the cures behaviour and finish
    curses.nocbreak()
    stdscr.keypad(False)
    curses.echo()
    curses.endwin()

clock()

Tip

As MySQL Shell treats an empty line as the termination of a multi line block of code, ensure you have some white space on the blank lines in the definition of clock() if you are copying and pasting the code.

Inside the clock() function, curses is first set up to initialize the screen, not to echo key inputs, and to react to keys instantly (don’t wait for enter to be hit). The latter is not needed here as there is nothing checking for key inputs, however in many cases (like the iotop example later in the blog), you want to listen for single key inputs, for example to stop the execution. The counter part of these steps are done at the end of the function to clean up.

Next the query that will be executed is defined. Here I take advantage of the X DevAPI’s support for preparing a query and use and re-use it later. This way, the query is only defined in a single spot. Then the screen is cleared and everything is ready for the loop that will do the main part of the job.

The loop in this example is very simple. It just goes through 10 iterations with a one second sleep at the end of each loop. In a real case, you may want to make this more resilient to delays or add another kinds of logic. The query is executed and the single row in the result is fetched. Finally, the addstr() curses method is used to print the output in the desired location (upper left corner in this case).

When you execute the function, you will get a result like in the following screen shot:

Using MySQL Shell as a clock
Using MySQL Shell as a clock

This is all fine, but using MySQL Shell to show a clock is hardly the most interesting use of MySQL Shell. Let’s look at an example that is more usable in the real World.

MySQL iotop

If you are a Linux user, you may know the iotop utility which allows you to monitor the I/O activity in a similar way to what the top command does CPU and memory usage for processes. Let’s implement a basic MySQL my_iotop module with the by_file_by_bytes function that displays the N (default 10) MySQL files that have had the most I/O, refreshes with a specified interval (default 10 seconds), and continues until you hit the q key.

This may sound like a complex task, however most of the steps are the same as in the previous example. The top N files with respect to I/O can be found using the global_io_global_by_file_by_bytes view from the sys schema. This sorts by the total number of bytes read and written for the file in descending order. So, only a simple single view query is needed. For this example to avoid the output handling to be too complex, fixed width columns will be used and file paths longer than 50 characters are truncated.

The only thing that really is required that was not part of the previous example is to add the limit to the number of files to include and to detect when q is entered. The limit is however easy to add when using the select() CRUD method.

Another thing that is worth doing is to include the feature in an external module. This makes it easier to reuse. This requires you to add the directory where you will save your modules to the ~/.mysqlsh/mysqlshrc.py file, for example:

import sys
sys.path.append("/home/jesper/.mysqlsh/lib")

See also my previous blog on using external modules for more information.

In this example the file with the source code is called my_iotop.py stored in the directory added to the mysqlshrc.py file.

Warning

The following code is devoid of error handling. If you intend to use this in production, please validate the input and check for errors.

The complete source is:

import curses
from datetime import datetime
from time import sleep

def global_by_file_by_bytes(session, max_files=10, delay=10):
    # Define the output format
    fmt_header = "| {0:50s} | {1:12s} | {2:13s} | {3:13s} | {4:12s} " \
               + "| {5:13s} | {6:13s} | {7:13s} | {8:8s} |"
    header = fmt_header.format("File", "# Reads", "Bytes Read", "Avg. Read",
                               "# Writes", "Bytes Write", "Avg. Write",
                               "Bytes Total", "Write %")
    bar = "+" + "-" * 52 + "+" + "-" * 14 + "+" + "-" * 15 + "+" + "-" * 15 \
        + "+" + "-" * 14 + "+" + "-" * 15 + "+" + "-" * 15 + "+" + "-" * 15 \
        + "+" + "-" * 10 + "+"
    fmt_row = "| {0:50.50s} | {1:12d} | {2:13s} | {3:13s} | {4:12d} " \
            + "| {5:13s} | {6:13s} | {7:13s} | {8:8s} |"
    
    # Setup curses
    stdscr = curses.initscr()
    curses.start_color()
    curses.init_pair(1, curses.COLOR_RED, curses.COLOR_WHITE)
    curses.noecho()
    curses.cbreak()
    # Listing for 1/10th of second at a time
    curses.halfdelay(1)
    stdscr.keypad(True)

    # Define the query
    sys_schema = session.get_schema("sys")
    table = sys_schema.get_table("io_global_by_file_by_bytes")
    query = table.select().limit(max_files)
    
    # Clear screen
    stdscr.clear()
    
    # Run the query and generate the report
    keep_running = True
    while keep_running:
        time = datetime.now()
        result = query.execute()

        stdscr.addstr(0, 0, time.strftime('%A %-d %B %H:%M:%S'), 
                      curses.color_pair(1))
        stdscr.addstr(2, 0, bar)
        stdscr.addstr(3, 0, header)
        stdscr.addstr(4, 0, bar)

        # Print the rows in the result
        line = 5
        for row in result.fetch_all():
            stdscr.addstr(line, 0, fmt_row.format(*row))
            line = line + 1

        stdscr.addstr(line, 0, bar)
        stdscr.refresh()

        # Wait until delay seconds have passed while listening for the q key
        while (datetime.now() - time).total_seconds() < delay:
            c = stdscr.getch()
            if c == ord("q"):
                keep_running = False
                break

    # Reset the cures behaviour and finish
    curses.nocbreak()
    stdscr.keypad(False)
    curses.echo()
    curses.endwin()

The example are using a few more of the curses features which I will not go into detail with. I will recommend you to read the Python documentation and the Curses Programming with Python tutorial, if you are interested in learning more about using curses.

You start the report by calling the global_by_file_by_bytes() function. The session for the connection is required as an argument. Optional arguments are the delay between each iteration (delay) and the maximum number of files to include in the report (max_files). An example using a delay of 5 seconds is:

mysql-py> import my_iotop
mysql-py> my_iotop.global_by_file_by_bytes(session, delay=5)

While the implementation shown here is quite rough in its edges, it does show the potential. And remember you have the full Python language available for manipulating the data. Click on the video below to see an example of the report.

Slides and Workbooks From Oracle OpenWorld & CodeOne

First of all, thanks to everyone who attended my sessions at the recent Oracle OpenWorld and Code One in San Francisco. It was a great privilege to be allowed to make a number of presentations.

All of the workbooks and scripts from the hands-on labs (HOL) and the slides from the talks have been made available at OpenWorld’s and Code One’s session catalogues. You can download the files by using the OpenWorld catalog searching for my name or the session number (see below). Click on the download icon for each of the presentations you are interested in.

Click on the download link in the Oracle OpenWorld session catalog to download the presentations.
Click on the download icon in the Oracle OpenWorld session catalog to download the presentations.

For the hands-on labs the downloadable file includes the workbook as well as the scripts related to the exercises. The workbook contains the instructions for setting up the system used for the exercises as well as the exercises themselves and some additionaly background information. For the talks, the download consists of a PDF version of the slides.

The three hands-on labs and three talks I had were:

  • DEV5957: Develop Python Applications with MySQL Connector/Python
    This covered MySQL Connector/Python in general from installation to best practices. The talk focused on using the connector with SQL tables using both SQL statements and NoSQL CRUD methods. If you are interested in how I installed MySQL Connector/Python on iPad (the screen shot on in the right hand side of slide showing the pip command), see my previous blog about installing MySQL Connector/Python in Pythonista 3.
  • DEV5959: Python and the MySQL Document Store
    This covered how to use MySQL Connector/Python (and a little of MySQL Shell in Python mode) with the MySQL JSON Document Store using the NoSQL API (the X DevAPI).
  • HOL1703: A Practical Introduction to the MySQL Document Store
    This hands-on lab used the MySQL Shell in Python mode to use the MySQL Document Store including an introduction to the CRUD methods. The lab also includes a comparison of implementing the same X DevAPI program using Python, Node.js, and Java.
  • HOL1706: Developing Modern Applications with the MySQL Document Store and NodeJS
    This lab is similar to HOL1703 except it mainly uses Node.js programs to use the MySQL Document Store.
  • HOL2986: Using MySQL Common Table Expressions and Window Functions
    An introduction to the new MySQL 8.0 query features: common table expressions (CTEs) and the window functions that can be used for analytic queries.
  • THT6703: NoSQL + SQL = MySQL
    A brief introduction to MySQL, MySQL 8, and how you can use it both as a NoSQL document store and a traditional SQL database.

Enjoy.

MySQL Shell 8.0.13 Prompt: Now with New Line Support

I have already blogged a couple of times about the MySQL Shell prompt. In the first blog, I wrote about how in general to configure it, and in the second blog, I showed how to install the necessary fonts to use the prompt with the Awesome and Powerline fonts.

In this blog, I will show a new feature of MySQL Shell 8.0.13 which adds support to have a line break in the prompt and still get multi line statements align correctly. I will first discuss why you may want to use the new feature, then go through the new templates using this feature, and finally show my current favourite prompt.

Why Use a New Line in the Prompt?

You may ask why this feature is needed? The MySQL Shell prompt has support for showing a lot of information. While this is very nice, it also makes the prompt rather long. When you then have your query/statement, it requires a fair amount of real estate. Adding a new line in the prompt allows you to keep all the great information while still limiting the overall with of the prompt. The following figure shows a comparison of the single line prompt with a multi line prompt:

MySQL Shell 8.0.13: Comparing single and multi line prompts
MySQL Shell 8.0.13: Comparing single and multi line prompts

Note that in the example, the multi line prompt includes the MySQL Server version which is not included in the single line prompt. It can of course be debated which of the two versions is preferable, but that is the beauty of MySQL Shell: you can choose the prompt that you prefer.

Now, lets look at the new templates in MySQL Shell 8.0.13.

New Prompt Templates with Line Breaks

There are three new prompt templates in the MySQL Shell installation. The prompt templates are located in /usr/share/mysqlsh/prompt/ on Linux and C:\Program Files\MySQL\MySQL Shell 8.0\share\mysqlsh\prompt on Microsoft Windows. You may also want to review the blog Configuring the MySQL Shell Prompt for more about the prompt templates. The three new prompt templates are:

  • prompt_dbl_256.json: The prompt uses 256 indexed colours. This is a two line version of the default prompt both on Oracle Linux 7 and Microsoft Windows 10.
  • prompt_dbl_256pl.json: Similar to the prompt_dbl_256.json prompt but using the Powerline font.
  • prompt_dbl_256pl+aw.json: Additionally adding the Awesome font symbols.

If you compare the prompt_dbl_256.json template with the default prompt_256.json template, the difference is:

The difference between the prompt_256.json and prompt_dbl_256.json templates.
The difference between the prompt_256.json and prompt_dbl_256.json templates.

So a new line is created using the "break": true syntax. That’s it. The other change in the screen shot is just to make the second line of the prompt be slightly indented.

This is great – if you want a new line before the end of the prompt, you can just use one of the new templates. However, personally I prefer to have my prompt slightly different.

My Favourite Prompt

My favourite prompt uses both the Awesome and Powerline fonts as well as adding a new line. I prefer to have the line break just before the language mode (the templates have the break after the mode). Additionally, I add the MySQL Server version (the value of the version system variable), and I use the Powerline separator on each new line.

The complete prompt definition is:

{
  "desc" : "256/24bit color terminal theme with MySQL prefix, default schema, host:port, ssl, + indicator for X protocol, MySQL version, new line, active mode. Requires Powerline patched font with awesome symbols (e.g. awesome-terminal-fonts)",
  "classes" : {
    "SQL" : {
      "fg" : "15",
      "bg" : "166"
    },
    "JS" : {
      "fg" : "0",
      "bg" : "221"
    },
    "Py" : {
      "fg" : "15",
      "bg" : "25"
    },
    "schema" : {
      "text": "\ue894 %schema%"
    },
    "noschema" : {
      "text": ""
    },
    "disconnected": {
    },
    "hostx" : {
      "text": "\ue895 %transport%+"
    },
    "hostc" : {
      "text": "\ue895 %transport%"
    },
    "SSLhostx" : {
      "text": "\ue895 %transport%+ \ue0a2"
    },
    "SSLhostc" : {
      "text": "\ue895 %transport% \ue0a2"
    },

    "production" : {
      "text": " PRODUCTION ",
      "bg": "red",
      "fg": "white"
    },

    "version": {
      "text": " %sysvar:version% ",
      "bg": "blue",
      "fg": 15
    }
  },
  "variables" : {
    "is_production": {
        "match" : {
            "pattern": "*;%host%;*",
            "value": ";%env:PRODUCTION_SERVERS%;"
        },
        "if_true" : "production",
        "if_false" : ""
    },
    "transport" : {
        "match" : {
          "pattern": "%socket%",
          "value": ""
        },
        "if_true": "%host%:%port%",
        "if_false": "localhost"
    }
  },
  "symbols" : {
    "separator" : "\ue0b0",
    "separator2" : "\ue0b1",
    "ellipsis" : "\u2026"
  },
  "prompt" : {
    "text" : "\ue0b0 ",
    "cont_text" : "  \ue0b0 ",
    "bg": "0"
  },
  "segments": [
    {
      "classes": ["disconnected%host%", "%is_production%"]
    },
    {
      "text": " My",
      "bg": 254,
      "fg": 23
    },
    {
      "separator": "",
      "text": "SQL ",
      "bg": 254,
      "fg": 166
    },
    {
      "classes": ["disconnected%host%", "%ssl%host%session%"],
      "shrink": "truncate_on_dot",
      "bg": 237,
      "fg": 15,
      "weight": 10,
      "padding" : 1
    },
    {
      "classes": ["disconnected%host%", "version"]
    },
    {
      "classes": ["noschema%schema%", "schema"],
      "bg": 242,
      "fg": 15,
      "shrink": "ellipsize",
      "weight": -1,
      "padding" : 1
    },
    {
      "text": " ",
      "bg": 0
    },
    {
      "break": true
    },
    {
      "classes": ["%Mode%"],
      "text": "%Mode%",
      "padding" : 1
    },
  ]
}

Examples of the resulting prompt are:

Example of the new prompt feature in MySQL Shell 8.0.13 with multi line queries.
Example of the new prompt feature in MySQL Shell 8.0.13 with multi line queries.

Enjoy.

Awesome MySQL Shell Prompt

A month ago, I wrote a blog on how you can configure the MySQL Shell prompt to suit your needs.  One thing I did not go into details with are the prompt templates prompt_256pl.json and prompt_256pl+aw.json. Common for both of these templates is that they require custom fonts to work.

In the two file names, pl stands for the PowerLine fonts and aw for the Awesome font. These fonts add symbols that are useful to create rich prompts, not only for MySQL Shell, but also for Bash, zsh, vim, etc. The symbols include an angle separator, a lock (that in MySQL Shell is used to symbolize an TLS/SSL connection), and so on.

The MySQL Shell Using Both the Powerline Fonts and the Awesome Terminal Font
If you want a MySQL Shell prompt like this, then read on.

This blog will show you how to install the necessary fonts to get an awesome MySQL Shell prompt on Oracle Linux 7, Red Hat Enterprise Linux (RHEL) 7, and CentOS 7. For other Linux distributions, the steps are the same or similar.

Specifying the Prompt Template

Before diving into installing the necessary fonts, let’s recall from the previous blog, how you choose how the prompt should look. Two ways to specify the prompt are:

  • Set the MYSQLSH_PROMPT_THEME environment variable to the path to the file with the required template. For the prompt_256pl+aw.json template using the el7 MySQL Shell RPM, the full path is /usr/share/mysqlsh/prompt/prompt_256pl+aw.json. This method is useful while testing.
  • Copy the prompt_256pl+aw.json file to ~/.mysqlsh/prompt.json. This way MySQL Shell will automatically pick up the new prompt definition. This is very useful when you have decided on your preferred prompt.

Awesome Terminal Fonts

If you want to use the prompt_256pl+aw.json template, then you will need to install the awesome font. One option is to use the patching-strategy branch of gabrielelana’s awesome-terminal-fonts project on GitHub. The steps to install the required font for the user are:

shell$ git clone https://github.com/gabrielelana/awesome-terminal-fonts.git
Cloning into 'awesome-terminal-fonts'...
remote: Counting objects: 329, done.
remote: Total 329 (delta 0), reused 0 (delta 0), pack-reused 329
Receiving objects: 100% (329/329), 2.77 MiB | 1.12 MiB/s, done.
Resolving deltas: 100% (186/186), done.

shell$ cd awesome-terminal-fonts

shell$ git checkout patching-strategy
Branch patching-strategy set up to track remote branch patching-strategy from origin.
Switched to a new branch 'patching-strategy'

shell$ mkdir -p ~/.local/share/fonts/

shell$ cp patched/SourceCodePro+Powerline+Awesome+Regular.* ~/.local/share/fonts/

shell$ fc-cache -fv ~/.local/share/fonts/
/home/myuser/.local/share/fonts: caching, new cache contents: 1 fonts, 0 dirs
/home/myuser/.local/share/fonts: caching, new cache contents: 1 fonts, 0 dirs
/var/cache/fontconfig: not cleaning unwritable cache directory
/home/myuser/.cache/fontconfig: cleaning cache directory
/home/myuser/.fontconfig: not cleaning non-existent cache directory
fc-cache: succeeded

This installs just the font required for MySQL Shell. You can however choose to install all of the fonts if you like.

It is necessary to restart the desktop environment for the fonts to be available. The resulting prompt (when logged in a default schema has been set) looks like:

The MySQL Prompt with the Awesome Terminal Font Installed
The MySQL Prompt with the Awesome Terminal Font Installed

Personally I am not entirely keen on how the separator looks, so I install another font to replace the separator.

Powerline Font

The Powerline fonts serve a similar purpose as the awesome font, but they are missing some of the symbols used by the prompt_256pl+aw.json template. It does however include the separator used by the template, and I personally prefer it over the Awesome Terminal Font separator. If you choose the prompt_256pl.json template, you can just install the Powerline Fonts and skip the Awesome Terminal Font.

The installation instructions in the manual are quite good. However, they assume you want to use the fonts for more than just MySQL Shell. For MySQL Shell the following steps are enough:

shell$ wget --directory-prefix="${HOME}/.local/share/fonts" \
            https://github.com/powerline/powerline/raw/develop/font/PowerlineSymbols.otf
...
2018-09-03 20:40:31 (6.51 MB/s) - ‘/home/myuser/.local/share/fonts/PowerlineSymbols.otf’ saved [2264/2264]

shell$ fc-cache -vf ~/.local/share/fonts/
/home/myuser/.local/share/fonts: caching, new cache contents: 2 fonts, 0 dirs
/home/myuser/.local/share/fonts: caching, new cache contents: 2 fonts, 0 dirs
/var/cache/fontconfig: not cleaning unwritable cache directory
/home/myuser/.cache/fontconfig: cleaning cache directory
/home/myuser/.fontconfig: not cleaning non-existent cache directory
fc-cache: succeeded

shell$ wget --directory-prefix="${HOME}/.config/fontconfig/conf.d" \
            https://github.com/powerline/powerline/raw/develop/font/10-powerline-symbols.conf
...
2018-09-03 20:41:45 (5.87 MB/s) - ‘/home/myuser/.config/fontconfig/conf.d/10-powerline-symbols.conf’ saved [2713/2713]

After restarting the desktop environment, the prompt looks like this:

The MySQL Shell Using Both the Powerline Fonts and the Awesome Terminal Font
The MySQL Shell Using Both the Powerline Fonts and the Awesome Terminal Font

Fantasque Awesome Powerline

A third option is the Fantasque Awesome Powerline fonts which as the name suggest include all the fonts needed for the prompt_256pl+aw.json template.

Thanks

Thanks to Lefred for this suggestion.

The steps to install the Fantasque Awesome Powerline fonts are familiar by now. The fonts can be downloaded using the ztomer’s GitHub repository and installed using the fc-cache command:

shell$ git clone https://github.com/ztomer/fantasque_awesome_powerline.git
Cloning into 'fantasque_awesome_powerline'...
remote: Counting objects: 10, done.
remote: Total 10 (delta 0), reused 0 (delta 0), pack-reused 9
Unpacking objects: 100% (10/10), done.

shell$ cd fantasque_awesome_powerline

shell$ mkdir -p ~/.local/share/fonts

shell$ cp *.ttf ~/.local/share/fonts/

shell$ fc-cache -fv ~/.local/share/fonts/
/home/myuser/.local/share/fonts: caching, new cache contents: 3 fonts, 0 dirs
/home/myuser/.local/share/fonts: caching, new cache contents: 3 fonts, 0 dirs
/var/cache/fontconfig: not cleaning unwritable cache directory
/home/myuser/.cache/fontconfig: cleaning cache directory
/home/myuser/.fontconfig: not cleaning non-existent cache directory
fc-cache: succeeded

Then restart and the font is ready. The resulting prompt is very similar to the previous and is arguable simpler to install as it is just one source:

MySQL Shell Prompt with the Fantasque Powerline Awesome Fonts
MySQL Shell Prompt with the Fantasque Powerline Awesome Fonts

You can of course also choose to play with the other symbols in the Powerline and Awesome Terminal fonts and make your own custom MySQL Shell prompt configuration file. Whichever is your preferred prompt, have fun playing with it.

MySQL Shell: Built-In Help

It can be hard to recall all the details of how a program and API work. The usual way to handle that is to look at the manual or a book. Another – and in my opinion – nice way is to have built-in help, so you can find the information without changing between the program and browser. This blog discuss how to obtain help when you use MySQL Shell.

Information

MySQL Shell is a client that allows you to execute queries and manage MySQL through SQL commands and JavaScript and Python code. It is a second generation command-line client with additional WebOps support. If you have not installed MySQL Shell yet, then you can download it from MySQL’s community downloads, Patches & Updates in My Oracle Support (MOS) (for customers), or Oracle Software Delivery Cloud (for trial downloads). You can also install it through MySQL Installer for Microsoft Windows.

MySQL Shell: Get help for a table object
MySQL Shell: Get help for a table object

MySQL Shell has a very nice and comprehensive built-in help. There is of course the help output produced using the --help option if you invoke the shell from the command line:

PS: MySQL> mysqlsh --help
MySQL Shell 8.0.12

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: mysqlsh [OPTIONS] [URI]
       mysqlsh [OPTIONS] [URI] -f <path> [script args...]
       mysqlsh [OPTIONS] [URI] --dba [command]
       mysqlsh [OPTIONS] [URI] --cluster

  -?, --help                    Display this help and exit.
  -e, --execute=<cmd>           Execute command and quit.
  -f, --file=file               Process file.
  --uri=value                   Connect to Uniform Resource Identifier. Format:
                                [user[:pass]@]host[:port][/db]
  -h, --host=name               Connect to host.
  -P, --port=#                  Port number to use for connection.
...

However, this help is not what makes MySQL Shell special. It is the help that you can see from within the shell when working in JavaScript or Python that is the worth some extra attention. There is both support for general help and obtaining help through objects.

General Help

The first layer of help is what is also known from the old mysql command-line client. A command existing of a backslash and a ?, h, or help (\?, \h or \help) will show information about the general usage of MySQL Shell:

mysql-py> \?
The Shell Help is organized in categories and topics. To get help for a
specific category or topic use: \? <pattern>

The <pattern> argument should be the name of a category or a topic.

The pattern is a filter to identify topics for which help is required, it can
use the following wildcards:

- ? matches any single charecter.
- * matches any character sequence.

The following are the main help categories:

 - AdminAPI       Introduces to the dba global object and the InnoDB cluster
                  administration API.
 - Shell Commands Provides details about the available built-in shell commands.
 - ShellAPI       Contains information about the shell and util global objects
                  as well as the mysql module that enables executing SQL on
                  MySQL Servers.
 - SQL Syntax     Entry point to retrieve syntax help on SQL statements.
 - X DevAPI       Details the mysqlx module as well as the capabilities of the
                  X DevAPI which enable working with MySQL as a Document Store

The available topics include:

- The dba global object and the classes available at the AdminAPI.
- The mysqlx module and the classes available at the X DevAPI.
- The mysql module and the global objects and classes available at the
  ShellAPI.
- The functions and properties of the classes exposed by the APIs.
- The available shell commands.
- Any word that is part of an SQL statement.

SHELL COMMANDS

The shell commands allow executing specific operations including updating the
shell configuration.

The following shell commands are available:

 - \                   Start multi-line input when in SQL mode.
 - \connect    (\c)    Connects the shell to a MySQL server and assigns the
                       global session.
 - \exit               Exits the MySQL Shell, same as \quit.
 - \help       (\?,\h) Prints help information about a specific topic.
 - \history            View and edit command line history.
 - \js                 Switches to JavaScript processing mode.
 - \nowarnings (\w)    Don't show warnings after every statement.
 - \option             Allows working with the available shell options.
 - \py                 Switches to Python processing mode.
 - \quit       (\q)    Exits the MySQL Shell.
 - \reconnect          Reconnects the global session.
 - \rehash             Refresh the autocompletion cache.
 - \source     (\.)    Loads and executes a script from a file.
 - \sql                Switches to SQL processing mode.
 - \status     (\s)    Print information about the current global session.
 - \use        (\u)    Sets the active schema.
 - \warnings   (\W)    Show warnings after every statement.

GLOBAL OBJEECTS

The following modules and objects are ready for use when the shell starts:

 - dba    Used for InnoDB cluster administration.
 - mysql  Support for connecting to MySQL servers using the classic MySQL
          protocol.
 - mysqlx Used to work with X Protocol sessions using the MySQL X DevAPI.
 - shell  Gives access to general purpose functions and properties.
 - util   Global object that groups miscellaneous tools like upgrade checker.

For additional information on these global objects use: <object>.help()

EXAMPLES
\? AdminAPI
      Displays information about the AdminAPI.

\? \connect
      Displays usage details for the \connect command.

\? check_instance_configuration
      Displays usage details for the dba.check_instance_configuration function.

\? sql syntax
      Displays the main SQL help categories.

This shows which commands and global objects are available. But there is more: you can also get help about the usage of MySQL Shell such as how to use the Admin API (for MySQL InnoDB Cluster), how to connect, or the SQL syntax. The search for relevant help topics are context sensitive, for example searching for the word select return different results depending on the mode and whether you are connected:

  • In Python or JavaScript mode without a connection, it is noted that information was found in the mysqlx.Table.select and mysqlx.TableSelect.select categories.
  • In Python or JavaScript mode with a connection, the SELECT SQL statement is included as a category.
  • In SQL mode the actual help text for the SELECT SQL statement is returned (requires a connection).

For example, to get help about the select method of a table object:

mysql-py> \? mysqlx.Table.select
NAME
      select - Creates a TableSelect object to retrieve rows from the table.

SYNTAX
      Table.select(...)
           [.where([expression])]
           [.group_by(...)[.having(condition)]]
           [.order_by(...)]
           [.limit(numberOfRows)[.offset(numberOfRows)]]
           [.lock_shared([lockContention])]
           [.lock_exclusive([lockContention])]
           [.bind(name, value)]
           [.execute()]

DESCRIPTION
      This function creates a TableSelect object which is a record selection
      handler.

      This handler will retrieve all the columns for each included record.

      The TableSelect class has several functions that allow specifying what
      records should be retrieved from the table, if a searchCondition was
      specified, it will be set on the handler.

      The selection will be returned when the execute function is called on the
      handler.
...

To get help for the SELECT SQL statement:

mysql-py> \? SQL Syntax/SELECT
Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]
    [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] 
      | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables, and
can include UNION statements and subqueries. See [HELP UNION], and
http://dev.mysql.com/doc/refman/8.0/en/subqueries.html. A SELECT
statement can start with a WITH clause to define common table
expressions accessible within the SELECT. See
http://dev.mysql.com/doc/refman/8.0/en/with.html.

...

URL: http://dev.mysql.com/doc/refman/8.0/en/select.html


mysql-py> \sql
Switching to SQL mode... Commands end with ;

mysql-sql> \? select
Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]
    [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] 
      | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables, and
can include UNION statements and subqueries. See [HELP UNION], and
http://dev.mysql.com/doc/refman/8.0/en/subqueries.html. A SELECT
statement can start with a WITH clause to define common table
expressions accessible within the SELECT. See
http://dev.mysql.com/doc/refman/8.0/en/with.html.
...

Note here how it is possible to get the help for the SELECT statement both from the Python (and JavaScript) as well as SQL modes, but the search term is different.

Tip

To get information about SQL statements, you must be connected to a MySQL instance.

When you use the JavaScript or Python modes there is another way to get  help based on your object. Let’s look at that.

Object Based Help

If you are coding in MySQL Shell using JavaScript or Python it may happen you need a hint how to use a given object, for example a table object. You can use the method described in the previous section to get help by searching for mysqlx.Table, however, you can also access the help directly from the object.

All of the X DevAPI objects in MySQL Shell has a help() method that you can invoke to have help returned for the object. For example, if you have an object named city for the city table in the world schema, then calling city.help() returns information about table object:

mysql-py> \use world
Default schema `world` accessible through db.

mysql-py> city = db.get_table('city')
mysql-py> city.help()
NAME
      Table - Represents a Table on an Schema, retrieved with a session created
              using mysqlx module.

DESCRIPTION
      Represents a Table on an Schema, retrieved with a session created using
      mysqlx module.

PROPERTIES
      name
            The name of this database object.

      schema
            The Schema object of this database object.

      session
            The Session object of this database object.

FUNCTIONS
      delete()
            Creates a record deletion handler.

      exists_in_database()
            Verifies if this object exists in the database.

      get_name()
            Returns the name of this database object.

      get_schema()
            Returns the Schema object of this database object.

      get_session()
            Returns the Session object of this database object.

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

      insert(...)
            Creates TableInsert object to insert new records into the table.

      is_view()
            Indicates whether this Table object represents a View on the
            database.

      select(...)
            Creates a TableSelect object to retrieve rows from the table.

      update()
            Creates a record update handler.

As you can see, the built-in help in MySQL Shell is a powerful resource. Make sure you use it.

MySQL Shell: Using External Python Modules

MySQL Shell is a great tool for working with MySQL. One of the features that make it stand out compared to the traditional mysql command-line client is the support for JavaScript and Python in addition to SQL statements. This allows you to write code you otherwise would have had to write outside the client. I showed a simple example of this in my post about the instant ALTER TABLE feature in MySQL 8.0.12 where a Python loop was used to populate a table with 1 million rows This blog will look further into the use of Python and more specifically external modules.

Using a customer table_tools module in MySQL Shell.
Using a customer table_tools module in MySQL Shell.

Using Standard Modules

Aforementioned loop that was used to populate a test table also showed another feature of MySQL Shell: You can use the standard Python modules just as you would do in any other Python script. For example, if you need to create UUIDs you can use the uuid module:

mysql-py> import uuid
mysql-py> print(uuid.uuid1().hex)
9e8ef45ea12911e8a8a6b0359feab2bb

This on its own is great, but what about your own modules? Sure, that is supported as well. Before showing how you can access your own modules, let’s create a simple module to use as an example.

Example Module

For the purpose of this blog, the following code should be saved in the file table_tools.py. You can save it in whatever directory you keep your Python libraries. The code is:

def describe(table):
    fmt = "{0:<11}   {1:<8}    {2:<4}   {3:<3}    {4:<9}    {5:<14}"

    # Create query against information_schema.COLUMNS
    session = table.get_session()
    i_s = session.get_schema("information_schema")
    i_s_columns = i_s.get_table("COLUMNS")

    query = i_s_columns.select(
        "COLUMN_NAME AS Field",
        "COLUMN_TYPE AS Type",
        "IS_NULLABLE AS `Null`",
        "COLUMN_KEY AS Key",
        "COLUMN_DEFAULT AS Default",
        "EXTRA AS Extra"
    )
    query = query.where("TABLE_SCHEMA = :schema AND TABLE_NAME = :table")
    query = query.order_by("ORDINAL_POSITION")

    query = query.bind("schema", table.schema.name)
    query = query.bind("table", table.name)

    result = query.execute()

    # Print the column names
    column_names = [column.column_name for column in result.get_columns()]
    print(fmt.format(*column_names))
    print("-"*67)

    for row in result.fetch_all():
        print(fmt.format(*row))

The describe function takes a Table object from which it works backwards to get the session object. It then queries the information_schema.COLUMNS view to get the same information about the table as the DESC SQL command. Both the table and schema name can be found through the table object. Finally, the information is printed.

The example is overly simplified for general usage as it does not change the width of the output based on the length of the data, and there is no error handling whatsoever. However, this is on purpose to focus on the usage of the code from within MySQL Shell rather than on the code.

Note

The same code works in a MySQL Connector/Python script except that the rows are returned as mysqlx.result.Row objects. So, the loop printing the rows look a little different:

for row in result.fetch_all():
values = [row[name] or "" for name in column_names]
print(fmt.format(*values))

With the function ready, it is time to look at how you can import it into MySQL Shell.

Importing Modules Into MySQL Shell

In order to be able to import a module into MySQL Shell, it must be in the path searched by Python. If you have saved table_tools.py into a location already searched, then that is it. However, a likely more common scenario is that you have saved the file in a custom location. In that case, you need to tell Python where to look for the files.

You modify the search path in MySQL Shell just as you would in a regular Python program. If you for example have saved the file to D:\MySQL\Shell\Python, then you can add that to the path using the following code:

import sys
sys.path.append("D:\MySQL\Shell\Python")

If this is something you need as a one off, then it is fine just to modify the path directly in MySQL Shell. However, if you are working on some utilities that you want to reuse, it becomes tedious. MySQL Shell has support for configuration files where commands can be executed. The one for Python is named mysqlshrc.py (and mysqlshrc.js for JavaScript).

MySQL Shell searches for the mysqlshrc.py file in four locations including global locations as well as user specific locations. You can see the full list and the search order in the MySQL Shell User Guide. The user specific file is %APPDATA%\MySQL\mysqlsh\mysqlshrc.py on Microsoft Windows and $HOME/.mysqlsh/mysqlshrc.py on Linux and macOS.

You can do more than just changing the search path in the mysqlshrc.py file. However, for this example nothing else is needed.

Using the Module

Now that MySQL Shell has been set up to search in the path where your module is saved, you can use it in MySQL Shell. For example to get the description of the world.city table, you can use the following commands:

mysql-py> import table_tools
mysql-py> \use world
Default schema `world` accessible through db.

mysql-py> table_tools.describe(db.city)
Field         Type        Null   Key    Default      Extra
-------------------------------------------------------------------
ID            int(11)     NO     PRI    None         auto_increment
Name          char(35)    NO
CountryCode   char(3)     NO     MUL
District      char(20)    NO
Population    int(11)     NO            0

The \use world command sets the default schema to the world database. As a side effect, it also makes the tables in the world database available as properties of the db object. So, it possible to pass an object for the world.city table as db.city to table_tools.describe() function.

That is it. Now it is your turn to explore the possibilities that have been opened with MySQL Shell.

Configuring the MySQL Shell Prompt

With the introduction of MySQL Shell 8.0, the second major version of the new command-line tool for MySQL, a new and rich featured prompt was introduced. Unlike the prompt of the traditional mysql command-line client, it does not just say mysql> by default. Instead it comes in a colour coded spectacle.

The default prompt is great, but for one reason or another it may be that you want to change the prompt. Before getting to that, let’s take a look at the default prompt, so the starting point is clear.

The Default Prompt

An example of the default prompt can be seen in the screen shot below. As you can see, there are several parts to the prompt, each carrying its information.

MySQL Shell with the default font.
MySQL Shell with the default font.

There are six parts. From left to right, they are:

  • Status: Whether it is a production system or whether the connection is lost. This part is not included in the above screen shot.
  • MySQL: Just a reminder that you are working with a MySQL database.
  • Connection: Which host you are connected to (localhost), which port (33060 – to the X protocol port), and that SSL is being used.
  • Schema: The current default schema.
  • Mode: Whether you are using JavaScript (JS), Python (Py), or SQL (SQL) to enter commands.
  • End: As per tradition, the prompt ends with a >.

Depending on your current status one or more of the parts may be missing. For example, the configuration options will only be present, when you have an active connection to a MySQL Server instance.

The prompt works well on a black background and thus brightly coloured text as in the screen shot, but for some other background and text colours, it is not so – or you may simply want different colours to signify which whether you are connected to a development or production system. You may also find the prompt too verbose, if you are recording a video or writing training material. So, let’s move on and find out how the prompt is configured.

The Prompt Configuration

Since the prompt is not just a simple string, it is also somewhat more complex to configure it than just setting an option. The configuration is done in a JSON object stored in a file named prompt.json (by default – you can change this – more about that later).

The location of prompt.json depends on your operating system:

  • Linux and macOS: ~/.mysqlsh/prompt.json – that is in the .mysqlsh directory in the user’s home directory.
  • Microsoft Windows: %AppData%\MySQL\mysqlsh\prompt.json – that is in AppData\Roaming\MySQL\mysqlsh directory from the user’s home directory.

If the file does not exist, MySQL Shell falls back on a system default. For example, on Oracle Linux 7 installation, the file /usr/share/mysqlsh/prompt/prompt_256.json is used. This is also the template that is copied to %AppData%\MySQL\mysqlsh\prompt.json on Microsoft Windows 10 installation.

The MySQL Shell installation includes several templates that you can choose from. These are:

  • prompt_16.json: A coloured prompt limited to use 16/8 color ANSI colours and attributes.
  • prompt_256.json: The prompt uses 256 indexed colours. This is the one that are used by default both on Oracle Linux 7 and Microsoft Windows 10.
  • prompt_256inv.json: Similar to prompt_256.json, but with an “invisible” background colour (it just uses the same as for the terminal) and with different foreground colours.
  • prompt_256pl.json: Same as prompt_256.json but with extra symbols. This Powerline patched font such as the one that is installed with the Powerline project. This will add a padlock with the prompt when you use SSL to connect to MySQL and use “arrow” separators.
  • prompt_256pl+aw.json: Same as prompt_256pl.json but with “awesome symbols”. This additionally requires the awesome symbols to be included in the Powerline font.
  • prompt_classic.json: This is a very basic prompt that just shows mysql-js>, mysql-py>, or mysql-sql> based on the mode in use.
  • prompt_nocolor.json: Gives the full prompt information, but completely without colours. An example of a prompt is: MySQL [localhost+ ssl/world] JS>

These are templates that you can use as is or modify to suite yours needs and preferences. One way to pick a theme is to copy the template file into the location of your user’s prompt definition. The templates can be found in the prompt directory of the installation, for example:

  • Oracle Linux 7 RPM: /usr/share/mysqlsh/prompt/
  • Microsoft Windows: C:\Program Files\MySQL\MySQL Shell 8.0\share\mysqlsh\prompt

Another option is to define the MYSQLSH_PROMPT_THEME environment variable to point to the file you want to use. The value should be the full path to the file. This is particularly useful if you want to try the different template to see what works best for you. For example, to use the prompt_256inv.json template from the command prompt on Microsoft Windows:

C:\> set MYSQLSH_PROMPT_THEME=C:\Program Files\MySQL\MySQL Shell 8.0\share\mysqlsh\prompt\prompt_256inv.json

Which gives the prompt:

The prompt when using the prompt_256inv.json template.
The prompt when using the prompt_256inv.json template.

If none of the templates work for you, you can also dive in at the deep end of the pool and create your own configuration.

Creating Your Own Configuration

It is not completely trivial to create your own configuration, but if you use the template that is closest to the configuration you want as a starting point, it is not difficult either.

A good source of help to create the perfect prompt is also the README.prompt file that is located in the same directory as the template files. The README.prompt file contains the specification for the configuration.

Instead of going through the specification in details, let’s take a look at the prompt_256.json template and discuss some parts of it. Let’s start at the end of the file:

  "segments": [
    {
      "classes": ["disconnected%host%", "%is_production%"]
    },
    {
      "text": " My",
      "bg": 254,
      "fg": 23
    },
    {
      "separator": "",
      "text": "SQL ",
      "bg": 254,
      "fg": 166
    },
    {
      "classes": ["disconnected%host%", "%ssl%host%session%"],
      "shrink": "truncate_on_dot",
      "bg": 237,
      "fg": 15,
      "weight": 10,
      "padding" : 1
    },
    {
      "classes": ["noschema%schema%", "schema"],
      "bg": 242,
      "fg": 15,
      "shrink": "ellipsize",
      "weight": -1,
      "padding" : 1
    },
    {
      "classes": ["%Mode%"],
      "text": "%Mode%",
      "padding" : 1
    }
  ]
}

This is where the elements of the prompt is defined. There are a few things that is interesting to note here.

First, notice that there is an object with the classes disconnected%host% and %is_production%. The names inside the %s are variables defined in the same file or that comes from MySQL Shell itself (it has variables such as the host and port). For example, is_production is defined as:

  "variables" : {
    "is_production": {
        "match" : {
            "pattern": "*;%host%;*",
            "value": ";%env:PRODUCTION_SERVERS%;"
        },
        "if_true" : "production",
        "if_false" : ""
    },

So, a host is considered to be a production instance if it is included in the environment variable PRODUCTION_SERVERS. When there is a match, and additional element is inserted at the beginning of the prompt to make it clear, you are working on with a production system:

Connected to a production system.
Connected to a production system.

The second thing to note about the list of elements is that there are some special functions such as shrink which can be used to define how the text is kept relatively short. For example, the host uses truncate_on_dot, so only the part before the first dot in the hostname is displayed if the full hostname is too long. Alternatively ellipsize can be used to add … after the truncated value.

Third, the background and foreground colours are defined using the bg and fg elements respectively. This allows you to completely customize the prompt to your liking with respect to colours. The colour can be specified in one of the following ways:

  • By Name: There are a few colours that are known by name: black, red, green, yellow, blue, magenta, cyan, white.
  • By Index: A value between 0 and 255 (both inclusive) where 0 is black, 63 light blue, 127 magenta, 193 yellow, and 255 is white.
  • By RGB: Use a value in the #rrggbb format. Requires the terminal supports TrueColor colours.

Tip

If you want to do more than make a few tweaks to an existing template, read the README.prompt file to see the full specification including a list of supported attributes and built-in variables. These may change in the future as more features are added.

One group of built-in variables that deserve an example are the ones that in some way depend on the environment or the MySQL instance you are connected to. These are:

  • %env:varname%: This uses an environment variable. The way that it is determined whether you are connected to a production server is an example of how an environment variable
  • %sysvar:varname%: This uses the value of a global system variable from MySQL. That is, the value returned by SELECT @@global.varname.
  • %sessvar:varname%: Similar to the previous but using a session system variable.
  • %status:varname%: This uses the value of a global status variable from MySQL. That is, the value returned by SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'varname'.
  • %status:varname%: Similar to the previous, but using a session status variable.

If you for example want to include the MySQL version (of the instance you are connected to) in the prompt, you can add an element like:

    {
      "separator": "",
      "text": "%sysvar:version%",
      "bg": 250,
      "fg": 166
    },

The resulting prompt is:

Including the MySQL Server version in the prompt.
Including the MySQL Server version in the prompt.

What next? Now it is your turn to play with MySQL Shell. Enjoy.