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:Microsoft Windows (Wincows/Linux)
Assigned to: Assigned Account
Tags: BLOB, longtext, Mediumtext, text, Tinyblob, tinytext, wrong field type
Triage: Triaged: D3 (Medium) / R3 (Medium) / E4 (High)

[27 Mar 2007 15:25] Alonso Murillo
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?)
[27 Mar 2007 16:19] Miguel Solorzano
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.
[31 Mar 2007 13:59] Alonso Murillo
// 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;
}
[2 Apr 2007 23:15] Miguel Solorzano
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

};
[13 Nov 2007 16:18] Brad House
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...
[18 Jun 2008 11:10] Konstantin Osipov
Jim, are you going to look at this bug anytime soon?
[20 Feb 2009 14:12] Tonci Grgin
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)
[14 Jul 2009 6:22] Tonci Grgin
Thanks for info Jim.