PHP MySQL - Why stored procedure call is ended unexpectedly in PHP code

Question

I have a strange problem with my stored procedure. I wrote a stored procedure that runs correctly on the MySQL console and MySQL GUI tool: SqlYog. But it doesn't work in PHP code. Is there any specific setting to run SP in PHP? In my stored procedure, I used MySQL session variables.

My environment: Windows 10 x64, PHP 7.3, MariaDB: 10.4.13-MariaDB.

I attached the part of my SP code.

Problem block is following:

        SET @p_number = v_number;
        SET @p_quantitySum = v_stock_net;
        EXECUTE stmt1 USING @p_number, @p_quantitySum;

And this is a prepared statement.

SET @sql_query = "
        SELECT 
            @b_id := id,
            @b_price := IFNULL(price, 0),           
            @b_ib_seq := seq,
            @b_ib_qty_accumulated := IFNULL(quantitySum, 0) 
        FROM
            (SELECT 
                ib.*,
                (@seq := @seq + 1) AS seq,
                @sum := (@sum + quantity) AS quantitySum 
            FROM
                buying ib 
                INNER JOIN 
                    (SELECT 
                        @sum := 0,
                        @seq := 0) b 
            WHERE ib.number = ?
            ORDER BY ib.date DESC) ib 
        WHERE quantitySum > ? 
        LIMIT 1 ;
    " ;
    PREPARE stmt1 FROM @sql_query ;

PHP call: mysqli_query($this->conn, "CALL sp_update_daily_buying('2021-01-21');")

And I run this select in loop. After executing EXECUTE stmt1 USING @p_number, @p_quantitySum;, my stored procedure is ended after 3rd execution. That's in the loop, 2 times are ok, but after 3rd execution, SP is ended.

As I mentioned above, in GUI tool and MySQL console, it works correctly (Loop ended correctly).

Is there any specific setting for it?

Thanks in advance!

question from:https://stackoverflow.com/questions/65835113/php-mysql-why-stored-procedure-call-is-ended-unexpectedly-in-php-code

Answer 1

I could fix this error in several hours.

The reason is why I used the multiple SELECT statements in the stored procedure which don't have a variable assignment or INSERT. So stored procedure returned multiple resultsets after the execution and it made PHP function mysqli_query not handle properly. I couldn't find the solution how to handle this situation in PHP. Maybe mysqli_multi_query can fix this.

But in the stored procedure I changed the multiple selects statements a little by adding variables assignments. And finally, I could get working.

SELECT          
            id,
            price,
            seq,
            quantitySum 
            INTO @vb_id, @vb_price, @vb_ib_seq, @vb_ib_qty_accumulated
        FROM
            (SELECT 
                ib.*,
                (@seq := @seq + 1) AS seq,
                @sum := (@sum + quantity) AS quantitySum 
            FROM
                item_buying ib 
                INNER JOIN 
                    (SELECT 
                        @sum := 0,
                        @seq := 0) b 
            WHERE ib.number = ?
            ORDER BY ib.date DESC) ib 
        WHERE quantitySum > ? 
        LIMIT 1 

Or we can use a temporary memory table to get the resultsets from select instead of using session variables.