Codeigniter Database Transactions

[eluser]Twisted1919[/eluser]
Hi , i have a simple question :
If let's say i start database transaction in a method within a model and i finish it in another method from same model that is called after the first one , if in the second method i encount an error and my sql from that method fails , then everything rolls back ? or it remains failed ?

[eluser]Jeremy Gimbel - Conflux Group[/eluser]
The transaction is on the database server side, so I imagine that how many queries are made or from where they are made has no bearing. Everything done after the start of the transaction should be rolled back.

[eluser]Twisted1919[/eluser]
I never used this until now and i am not sure how it works , but i have something that i really need to rollback if something fails.
here is the code :

Code:

//MODEL :
public function delete_gift($gift_id)
    {
    $this->db->trans_start();
    
    $sql = 'SELECT `for` FROM '.TBL_USER_COMMENTS.' WHERE gift_id = '.$gift_id.' ' ;
    $query = $this->db->query($sql);
    if($query->num_rows() > 0)
        {
        $result = $query->result();
        foreach($result AS $upd)
            {
            $this->db->where('user_id',$upd->for)->set('comments','comments-1',FALSE)->update(TBL_USERS);
            }
        }
    $this->db->where('gift_id',$gift_id)->delete(TBL_USER_COMMENTS);
    $this->db->where('gift_id',$gift_id)->delete(TBL_GIFTS);
    
    $this->db->trans_complete();
    
    return $this->db->trans_status() === FALSE ? FALSE : TRUE ;
    }

Because i must update the user comments number and there might be many users needing the update i wish to rollback if something goes wrong .
In the user guide says that if i do :

Code:

$this->db->trans_start(true);

My action will be rolled back, bc that's test mode but it didn't happened , it just updated and deleted the needed records .
Any ideea ?

[eluser]ciKD[/eluser]
[quote author="Twisted1919" date="1269824196"]
In the user guide says that if i do :

Code:

$this->db->trans_start(true);

My action will be rolled back, bc that's test mode but it didn't happened , it just updated and deleted the needed records .
[/quote]

Have the same problem with CI 1.7.2 and MySQL 5.1.43

First I thought I had found the problem, because my table was MyISAM and the docs say:

In MySQL, you'll need to be running InnoDB or BDB table types rather than the more common MyISAM.

Have changed the table type to InnoDB as decribed here

Code:

ALTER TABLE mytable ENGINE=InnoDB;

Change worked fine, phpMyAdmin shows it as Type InnoDB, but when I tried again, still no rollback if testmode is used. It just updates the table as if there would be no transaction used at all.

Can anybody else (besides Twisted1919) confirm this?

[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;

[eluser]ciKD[/eluser]
After a few tests based on the code (for a different problem), found in an old posting here, I can conclude that "Test Mode" is currently broken in 1.7.2.

Example similar to above linked old posting:
1) Create a mysql table called Customers with 3 columns CustomerID, CustomerEmail and CustomerName. Make sure the table is InnoDB (if using mySQL). ['db_debug'] can be true or false, does not matter, if set to off, you'll see the echo lines better.

Code:

Create table Customers (
    CustomerID Int NOT NULL AUTO_INCREMENT,
    CustomerName Char(20) NOT NULL,
    CustomerEmail Char(100) NOT NULL,
    UNIQUE (CustomerID),
Primary Key (CustomerID)) ENGINE = InnoDB;

2) copy the code below to your controllers directory.

test.php

Code:

<?php
class Test extends Controller {

  function Test()
  {
    parent::Controller();
  }
  function dbtest($rollback)
  {
    $this->db->trans_begin();
    $this->db->insert('customers',array('CustomerEmail' => 'e1',
                                        'CustomerName' => 'n1'));
    $this->db->insert('customers',array('CustomerEmail' => 'e2',
                                        'CustomerName' => 'n2'));

    if ($rollback) {
      $this->db->insert('customers',array('CAUSE ROLLBACK' => 'e2',
                                          'CustomerName' => 'n2'));
    }

    if ($this->db->trans_status()===FALSE)
    {
      $this->db->trans_rollback();
      echo "rollback";
      return True;
    } else {
      $this->db->trans_commit();
      echo "commit";
      return False;
    }
  }

  // ciKD
  function dbtest_auto($rollback)
  {
    $this->db->trans_start();
    $this->db->insert('customers',array('CustomerEmail' => 'auto_e1_' . $rollback,
                                        'CustomerName' => 'auto_n1_' . $rollback));
    $this->db->insert('customers',array('CustomerEmail' => 'auto_e2_' . $rollback,
                                        'CustomerName' => 'auto_n2_' . $rollback));

    if ($rollback) {
      $this->db->insert('customers',array('CAUSE ROLLBACK' => 'auto_e2_' . $rollback,
                                          'CustomerName' => 'auto_n2_' . $rollback));
    }
  
    $this->db->trans_complete();

    // if setting $db['default']['db_debug'] = TRUE; then we actually never get here
    // because $rollback causes db_error display, but the transaction itself works as
    // expected, rollback of first two is obviously done fine

    echo "auto...<br>";

    if ($this->db->trans_status()===FALSE)
    {
      echo "auto rollback";
      return True;
    } else {
      echo "auto commit";
      return False;
    }
  }

  // ciKD
  function dbtest_testmode($testmode)
  {
    $this->db->trans_start(($testmode == 1) ? TRUE : FALSE);
    $this->db->insert('customers',array('CustomerEmail' => 'testmode_e1_' .$testmode,
                                        'CustomerName' => 'testmode_n1_' . $testmode));
    $this->db->insert('customers',array('CustomerEmail' => 'testmode_e2_' . $testmode,
                                        'CustomerName' => 'testmode_n2_' . $testmode));

    $this->db->trans_complete();
    echo "testmode $testmode ... ";
    if ($testmode) {
        echo "all should be rolled back now, is it? check your table... ";
    }

    if ($this->db->trans_status()===FALSE)
    {
      echo "trans_status=FALSE";
      return True;
    } else {
      echo "trans_status=TRUE";
      return False;
    }
  }
}
?&gt;

3) Try calling the controller http://localhost/.../index.php/test/dbtest/1 nothing should be inserted. Then try http://localhost/.../index.php/test/dbtest/0 and two row should be inserted, trans_begin() and manual trans_rollback() and trans_commit() is used here.

4) Try calling the controller http://localhost/.../index.php/test/dbtest_auto/1 nothing should be inserted. Then try http://localhost/.../index.php/test/dbtest_auto/0 and two row should be inserted, trans_start() and trans_complete() is used here.

5) Try calling the controller http://localhost/.../index.php/test/dbtest_testmode/1 nothing should be inserted. Then try http://localhost/.../index.php/test/dbtest_testmode/0 and two row should be inserted, trans_start() and trans_complete() is used here.

With current 1.7.2 case 5) fails, rows are inserted and not rolled back!

A very old open bug-report from (11/29/2008) exists for 1.7.0 with a solution, which obviously would also work with 1.7.2 (if inserted starting line 528), but as this report is very old, I will report a new bug now. Edit: done

Edit: @WanWizard, thanks for your confirmation!

[eluser]WanWizard[/eluser]
Checked the pre-2.0 code on BitBucket, and confirmed that also contains this bug.

I've added the solution (link to this post) to the bug report.

[eluser]Twisted1919[/eluser]
Thanks WanWizard for your interest in this .
Seems like that is really a bug , something that persists from some versions ago i think , because not too many people used the tranasctions .
Hope this gets fixed soon enough .

[eluser]ciKD[/eluser]
[quote author="Twisted1919" date="1270514716"]Seems like that is really a bug , something that persists from some versions ago i think , because not too many people used the tranasctions.[/quote]

Seems like that. People seem not to care too much if db is really delivering or not, many just assume that mysql is always online/working without any errors.

I have analyzed many many different MY_Model variants for CI during last few days and a lot of people totally forget any error-checking and I did not find a single one with transactions built in, e.g. if they add a function for updating multipe rows or similar. Often only success of last (or first) insert/update/delete is returned, even saw things similar to or die('db err!');, order-by fields are not checked for existance in table if they come from client, like in any ajax-pagination, etc.

Too bad, CI can do that all, besides the 'test mode' param problem here.

To clarify: Without using 'test mode' the rollbacks in case of error works fine if transactions are used, in both 'Active Records' and 'old-style' db->query('SELECT..');

[eluser]Twisted1919[/eluser]
Heh cIKD , what you are explaining is not CI fault , is that most programmers are in hurry i believe .
I don't want to comment on this . But i want to continue on transactions thing .
Let me tell u what happened 10 minutes ago .
I had a field , int() that updates to keep count of users having same interests on one website , not really important which .
Entire database itself is INNODB , so i said , let's go further with transactions, and forget about test mode issue , "i don't need it " . So i made an update in my table and tried to update that field and add + 1 .
It ended up tu 489345644 . Entire server frozen when i executed the update command via page and didn't stop till i logged in via ssh (very very hard with 2 or 3 timeouts) and killed the mysql server. After i restarted the server , i logged in into phpmyadmin and i saw that number in my field .
What should i believe now ?