MySQL Bugs: #27475: mysql_fetch_field do NOT report correctly certain field types

Bug #27475	mysql_fetch_field do NOT report correctly certain field types
Submitted:	27 Mar 2007 15:25	Modified:	2 Apr 2007 23:15
Reporter:	Alonso Murillo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	Mysql 5.0.37/5.BK/4.1BK	OS:	Windows (Wincows/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	BLOB, longtext, Mediumtext, text, Tinyblob, tinytext, wrong field type

Description:
When ever you call mysql_fetch_field function from libmySQL.dll and store the result if you try to retrive field type for the following types:

Tinyblob, blob, tinytext, text, mediumtext, longtext

It always return a value of 252

How to repeat:
invoke mysql_init, 0
mov GpMySQL, eax
invoke mysql_real_connect, GpMySQL, lpHostName, lpUser, lpPassword, lpDatabase, dwPort, NULL, 0
invoke mysql_select_db,GpMySQL,addr szAbraTabla
invoke lstrlen, lpRequest
invoke mysql_real_query, GpMySQL, lpRequest, eax
invoke  mysql_store_result, GpMySQL
mov pMyRES, eax
invoke mysql_fetch_field,pMyRES

Suggested fix:
DEVELOP A PATCH FOR MYSQL server or libmysql.dll (I don't know where the problem is?)

Thank you for the bug report. Could you please provide a complete test
case: create table script and the C API client code. Thanks in advance.

// Includes...
#include <iostream>
#include <windows.h>
#include <mysql/mysql.h>
#include <mysql/mysqld_error.h>
#include <cstring>
#include <cstdio>
using namespace std;
// Programa principal
int main()
{
// Variables
MYSQL *myData;
MYSQL_RES *res;
MYSQL_ROW row;
MYSQL_FIELD *columna;
int i, j, k, l;
unsigned long *lon;
MYSQL_ROW_OFFSET pos;
// Intentar iniciar MySQL:
if(!(myData = mysql_init(0))) {
// Imposible crear el objeto myData
cout << "ERROR: imposible crear el objeto myData." << endl;
rewind(stdin);
getchar();
return 1;
}
// Debe existir un usuario "curso" con clave de acceso "clave" y
// con al menos el privilegio "SELECT" sobre la tabla
// "prueba.gente"
if(!mysql_real_connect(myData, NULL, "curso", "clave", "prueba",
MYSQL_PORT, NULL, 0)) {
// No se puede conectar con el servidor en el puerto especificado.
cout << "Imposible conectar con servidor mysql en el puerto "
<< MYSQL_PORT << " Error: " << mysql_error(myData) << endl;
mysql_close(myData);
rewind(stdin);
getchar();
return 1;
}
// Conectar a base de datos.
if(mysql_select_db(myData, "prueba")) {
// Imposible seleccionar la base de datos, posiblemente no existe.
cout << "ERROR: " << mysql_error(myData) << endl;
mysql_close(myData);
rewind(stdin);
getchar();
return 2;
}
// Hacer una consulta con el comando "SELECT * FROM gente":
if(mysql_query(myData, "SELECT * FROM gente")) {
// Error al realizar la consulta:
cout << "ERROR: " << mysql_error(myData) << endl;
mysql_close(myData);
rewind(stdin);
getchar();
return 2;
}
// Almacenar el resultado de la consulta:
if((res = mysql_store_result(myData))) {
// Obtener el número de registros seleccionados:
i = (int) mysql_num_rows(res);
// Obtener el número de columnsa por fila:
j = (int) mysql_num_fields(res);
// Mostrar el número de registros seleccionados:
cout << "Consulta: SELECT * FROM gente" << endl;
cout << "Numero de filas encontradas: " << i << endl;
cout << "Numero de columnas por fila: " << j << endl;
// Información sobre columnas usando mysql_fetch_field:
cout << endl << "Informacion sobre columnas:" << endl;
for(l = 0; l < j; l++) {
columna = mysql_fetch_field(res);
cout << "Nombre: " << columna->name << endl;
cout << "Longitud: " << columna->length << endl;
cout << "Tipo: " << (columna->type ? columna->type << endl;
// THE RESULT TYPE FOR TEXT FIELD 252

// Liberar el resultado de la consulta:
mysql_free_result(res);
}
// Cerrar la conexión
mysql_close(myData);
// Esperar a que se pulse una tecla y salir.
rewind(stdin);
getchar();
return 0;
}

Thank you for the bug report.

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

#define DB_HOST			"localhost" 
#define DB_USER			"root"
#define DB_PASSW		""
#define DB_NAME			"test"
#define DB_PORT			3306
#define DB_UNIX_SOCKET	 "/tmp/mysql.sock" /* NULL  */
#define CREATE_QUERY "Create Table if not exists tb_type (col_TinyBlob tinyblob, \
col_Blob blob,col_TinyText tinytext,col_Text text,col_MediumText mediumtext, \
col_LongText longtext)"
#define SELECT_QUERY "select * from tb_type"
void main( void )
{
  MYSQL mysql;
  MYSQL_RES *result;
  unsigned int num_fields;
  unsigned int i;
  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))
  {
    printf("Error: %s\n",mysql_error(&mysql));
    return;
  }
  else
	printf("Connected to the server: %s\n",mysql_get_server_info(&mysql));

  if (mysql_query( &mysql, CREATE_QUERY) )
  {
    printf("Error (query): %s\n", mysql_error( &mysql ));
    mysql_close( &mysql );
    return;
  }

  if (mysql_query( &mysql, SELECT_QUERY) )
  {
    printf("Error (query): %s\n", mysql_error( &mysql ));
    mysql_close( &mysql );
    return;
  }

  result = mysql_store_result(&mysql);
  num_fields = mysql_num_fields(result);
 	  
  fields = mysql_fetch_fields(result);

  for(i = 0; i < num_fields; i++)
  {
	printf("%s: %u\n",fields[i].name, fields[i].type);
  }
  
  mysql_free_result(result);
  mysql_close(&mysql);
}

[miguel@light 5.0]$ ./bug27475
Connected to the server: 5.0.40-debug
col_TinyBlob: 252
col_Blob: 252
col_TinyText: 252
col_Text: 252
col_MediumText: 252
col_LongText: 252
[miguel@light 5.0]$ cat /etc/issue
Fedora Core release 6 (Zod)
Kernel \r on an \m

[[miguel@light 4.1]$ ./bug27475 
Connected to the server: 4.1.23-debug
col_TinyBlob: 252
col_Blob: 252
col_TinyText: 252
col_Text: 252
col_MediumText: 252
col_LongText: 252
[miguel@light 4.1]$ 

[miguel@light 5.1]$ ./bug27475 
Connected to the server: 5.1.18-beta-debug
col_TinyBlob: 252
col_Blob: 252
col_TinyText: 252
col_Text: 252
col_MediumText: 252
col_LongText: 252

#define packet_error (~(unsigned long) 0)

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_VARCHAR,
                        MYSQL_TYPE_BIT,
                        MYSQL_TYPE_NEWDECIMAL=246,
                        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

};

I ran into this issue too, and there is a workaround of if fields[i].type is MYSQL_TYPE_BLOB, you can check fields[i].charsetnr and if it is NOT 63, then it is a TEXT field, otherwise it is a BLOB.

I got that workaround from here:
http://bugs.mysql.com/bug.php?id=11974

It appears to work in 5.0.45 at least... You'd think the C API could do that check on its own internally and return MYSQL_TYPE_STRING instead in those instances...

Jim, are you going to look at this bug anytime soon?

Omer, what is the "acceptable workaround" here??? This is killing me with connectors, see Bug#24886 for example. Requesting re-triage as connectors simply can't rely on guessing the correct metadata or depend on such slow implementation of I__S.

Server version: 5.1.31 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test43055 (vcfld VARCHAR (50) NOT NULL PRIMARY KEY, txtfld TEXT) Engine=MyISAM charset latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from test43055;
Field   1:  `vcfld`
Catalog:    `def`
Database:   `test`
Table:      `test43055`
Org_table:  `test43055`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     50
Max_length: 0
Decimals:   0
Flags:      NOT_NULL PRI_KEY NO_DEFAULT_VALUE PART_KEY

Field   2:  `txtfld`
Catalog:    `def`
Database:   `test`
Table:      `test43055`
Org_table:  `test43055`
Type:       BLOB   <<<<<
Collation:  latin1_swedish_ci (8)
Length:     65535
Max_length: 0
Decimals:   0
Flags:      BLOB

0 rows in set (0.00 sec)

Thanks for info Jim.