An evening hack – Com_ping

My boss vented about the lack of a Com_ping SHOW GLOBAL STATUS variable earlier, and I figured it would be dead easy to hack in.

A few minutes later:

Cerberus:mysql-next-mr mark$ bzr diff ./sql
=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc       revid:alik@sun.com-20100114090008-3rsdmlp1w2mqgrhg
+++ sql/mysqld.cc       2010-03-02 22:58:45 +0000
@@ -3174,6 +3174,7 @@
   {"load",                 (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_LOAD]), SHOW_LONG_STATUS},
   {"lock_tables",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_LOCK_TABLES]), SHOW_LONG_STATUS},
   {"optimize",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_OPTIMIZE]), SHOW_LONG_STATUS},
+  {"ping",                 (char*) offsetof(STATUS_VAR, com_ping), SHOW_LONG_STATUS},
   {"preload_keys",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PRELOAD_KEYS]), SHOW_LONG_STATUS},
   {"prepare_sql",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PREPARE]), SHOW_LONG_STATUS},
   {"purge",                (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PURGE]), SHOW_LONG_STATUS},
@@ -3350,11 +3351,12 @@
     We have few debug-only commands in com_status_vars, only visible in debug
     builds. for simplicity we enable the assert only in debug builds
 
-    There are 8 Com_ variables which don't have corresponding SQLCOM_ values:
+    There are 9 Com_ variables which don't have corresponding SQLCOM_ values:
     (TODO strictly speaking they shouldn't be here, should not have Com_ prefix
     that is. Perhaps Stmt_ ? Comstmt_ ? Prepstmt_ ?)
 
       Com_admin_commands       => com_other
+      Com_ping                 => com_ping
       Com_stmt_close           => com_stmt_close
       Com_stmt_execute         => com_stmt_execute
       Com_stmt_fetch           => com_stmt_fetch
@@ -3368,7 +3370,7 @@
     of SQLCOM_ constants.
   */
   compile_time_assert(sizeof(com_status_vars)/sizeof(com_status_vars[0]) - 1 ==
-                     SQLCOM_END + 8);
+                     SQLCOM_END + 9);
 #endif
 
   if (get_options(&remaining_argc, &remaining_argv))

=== modified file 'sql/sql_class.h'
--- sql/sql_class.h     revid:alik@sun.com-20100114090008-3rsdmlp1w2mqgrhg
+++ sql/sql_class.h     2010-03-02 22:56:05 +0000
@@ -443,6 +443,7 @@
   ulong ha_discover_count;
   ulong ha_savepoint_count;
   ulong ha_savepoint_rollback_count;
+  ulong com_ping;
 
   /* KEY_CACHE parts. These are copies of the original */
   ulong key_blocks_changed;

=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc    revid:alik@sun.com-20100114090008-3rsdmlp1w2mqgrhg
+++ sql/sql_parse.cc    2010-03-02 23:23:41 +0000
@@ -1409,7 +1409,7 @@
     break;
   }
   case COM_PING:
-    status_var_increment(thd->status_var.com_other);
+    status_var_increment(thd->status_var.com_ping);
     my_ok(thd);                                // Tell client we are alive
     break;
   case COM_PROCESS_INFO:

COM_PING has always historically been recorded under Com_admin_commands (which is tracked with com_other), however this also includes a number of other commands:

COM_TABLE_DUMP
COM_CHANGE_USER
COM_BINLOG_DUMP
COM_SHUTDOWN
COM_DEBUG

COM_CHANGE_USER can also be used frequently with connection pools, as can COM_PING. It would be nice to differentiate them when tracking down issues within such environments.

Here’s the bug that was opened:

http://bugs.mysql.com/bug.php?id=51667

And a quick test:

Cerberus:msb_5_5_5 mark$ mysql -u root -pmsandbox -h 127.0.0.1 -P 5550 -e "show global status like 'com_ping'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_ping      | 0     |
+---------------+-------+
Cerberus:msb_5_5_5 mark$ php ~/Dev/tests/com_ping.php
Connected!
Cerberus:msb_5_5_5 mark$ mysql -u root -pmsandbox -h 127.0.0.1 -P 5550 -e "show global status like 'com_ping'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_ping      | 1     |
+---------------+-------+
Cerberus:msb_5_5_5 mark$ php ~/Dev/tests/com_ping.php
Connected!
Cerberus:msb_5_5_5 mark$ mysql -u root -pmsandbox -h 127.0.0.1 -P 5550 -e "show global status like 'com_ping'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_ping      | 2     |
+---------------+-------+

The test script:

< ?php

$conn = mysql_connect('127.0.0.1:5550','root','msandbox');
mysql_select_db('test',$conn);

if (!mysql_ping ($conn)) {
   echo "Connection Dead!\n";
} else {
   echo "Connected!\n";
}

?>

Disclaimer: Not sure if/when/where this might get in! It is a change in behavior with regards to Com_admin_commands