How do I identify which query failed in php mysqli transaction with try catch finally

Question

How can I identify which query caused a rollback when executing multiple prepared queries in a PHP MySQL transaction? I'd like to do this to provide a meaningful message, via email, to a particular person so would prefer not to simply send the exception response.

I believe the code snippet below (adapted from here) using an update and an insert as examples will execute the two queries or roll back both of them. But is there a way, in this simple example, to put two echo statements in the catch block that will show a different message depending upon which query, the update or the insert, failed?

<?php

$conn = mysqli_connect($servername, $username, $password, $dbname);  //connect to database
if (mysqli_connect_errno()) {      // Check connection
    log_message("Failed to connect to MySQL  " . mysqli_connect_error());
    exit();
}
try { //finally
    $stmt1 = $conn->prepare("UPDATE table1 SET f1 = 1 WHERE f2 = ? AND f3 = ?");
    $stmt2 = $conn->prepare("INSERT INTO table2 (f4,f5) VALUES (?,?)");
    $conn->begin_transaction();
    try {  // catch
        $stmt1->bind_param('iii', $data1, $data2, $data3); //bind parameters to update query
        $stmt1->execute();                               //execute update
        $stmt2->bind_param('ii', $data4, $data5);         //bind parameters to insert query
        $stmt2->execute();                               //execute insert
        $conn->commit();                                 //no errors, commit both queries
    } //end try catch
    catch (Exception $e) {                               //something went wrong
        $conn->rollback();
        echo "SQL error ".$e->getMessage();              //report error - but which query failed?
    }
}//try finally
finally {
    mysqli_close($conn);
}

Note: The code above is a modified version of my real code (which is rather more complex) just to illustrate the point for this question and to show the general structure of my real code. If you spot a syntax error in it then please feel free to edit it rather than leave a comment telling me it won't run as it is or that it's not my real code.

Answer 1

The exception you catch will contain the file's name and the line on which the exception happened. Using this information you can figure out which statement failed.

The exception you get from mysqli doesn't contain the SQL query used because the exception could have been thrown for many different reasons. It doesn't have to be the fault of the SQL at all. Your transaction could roll back for issues unrelated to SQL. So, there is no way to determine which SQL was at fault.

But this is ok. If you want to inform the developer of an issue, you can just send them the exception together with the full backtrace. They can look at the code and find the line number from which the exception occurred. There is no need to send this information to anyone who is not a developer. If you are thinking of sending this to the database administrator, then it would be wrong. First the developer should investigate the exception as it's most likely an issue in the code. If they decide it's an issue with the database then the developer should forward the incident to the relevant person. However, the code should inform only the developer of the issue.