PHP Apache crashes while executing a STORED PROCEDURE

Question

Problem

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 is crashing with error:

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

What I tried

1. This code is working fine and it's returning correctly the results:

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

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)"))) {

2. Tried from MySQl workbench and both calls are working fine.
3. I'm using WAMP 64b on a WIN7 Enterprise 64b, I tried with WAMP 32b but got same problem.
4. I checked windows event and I found httpd.exe is crashing caused by php5ts.dll
5. If you search for "httpd.exe php5ts.dll" on google you can find a lot of people incountering this problem. But I didn't find a solution for WAMP...
6. Tried with AMPPS, exact same problem

PHP Error Log

[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 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

EDIT

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;

if I don't open the cursor

open tmp_cursor;

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

Answer 1

If php5ts.dll, or any other part of apache crashes in a reproducible way every time you run a short script, you have most likely hit a bug in php. Especially if you run an up-to-date apache with a recent php-version downloaded from php.net you have a very good chance of getting support from the PHP team. So read through https://bugs.php.net/how-to-report.php, see if you can reproduce the crash when running the script without apache (in a separate script on the cli using php.exe) and generate a backtrace which you can then submit as a bug.

Bugs like these can sometimes be worked around but rarely be fixed from the context of your script. PHP should never crash the webserver and if it does this reproducibly a bug should be filed so it can be fixed

Answer 2

I find out how to reproduce it, I believe it's a php/apache bug

PHP code:

if (!($stmt = $mysqli->prepare("CALL test()"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

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

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

Store procedure code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
declare test_var varchar(100) default "ciao";
declare bNoMoreRows bool default false;
declare test_cursor cursor for
    select id from tmp_folder;
declare continue handler for not found set bNoMoreRows := true;

create temporary table tmp_folder select "test" as id;

open test_cursor;

fetch test_cursor into test_var;

close test_cursor;

select test_var;

drop temporary table if exists tmp_folder;

END

Bug opened: Apache: https://bz.apache.org/bugzilla/show_bug.cgi?id=58136

Php: https://bugs.php.net/bug.php?id=70073

Answer 3

Another one bites the dust here. Just dropped in to offer a workaround.

SP with a cursor only crashes when prepare() is used.

If you change it to multi_query() call it would work. A simple reproducible POC

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = mysqli_connect('127.0.0.1','user','pass','db');

$mysqli->query("DROP PROCEDURE IF EXISTS mysqli_crash");
$mysqli->query("CREATE PROCEDURE mysqli_crash ()
BEGIN
    DECLARE col TEXT;
    DECLARE res CURSOR FOR SELECT 1 from dual;
    OPEN res;
    FETCH res INTO col;
    CLOSE res;
    SELECT 1 from dual;
END;");

echo  "--- multi_query ---\n";

$mysqli->multi_query("call mysqli_crash()");
$result = $mysqli->store_result();
echo json_encode($result->fetch_all(MYSQLI_ASSOC)),"\n";
$mysqli->next_result();

echo  "--- prepare ---\n";

$stmt = $mysqli->prepare("call mysqli_crash()");
$stmt->execute();
$result = $stmt->get_result();
echo json_encode($result->fetch_all(MYSQLI_ASSOC)),"\n";
$stmt->next_result();

Yes, prepare is preferred but if your stored procedure is using a cursor there is no choice.

Answer 4

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

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.

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.

And mysqli_multi_query is not compatible with mysqli_prepare.

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"
}