Running multiple queries with mysqli_multi_query and transactions

Question

I'm developing an update system for a Web Application written in PHP. In the process of the update I might need to execute a bunch of MySQL scripts.

The basic process to run the scripts is:

1. Search for the Mysql scripts
2. Begin a transaction
3. Execute each script with mysqli_multi_query since a script can contain multiple queries
4. If everything goes ok COMMIT the transaction, otherwise ROLLBACK.

My code looks something like:

$link = mysqli_connect(...);

mysqli_autocommit($link, false);

// open dir and search for scripts in file.
// $file is an array with all the scripts
foreach ($scripts as $file) {

    $script = trim(file_get_contents($scriptname));

    if (mysqli_multi_query($link, $script)) {
        while (mysqli_next_result($link)) {
            if ($resSet = mysqli_store_result($link)) { mysqli_free_result($resSet); }

            if (mysqli_more_results($link)) { }
        }
    }

    // check for errors in any query of any script
    if (mysqli_error($link)) {
        mysqli_rollback($link);
        return;
    }
}
mysqli_commit($link);

Here is an example of the scripts (for demonstration purposes):

script.1.5.0.0.sql:
update `demo` set `alias` = 'test1' where `id` = 1;
update `users` set `alias` = 'user1' where `id` = 1;

script 1.5.1.0.sql: 
insert into `users`(id, key, username) values(3, '100', 'column key does not exist');
insert into `users`(id, key, username) values(3, '1', 'column key exists');

In this case, script 1.5.0.0 would execute without errors and script 1.5.1.0 would generate an error (for demonstration purposes, let's say that column key is unique and there is already a row with key = 1).

In this case I want to rollback every query that was executed. But what happens is that the first insert of 1.5.1.0 is not in the database (correctly) but the updates from 1.5.0.0 were executed successfully.

Remarks:

1. My first option was to split every query from every script with ";" and execute the queries independently. This is not an option since I have to be able to insert HTML code to the database (ex: if I want to insert something like "& nbsp;")
2. I've already searched StackOverflow and google and came across solutions like this one but I would prefer using a solution like mysqli_multi_query rather than using a function to split every query. It's more understandable and easier for debug purposes
3. I haven't tested it, but I believe that I could merge all the scripts and execute just a query. However it would be usefull to execute one script at a time so that I can figure out which script has the error.
4. The tables engine is InnoDB.

Appreciate if you can point some way to make this work.

Answer 1

Ok, after spending another day debugging, i've discovered the problem.

Actually, it has nothing to do with the code itself or with mysqli functions. I'm used to MS SQL transactions which supports DDL statements. MySQL does not supports DDL statements and commits data implicitly (Implicit commit). I had one DROP Table in one of the scripts that was auto commiting data.

Answer 2

Edit:mysqli_multi_query() only returns false if the first query fails. If the first query doesn't fail then your code will run mysql_store_result() which if it succeeds will leave mysqli_error() empty. You need to check for errors after every mysqli function that can succeed or fail.