php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #57109 Calling stored procedure several times
Submitted: 2006-06-23 13:30 UTC Modified: 2008-09-12 11:06 UTC
From: stasb at solidcactus dot com Assigned:
Status: Closed Package: PDO_MYSQL (PECL)
PHP Version: 5.1.2 OS: Windows XP
Private report: No CVE-ID: None
View Developer Edit
 [2006-06-23 13:30 UTC] stasb at solidcactus dot com 
Description:
------------
information from phpinfo();

PHP Version 5.1.4

MySQL Support enabled 
Active Persistent Links  0  
Active Links  0  
Client API version  5.0.21  
PDO support enabled 
PDO drivers  sqlite, mysql, sqlite2  
pdo_mysql
PDO Driver for MySQL, client library version 5.0.21 

Exception happening when calling several stored procedure or query after store procedure execution.

When calling MySQL Stored Procedure, MySQL returns several rowset and probably that's why PDO driver for MySQL has problem. It works fine if just SELECT statements would be used.

After Exception (Lost connection to MySQL ...) any attempts to connect to database will throw new exception :

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in 

Thanks,
Stanislav Beletsky

Reproduce code:
---------------
/*


DELIMITER $$;

DROP PROCEDURE IF EXISTS `sccc`.`proc_Testing`$$

CREATE PROCEDURE `proc_Testing`()
BEGIN


	SELECT 1;


END$$

DELIMITER ;$$


*/



$conn=new PDO("mysql:host=192.168.2.127;dbname=sccc","","",array(PDO::ATTR_PERSISTENT=>true));

$conn->setAttribute(PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION);

$comm=$conn->query("CALL proc_Testing()");

$comm->execute();					
				
$comm->fetchAll();



$comm->closeCursor();
$comm=null;				

//actually any SQL query will throw exception
$comm=$conn->query("CALL proc_Testing()");

$comm->execute(); 

$comm->fetchAll();
$comm->closeCursor();
$comm=null;

//<-- Exception 
//Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query' in 

Expected result:
----------------
No errors

Actual result:
--------------
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query' in 

or 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in 

after first one

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-07-03 02:39 UTC] jprice at netspace dot net dot au 
I can also reproduce this bug every time using PHP 5.0.5 onward (up to 5.1.4) and MySQL 5.0.15.

I've also tried using the Zend Frameworks implementation of pdo which did not help either.

In addition, the MySQL logfile says: [Warning] Aborted connection 3 to db: 'test' user: 'root' host: 'localhost' (Got an error reading communication packets)

PHP can perform several dozen operations on the database but after a StoredProcedure is called, this error is immediately encountered on the next call.
 [2006-08-07 11:24 UTC] webvelosix at yahoo dot com 
PHP Version 5.2.0RC2-dev Build 2006-08-07
MySQL Client API library version: 5.0.22

In regards to the above test, I can reproduce the bug on two different workstations running Windows XP and 2000.

Calling the test code through the browser initially succeeds; when the page is refreshed two or more times, the bug randomly appears. Cannot reproduce it from the CLI, however.
 [2006-10-03 05:21 UTC] mehran20 at hotmail dot com 
Me too.
I mean I've faced the same problem and I hope you guys do something about it soon because I've based my project on this capability and I'm not feeling so good right now!!
The descriptions are enough, I believe. The only thing I can add to it is that I've tested this on PHP 5.1.6 (lastes version at the moment) and MySQL Client API version 5.0.24a and it was on WinXP SP1 & SP2.
And I'm sure that it's not MySQL's prolem since I've executed the same SQL on mysql.exe (command prompt terminal) and it worked just fine.
Oh btw, I've tested the same code with mysqli and mysql_* functions. As for mysqli, the same problem remains but for mysql_* functions, I could not execute the SQL becuase it said that the result can't be recognized or something! I believe that these functions can not execute stored procedures!
 [2006-10-11 09:25 UTC] mehran20 at hotmail dot com 
Hi,
I just wanted to add that I've seen the other two bug reports that were discussing the same problem:

http://pecl.php.net/bugs/bug.php?id=5827
http://pecl.php.net/bugs/bug.php?id=7457

And I've tested all the solutions that they proposed but yet I'm having the problem. Passing on the closeCursor and setting null the $statement, in the first report it was mentioned to use $stmt->nextRowset() which produces the following exception (I have ommited some of the useless lines):

===========================================================
[message:protected] => SQLSTATE[HYC00]: Optional feature not implemented
[code:protected] => HYC00
[trace:private] => Array
(
    [0] => Array
        (
            [function] => nextRowset
            [class] => PDOStatement
            [type] => ->
            [args] => Array
                (
                )

        )

)
===========================================================

And as for the second one, it seems that the final solution is to use MySQL Client API version 5.0.22, well I've tested this version and 5.0.24a and through both of them I could reproduce the error!!
And Finally I test setting PDO::ATTR_EMULATE_PREPARES, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY. This way the result was a bit better but yet, time to time, it would give the following exception:

===========================================================
[message:protected] => SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query
[code:protected] => HY000
[trace:private] => Array
(
    [0] => Array
        (
            [function] => query
            [class] => PDO
            [type] => ->
            [args] => Array
                (
                    [0] => call test()
                )

        )

)
===========================================================

As I said before, this is not happenning all the time. Mostly it's working fine but once in 10 times it would happen, which is not acceptable, at least to me.
All of these are tested with PHP 5.1.6 and MySQL Client API version 5.0.24a and MySQL Server version 5.0.24a-community and it was all in WinXP.
I hope there's someone who can help me out because I really need this one..

Mehran Ziadloo
 [2006-12-09 10:32 UTC] mike at we11er dot co dot uk 
I have confirmed this bug on the following versions. These are all under Windows XP.

php 5.2 official, with mysql 5.0.27 (using libmysql.dll from php's install, and from mysql's install)


php 6.0 cvs snapshot as of 9:30 this morning, because a pdo dev told me they fixed some things. (using libmysql.dll from php's install, and from mysql's install)

Same as above, but with mysql 5.0.22 (using libmysql.dll from php's install, and from mysql's install)

When using MySQL's libmysql.dll I get the error:



When using PHP's libmysql.dll I get:

General error: 2013 Lost connection to MySQL server during query

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other
unbuffered queries are active.
 [2007-02-07 04:29 UTC] denis dot podgurskiy at cofelab dot ru 
Hi there
This problem can be resolved - see http://bugs.php.net/bug.php?id=39858. I have described how to use PDO_ODBC instead PDO_MYSQL on windows (but still PDO_MYSQL on Nix) and this approach works with SPs and transaction.
Good luck, Denis
 [2008-09-12 11:06 UTC] johannes at schlueters dot de 
This bug has been fixed in CVS.

In case this was a documentation problem, the fix will show up at the
end of next Sunday (CET) on pecl.php.net.

In case this was a pecl.php.net website problem, the change will show
up on the website in short time.
 
Thank you for the report, and for helping us make PECL better.

Use mysqlnd instead of libmysql ((--with-pdo-mysql=mysqlnd) with PHP 5.3 and the issue is fixed (Windows builds use mysqlnd)
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sun Dec 22 06:01:30 2024 UTC