CODEWITHSUNDEEP
phpdatabasemysqlitransactions
Notsileous
Notsileous

Reputation: 560

mysqli rollback commit is not working with innodb table

I am trying to set up transaction rollbacks but no matter what I do, the insert is not rolled back. I have confirmed the DB engine is innoDB:

Name                            Engine  Version     Row_format  
order_fulfillment_discounts     InnoDB    10         Dynamic

I initially tried adding it the real queries but began pairing it down when that was not working. What was left is the most dirt simple code:

$dbConnect = mysqli_connect("localhost", "root", "", "dealers");

$dbConnect->autocommit(false);
$dbConnect->begin_transaction();

$dbConnect->query("INSERT INTO order_fulfillment_discounts (fulfillment_id, order_id, discount_id, discount_type, discount_category) VALUES (1,1,1,'foo','test')");
$dbConnect->commit();
$dbConnect->rollback();

That is the entirety of the code, there are no files being required and nothing else making a connection.

The commit works fine, I can debug and see that the insert does not occur until commit() is called so I know transactions in general are working. The result of the commit is TRUE and the result of the rollback is also TRUE.

I am running this on localhost on a Mac using PHP 8 with Homebrew. Is there a possibility that it is a setting/permission that prevents rollbacks for safety? The root DB user has all the permissions and I even added a password to the database to see if that would help.

I have run it both in phpstorm and in a browser. I have debugged it so there is time between commit and rollback as well as running it with a sleep(). I have tried specifying a transaction name.

Everything works the same, the commit always works as expected and the rollback does not.

Upvotes: 0

Views: 26

Answers (0)

Related Questions