| 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: | |
| 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 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>
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.