Bug #26994 I_S.GLOBAL_STATUS and I_S.SESSION_STATUS use floats in VARIABLE_VALUE column
Submitted: 9 Mar 2007 11:47 Modified: 30 May 2007 15:00
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:5.1.17-bk OS:Linux (Linux ia32/x86_64)
Assigned to: Sergey Glukhov
Tags: bfsm_2007_04_19, information_schema, status variables
Triage: D5 (Feature request)

[9 Mar 2007 11:47] Jon Stephens
Description:
The VARIABLE_VALUES columns of these tables display the values in floating-point format. This is not appropriate, since most of the values are counts (integers) and some (e.g. hostnames) are strings.

It's an annoyance when working with values that are expected to be integers. 

String values get converted to 0.0000000, which makes them completely useless.

How to repeat:
mysql> select @@version;
+-------------------+
| @@version         |
+-------------------+
| 5.1.17-beta-debug |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'NDB%';
+--------------------------+----------------+
| VARIABLE_NAME            | VARIABLE_VALUE |
+--------------------------+----------------+
| NDB_CLUSTER_NODE_ID      |     10.0000000 |
| NDB_CONFIG_FROM_HOST     |      0.0000000 |
| NDB_CONFIG_FROM_PORT     |   1186.0000000 |
| NDB_NUMBER_OF_DATA_NODES |      4.0000000 |
+--------------------------+----------------+
4 rows in set (0.01 sec)

Compare this with the following:

mysql> SHOW STATUS LIKE 'NDB%';
+--------------------------+---------------+
| Variable_name            | Value         |
+--------------------------+---------------+
| Ndb_cluster_node_id      | 10            |
| Ndb_config_from_host     | 192.168.0.103 |
| Ndb_config_from_port     | 1186          |
| Ndb_number_of_data_nodes | 4             |
+--------------------------+---------------+
4 rows in set (0.00 sec)

Simialr results can be obtained for the I_S.SESSION_STATUS table.

Suggested fix:
Do not convert values to floating-point before storing in these tables.
[9 Mar 2007 12:01] Valerii Kravchuk
Thank you for a bug report. Verified just as described.
[14 Mar 2007 14:32] Jon Stephens
I am reopening this bug and requesting re-evaluation. 

There are additional examples showing why the current behaviour is broken. Here's one.

mysql> show status like 'slave%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Slave_open_temp_tables     | 0     |
| Slave_retried_transactions | 0     |
| Slave_running              | ON    |
+----------------------------+-------+
3 rows in set (0.00 sec)

If you look at the output from SHOW STATUS, there's not a single float value among the whole lot.

mysql> select variable_name,variable_value from information_schema.global_status where variable_name like 'slave%';
+----------------------------+----------------+
| variable_name              | variable_value |
+----------------------------+----------------+
| SLAVE_OPEN_TEMP_TABLES     | 0.0000000      |
| SLAVE_RETRIED_TRANSACTIONS | 0.0000000      |
| SLAVE_RUNNING              | 0.0000000      |
+----------------------------+----------------+
3 rows in set (0.02 sec)

So if I look at SHOW STATUS, I'll know that there's a replication slave, but if I perform the equivalent query on I_S.GLOBAL_STATUS, I'll see 0.0000000 -> 0 -> FALSE? Whuh...?

ssl_cipher, ssl_cipher_list, and ssl_version are also strings IIRC.

The I_S.*_VARIABLES use a text type for the same column:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> describe global_variables;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| VARIABLE_NAME  | varchar(64) | NO   |     |         |       |
| VARIABLE_VALUE | longtext    | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe session_variables;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| VARIABLE_NAME  | varchar(64) | NO   |     |         |       |
| VARIABLE_VALUE | longtext    | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> describe global_status;
+----------------+---------------+------+-----+-----------+-------+
| Field          | Type          | Null | Key | Default   | Extra |
+----------------+---------------+------+-----+-----------+-------+
| VARIABLE_NAME  | varchar(64)   | NO   |     |           |       |
| VARIABLE_VALUE | decimal(22,7) | NO   |     | 0.0000000 |       |
+----------------+---------------+------+-----+-----------+-------+
2 rows in set (0.01 sec)

mysql> describe session_status;
+----------------+---------------+------+-----+-----------+-------+
| Field          | Type          | Null | Key | Default   | Extra |
+----------------+---------------+------+-----+-----------+-------+
| VARIABLE_NAME  | varchar(64)   | NO   |     |           |       |
| VARIABLE_VALUE | decimal(22,7) | NO   |     | 0.0000000 |       |
+----------------+---------------+------+-----+-----------+-------+
2 rows in set (0.01 sec)

Additional examples could likely be found, but I think I've made my point: Using DECIMAL for these values is broken and must be fixed.
[14 Mar 2007 17:43] Peter Gulutzan
Re-evaluation shows:

This was intended, well known at design time, and approved.
[13 Apr 2007 11:24] Arjen Lentz
Here's a patch that fixes the issue (bug or not), it actually mainly removes 110 lines of code and instead re-uses other code that makes it perfectly in sync with SHOW STATUS behaviour. Of course it'll need review by someone with more server expertise, but fundamentally it works. Queries from global_status and session_status tables, with and without where clauses.

===== sql/sql_show.cc 1.404 vs edited =====
--- 1.404/sql/sql_show.cc	2007-04-07 19:55:24 +10:00
+++ edited/sql/sql_show.cc	2007-04-13 15:21:28 +10:00
@@ -5175,119 +5175,6 @@
   DBUG_RETURN(0);
 }
 
-int fill_schema_status(THD *thd, SHOW_VAR *variables,
-                       struct system_status_var *status_var,
-                       const char *prefix, TABLE *table)
-{
-  SHOW_VAR tmp, *var;
-  SHOW_TYPE show_type;
-  LEX_STRING null_lex_str;
-  char buff[SHOW_VAR_FUNC_BUFF_SIZE];
-  char name_buf[64], *name_pos;
-  int name_len;
-  DBUG_ENTER("fill_schema_status");
-  
-  null_lex_str.str= 0;
-  null_lex_str.length= 0;
-  
-  name_pos= strnmov(name_buf, prefix, sizeof(name_buf) - 1);
-  if (*prefix)
-    *name_pos++= '_';
-  name_len= name_buf + sizeof(name_buf) - name_pos;
-  
-  for (; variables->name; variables++)
-  {
-    strnmov(name_pos, variables->name, name_len);
-    name_buf[sizeof(name_buf) - 1]= 0;
-    make_upper(name_buf);
-    
-    for (var= variables; var->type == SHOW_FUNC; var= &tmp)
-      ((mysql_show_var_func)(var->value))(thd, &tmp, buff);
-      
-    show_type= var->type;
-    
-    if (show_type == SHOW_ARRAY)
-    {
-      fill_schema_status(thd, (SHOW_VAR*) var->value,
-                         status_var, name_buf, table);
-    }
-    else
-    {
-      char *value= var->value;
-      
-      restore_record(table, s->default_values);
-      table->field[0]->store(name_buf, strlen(name_buf), system_charset_info);
-      
-      if (show_type == SHOW_SYS)
-      {
-        show_type= ((sys_var*) value)->show_type();
-        value= (char*) ((sys_var*) value)->value_ptr(thd, OPT_GLOBAL,
-                                                     &null_lex_str);
-      }
-                                                     
-      switch (show_type)
-      {
-      case SHOW_DOUBLE_STATUS:
-        value= (char*) status_var + (ulong) value;
-        table->field[1]->store(*(double*) value);
-        break;
-      case SHOW_LONG_STATUS:
-        value= (char*) status_var + (ulong) value;
-        /* fall through */
-      case SHOW_LONG:
-      case SHOW_LONG_NOFLUSH: /* the difference lies in refresh_status() */
-        table->field[1]->store((longlong) *(long*) value, false);
-        break;
-      case SHOW_LONGLONG:
-        table->field[1]->store(*(longlong*) value, false);
-        break;
-      case SHOW_HA_ROWS:
-        table->field[1]->store((longlong) *(ha_rows*) value, false);
-        break;
-      case SHOW_BOOL:
-        table->field[1]->store((longlong) *(bool*) value, false);
-        break;
-      case SHOW_MY_BOOL:
-        table->field[1]->store((longlong) *(my_bool*) value, false);
-        break;
-      case SHOW_INT:
-        table->field[1]->store((longlong) *(uint32*) value, false);
-        break;
-      case SHOW_HAVE: /* always displayed as 0 */
-        table->field[1]->store((longlong) 0, false);
-        break;
-      case SHOW_CHAR_PTR:
-        value= *(char**) value;
-        /* fall through */
-      case SHOW_CHAR: /* always displayed as 0 */
-        table->field[1]->store((longlong) 0, false);
-        break;
-      case SHOW_KEY_CACHE_LONG:
-        value= (char*) dflt_key_cache + (ulong) value;
-        table->field[1]->store((longlong) *(long*) value, false);
-        break;
-      case SHOW_KEY_CACHE_LONGLONG:
-        value= (char*) dflt_key_cache + (ulong) value;
-        table->field[1]->store(*(longlong*) value, false);
-        break;
-      case SHOW_UNDEF: /* always displayed as 0 */
-        table->field[1]->store((longlong) 0, false);
-        break;
-      case SHOW_SYS: /* cannot happen */
-      default:
-        DBUG_ASSERT(0);
-        break;
-      }
-      
-      table->field[1]->set_notnull();
-      if (schema_table_store_record(thd, table))
-        DBUG_RETURN(1);
-    }
-  }
-  
-  DBUG_RETURN(0);
-}
-
 int fill_schema_global_status(THD *thd, TABLE_LIST *tables, COND *cond)
 {
   STATUS_VAR tmp;
@@ -5296,8 +5183,8 @@
   
   pthread_mutex_lock(&LOCK_status);
   calc_sum_of_all_status(&tmp);
-  res= fill_schema_status(thd, (SHOW_VAR*) all_status_vars.buffer,
-                          &tmp, "", tables->table);
+  res= show_status_array(thd, NullS, (SHOW_VAR *)all_status_vars.buffer, OPT_GLOBAL,
+                         &tmp, "", tables->table, 1);
   pthread_mutex_unlock(&LOCK_status);
   
   DBUG_RETURN(res);
@@ -5309,8 +5196,8 @@
   DBUG_ENTER("fill_schema_session_status");
   
   pthread_mutex_lock(&LOCK_status);
-  res= fill_schema_status(thd, (SHOW_VAR*) all_status_vars.buffer,
-                          &thd->status_var, "", tables->table);
+  res= show_status_array(thd, NullS, (SHOW_VAR *)all_status_vars.buffer, OPT_SESSION,
+                         thd->initial_status_var, "", tables->table, 1);
   pthread_mutex_unlock(&LOCK_status);
   
   DBUG_RETURN(res);
@@ -5705,7 +5592,7 @@
 ST_FIELD_INFO status_fields_info[]=
 {
   {"VARIABLE_NAME", 64, MYSQL_TYPE_STRING, 0, 0, "Variable_name"},
-  {"VARIABLE_VALUE", 2207, MYSQL_TYPE_DECIMAL, 0, 0, "Value"},
+  {"VARIABLE_VALUE", 65535, MYSQL_TYPE_STRING, 0, 1, "Value"},
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0}
 };
[30 May 2007 15:00] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.19 changelog.