Codeigniter, error tracking in transaction

Question

I'm running a little method in CodeIgniter to insert some lines in the database (same table). I would like to see which insertion have failed inside a transaction (by returning an array of titles). My code is :

$failure = array(); //the array where we store what failed
$this->db->trans_start();
foreach ($data as $ressourceCsv){ //data is an array of arrays to feed the database
    $this->ajout_ressource($ressourceCsv); //method to insert (basically, just an insert with active record)
    if (($this->db->_error_message())!=null) { 
          $failure[] = $ressourceCsv['title']; 
    }
}
$this->db->trans_complete();
return $failure;

The fact is that if I don't make it a transaction (no $this->db->trans_...), it works perfectly and I have an array containing a few titles. But with the transaction, the array contains every titles since the first error. Is there a way to just get the title from the insertion which caused the transaction to rollback?

I have also tried with :

$failure = array(); //the array where we store what failed
$this->db->trans_start();
foreach ($data as $ressourceCsv){ //data is an array of arrays to feed the database

    if (!$this->ajout_ressource($ressourceCsv)) { //active record insertion return true
          $failure[] = $ressourceCsv['title'];   // if successful
    }
}
$this->db->trans_complete();
return $failure;

user9645 · Answer 1 · 2013-07-02T13:07:04.807

I believe that once an error occurs inside a transaction, you must rollback before any more DB mods can be made. That would explain the behavior you are seeing. After the first error, the transaction is "aborted" and you continue your loop, causing every subsequent SQL command to fail as well. This can be illustrated as follows:

db=# select * from test1;
 id | foo | bar
----+-----+-----
(0 rows)

db=# begin;
BEGIN
db=# insert into test1 (foo, bar) values (1, 'One');
INSERT 0 1
db=# insert into test1 (foo, bar) values (Oops);
ERROR:  column "oops" does not exist
LINE 1: insert into test1 (foo, bar) values (Oops);
                                             ^
db=# insert into test1 (foo, bar) values (2, 'Two');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
db=# select * from test1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
db=# commit;
ROLLBACK
ace_db=# select * from test1;
 id | foo | bar
----+-----+-----
(0 rows)

db=#

Note it seems that "commit" does a "rollback" if there was an error (it was not a typo.)

Also BTW: use $this->db->trans_status() === FALSE to check for an error during the transaction.

Update: Here's some (untested) code to do it in a transaction so that the inserts are not seen by others until you are ready:

$failure = array(); //the array where we store what failed
$done = false;
do {
    $this->db->trans_begin();
    foreach ($data as $key => $ressourceCsv){ //data is an array of arrays to feed the database
        $this->ajout_ressource($ressourceCsv); //method to insert (basically, just an insert with active record)
        if ($this->db->trans_status() === false) { // an insert failed
            $failure[] = $ressourceCsv['title'];   // save the failed title
            unset($data[$key]);                    // remove failed insert from data set
            $this->db->trans_rollback();           // rollback the transaction
            break;                                 // retry the insertion
        }
    }
    $done = true;                                  // completed without failure
} while (count($data) and ! $done);                // keep going until no data or success

/*
 * Two options (uncomment one):
 * 1. Commit the successful inserts even if there were failures.

$this->db->trans_commit();

 * 2. Commit the successful inserts only if no failures.

if (count($failure)) {
    $this->db->trans_rollback();
} else {
    $this->db->trans_commit();
}
*/

return $failure;

Ok, so if I get it right, the very structure of transactions does not allow me to proceed as I wanted. Also I can't use $this->db->trans_status() === FALSE because I actually wrap the transaction lines in an if statement (I'm not always using a transaction). To solve my problem, I finally stored the id of the successful insertion to delete them in the end if I want a rollback and the $failure array is not empty — Dargor, Jul 02 '13 at 11:19
I suppose if you **must** do all your inserts at once regardless of any failures, then you can do that, but if your intention is that nobody else will see any of those inserts if some fail, then you can't accomplish it that way. You could have a boolean column called "accepted" and set it TRUE only after all the inserts are finished and none failed - and add a `WHERE accepted = TRUE` clause to your other queries. — user9645, Jul 02 '13 at 11:53
I added the Update to show how to do the loop inside a transaction if you want. — user9645, Jul 02 '13 at 13:08

Codeigniter, error tracking in transaction

1 Answers1

Linked