MySQLI Rollback not working

[Tazz]

I am new to MySQLI and tried to test the rollback functionality.

 

I created a simple database called mysqli and one table called test with and id and name field.

 

my PHP is as follows:

$mysqli = new mysqli('localhost',name,password,'mysqli');

$mysqli->autocommit(FALSE);

$result = $mysqli->query("INSERT INTO test (name) VALUES('bla')");

$mysqli->commit();
$mysqli->rollback();

 

After hitting the page, it does the insert, but does not roll back the transaction.  So it ends up being inserted into the DB.  Why is the rollback not working?

[bh]

Hi,

 

you've done a commit, so your commands executes and uploaded into your db. If you want to rollback your exec doesnt call commit before rollback.

 

$mysqli->autocommit(FALSE);
// these command will be rollbacked
$mysqli->rollback();

 

use exception to handle an elegant way to commit/rollback

[Tazz]

Thanks for the reply, so I cannot rollback anything already committed?

 

That seems a bit weird as all the examples I've seen online suggests that.

[bh]

If you start a transaction, that means you have an active transaction. You can finish your active transaction with a commit or a rollback. After you commited a transaction you cant rollback it.

So start a transaction, check error, if theres an error rollback your transaction, else commit it.

 

Some useable info: You cannot create a transaction on non-transaction tables (eg MyIsam), and you cannot rollback DDL statements.

[Tazz]

Ahh... That makes sense!  Thanks for telling me about the fact that I need to make sure my tables are InnoDB, all mine were MyISAM.  I'll play around with it a little bit more, but the way you described it to me now makes perfect sense. Thanks again!

[bh]

Your welcome.