Bug #15613 libmysqlclient API function mysql_stmt_prepare returns wrong field length
Submitted: 9 Dec 2005 0:38 Modified: 2 Feb 2006 4:58
Reporter: Rasmus Lerdorf Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.15, 5.0.18-BK OS:Linux (Linux)
Assigned to: Konstantin Osipov CPU Architecture:Any

[9 Dec 2005 0:38] Rasmus Lerdorf
Description:
To avoid a quick "this is a PHP bug" response here, the short version of this report is that PHP's mysqli extension is getting an incorrect field length back from MySQL's mysql_stmt_prepare() call.  I have looked through the code on the PHP side and I really don't see that we are doing anything wrong on our end.

If I have a table with this schema:

create table bugdemo
(
    name varchar(255) not null,
    value text character set utf8,

    primary key(name)
) type = InnoDb;

And I insert a string into the text field that is 21850 characters long consisting of 012345678901234567890...

A mysqldump correctly shows that the string is in there and using the ext/mysql extension I can correctly fetch all 21850 bytes out of that string.  However, when I use mysqli it fails because the mysql_stmt_prepare() call returns a stmt->stmt->fields[0] that looks like this:

(gdb) p *stmt->stmt->fields
$7 = {
  name = 0x874bd30 "value", 
  org_name = 0x874bd38 "value", 
  table = 0x874bd20 "bugdemo", 
  org_table = 0x874bd28 "bugdemo", 
  db = 0x874bd18 "test", 
  catalog = 0x874bd10 "def", 
  def = 0x0, 
  length = 21845, 
  max_length = 0, 
  name_length = 5, 
  org_name_length = 5, 
  table_length = 7, 
  org_table_length = 7, 
  db_length = 4, 
  catalog_length = 3, 
  def_length = 0, 
  flags = 16, 
  decimals = 0, 
  charsetnr = 8, 
  type = MYSQL_TYPE_BLOB
}

Where did this 21845 number come from?  It is 5 bytes too short and therefore in the mysqli extension we end allocating a buffer that is 5 bytes too small.  At smaller string lengths it works ok.  Is something internally in this code confusing bytes and characters or something?

The code for the PHP mysqli_prepare call looks like this:

...
    stmt = (MY_STMT *)ecalloc(1,sizeof(MY_STMT));

    if ((stmt->stmt = mysql_stmt_init(mysql->mysql))) {
        if (mysql_stmt_prepare(stmt->stmt, query, query_len)) {
...

at this point stmt->stmt->fields[0] contains this incorrect field length.  query and query_length are correct.

(gdb) p query
$8 = 0x874d4ec "select value from bugdemo where name = ?"
(gdb) p query_len
$9 = 40

How to repeat:
To reproduce with PHP and mysqli: 

<?php
    $dbc = new mysqli("localhost", "nobody", "", "test");
    $dbc->autocommit(false);
    $dbc->query("delete from bugdemo");
    $dbc->commit();
    /* insert a really long string */
    /* worked for up to 2184*10 chars, then breaks for larger */
    $longstring = "";
    for($i = 0; $i < 2185; ++$i)
        $longstring .= "0123456789";
    $name = "foo";

    print "length of longstring == ".strlen($longstring)."\n";
    $insert = $dbc->prepare("insert into bugdemo(name, value) values(?, ?)");
    $insert->bind_param("ss", $name, $longstring);
    $insert->execute();
    $dbc->commit();
    $select = $dbc->prepare("select value from bugdemo where name = ?");
    $select->bind_param("s", $name);
    $select->bind_result($value);
    $select->execute();
    $select->fetch();

    $length = strlen($value);
    print "length of value == $length\n";
    $errors = 0;

    if (strcmp($value, $longstring)) {
        print "The string values don't match\n";
        print "Original (last 20 bytes) ".substr($longstring,-20)."\n";
        print "From DB  (last 20 bytes) ".substr($value,-20)."\n";
    }
?>

For me this outputs:
length of longstring == 21850
length of value == 21850
The string values don't match
Original (last 20 bytes) 01234567890123456789
From DB  (last 20 bytes) 0123456789012345?

If we avoid the mysql_stmt_prepare and use the older API calls using this:

<?php
    $dbc = mysql_connect("localhost","nobody");
    mysql_select_db("test");
    mysql_query("delete from bugdemo");

    /* insert a really long string */
    /* worked for up to 2184*10 chars, then breaks for larger */
    $longstring = "";
    for($i = 0; $i < 2185; ++$i)
        $longstring .= "0123456789";
    $name = "foo";

    print "length of longstring == ".strlen($longstring)."\n";

    mysql_query("insert into bugdemo(name, value) values('$name', '$longstring')");
    $res = mysql_query("select value from bugdemo where name = '$name'");
    $row = mysql_fetch_row($res);
    $value = $row[0]; 
    $length = strlen($value);
    print "length of value == $length\n";
    $errors = 0;

    if (strcmp($value, $longstring)) {
        print "The string values don't match\n";
        print "Original (last 20 bytes) ".substr($longstring,-20)."\n";
        print "From DB  (last 20 bytes) ".substr($value,-20)."\n";
    }
?>

It works just fine.
[9 Dec 2005 13:59] Valeriy Kravchuk
Thank you for a bug report. Looks like the problem is with 5.0.x C API really. Verified just as described with your first PHP script, with 2 lines added at the end:

    printf("Client library version: %s\n", mysqli_get_client_info());
    printf("Server version: %s\n", $dbc->server_info);

[openxs@Fedora 4.1]$ php ../5.0/15613_1.php
Content-type: text/html
X-Powered-By: PHP/5.0.5

length of longstring == 21850
length of value == 21850
The string values don't match
Original (last 20 bytes) 01234567890123456789
From DB  (last 20 bytes) 012345678901234556й▼
Client library version: 5.0.18
Server version: 4.1.17
[17 Jan 2006 21:52] Konstantin Osipov
/* 
  Bug #15613: "libmysqlclient API function mysql_stmt_prepare returns wrong
  field length"
*/ 
   
static void test_bug15613()
{  
  MYSQL_STMT *stmt;
  const char *stmt_text;
  MYSQL_RES *metadata;
  MYSQL_FIELD *field;
  int rc;
  myheader("test_bug15613");
   
  /* I. Prepare the table */
  mysql_query(mysql, "drop table if exists t1");
  mysql_query(mysql, "create table t1 (a text character set utf8)");
  rc= mysql_query(mysql,
                  "insert into t1 (a) values (repeat('0123456789', 2185))");
  myquery(rc);
  
  stmt= mysql_stmt_init(mysql);

  /* II. Check SELECT metadata */
  stmt_text= ("select a from t1");
  rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
  metadata= mysql_stmt_result_metadata(stmt);
  field= mysql_fetch_field_direct(metadata, 0);
  if (!opt_silent)
    printf("Field length: %lu\n", field->length);
  DIE_UNLESS(field->length >= 21850);

  /* III. Cleanup */
  rc= mysql_query(mysql, "drop table t1");
  mysql_stmt_close(stmt);
}
[17 Jan 2006 23:03] Konstantin Osipov
The returned value is wrong and does not depend on the length of the inserted data.
It's always the same. It seems that pack_flag is calculated wrongly when creating the table.
[18 Jan 2006 19:50] Bugs System
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/commits/1294
[18 Jan 2006 19:58] Konstantin Osipov
Rasmus, Georg,
the bug will be fixed and the server will return the maximum possible number of characters a TEXT field can occupy. However, allocating this amount of bytes is not recommended. E.g. for MEDIUMTEXT column, the value is equal to 16M, for LONGTEXT it's 2^32.
This is a shortage of the API: there is no way to know in advance the actual amount of bytes a BLOB/TEXT field occupies. One has to submit a MYSQL_BIND buffer with initialized 'buffer_length' and 'length'  members, and then call mysql_stmt_fetch: if 'buffer_length' is not enough to store the field, its actual length will be written to '*length'.
You're welcome to submit a feature request that will address this shortcoming.
[19 Jan 2006 6:58] Alexander Barkov
Looks ok to push for me.
[19 Jan 2006 22:07] Konstantin Osipov
Fixed in 4.1 tree, tagged 4.1.18, pushed into 5.0 tree, tagged 5.0.19
[26 Jan 2006 3:01] Mike Hillyer
Ok, where did it wind up? .17 or .18?
[26 Jan 2006 11:31] Konstantin Osipov
.18
[2 Feb 2006 4:58] Mike Hillyer
Documented in 5.0.18 changelog:

 <listitem>
        <para>
          <literal></literal> API function
          <literal>mysql_stmt_prepare</literal> returned wrong
          field length for TEXT columns. (Bug #15613)
        </para>
      </listitem>