MySQL Bugs: #8550: Wrong calculate field length in the mysql_stmt_bind

Bug #8550	Wrong calculate field length in the mysql_stmt_bind_result()
Submitted:	16 Feb 2005 16:16	Modified:	16 Feb 2007 19:55
Reporter:	Alex Dzuba	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.26-BK, 5.0, 4.1.9	OS:	Windows (Windows)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Hello MySql Team.
I have found new bug again.
I need receive information about field lengths, and max_length for MYSQL_TYPE_TIMESTAMP after execute mysql_fetch_fields() equal 30 bytes.
However, after mysql_stmt_bind_result length for this field type sets to 36 bytes.
I think need set alignment to 1 byte in the mysql_tyme.c for MYSQL_TIME structure. 

libmysql.c: 
3958:    case MYSQL_TYPE_TIMESTAMP:
3959:      param->fetch_result= fetch_result_datetime;
3960:      *param->length= sizeof(MYSQL_TIME);
3961:      break;

How to repeat:
	if (!(m_stmt= mysql_stmt_init(m_mysql)))
		return false;

	if(mysql_stmt_prepare(m_stmt, m_sql, strlen(m_sql)) != 0)
		return false;

	m_prepare_meta_result = mysql_stmt_result_metadata(m_stmt);
		if(mysql_stmt_execute(m_stmt) != 0) return false;

		my_bool one= 1;
		if (mysql_stmt_attr_set(m_stmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*) &one) != 0)
			return false;
		if ( mysql_stmt_store_result(m_stmt) != 0)
			return false;

    MYSQL_FIELD *field= mysql_fetch_fields(m_prepare_meta_result);
    m_fieldcount = mysql_num_fields (m_prepare_meta_result);

		for(i = 0; i < m_fieldcount; i++)
		{
			m_fields[i].m_value = new char[field[i].max_length + 1]; // here alloc

			m_binds[i].buffer_type = field[i].type;
			m_binds[i].buffer = (char *)m_fields[i].m_value;
			m_binds[i].buffer_length = field[i].max_length + 1;
			m_binds[i].is_null = &m_fields[i].m_isnull;
			m_binds[i].length = &m_fields[i].m_length;
		}
		if (mysql_stmt_bind_result(m_stmt, m_binds) != 0)
			return false;
// found alloc size < than need for real value

Could you please provide your complete code's test case.

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Assuming you have a MYSQL_STMT, and MYSQL object, the following code reproduces the bug as long as there is a date, time or timestamp field in the query.

MYSQL * db = ??;
MYSQL_STMT * st = ??;

MYSQL_RES * res = mysql_stmt_result_metadata(st);
int fields = mysql_num_fields(res);

// Use any fixed length here, its not important
char bindBuffer[256];
unsigned long * bindLengths = new unsigned long[fields];

MYSQL_FIELD * field = mysql_fetch_fields(res);
for (int i=0;i<fields;++i)
{	
    int length = (field[i].max_length)?field[i].max_length:field[i].length;
    bind[i].buffer_length = length; 			
    bind[i].buffer_type = field[i].type;
    bind[i].buffer = bindBuffer;
    bind[i].length = &bindLengths[i]; 
}

mysql_stmt_bind_result(st,bind);

int state=0;
while ((state=mysql_stmt_fetch(st))==0)
{
    for (int i=0;i<fields;++i)
    {	
	assert(bindLengths[i] <= bind[i].buffer_length);
    }
}

Sorry, this occurs on version 5.0 !

Bug #22114 was marked as duplicate of this one.

Yes, it looks like sizeof(MYSQL_TIME) should be returned for TIMESTAMP column, and not a "display width" of 19 characters, or anything else.

This is a documentation issue.

Prepared statement API always returns the length of string representation in field->max_length. The rationale behind this is that unless your output buffers are strings, you can easily find out the maximum needeed buffer size by means of sizeof().

Below is the code in the client library that is responsible for max_length assignment for fixed size data types:

 case MYSQL_TYPE_TINY:
    param->pack_length= 1;
    field->max_length= 4;                     /* as in '-127' */
    break;
  case MYSQL_TYPE_YEAR:
  case MYSQL_TYPE_SHORT:
    param->pack_length= 2;
    field->max_length= 6;                     /* as in '-32767' */
    break;
  case MYSQL_TYPE_INT24:
    field->max_length= 9;  /* as in '16777216' or in '-8388607' */
    param->pack_length= 4;
    break;
  case MYSQL_TYPE_LONG:
    field->max_length= 11;                    /* '-2147483647' */
    param->pack_length= 4;
    break;
  case MYSQL_TYPE_LONGLONG:
    field->max_length= 21;                    /* '18446744073709551616' */
    param->pack_length= 8;
    break;
  case MYSQL_TYPE_FLOAT:
    param->pack_length= 4;
    field->max_length= MAX_DOUBLE_STRING_REP_LENGTH;
    break;
  case MYSQL_TYPE_DOUBLE:
    param->pack_length= 8;
    field->max_length= MAX_DOUBLE_STRING_REP_LENGTH;
    break;
  case MYSQL_TYPE_TIME:
    field->max_length= 15;                    /* 19:23:48.123456 */
    param->skip_result= skip_result_with_length;
  case MYSQL_TYPE_DATE:
    field->max_length= 10;                    /* 2003-11-11 */
    param->skip_result= skip_result_with_length;
    break;
    break;
  case MYSQL_TYPE_DATETIME:
  case MYSQL_TYPE_TIMESTAMP:
    param->skip_result= skip_result_with_length;
    field->max_length= MAX_DATE_STRING_REP_LENGTH;

http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html
http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-fields.html

The rational you have given does not make sense.  This is not a documentation issue, if anything it is an api design issue.

You cant have an api function called mysql_fetch_fields which was designed to return field definitions and at the same time expect users to use sizeof() for all non string types. 

Why is the constant MAX_DATE_STRING_REP_LENGTH set to 30, when the sizeof(MYSQL_TIME) is 36 bytes, or is it just a coincidence that for all other types the max_length happens to be creater than the sizeof() ?

Why is it not documented that max_length returns the string representation and not the "maximum length for the field", as currently stated.  

Why would you want the string representation length and not the field length anyway. What is the purpose of returning the string lengths in the MYSQL_FIELD structure and how do you propose that these would be used ?

I think your missing the point of the problem.  The MYSQL_FIELD data structure returns max_length's so that the user can allocate buffers which can be bound to MYSQL_BIND to receive the field data using mysql_stmt_fetch().

There must be a *consistent* way for the user to determine the maximum length of each field.  Why should the user assume that max_length is only valid for strings ?

It doesnt make sense that the user should jump through hoops to determine the size of the buffer required for strings but not be able to use the same approach for other types.

Dear Clarke, 
let me try to answer your questions.

> You cant have an api function called mysql_fetch_fields which was designed to
> return field definitions and at the same time expect users to use sizeof() for
> all non string types. 

mysql_fetch_fields is a function from 3.23 text protocol API and returns MYSQL_FIELD strcuture, which is also part of the 3.23 text protocol.
In 3.23 text protocol member max_length is defined to return the maximal buffer size that is needed to hold string, not binary, representation of a column.
Well, the whole concept of binary representation is not available in 3.23 text protocol.

This definition of max_length member is not changed by the prepared statements
protocol. Regardless of the protocol, max_length contains the maximal length
of string representation.

I agree that it is a valid request to have a member that contains length of `binary' representation. Unfortunately MYSQL_FIELD structure was not extended
with addition of the binary protocol.

> Why is the constant MAX_DATE_STRING_REP_LENGTH set to 30, when the
> sizeof(MYSQL_TIME) is 36 bytes, or is it just a coincidence that for all other
> types the max_length happens to be creater than the sizeof() ?

It is a coincidence. MAX_DATE_STRING_REP_LENGTH means (as the comment for this variable in the code clarifies) - maximal length of string representation of MYSQL_TIME.

> Why is it not documented that max_length returns the string representation and
> not the "maximum length for the field", as currently stated.  

This is a deficiency in our documentation that we will fix.

> Why would you want the string representation length and not the field length
> anyway. What is the purpose of returning the string lengths in the MYSQL_FIELD
> structure and how do you propose that these would be used ?

This field can be used if you would like to convert all columns to text format on retreival. In this case you can assign all MYSQL_BIND buffer_type's to 
MYSQL_TYPE_STRING and allocate buffers of sufficient size (the size is given in
max_length).

Note, that even if max_length contained binary length, you couldn't have 
simply assigned buffers of this length to MYSQL_BIND.buffer. Buffers that
will store integer types need to be properly aligned, and unless you allocate
every single buffer with an own "malloc" you risk getting an alignment error (SIGBUS).

> I think your missing the point of the problem.  The MYSQL_FIELD data structure
> returns max_length's so that the user can allocate buffers which can be bound > to MYSQL_BIND to receive the field data using mysql_stmt_fetch().

I have to say I perhaps do and your point is valid. But in any case this is broken-as-designed - that is, if you're indeed right, I missed the point when wrote this code. Still, changing the semantics of max_length depending on the protocol does not seem to be right to me - but I agree we could perhaps have a separate member that holds the binary length. And you can always unequivocally 
deduce the length of binary representation from the type of the C/C++ language type you specify for binding and its corresponding MYSQL_ type code.

> There must be a *consistent* way for the user to determine the maximum length 
> of each field.  Why should the user assume that max_length is only valid for
> strings ?

max_length is valid for all types if you set STMT_ATTR_UPDATE_MAX_LENGTH. But
currently it contains maximal length of string representation.
If you don't need this, you don't have to jump through hoops - the length
of binary representation of fixed-size objects does not change from record to record, so it has to be set only once, when you bind output buffers.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I have updated this section to indicate that max_length
refers to the string representation fo the values, and
that it is off by default for prepared statements and
must be enabled:

http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html

I have update this section to point out the distinction
between the values returned in max_length and the sizes
of the output buffers needed for prepared statement value
retrieval:

http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html