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:

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:

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:

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().

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:

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:

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:

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:

Similar for the procedures:

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:

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:

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:

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.

 

One thought on “MySQL 8: Drop Several Stored Events, Procedures, or Functions

  1. Simon Mudd 3 December, 2018 / 22:36

    Thanks for the writeup on how to work around something which worked in MySQL 5.7. Manipulating system objects needs to be handled carefully but facilities to do that should be provided. It SHOULD be possible to do this sort of task on the database server itself. The process SHOULD be easy.

    Having to rely on external things to handle this is just broken in my opinion even if the MySQL Shell give the user more flexibility.

    Most other RDBMSes use internal system stored procedures to handle this, the implementation being (partly) hidden from the user, but providing a consistent interface even if internals change. This sounds like the perfect usage of the sys schema to handle things like this.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.