I'm trying to solve what seems to be quite an obscure problem, which I think might be a bug in either the mysqlnd driver or mysql, or could be on the PHP side. I'm hoping someone will be able to confirm if this is a bug, or if I'm doing something wrong (which is probably more likely!). I'm using php 5.6.11-1ubuntu3 and MariaDB/mysql version 10.0.20-MariaDB-0ubuntu0.15.04.1 on Ubuntu 15.10.
The code I'll post is proof of concept and isn't the original code I've had issues with - it is for illustrative purposes only, though it does run.
Please note, this is /not/ a problem calling stored procedures generally and getting the results. I know that a stored proc will return multiple results sets which need processing, and this is handled. The problem is specifically when a stored procedure uses a cursor.
I've created the following POC php code to call a stored procedure via a prepared statement in mysqli:
ini_set("display_errors", 1);
ini_set("display_startup_errors", 1);
ini_set("error_reporting", E_ALL);
ini_set("log_errors", true);
error_reporting("E_ALL");
/* Trying to find a solution to this bug:
* mysqli used to connect
* call stored proc via a prepared stmt. Proc contains a cursor.
* Connection is lost during processing, possibly at the point cursor is opened.
*/
function dbg()
{
foreach( func_get_args() as $a )
{
if( !is_string($a) ) $a = var_export($a, true);
echo "\n<br/>\nDBG: " . $a;
ob_flush();
flush();
}
}
function sdown()
{
dbg("SHUT DOWN");
}
function err($num, $err)
{
dbg("EH: $err");
}
register_shutdown_function("sdown");
set_error_handler(err);
try
{
test();
}
catch( Exception $e )
{
dbg("Exception caught: " . $r->getMessage());
}
function test()
{
dbg("Connecting...");
$db = new mysqli("localhost", "testuser", "testtesttest", "testdb");
dbg("Connected");
if( $db->connect_error )
throw new Exception("Failed connecting to DB server");
dbg("set charset");
$db->set_charset("utf8-bin");
$stmt = $db->stmt_init();
dbg("init");
$result = $stmt->prepare("call curTest(4,5)");
dbg("prepared");
$stmt->execute();
dbg("executed");
$stmt->store_result();
$stmt->bind_result($a, $b);
dbg("bound");
do {
dbg("About to fetch...");
$result = $stmt->fetch();
dbg("fetched");
dbg("RES:", $result);
if( $result == false )
{
dbg(" ** ERROR: " . $db->error);
exit;
}
dbg("Got vars:", $a, $b);
$result->free();
} while ($stmt->more_results() && $stmt->next_result());
}
The stored procedure being called looks like this:
create procedure curTest (AuthTok char(32), LogId char(11))
begin
declare vBC varchar(10);
declare vBID, done int unsigned;
declare cur1 cursor for select Id, BookingCode from Bookings;
declare continue handler for not found set done=true;
drop table if exists tBC;
create temporary table tBC (bid int unsigned, bc varchar(10));
insert into tBC (bid, bc) values (42, "foo");
open cur1;
set done = false;
read_loop: loop
fetch cur1 into vBID, vBC;
if done then
leave read_loop;
end if;
insert into tBC (bid, bc) values (vBID, vBC);
end loop;
close cur1;
select * from tBC;
drop table tBC;
end
When it runs the procedure, this output is displayed on screen. Note the EH line which tells me something serious has gone wrong with the protocol whilst talking to the server. I actually lose connection at this point, which isn't shown in this POC code. Also note that it is the very first "fetch" which goes wrong; this isn't a problem with "multiple result sets".
DBG: Connecting...
DBG: Connected
DBG: set charset
DBG: init
DBG: prepared
DBG: executed
DBG: bound
DBG: About to fetch...
DBG: EH: Packets out of order. Expected 1 received 5. Packet size=15
DBG: fetched
DBG: RES:
DBG: false
DBG: ** ERROR:
DBG: SHUT DOWN
If I run this from the mysql client, I receive the expected results:
MariaDB [hb]> call curTest(1,2);
+------+----------+
| bid | bc |
+------+----------+
| 42 | foo |
| 1 | PCRFXDVX |
+------+----------+
2 rows in set (0.07 sec)
Query OK, 0 rows affected (0.09 sec)
If the stored procedure is edited to only have the cursor open and close and no cursor loop, I still get the same error.
But if it is edited so that the cursor is not opened or closed at all, then it works fine:
create procedure curTest (AuthTok char(32), LogId char(11))
begin
declare vBC varchar(10);
declare vBID, done int unsigned;
declare cur1 cursor for select Id, BookingCode from Bookings;
declare continue handler for not found set done=true;
drop table if exists tBC;
create temporary table tBC (bid int unsigned, bc varchar(10));
insert into tBC (bid, bc) values (42, "foo");
-- open cur1;
--
-- set done = false;
-- read_loop: loop
-- fetch cur1 into vBID, vBC;
-- if done then
-- leave read_loop;
-- end if;
-- insert into tBC (bid, bc) values (vBID, vBC);
-- end loop;
--
-- close cur1;
select * from tBC;
drop table tBC;
end
This gives me a nice clean run:
DBG: Connecting...
DBG: Connected
DBG: set charset
DBG: init
DBG: prepared
DBG: executed
DBG: bound
DBG: About to fetch...
DBG: fetched
DBG: RES:
DBG: true
DBG: Got vars:
DBG: 42
DBG: foo
DBG: SHUT DOWN
I have googled a lot for this and keep finding dead ends. The closest matches I could find is where people experienced similar with PDO and fixed it by using the "emulate prepares" PDO setting. The feeling was that there is a driver problem. mysqli doesn't seem to have an equivalent setting.
So I'm wondering -
Has anyone here experienced this?
Is there a setting or a step I'm missing which would make this work?
Thanks very much in advance!
Thanks,
-Oli
