Description:
We have found that during execution of a PS which calls a SP, if there are more than one result sets from the SP, excluding the status result set, then after the first one the Server switches from the binary protocol, which is normal for a PS execution to the text protocol.
The flaw can't be reproduced with libmysql, as libmysql doesn't support execution of a PS which calls a SP that returns multiple results. mysqlnd, the mysql native driver for PHP supports it because the server supports it. The protocol switch doesn't happen always but in some cases and we have a test case.
On my machine the bug happens when testing from Linux with MySQL running on Windows. Johannes reported Linux->Linux problems too.
Here is network dump, from my machines, done with wireshark. There are 2 packets shown in Wireshark after the PS execution. I am copying from the second one, which contains the problem.
MySQL on Linux, everything is fine:
0040 3f a4 01 00 00 06 01 26 00 00 07 03 64 65 66 04 ?......&....def.
0050 74 65 73 74 04 74 65 73 74 04 74 65 73 74 02 69 test.test.test.i
0060 64 02 69 64 0c 3f 00 0b 00 00 00 03 00 00 00 00 d.id.?..........
0070 00 05 00 00 08 fe 00 00 0a 00 06 00 00 09 00 00 ................
0080 d2 04 00 00 05 00 00 0a fe 00 00 0a 00 .............
0040 96 97 01 00 00 06 01 26 00 00 07 03 64 65 66 04 .......&....def.
0050 74 65 73 74 04 74 65 73 74 04 74 65 73 74 02 69 test.test.test.i
0060 64 02 69 64 0c 3f 00 0b 00 00 00 03 00 00 00 00 d.id.?..........
0070 00 05 00 00 08 fe 00 00 2a 00 05 00 00 09 04 31 ........*......1
0080 32 33 34 05 00 00 0a fe 00 00 2a 00 234.......*.
First comes the metadata. Then there is an EOF packet (0xFE status), on the forth line, which is 5 bytes long - 0x05 before 0xFE, 2A is the server status. Then comes the data. On linux we seen a 6 byte packet, Windows shows 5 bytes. The packet number is 0x09, in both cases, because it's the 9th packet. Then comes the real data. On Linux we see 0x00, which is correct for the binary protocol, showing no errors. Then comes one byte for encoding NULL values, we have none (the formula is ((field_count + 9) div 8) bytes) and then we see D204, which is 0x4D2, which is 1234. If we look the Windows Server dump, then we see 0x31 0x32 0x33 0x34, which is 1234 ASCII encoded (seen on the right too). The 0x4 before 0x31 is there to show that this is a string with length 4 bytes, although the column is an int, and in the binary protocol is sent encoded as int, 4 bytes, as seen in the Linux Server dump.
How to repeat:
You will need PHP 5.3-dev, because it comes with mysqlnd. Downloadable from http://snaps.php.net . Either take
- (PHP 5.3, win32)
or
- get PHP sources, unpack them and
-- run ./configure --with-mysqli=mysqlnd && make
-- then in sapi/cli/php you have a binary to use for testing
<?php
$HOST = "192.168.1.114";
// $HOST = "127.0.0.1";
$USER = "root";
$PASS = "root";
$DB = "test";
$c = mysqli_connect($HOST, $USER, $PASS, $DB);
$c->query('DROP TABLE IF EXISTS test');
$c->query('CREATE TABLE test(id INT)');
$c->query('INSERT INTO test VALUES (1234)');
$c->query('DROP PROCEDURE IF EXISTS p');
$c->query('CREATE PROCEDURE p() BEGIN SELECT id FROM test; SELECT id FROM test; END;');
/* call in the first connection is needed */
$c->query("CALL p()");
/* close and open new one, or it is not repeatable.
once structs are created and one call is done,
this init phase can be skipped
*/
$c->close();
$c = mysqli_connect($HOST, $USER, $PASS, $DB);
$s = $c->prepare("CALL p()");
$s->execute();
do {
$s->bind_result($a);
while ($s->fetch()) {
var_dump($a);
}
} while ($s->next_result());
?>
Output here:
int(1234)
int(3420978)
