添加select语句时mysqli_commit失败

Question

Problem:

Unable to store data with mySQL stored procedure with mysqli_begin_transaction.

Details:

The below code will do simple insert and select using mysql stored procedure. Code runs fine without select statement. However once the select statement is added, it won't commit any data even the query returns success at PHP side.

Snippets (PHP):

$DB_DRRM_SQLI = mysqli_connect("localhost","root","", "sandbox_db");
mysqli_begin_transaction($DB_DRRM_SQLI);

$SQL_QUERY_CODE = "CALL SANDBOX_TEST()";
$DB_QUERY = mysqli_query($DB_DRRM_SQLI, $SQL_QUERY_CODE);


// ERROR REPORTING
if($DB_QUERY === false)
{
    echo mysqli_error($DB_DRRM_SQLI);
    mysqli_rollback($DB_DRRM_SQLI);
}
else
{
    echo 'success';
    mysqli_commit($DB_DRRM_SQLI);
}
exit;

Snippets (mySQL Stored procedure):

BEGIN
    INSERT INTO 
    `sandbox_table`
    (
        `SOME_STRING`
    )
    VALUES
    (
        'ABCDEFGHIJKL...'
    );

    SELECT 
        LAST_INSERT_ID() AS INSERTED_ID,
        'ABCDE...' AS OTHER_PARAMS; 
END

Database (Table sandbox_table):

1. RECORD_PRIMARY_ID (Int - Auto increment)
2. SOME_STRING (Varchar - 500 length)

Spec:

• PHP version: 5.6.14
• 10.1.8-MariaDB
• Storage Engine: InnoDB

Notes:

• If transaction is made at stored procedure works fine, but I need a PHP managed transaction to handle multiple query requests and response depending on the result of query.
• (It can be a possible last resort if there's no other solution, where I need to convert whole PHP code to stored procedure and need pass tons of parameter)

Methods Tested:

• Tried with other PHP version 7.0.9 with same result (10.1.16-MariaDB)
• Tested with new database with no other data except sandbox_tableand above stored procedure.
• Tested without additional include libraries (tested with purely on above snippets).

Answer 1

Solution:

It was caused by Commands out of sync error at mysqli_commit. Seems the mysqli won't allow committing transaction while the query is open, which happens if you add select statement to above stored procedure.

So to handle this, it must close the query first or put the query to buffer.

Snippets (PHP):

// SQL Database
$DB_DRRM_SQLI = mysqli_connect("localhost","root","", "sandbox_db");
mysqli_begin_transaction($DB_DRRM_SQLI);

$SQL_QUERY_CODE = "CALL SANDBOX_TEST()";
$DB_QUERY = mysqli_query($DB_DRRM_SQLI, $SQL_QUERY_CODE);

// ERROR REPORTING
if($DB_QUERY === false)
{
    echo mysqli_error($DB_DRRM_SQLI);
    mysqli_rollback($DB_DRRM_SQLI);
}
else
{
    // Must free current query result before committing transaction
    @mysqli_free_result($DB_QUERY);
    @mysqli_next_result($DB_DRRM_SQLI);

    if(mysqli_commit($DB_DRRM_SQLI) === false)
    {
        echo mysqli_error($DB_DRRM_SQLI);
    }
    else
    {
        echo 'success';
    }
}
exit;