I'm finding myself a bit confused by the way MySQLi handles savepoints when encountering an erroneous query.
Here's an MRE of the problem I'm having:
$mysqli = new mysqli($_HOST, $_USER, $_PASS, $_DB_NAME); // replace with credentials
$mysqli->begin_transaction();
$mysqli->savepoint("SP1"); // savepoint() method - I get the same problem when using the SAVEPOINT query
$mysqli->query("INSERT INTO Users (Username, FavouriteColour) VALUES ('Alice', 'Red');"); // valid query
$mysqli->savepoint("SP2");
$mysqli->query("INSERT INTO Users (Username, FavouriteColour) VALUES ('Bob', 'Green');"); // another valid query
$mysqli->savepoint("SP3");
$mysqli->query("INSERT INTO Users (Username, FavouriteColour) VALUES ('Charlie', 'Blue');"); // third valid query
$mysqli->query("CREATE TABLE Users (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, Username VARCHAR(255), FavouriteColour VARCHAR(64));"); // obviously invalid query - Users already exists.
$mysqli->query("ROLLBACK TO SAVEPOINT SP2"); // Time to rollback! I use the ROLLBACK query since the rollback($name) method doesn't actually rollback to the savepoint with the name parameter.
$mysqli->commit(); // Commit the transaction.
After this small transaction, the last two adds should have been rolled back, right? That's definitely what happens when there isn't an invalid query in the middle of the transaction. Alice should be added, but Bob and Charlie shouldn't.
The presence of a (not syntactically, but logically) invalid query in the middle of the transaction apparently seems to throw MySQLi off completely. Instead of either keeping the transaction and letting me roll back to when I feel like it, or undoing the entire transaction (which would be understandable since there's been an error in there), MySQLi keeps the whole transaction but deletes all the savepoints. When I try to rollback to any of the three savepoints above, it gives me a "savepoint does not exist" error. As a result, all three valid queries are committed (even if I don't manually commit them at the end, and even if I try to roll back the last two, as above).
Unless I'm doing something wrong here, MySQLi is deciding that the best way to deal with an error is to keep executing all the queries but getting rid of all the savepoints (which are...how you deal with errors). Please let me know if either myself or MySQLi is being stupid here!
Note - when there is no invalid query in the transaction, the savepoint / rollback works perfectly well. The first query gets executed, then the second and the third, but the rollback to SP2 cancel the second and third queries, so only the first is committed.
Transactions are implicitly committed by DDL statements. A SAVEPOINT is part of a transaction. When a transaction is committed, the savepoints are deleted.
All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint.
The transaction is committed before the DDL statement is attempted. Therefore, it's irrelevant whether the CREATE TABLE query succeeds or fails. The savepoints will be removed either way.