In an earlier post, I commented on clients and utility programs which seem to no longer be useful, and opened (or referenced existing) public bug reports to deprecate and remove, where appropriate.  That effort came actually was the product of a different initiative:  I was looking for clients which might leverage the full spectrum of MySQL protocol commands in an effort to understand whether certain protocol commands are in use.  I thought I would share my observations, in the hope that we might also get feedback from others regarding usage of these commands. And even though it’s no longer spring (I started this post in April), I finally found time to finish this post.

The first group to mention are those which are refused by the server.  Some are explicitly identified, others are handled as they fall through to the default case in sql_parse.cc – but all are ignored, and I’m assuming that nobody cares about these commands as a result:

COM_SLEEP (0x00)

As noted in the documentation, this is an internal server command only.

COM_TIME (0x0f)

Also an internal command ignored in the protocol handling.

COM_DELAYED_INSERT (0x10)

One more internal command.

COM_END (0x1f)

This one isn’t documented anywhere, but it’s explicitly handled in the code.  It appears to be a guard against receiving command values greater than the allowable range:

  command= (enum enum_server_command) (uchar) packet[0];

  if (command >= COM_END)
    command= COM_END;                // Wrong command
So nothing much to talk about here.

COM_CREATE_DB (0x05)

Now we start to get interesting.  I expected that mysqladmin create db would leverage COM_CREATE_DB, but I was wrong.  I started poking around looking to see if I could find any other clients which might issue COM_CREATE_DB, gave up, and eventually wrote my own pseudo-client which would allow me to send COM_CREATE_DB protocol commands.  I should have looked at the server source code first; there’s no place in which COM_CREATE_DB gets handled, and it falls through to the default handling:

default:
 my_message(ER_UNKNOWN_COM_ERROR, ER(ER_UNKNOWN_COM_ERROR), MYF(0));
 break;

It’s not currently marked as deprecated in the protocol docs (bug 69074), but it seems as though support for it was removed way, way back.  mysqladmin uses COM_QUERY instead.  This corresponds to the mysql_create_db() function in the C API, which has been deprecated since 4.0, with the recommendation to use com_query() instead.

COM_DROP_DB

Same as COM_CREATE_DB above.

Interestingly, all of the above still have counters exposed via PERFORMANCE_SCHEMA, and even though invoking this protocol commands results in an error, you can still see the relevant counters increase:

mysql> SELECT event_name, count_star, sum_errors
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name LIKE 'statement/com/Sleep';
+---------------------+------------+------------+
| event_name          | count_star | sum_errors |
+---------------------+------------+------------+
| statement/com/Sleep |          1 |          1 |
+---------------------+------------+------------+
1 row in set (0.00 sec)

I noticed this when I started wondering about the documentation claims that some of above represent internal server commands – I wondered whether PERFORMANCE_SCHEMA might indicate they are actually used. I couldn’t observe any counters incrementing from internal server usage, but with my custom client, I could trigger execution (and error) counters to increase. Here’s a list of all COM_* equivalent events in PERFORMANCE_SCHEMA:

mysql> SELECT event_name, count_star, sum_errors
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name LIKE 'statement/com/%';
+--------------------------------+------------+------------+
| event_name                     | count_star | sum_errors |
+--------------------------------+------------+------------+
| statement/com/Sleep            |          1 |          1 |
| statement/com/Quit             |          3 |          0 |
| statement/com/Init DB          |          0 |          0 |
| statement/com/Query            |          0 |          0 |
| statement/com/Field List       |          0 |          0 |
| statement/com/Create DB        |          1 |          1 |
| statement/com/Drop DB          |          1 |          1 |
| statement/com/Refresh          |          0 |          0 |
| statement/com/Shutdown         |          0 |          0 |
| statement/com/Statistics       |          0 |          0 |
| statement/com/Processlist      |          3 |          0 |
| statement/com/Connect          |          0 |          0 |
| statement/com/Kill             |          0 |          0 |
| statement/com/Debug            |          0 |          0 |
| statement/com/Ping             |       1609 |          0 |
| statement/com/Time             |          0 |          0 |
| statement/com/Delayed insert   |          0 |          0 |
| statement/com/Change user      |          0 |          0 |
| statement/com/Binlog Dump      |          0 |          0 |
| statement/com/Table Dump       |          0 |          0 |
| statement/com/Connect Out      |          0 |          0 |
| statement/com/Register Slave   |          0 |          0 |
| statement/com/Prepare          |          0 |          0 |
| statement/com/Execute          |          0 |          0 |
| statement/com/Long Data        |          0 |          0 |
| statement/com/Close stmt       |          0 |          0 |
| statement/com/Reset stmt       |          0 |          0 |
| statement/com/Set option       |          0 |          0 |
| statement/com/Fetch            |          0 |          0 |
| statement/com/Daemon           |          0 |          0 |
| statement/com/Binlog Dump GTID |          0 |          0 |
| statement/com/Error            |          0 |          0 |
| statement/com/                 |          0 |          0 |
+--------------------------------+------------+------------+
33 rows in set (0.00 sec)

What’s interesting to me about this list is that there are clearly events which correspond to COM_* protocol commands, but are not yet documented. I submitted Bug#69927 to improve the documentation here. It also isn’t clear to me what the “statement/com” event is; if it’s meant to be a rollup (which could be nice), it’s not performing as expected (Bug#69928).

Another interesting observation is that the documentation for COM_PROCESS_INFO indicates that it’s been deprecated since MySQL 4.1, but it’s clearly still functioning (statement/com/Processlist events increase and no errors).  I submitted Bug#69929 to remove support for COM_PROCESS_INFO in MySQL 5.6 (FYI, mysqladmin uses COM_QUERY to issue SHOW PROCESSLIST).

I’m also interested in usage of COM_DEBUG – does anybody use this?  I know that’s what mysqladmin debug sends, but in my six years as part of the MySQL Support Team, I don’t recall anybody ever having used it.  Would people care if it were deprecated?