PHP Apache 在执行存储过程时崩溃 [英] PHP Apache crashes while executing a STORED PROCEDURE

问题描述

当我执行以下代码时(我正在调用一个带有 5 个 IN 参数和 1 个 OUT 参数的存储过程)

When I execute the following code (I'm calling a stored procedure with 5 IN parameters and 1 OUT parameter)

$conn->query("SET @res = ''");

$mysqli=$conn;
if (!($stmt = $mysqli->prepare("CALL retrieve_matches(5,3, 16, 2, false, @res)"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

do {
    if ($res = $stmt->get_result()) { //Apache crash on this call
        printf("---\n");
        var_dump(mysqli_fetch_all($res));
        mysqli_free_result($res);
    } else {
        if ($stmt->errno) {
            echo "Store failed: (" . $stmt->errno . ") " . $stmt->error;
        }
    }
} while ($stmt->more_results() && $stmt->next_result());

apache 因错误而崩溃:

apache is crashing with error:

AH00428:父进程:子进程 9628 退出，状态为 255 --重新启动.

AH00428: Parent: child process 9628 exited with status 255 -- Restarting.

我尝试了什么

1. 此代码运行良好，并正确返回结果:

$conn->query("SET @res = ''");
$res=$conn->query("CALL retrieve_matches(5,3, 16, 2, false, @res)");
var_dump($res->fetch_assoc());

注意:如果我只是更改存储过程输入中的数字，则上面使 Apache 崩溃的相同代码可以正常工作，例如:

NOTE: The same code above that make Apache crashing is working correctly if I just change the number in input of the stored procedure like:

if (!($stmt = $mysqli->prepare("CALL retrieve_matches(5,6, 16, 2, false, @res)"))) {

1. 在 MySQl 工作台上尝试过，两个调用都正常工作.
2. 我在 WIN7 Enterprise 64b 上使用 WAMP 64b，我尝试使用 WAMP 32b，但遇到了同样的问题.
3. 我检查了 windows 事件，发现 httpd.exe 崩溃是由 php5ts.dll 引起的
4. 如果你在谷歌上搜索httpd.exe php5ts.dll"，你会发现很多人遇到了这个问题.但是我没有找到 WAMP 的解决方案...
5. 使用 AMPPS 尝试过，完全相同的问题

PHP 错误日志

[10-Jul-2015 15:30:03 UTC] PHP Warning:  PHP Startup: Unable to load dynamic library 'C:/Program Files/wamp/bin/php/php5.5.12/ext/php_ldap.dll' - Impossibile trovare il modulo specificato.

 in Unknown on line 0

[10-Jul-2015 15:30:04 UTC] PHP Warning:  PHP Startup: Unable to load dynamic library 'C:/Program Files/wamp/bin/php/php5.5.12/ext/php_intl.dll' - Impossibile trovare il modulo specificato.

APACHE 错误日志:

APACHE error log:

[Tue Jul 14 15:02:13.038276 2015] [mpm_winnt:notice] [pid 7044:tid 404] AH00428: Parent: child process 9448 exited with status 255 -- Restarting.
[Tue Jul 14 15:02:13.324305 2015] [mpm_winnt:notice] [pid 7044:tid 404] AH00455: Apache/2.4.9 (Win32) PHP/5.5.12 configured -- resuming normal operations
[Tue Jul 14 15:02:13.329306 2015] [mpm_winnt:notice] [pid 7044:tid 404] AH00456: Apache Lounge VC11 Server built: Mar 16 2014 12:13:13
[Tue Jul 14 15:02:13.329306 2015] [core:notice] [pid 7044:tid 404] AH00094: Command line: 'C:\\Program Files\\wamp\\bin\\apache\\apache2.4.9\\bin\\httpd.exe -d C:/Program Files/wamp/bin/apache/apache2.4.9'
[Tue Jul 14 15:02:13.352308 2015] [mpm_winnt:notice] [pid 7044:tid 404] AH00418: Parent: Created child process 3140
[Tue Jul 14 15:02:14.528388 2015] [mpm_winnt:notice] [pid 3140:tid 332] AH00354: Child: Starting 64 worker threads.

我真的迷路了，我应该去哪里找问题?非常感谢您的帮助

I'm really lost here, where should I look for the issue? Thanks very much for your help

编辑

我意识到存储过程retrieve_matches"正在调用不同的存储过程以改变值的函数.我调试了导致 Apache 崩溃的存储过程retrieve_standalone".这个程序正在做选择/插入，我检查过并且插入是正确的.但是在retrieve_standalone"中，我以一种奇怪的方式使用游标:

I realized that the stored procedure "retrieve_matches" is calling different stored procedure in function of the changed value. I debugged the stored procedure "retrieve_standalone" that is the one that make Apache crashing. This procedure is doing select/insert, I checked and the insert are made correctly. BUT inside "retrieve_standalone" I'm using a cursor in a weird way:

declare bNoMoreRows bool default false;
declare tmp_cursor cursor for
    select comp_id from to_match; -- to_match is a temporary table
declare continue handler for not found set bNoMoreRows := true;

如果我不打开光标

open tmp_cursor;

一切正常！！所以我想我发现了问题，现在:我该如何解决?

everything is working fine!! So I guess I found the issue, now: how can I solve it?

推荐答案

显然，PHP 人员和 MySQLi 人员之间存在一些利益转移 - 即 scaricabarile.

Apparently there's a bit of buck passing - i.e. scaricabarile - between PHP guys and MySQLi guys.

似乎正在发生的事情是 MySQLi 以不正确"的方式做出反应，并向 PHP 返回一个意外的值(我敢打赌它是一个 NULL)，PHP 会适当地进行核心转储.这种行为已记录，PHPland 的结论是:不是 [PHP] 漏洞".在他们这边，MySQLi 人员坚持认为是 PHP 没有正确检查返回的结果.无论如何，不正确"的结果取决于您的查询.

What seems to be happening is that MySQLi reacts in an "improper" way, and return an unexpected value (I'd bet a small sum on it being a NULL) to PHP, which duly coredumps. This behaviour is documented and the verdict from PHPland is: "Not a [PHP] bug". On their side, the MySQLi guys maintain that it's PHP which is not correctly checking the returned result. And that 'improper' results depended on your query anyway.

所以我打算冒险并假设您的问题与通信困难"相同，因此问题变成:您的查询强制 MySQLi 删除/重新连接".为什么呢?显然(一些)存储过程 需要 mysqli_multi_query 才能正常运行.

So I'm going out on a limb and supposing that yours is the same problem of "communication difficulties", so the problem becomes: "Your query forces MySQLi to drop/reconnect". Why is that so? Apparently (some) stored procedures require mysqli_multi_query in order to behave properly.

并且 mysqli_multi_query 与 mysqli_prepare 不兼容.

And mysqli_multi_query is not compatible with mysqli_prepare.

所以我建议在不准备查询的情况下尝试，并使用 mysqli_multi_query 运行它.

So I'd suggest trying without preparing the query, and running it with mysqli_multi_query.

$conn->query("SET @res = ''");
$conn->multi_query("CALL retrieve_matches(5,3, 16, 2, false, @res)");

do {
    if ($result = $conn->store_result()) {
        while ($row = $result->fetch_row()) {
            var_dump($row);
        }
        $result->free();
    }
} while ($conn->more_results() && $conn->next_result());

有了这段代码，你的测试用例就如我所料地得到了我，

With this code, your test case gets me, as expected,

array(1) {
  [0] =>
  string(4) "test"
}

这篇关于PHP Apache 在执行存储过程时崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！