Mysqli transaction fail to rollback when insert is failed

I have 3 databases, where I need to create database A, then database B, then create the linking database A_B.

I use mysqli transactions in PHP, there is a weird case(this is the first time i use php transactions), where it never rollback when it fails, case like database A fail to insert, but B still created. and A_B created incorrectly.

mysqli_begin_transaction($link);
try{

            $sql = "INSERT INTO A (
                     ....)
                    VALUES
                    (?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?);";
            $stmt = mysqli_prepare($link,$sql);
            mysqli_stmt_bind_param($stmt,"sssssssssssss",
                   ....
            );
            mysqli_stmt_execute($stmt);
            $param_new_A_Rec = mysqli_insert_id($link);
            
            for($i = 0; $i < $tot; $i++){
     
                            
               $sql = "INSERT INTO B (..)
                VALUES
                    (?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?);";

            $stmt = mysqli_prepare($link,$sql);
            mysqli_stmt_bind_param($stmt,"ssssssssssss",
                   ...
            );

            mysqli_stmt_execute($stmt);
            $param_new_B_Rec = mysqli_insert_id($link);
            
            $sql = "INSERT INTO A_B (
                    param_new_A_Rec,
                    param_new_B_Rec,
                     ....                       
              (?,?,?,?,?,?);";
            $stmt = mysqli_prepare($link,$sql);
            mysqli_stmt_bind_param($stmt,"ssssss",
                    ....
            );
            mysqli_stmt_execute($stmt);

            }


            mysqli_commit($link);
            
    }catch (mysqli_sql_exception $exception) {

            mysqli_rollback($link);
          
            throw $exception;
            
    }

Why if A failed, it never hit the rollback() and it created the incorrect A_B and B data? Did I miss anything here? any help would be greatly appreciated!

Answer

In fact, I need to add this

“mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);”

Now it is catching the invalid use case:

PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect integer value: ” for column ‘boolean_X’ at row 1 in yourFile.php

Advertisement

Answer