php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #56622 Calling MySQL5 stored procedures multiple times
Submitted: 2005-10-30 20:44 UTC Modified: 2006-04-09 02:50 UTC
From: Jared dot Williams1 at ntlworld dot com Assigned: wez
Status: Closed Package: PDO_MYSQL (PECL)
PHP Version: 5_1 CVS-2005-10-30 (dev) OS: Win2000
Private report: No CVE-ID:
 [2005-10-30 20:44 UTC] Jared dot Williams1 at ntlworld dot com
Description:
------------
Calling a MySQL5.0.15 stored procedure twice causes 

Warning: PDO::query() [function.query]: SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query in ...\pdotest.php on line 14

Reproduce code:
---------------
CREATE DATABASE pdotest;
DELIMITER $$
CREATE PROCEDURE `pdotest`.`spIsStringComparisonCaseInsensitive` ()
BEGIN
     SELECT 'a' = 'A' AS isInsensitive;
END $$
DELIMITER ;

------

$pdo = new PDO('mysql:localhost;dbname=pdotest', 'root', '');
	$pdo->setAttribute(PDO_ATTR_ERRMODE,PDO_ERRMODE_WARNING);
	$stmt = $pdo->query('CALL pdotest.spIsStringComparisonCaseInsensitive()');

	foreach($stmt as $row)
	{
		echo $row['isInsensitive'];
	}
/* Line below causeing the warning */
	$stmt2 = $pdo->query('CALL pdotest.spIsStringComparisonCaseInsensitive()');
	foreach($stmt2 as $row)
	{
		echo $row['isInsensitive'];
	}

Expected result:
----------------
print 1 twice, without a warning.

Actual result:
--------------
Print 1 followed by

Warning: PDO::query() [function.query]: SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query in ...\pdotest.php on line 14

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-11-16 01:52 UTC] wez@php.net
Try closing the first statement out before kicking off the second:

$stmt = null;
 [2005-11-16 05:58 UTC] Jared dot Williams1 at ntlworld dot com
Doesn't fix it. 

Updated code, for PHP5.1RC5-dev

<?php

	$pdo = new PDO('mysql:localhost;dbname=pdotest', 'root', '');
	$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
	$stmt = $pdo->query('CALL pdotest.spIsStringComparisonCaseInsensitive()');

	foreach($stmt as $row)
	{
		echo $row['isInsenstive'];
	}
	$stmt = NULL;


/* Line below causeing the warning */
	$stmt2 = $pdo->query('CALL pdotest.spIsStringComparisonCaseInsensitive()');
	foreach($stmt2 as $row)
	{
		echo $row['isInsenstive'];
	}
?>

Also tried this with mysqli, seem to suffer from the same problem.
 [2005-11-16 10:13 UTC] wez@php.net
Sounds like a mysqlclient or server side issue to me.
Can you contact the mysql folks and see what they say?
 [2005-11-16 14:23 UTC] Jared dot Williams1 at ntlworld dot com
Posted bug report on MySQL after reading http://lists.mysql.com/mysql/189351 

http://bugs.mysql.com/bug.php?id=14993
 [2005-11-24 16:34 UTC] wez@php.net
Suspending pending confirmation that it is a mysql bug.
 [2006-02-02 15:06 UTC] hholzgra@php.net
the problem simply is that a procedure call always produces
an additional result set with error information (which is usually 
empty but still it is always sent)

so when processing a procedure call result you have to use $stmt->nextRowset()
to iterate over the result sets returned

executing a new query before the results from the previous one
are completely processed is not allowed by the mysql client
protocol, and as pdo seems to be using mysql_use_result()
it runs into issues here as it doesn't ensure that all results have
been processed first when accepting a new query
 [2006-03-25 06:51 UTC] antimon at gmail dot com
Any update on this?
Well, it is not a mysql bug.
 [2006-04-09 02:50 UTC] wez@php.net
The closeCursor() implementation in PDO_MYSQL did not handle multiple result sets (fixed now in CVS).
And neither did the statement dtor (also fixed now in CVS).

You either need to set the $stmt to null in between queries, or call $stmt->closeCursor().

This does not happen implicitly at the end of iterating over a result set (by design): you don't want to mix different result sets.
 [2006-04-22 08:27 UTC] info at codexp dot net
This does not seem to be fixed. I have tried the latest snapshot for Win32 ( http://snaps.php.net/win32/php5.1-win32-200604221030.zip ) 
And the problems still appears.
Getting exactly the same error as the reporter described.
Setting $stmt = null; and/or using $stmt->closeCursor(); does not solve the problem.

And as i have seen: calling several different SP is important :) Any further help on this matter?
 [2006-06-16 12:40 UTC] cap at unagon dot com
Me too...in a different case.

I am using persistent PDO connetions and I get the same error message but with some differences:

1) I only get the error randomly (approx. every 20th call of the script) and when there is high load on the server.
2) I get the error in the prepare statement, not in the execute statement
3) nextRowset and closeCursor does not change anything. 

My present workaround is not to use persistent PDO connections - then the code works fine.

Thus, I guess the error is somewhere inside of the connection pooling code of PDO.

Here my stuff:

try {
   $selectDBH = new PDO('mysql:host='.$host.';dbname='.$db, $selectuser, $selectpass, array(PDO::ATTR_PERSISTENT => true));
    $selectDBH->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

    $sth = NULL; 
    $sth  = $selectDBH->prepare('SELECT COUNT(1) FROM NOTES WHERE RKEY=?');
    $sth->execute(array($locationP)); 
    $resuNumP = $sth->fetchAll(PDO::FETCH_NUM);
    do {} while ($sth->nextRowset());   $sth->closeCursor();
 [2006-08-11 17:43 UTC] roger at synergex dot com
WEll I see it on the C interface too, it dies in the prepare statement - and is load dependant - but can be reproduced on windows and linux with just one user however.
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Sun Apr 20 22:01:56 2014 UTC