| Bug #14993 | Calling MySQL5 stored procedures multiple times from PHP5 | ||
|---|---|---|---|
| Submitted: | 16 Nov 2005 19:20 | Modified: | 3 Feb 2006 14:21 |
| Reporter: | Jared Williams | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.15-nt-max | OS: | Windows (Windows 2000) |
| Assigned to: | CPU Architecture: | Any | |
[16 Nov 2005 19:20]
Jared Williams
[15 Dec 2005 8:09]
Eric Hsu
hi all, I'm having the same problem. Me and my friend are developing a web app. He is developing under windows 2003; I'm developing under gentoo linux; we have our source controled via subversion; our script connects to his windows mysql 5.0.16. I have a php script which calls one stored procedure after another, the script works ok under my linux which is running apache 2.0.55, php 5.0.5 and pecl-pdo 0.9. However, when he sync the code to his windows and run, the browser's connection was reset by apache every time the script ran. He's running windows version apache 2.0.55, official php 5.1.1 with pdo bundled. Then, I tried to downgrade his php to 5.0.5, and download the latest pdo/pdo_mysql from http://pecl4win.php.net/, then, the PDOException was captured correctly and got the "Lost connection to MySQL server during query" error, no more connection reset between the browser and apache. That enlightened me a bit, then I download the latest pdo/pdo_mysql for php 5.1.1, overwrite the bundled pdo/pdo_mysql files, then, no more connection reset, instead, the PDOException was triggered and I got the "Lost connection to MySQL server during query". So, I bet the pdo/pdo_mysql bundled with php 5.1.1 (for win) has bug while dealing with this lost connection error, the PDOException couldn't be thrown correctly and causes apache reset the script's connection. I'm not sure if this is mysql's bug. Since with the apache 2.0.55 and php 5.0.5 with pecl-pdo 0.9 under my gentoo box, the script runs without any problem. But with windows version apache 2.0.55, php 5.0.5/5.1.1 with lastest pdo/pdo_mysql, the script get the "Lost connection" error everytime it runs! Remember, we're connecting to the same mysql server (runs under my friends win2003) with the same php script. One works, the other not.
[15 Dec 2005 10:35]
Eric Hsu
update: I setup mysql server 5.0.16 on my gentoo linux box, then ran the script from my friend's win2003, got the "Lost connection" error; Ran the script from my linux, the browser pop up a filedownload dialog asking me to download my script! After the the script was saved, it was only a blank page. Then I read the apache's log and find these: [Thu Dec 15 18:26:50 2005] [notice] child pid 9582 exit signal Segmentation fault (11) [Thu Dec 15 18:26:50 2005] [notice] child pid 9583 exit signal Segmentation fault (11) [Thu Dec 15 18:27:53 2005] [notice] child pid 9567 exit signal Segmentation fault (11) [Thu Dec 15 18:27:53 2005] [notice] child pid 9569 exit signal Segmentation fault (11)
[15 Dec 2005 10:43]
Eric Hsu
To conclude what I've found: It works only if I connect my php script from my gentoo linux to my friend's windows mysql. oh no...
[19 Dec 2005 8:37]
Eric Hsu
guys,
I found the workaround, don't use persistent connection, reconnect mysql each time before you send the query:
$dbh = new PDO(...);
$stmt = $dbh->prepare('call foo()');
$stmt->execute();
$dbh = new PDO(...);
$stmt = $dbh->prepare('call bar()');
$stmt->execute();
No more "Lost connection.." error.
However, I got report from user that mysql crashes and auto restarts frequently if I use this workaround. It' s really a headache.
[29 Jan 2006 19:18]
Thilo Raufeisen
I´ve got the same problem. First call to a stored procedure works fine, second call fails with a "lost connection". I´ve tested this with PHP 5.1.2 and PDO. Mysqld 5.0.18 If I add a "sleep (1)" between the calls, it works. The "client" is a centos-server with the official mysql-binaries. PHP was build on this machine so that it is using the "correct" clientlibs. The "server" is an ubuntu-testserver with packages from backports.org. However, I´ve tested the same with another centos-server which is using the official binaries from mysql.com With the centos-server everything is fine. No "lost connection"-errors. Also if the client is the ubunut-testserver and the server is a centos-server everything works fine. To sum it up: centos -> ubuntu = bad centos -> centos = good ubuntu -> centos = good Seems like a problem of the mysqld-build of the server. Anyway a bit strange, that a custom build produces such an error.
[2 Feb 2006 19:45]
Hartmut Holzgraefe
actually not a mysql bug at all, the connection is terminated due to the fact that a new query is sent before the results of the previous one are completely processed the actual issue is that a call to a stored procedure produces multiple result sets, one for the status of the call itself and one for every query issued within the procedure that returns results to the outside world so when processing a procedure call result you have to use $stmt->nextRowset() to iterate over the result sets returned http://php.net/pdostatement-nextrowset when using ext/mysqli instead of PDO::mysql you have to use the more_results() and next_result() methods for this: http://php.net/mysqli_more_results http://php.net/mysqli_next_result the initial example in this bug report needs to be rewritten like this: <?php $pdo = new PDO('mysql:localhost;dbname=pdotest', 'root', ''); $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); $stmt = $pdo->query('CALL pdotest.spIsStringComparisonCaseInsensitive()'); do { foreach($stmt as $row) { echo $row['isInsensitive']; } } while ($stmt->nextRowset()); $stmt2 = $pdo->query('CALL pdotest.spIsStringComparisonCaseInsensitive()'); do { foreach($stmt2 as $row) { echo $row['isInsensitive']; } } while ($stmt2->nextRowset()); ?> i agree that PDO might try to handle this more gracefully, but this is not a mysql issue, so i will close this bug report and will comment on the PECL bug entry instead
