No more mysql.proc in MySQL 8.0

MySQL has thrown away the mysql.proc table for version 8.0 Development Releases.

The best explanation that I’ve seen is the one that Dmitry Lenev gave at a conference last October.

To summarize it: mysql.proc and its ilk are non-transactional, redundancy is bad, and MySQL can fix some known bugs by moving over to information_schema tables backed by InnoDB. Of course I approve for a separate reason: mysql.proc is non-standard and therefore it is a mistake.

On the other hand, programmers that have invested some time in using mysql.proc will have some trouble changing them to use information_schema.routines instead.

Table definition differences

I did a complex left join of the information_schema.columns for
mysql.proc (P) and for information_schema.routines (R) in MySQL 5.7, and saw this.

Remember the above chart is for MySQL version 5.7.
For MySQL 8.0.2 these column definitions have changed:
ROUTINE_SCHEMA: was varchar(64) utf8_general_ci, will be varchar(64) utf8_tolower_ci
ROUTINE_TYPE: was varchar(9) utf8_general_ci, will be enum
DATA_TYPE: was varchar(9), will be longtext
EXTERNAL_LANGUAGE: was varchar(64), will be binary(0)
SQL_DATA_ACCESS: was varchar(64), will be enum
SECURITY_TYPE: was varchar(7), will be enum
CREATED: was datetime, will be timestamp
LAST_ALTERED: was datetime, will be timestamp
DEFINER: was varchar(77) utf8_general_ci, will be varchar(93) utf8_bin
CHARACTER_SET_CLIENT: was varchar(32), will be varchar(64)
COLLATION_CONNECTION: was varchar(32), will be varchar(64)
DATABASE_COLLATION: was varchar(32), will be varchar(64)
… and more changes are possible.

I have included the dirt about column data type and collation so that it’s clear they are never exactly the same. This might affect applications that depend on exact size allocations and precise ordering. But usually it will make no difference to either programmers or end users.

What the chart shows is that mysql.proc.db corresponds to information_schema.routines.ROUTINE_SCHEMA, mysql.proc.name corresponds to information_schema.routines.ROUTINE_NAME, and so on. So if I had a simple SQL statement like

SELECT db, name FROM mysql.proc;

I could convert with ease to

SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM information_schema.routines;

(By the way I used name rather than specific_name because it’s in the primary key; the value is the same.)

However, three mysql.proc columns — param_list, returns, body — have no corresponding columns in information_schema.routines. Converting them will be more work.

param_list and returns

Let’s try

CREATE FUNCTION fx1(paramx1 INT, paramx2 DOUBLE) RETURNS CHAR(5) RETURN 'xxxxx';

Let’s look at it via mysql.proc (whenever I show mysql.proc I’m using MySQL 5.7):

SELECT param_list, returns FROM mysql.proc WHERE name='fx1';

Result:

+-------------+------------------------+
| param_list  | returns                |
+-------------+------------------------+
| paramx1 int | char(5) CHARSET latin1 |
+-------------+------------------------+

Now let’s look at it with

SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME, DTD_IDENTIFIER
FROM information_schema.routines
WHERE routine_name='fx1';

Result:

+-----------+--------------------------+--------------------+----------------+
| DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_SET_NAME | DTD_IDENTIFIER |
+-----------+--------------------------+--------------------+----------------+
| char      |                        5 | latin1             | char(5)        |
+-----------+--------------------------+--------------------+----------------+

This isn’t too bad — all we have to do, (with sql_mode=’pipes_as_concat’) is concatenate
DATA_TYPE || ‘(‘ || CHARACTER_MAXIMUM_LENGTH || ‘)’ || ‘ CHARSET || CHARACTER_SET_NAME
or, even simpler,
DTD_IDENTIFIER || ‘ CHARSET ‘ || CHARACTER_SET_NAME
and we’ve got “char(5) CHARSET latin1”, the same as what’s in mysql.proc.returns. Using DTD_IDENTIFIER avoids complications with other data types so I’ll always go with it.

It’s trickier to find a substitute for param_list, because parameters aren’t in information_schema.routines at all. We’d find them by saying

SELECT ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DTD_IDENTIFIER
FROM information_schema.parameters
WHERE specific_name = 'fx1';
+------------------+----------------+----------------+----------------+
| ORDINAL_POSITION | PARAMETER_MODE | PARAMETER_NAME | DTD_IDENTIFIER |
+------------------+----------------+----------------+----------------+
|                0 | NULL           | NULL           | char(5)        |
|                1 | IN             | paramx1        | int(11)        |
|                2 | IN             | paramx2        | double         |
+------------------+----------------+----------------+----------------+

We don’t need to do anything with parameter #0 (it’s just a copy of what’s in information_schema.routines.returns); we only need to merge parameter #1 and parameter #2 into the main query. Like this (with sql_mode=’pipes_as_concat’), but skipping the details we’ve already seen:

SELECT routine_name, routine_body,
       (SELECT group_concat(parameter_name || ' ' || dtd_identifier)
        FROM information_schema.parameters p
        WHERE p.specific_name = outertable.routine_name
        AND ordinal_position > 0)
       AS param_list
FROM information_schema.routines outertable
WHERE routine_name = 'fx1';

Result:

+--------------+--------------+--------------------------------+
| ROUTINE_NAME | ROUTINE_BODY | param_list                     |
+--------------+--------------+--------------------------------+
| fx1          | SQL          | paramx1 int(11),paramx2 double |
+--------------+--------------+--------------------------------+

In other words, we can get param_list from information_schema.routines by adding a subquery that accesses information_schema.parameters. Notice the assumption that the list will be ordered, I’m depending on a quirk.

body

Let’s try:

CREATE PROCEDURE px1() SELECT _latin1 0xe3;
SELECT body, body_utf8, _latin1 0xe3 FROM mysql.proc WHERE name = 'px1';

Result:

+---------------------+--------------+--------------+
| body                | body_utf8    | _latin1 0xe3 |
+---------------------+--------------+--------------+
| SELECT _latin1 0xe3 | SELECT  0xe3 | ã            |
+---------------------+--------------+--------------+

Now let’s try:

CREATE PROCEDURE px2() SELECT 'abc''def';
SELECT body, body_utf8 FROM mysql.proc WHERE name = 'px2';

Result:

+-------------------+------------------+
| body              | body_utf8        |
+-------------------+------------------+
| SELECT 'abc''def' | SELECT 'abc'def' |
+-------------------+------------------+

So you can see that body and body_utf8 are different. In a sense, both are correct — body is what you want if you are going to make a copy of the routine, body_utf8 is what you want if you want to see what the output would look like if you invoked the routine. So it’s pretty useful that mysql.proc has both.

Unfortunately, information_schema.routines does not. It has no equivalent of body. It only has an equivalent of body_utf8.

Loyal readers may recall that I’ve talked before about the possible information losses when making everything UTF8 but this is even worse. Without an equivalent of mysql.proc.body, you cannot reliably make exact copies of routines even if they are in UTF8.

Privilege differences

Actually the change amounts to more than just the differences between the definitions of the tables. There’s also the fact that PRIVILEGES are different — you can GRANT on mysql.* tables, you cannot GRANT on information_schema.* tables. And what users can see differs depending on how granting is done.

Therefore, for all users who currently hold a SELECT privilege on mysql.proc, we are going to have to work around the problem that there is no way to grant the exact same privilege on information_schema.routines. In other words, if the old (MySQL 5.7) statement was

GRANT SELECT ON mysql.proc TO 'jean'@'localhost';

To be the same as that, you need a way to let jean see all the columns in all the routines, but not anything other than the routines. This is possible with the DEFINER clause in routines and views. For example, assuming ‘root’@’localhost’ is a powerful user:

CREATE
DEFINER = 'root'@'localhost'
SQL SECURITY DEFINER
VIEW v_routines
AS SELECT * FROM information_schema.routines;
GRANT SELECT ON v_routines TO 'jean'@'localhost';

SHOW

Quote from MySQL 5.7 manual describing SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION:
“To use either statement, you must be the user named in the routine DEFINER clause or have SELECT access to the mysql.proc table.”

Quote from MySQL 8.0 manual describing SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION:
“To use either statement, you must have the global SELECT privilege.”

Essentially, the privilege requirement in 5.7 is what’s needed for looking at mysql.proc, but the privilege requirement in 8.0 is what’s needed for looking at information_schema.routines.

But this time we can’t work around by creating a view. If I’m interpreting “global SELECT privilege” correctly, the expectation is that if you want to say SHOW CREATE PROCEDURE, you need

GRANT SELECT ON *.* TO user_who_wants_to_show_create_procedure;

Either I’m misinterpreting, or MySQL is demanding that you grant a very broad privilege for a very narrow requirement.

This is too bad because, though SHOW statements are junk, this one will be necessary in MySQL 8.0. That’s because it has the body value right: it does not turn SELECT ‘abc”def’ into SELECT ‘abc’def’ and so on. Thus, it is the only way to get the equivalent of MySQL 5.7’s mysql.proc.body value. Using a connector, you can put this correct value into another table with something like this, leaving out details:

  mysql_real_query("SHOW PROCEDURE p;")
  mysql_fetch_row() /* there's only going to be one row at most */
  mysql_fetch_field() /* the third field is `Create procedure` */
  mysql_real_query("UPDATE copy_of_routines") /* set body value */

Other ways to get lists of routines

Although MySQL 8.0 is missing mysql.proc, it isn’t missing all the grant-related tables (yet). So you can still say

SELECT * FROM mysql.procs_priv;

Not only that, you can still use SHOW PROCEDURE STATUS — and then put the results in a @variable! As far as I know this is undocumented, but it’s been around for years and nobody has bothered to disallow it.
Try this:

SET @procedurelist = '';
SHOW PROCEDURE STATUS WHERE (@procedurelist := CONCAT(@procedurelist, `Name`, ','));
SELECT @procedurelist;

Result:

+----------------------------------------------------- ...
| create_synonym_db,diagnostics,execute_prepared_stmt, ...
+----------------------------------------------------- ...

Demonstrably, the @procedurelist variable now has a list of all procedures. Its only recommended use is to show unbelievers that with MySQL all things are possible.

Effect on ocelotgui debugger

As you might have guessed by now, we have a program that uses mysql.proc, namely the Ocelot open source GUI client for MySQL and MariaDB (ocelotgui). Well, one of its feature components is a debugger for MySQL stored routines, and you can see all the “mysql.proc” references in our source code for that module.

Our plan is: wait and see if MySQL makes the transition easier. But if that doesn’t happen, within a few months we’ll change ocelotgui to implement all the methods that I’ve described. So if you want to see how it’s done in detail, with an actual working application, just watch the code related to mysql.proc until it changes. It’s on github.
UPDATE (September 2018) our latest release 1.0.7 can be used for debugging MySQL 8.0 routines.