Bug #51667 Need status variable for com_ping

• Submitted: 2 Mar 2010 23:49
• Reporter: Todd Farmer (OCA)
• Status: Verified
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• Assigned to: Geir Høydalsvik
• CPU Architecture: Any
• Tags: com_ping, mysql_ping

[2 Mar 2010 23:49] Todd Farmer

Description:
There is no status variable (global or session) for com_ping.  This would be useful for tracking the number of times a connection pool pinged a server to test for connection viability.

How to repeat:
SHOW STATUS LIKE '%ping%';

Suggested fix:
Add Com_ping status variable.

[2 Mar 2010 23:56] Mark Leith

=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc       revid:[e-mail]-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:[e-mail]-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:[e-mail]-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:

[3 Mar 2010 5:09] MySQL Verification Team

related: bug #28405 (please add a Com_change_user status variable)

[30 Jul 2013 20:45] Todd Farmer

Documenting this as a workaround for others interested:  Getting this information is possible via PERFORMANCE_SCHEMA in MySQL 5.6:

mysql> SELECT SUM(count_star) as pings
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name = 'statement/com/Ping';
+-------+
| pings |
+-------+
|    12 |
+-------+
1 row in set (0.02 sec)