MySQL Bugs: #12817: SHOW STATUS now blob fields

Bug #12817	SHOW STATUS now blob fields
Submitted:	26 Aug 2005 0:11	Modified:	9 Sep 2005 19:50
Reporter:	Richard de Courtney	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.14	OS:	Windows (WINDOWS 2003/Linux)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
When using the SHOW STATUS query, status values are being returned in BLOB format rather than plain varchar or int, this was different in 4.1.13 (not sure what the old format was as never noticed!), but when retrieving the status of the db for our statistics page (http://www.wirelessmedia.com/statistics/ ) or for programs such as navicat, corrupted text is being retrieved.

How to repeat:
do SHOW STATUS as a query

Suggested fix:
change the field type

downgraded

Thanks for bug report. I tested on 4.4.14. Navicat returns all values as BLOB.
It works correct on 5.0.11

I see that this has been downgraded to a S4 (feature request), we have scripts that analyze load and performance of our mysql databases, as I guess do many other people who operate business critical databases. Having stats in a consistant and readable format is critical to my business as well as many others, and therefore is not a feature request (as I'm not requesting a new feature), but a bug that impacts the performance, reliability and the ability for us to minimize downtime, hence why it was logged at S3. Thanks!

Thank you for the bug report I was able to repeat with client application
with code showed at the bottom;

/include/mysql_com.h

enum enum_field_types { MYSQL_TYPE_DECIMAL, MYSQL_TYPE_TINY,
			MYSQL_TYPE_SHORT,  MYSQL_TYPE_LONG,
			MYSQL_TYPE_FLOAT,  MYSQL_TYPE_DOUBLE,
			MYSQL_TYPE_NULL,   MYSQL_TYPE_TIMESTAMP,
			MYSQL_TYPE_LONGLONG,MYSQL_TYPE_INT24,
			MYSQL_TYPE_DATE,   MYSQL_TYPE_TIME,
			MYSQL_TYPE_DATETIME, MYSQL_TYPE_YEAR,
			MYSQL_TYPE_NEWDATE,
			MYSQL_TYPE_ENUM=247,
			MYSQL_TYPE_SET=248,
			MYSQL_TYPE_TINY_BLOB=249,
			MYSQL_TYPE_MEDIUM_BLOB=250,
			MYSQL_TYPE_LONG_BLOB=251,
			MYSQL_TYPE_BLOB=252,
			MYSQL_TYPE_VAR_STRING=253,
			MYSQL_TYPE_STRING=254,
			MYSQL_TYPE_GEOMETRY=255

};

			MYSQL_TYPE_BLOB=252,
			MYSQL_TYPE_VAR_STRING=253,

Testing on Windows:

c:\mysql\bin>c:\temp\bug12817
Connected to the server: 4.1.13-nt
SHOW STATUS returned number of rows= 162
Field Name: Variable_nameField Type: 253
Field Name: ValueField Type: 253

c:\mysql\bin>c:\temp\bug12817
Connected to the server: 4.1.15-nt
SHOW STATUS returned number of rows= 162
Field Name: Variable_nameField Type: 253
Field Name: ValueField Type: 252

Testing on Linux:

miguel@hegel:~/dbs/4.1> ./bug12817
Connected to the server: 4.1.15-debug-log
SHOW STATUS returned number of rows= 185
Field Name: Variable_nameField Type: 253
Field Name: ValueField Type: 252

Client Application for test:

#include <iostream>
#include <my_global.h>
#include "mysql.h"

#define DB_HOST					"localhost"
#define DB_USER					"root"
#define DB_PASSW				""
#define DB_NAME					"test"
#define DB_PORT					0
#define DB_UNIX_SOCKET	NULL // "/tmp/mysql.sock"

#define QUERY_SHOW			"SHOW STATUS"
using namespace std;

int main( )
{
  MYSQL			mysql;
	MYSQL_RES	* res;
	unsigned int num_fields;
	MYSQL_FIELD *fields;
  
  mysql_init(&mysql);
 
  if (!mysql_real_connect(&mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME,
						   DB_PORT,DB_UNIX_SOCKET,0))
  {
    cout << "Error on Connect: " << mysql_error(&mysql) << endl;
    return 1;
  }
  else
	cout << "Connected to the server: " << mysql_get_server_info(&mysql) << endl;

  if (mysql_query(&mysql, QUERY_SHOW))
  {
	  cout << "Error with Query: " << mysql_error(&mysql) << endl;
	  return 1;
  }

	res = mysql_store_result( &mysql );

	unsigned int i =  mysql_num_rows( res ) ;

	cout << QUERY_SHOW << " returned number of rows= " << i << endl;
  
	num_fields = mysql_num_fields( res );
  fields = mysql_fetch_fields( res );

  for (i = 0; i < num_fields; i++)
		 cout << "Field Name: " << fields[i].name << "Field Type: " << fields[i].type << endl;
 
	mysql_free_result( res ) ;	
	mysql_close(&mysql);
  return 0;

}

Thanks guys, you do a great job :-)

If there's any more info you need, please let me know.

Guys,

As you've got navicat, are you finding problems when you click on a table and select DESIGN TABLE, since the upgrade from 4.1.13 to 4.1.14, that this now doesn't work? I have the mac version of navicat here (latest version), and when right clicking on the table and opting for design table, nothing happens, whereon our other db at 4.1.13 it works fine.

If you want me to log another call about the above, please let me know.

- Rick

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29238

fixed in 4.1.15

Noted in 4.1.13, 4.1.15, 5.0.13 changelogs.
(4.1.13 and 4.1.15 to noted the reversion of
earlier fix, 5.0.13 to note fix in 5.0.)