PHP Apache crashes while executing a STORED PROCEDURE

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

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

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.

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