MySQL Bugs: #12070: Cannot bind new VARCHAR result column

Bug #12070	Cannot bind new VARCHAR result column
Submitted:	20 Jul 2005 19:56	Modified:	2 Mar 2006 2:04
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Embedded Library ( libmysqld )	Severity:	S2 (Serious)
Version:	5.0	OS:
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Using libmysqld, cannot bind to result column that is new VARCHAR type; raises error "Using unsupported buffer type: 15".  Regular server seems unaffected.

How to repeat:
#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"

const char *args[] =
{
  "unused for embedded",
  "--defaults-file=my.cnf"
};

const char *server_groups[] =
{
  "test-server", "embedded", "server", NULL
};

int main()
{
  MYSQL* m;
  MYSQL_STMT* s;
  MYSQL_BIND r[1];
  char c1buff[ 100 ];
  my_bool c1null;
  unsigned long c1len;
  char *sql= "SELECT a FROM t1";

  mysql_server_init(
                    sizeof(args)/sizeof(char *),
                    (char**)args,
                    (char**)server_groups
                    );
  m= mysql_init(NULL);
  mysql_real_connect(m,NULL,NULL,NULL,NULL,0,NULL,0);
  mysql_query(m, "CREATE DATABASE IF NOT EXISTS test");
  mysql_query(m, "USE test");
  mysql_query(m, "DROP TABLE IF EXISTS t1");
  mysql_query(m, "CREATE TABLE t1 ( a VARCHAR(10) )");
  mysql_query(m, "INSERT INTO t1 (a) VALUES ('ABCDEFG')");
  s= mysql_stmt_init(m);
  mysql_stmt_prepare(s, sql, strlen(sql));
  mysql_stmt_execute(s);
  memset(r, 0, sizeof(r));
  r[0].buffer_type= MYSQL_TYPE_VAR_STRING;
  r[0].buffer= &c1buff;
  r[0].buffer_length= 100;
  r[0].is_null= &c1null;
  r[0].length= &c1len;
  mysql_stmt_bind_result(s,r);
  puts(mysql_stmt_error(s));
 /* Using unsupported buffer type: 15 (parameter: 1) */
  mysql_stmt_close(s);
  mysql_close(m);
  mysql_server_end();
}

Suggested fix:
n/a

I ran in to this problem on 5.0.13rc (custom compile for QNX 6.3.1, not that it turned out to be relevant).  I tracked this problem to a change in the client-server protocol that hadn't been mirrored in the embedded server protocol: in Protocol::send_fields (protocol.cc) there was an explicit switch of MYSQL_TYPE_VARCHAR to MYSQL_TYPE_VAR_STRING when sending fields to the client.  Protocol::send_fields (lib_sql.cc) did not perform this step.   Copying the code across seems to resolve the issue. I did not perform a deeper analysis of any other changes to the client-server protocol that might need to be replicated on the embedded side.

I've attached a patch with this fix.

Well, that serves me right to comment without clicking 'attach file' first.  Sorry!  I'm not sure what the policy is here - should I post a diff inline?

Hi.
I can verify that I have tried Will Miles' suggested fix to lib_sql.cc and this indeed worked.  I hope this gets integrated for 5.0 release!

This workaround also worked from me. However, it seems to crash the system when a field from type VARCHAR is returned. For example "SHOW TABLES LIKE...". Any Idea how to got over it?

Noted in 5.0.19 changelog.

The embedded server did not allow binding of columns to the
<literal>MYSQL_TYPE_VAR_STRING</literal> data type in prepared
statements. (Bug #12070)