An evening hack - Com_ping
March 3rd, 2010
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
Very strange, I was looking at sql/sql_parse.cc just yesterday and noticing it was strange how com_statistics/com_ping was handled.
This patch looks very useful.