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?
COM_CREATE_DB/COM_DROP_DB are ifdef-outed. Support was removed long ago and I my memory doesn’t lie to me Kostja was the executor. COM_SLEEP = there is no command, COM_END – yes, this is a guard typical for for all kind of enums.
COM_DEBUG sends a signal to the server and the server dumps to its stdout some debugging information. For one, the event scheduler dumps there some useful data.
I explored some of those depths some (looking it up … 7) years ago when I wrote my MySQL protocol analyzer.
COM_SLEEP, COM_TIME, COM_DELAYED_INSERT … those are there because SHOW PROCESSLIST looks at a field in the thd structure to show a “thread state” – rather the currently processed command. For threads that are not actually processing a command, this field is filled with such a pseudo-command (most common: COM_SLEEP).
COM_END is clearly an end marker for the enum type. As such it is never exposed.
COM_CREATE_DB and COM_DROP_DB correspond to the mysql_create_db() and mysql_drop_db() functions in the C API. However already 7 years agi libmysqlclient didn’t actually implement those functions unless you compiled it with -DUSE_OLD_FUNCTIONS in CFLAGS. So actually you can’t have any client programm using those command codes unless they are hand-rolled 🙂
Finally COM_DEBUG: I (have) often see(n) people using mysqladmin debug to get information about allocated memory. IMHO this should not be deprecated unless this information has been made available elsewhere.
Hi Axel,
Thanks for explaining the “internal usage” cited by the documentation in more detail – that’s helpful! Regarding COM_CREATE_DB and COM_DROP_DB, I did actually hand-roll a client to send those options in testing, and the server rejects them (probably also needs to be compiled with the same CFLAGS). Your comment about mysqladmin debug is interesting – I’ve not heard anybody claim to have used that tool recently or with any frequency. Maybe if we get visibility into memory usage from PERFORMANCE_SCHEMA in a future release, we’ll consider deprecating/removing COM_DEBUG support then.
Todd