FrozenFire FrozenFire - 2 months ago 9
MySQL Question

mysqli_commit fails when select statement is added

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_table
    and above stored procedure.

  • Tested without additional include libraries (tested with purely on above snippets).


Answer

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;
Comments