Codeigniter Database Transactions

[eluser]WanWizard[/eluser]
The fact that standard CI doesn't store simple queries makes it a bit difficult to debug.

Since our framework uses a modified driver that does log all queries, I ran a simple test:

Code:

$this->db->trans_start(TRUE);
$this->db->query('INSERT INTO cms_testje (name, value) VALUES ("name", "value")');
$this->db->trans_complete();

In the profiler output, this results in:

Code:

0.0001      SET AUTOCOMMIT=0
0.0001      START TRANSACTION
0.0002      INSERT INTO cms_testje (name, value) VALUES ("name", "value")
0.0026      COMMIT
0.0001      SET AUTOCOMMIT=1

So you're right, I does a COMMIT, not a ROLLBACK as it should according to the docs.

Diving into the code, I see that the trans_complete() method checks the _trans_status variable, while the trans_begin() method in the MySQLI driver sets the _trans_failure variable. A quick scan through the other drivers confirms they all use this variable.

To fix the bug in the generic driver class, look in DB_driver.php for:

Code:

// The query() function will set this flag to FALSE in the event that a query failed
if ($this->_trans_status === FALSE)

and change it to:

Code:

// The query() function will set this flag to FALSE in the event that a query failed
if ($this->_trans_status === FALSE OR $this->_trans_failure === TRUE)

This now produces:

Code:

0.0001      SET AUTOCOMMIT=0
0.0001      START TRANSACTION
0.0002      INSERT INTO cms_testje (name, value) VALUES ("name", "value")
0.0033      ROLLBACK
0.0001      SET AUTOCOMMIT=1

Note that this is a quick fix, I think all the driver files should be altered, so the line:

Code:

$this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;

reads:

Code:

$this->_trans_status = ($test_mode === TRUE) ? FALSE : TRUE;