CodeIgniter Transactions - trans_status and trans_complete return true but nothing being committed

Question

Problem:

I have written a function in my model to insert an order into my database. I am using transactions to make sure that everything commits or else it will be rolled back.

My problem is that CodeIgniter is not showing any database errors, however it is rolling back the transaction but then returning TRUE for trans_status. However, this only happens if there is a discount on the order. If there is no discount on the order, everything commits and works properly.

I am currently using CodeIgniter 3.19, PHP (7.2), mySQL (5.7), and Apache 2.4. (Working on Ubuntu 18.04)

The function logic works as such:

• Inserts the order array into tbl_orders
• Saves order_id, and goes through each of the order products (attaches order_id) and inserts the product in tbl_order_products,
• Saves order_product_id and attaches it to an array of users attendance options and inserts that into tbl_order_attendance
• Takes the payment transaction array (attaches the order_id) and inserts that into tbl_transactions
• IF there is a discount on the order, it decreases the discount_redeem_count (number of redeemable discount codes) by 1.
  

Actual Function

[Function]:

public function add_order(Order $order, array $order_products, Transaction $transaction = NULL){
  $this->db->trans_start();

  $order->create_order_code();
  $order_array = $order->create_order_array();

  $this->db->insert('tbl_orders', $order_array);
  $order_id = $this->db->insert_id();
  $new_order = new Order($order_id);

  foreach($order_products as $key=>$value){
    $order_products[$key]->set_order($new_order);
    $order_product_array = $order_products[$key]->create_order_product_array();

    $this->db->insert('tbl_order_products', $order_product_array);
    $order_product_id = $this->db->insert_id();

    $product = $order_products[$key]->get_product();

    switch ($product->get_product_class()){
        case 'Iteration':
            $this->db->select('module_id, webcast_capacity, in_person_capacity');
            $this->db->from('tbl_modules');
            $this->db->where('iteration_id', $product->get_product_class_id());
            $results = $this->db->get()->result_array();
            break;
        case 'Module':
            $this->db->select('module_id, webcast_capacity, in_person_capacity');
            $this->db->from('tbl_modules');
            $this->db->where('module_id', $product->get_product_class_id());
            $results = $this->db->get->result_array();
            break;
      }

      if(!empty($results)){
        foreach($results as $result){
        $module_id = $result['module_id'];

        if($result['webcast_capacity'] !== NULL && $result['in_person_capacity'] !== NULL){
          $attendance_method = $order_products[$key]->get_attendance_method();
        }elseif($result['webcast_capacity'] !== NULL && $result['in_person_capacity'] === NULL){
          $attendance_method = 'webcast';
        }elseif($result['webcast_capacity'] === NULL && $result['in_person_capacity'] !== NULL){
          $attendance_method = 'in-person';
        }

        $order_product_attendance_array = array(
          'order_product_id' => $order_product_id,
          'user_id' => $order_products[$key]->get_customer(true),
          'module_id' => $module_id,
          'attendance_method' => $attendance_method,
        );

        $order_product_attendance[] = $order_product_attendance_array;
      }
      $this->db->insert_batch('tbl_order_product_attendance', $order_product_attendance);
    }

    if(!empty($order_products[$key]->get_discount())){
      $discount = $order_products[$key]->get_discount();
    }
  }

  if(!empty($transaction)){
    $transaction->set_order($new_order);
    $transaction_array = $transaction->create_transaction_array();
    $this->db->insert('tbl_transactions', $transaction_array);
    $transaction_id = $this->db->insert_id();
  }

  if(!empty($discount)){
    $this->db->set('discount_redeem_count', 'discount_redeem_count-1', false);
    $this->db->where('discount_id', $discount->get_discount_id());
    $this->db->update('tbl_discounts');
  }

  if($this->db->trans_status() !== false){
    $result['outcome'] = true;
    $result['insert_id'] = $order_id;
    return $result;
  }else{
    $result['outcome'] = false;
    return $result;
  }
}

When this function completes with a discount, both trans_complete and trans_status return TRUE. However the transaction is never committed.

What I've tried:

• I have dumped the contents of $this->db->error() after each query and there are no errors in any of the queries.

• I have used this->db->last_query() to print out each query and then checked the syntax online to see if there were any problems, there were none.

• I also tried changing to using CodeIgniters Manual Transactions like:

[Example]

$this->db->trans_begin();
 // all the queries
if($this->db->trans_status() !== false){
    $this->db->trans_commit();
    $result['outcome'] = true;
    $result['insert_id'] = $order_id;
    return $result;
}else{
    $this->db->trans_rollback();
    $result['outcome'] = false;
    return $result;
}

• I have tried echoing and var_dumping all of the return insert_ids and they all work, I have also outputted the affected_rows() of the UPDATE query and it is showing that 1 row was updated. However, still nothing being committed:

[Values Dumped]

int(10) // order_id
int(10) // order_product_id
array(3) { 
    ["module_id"]=> string(1) "1" 
    ["webcast_capacity"]=> string(3) "250" 
    ["in_person_capacity"]=> string(3) "250" } // $results array (modules)

array(1) { 
    [0]=> array(4) { 
        ["order_product_id"]=> int(10 
        ["user_id"]=> string(1) "5" 
        ["module_id"]=> string(1) "1" 
        ["attendance_method"]=> string(7) "webcast" } } // order_product_attendance array

int(9) // transaction_id
int(1) // affected rows
string(99) "UPDATE `tbl_discounts` 
            SET discount_redeem_count = discount_redeem_count- 1 
            WHERE `discount_id` = 1" // UPDATE query

- I have also tried replacing the last UPDATE query with a completely different one that tries to update a different table with different values. That query ALSO did not work, which makes me think that I am hitting some sort of memory limit with the transaction. However, when monitoring mysqld processes, none of them seem to spike or have difficulty.

• I have tried submitting an order that doesn't have a discount and the entire process works! Which leads me to believe that my problem is with my UPDATE query. [After Update:] But it seems that the update query is working as well.

Suggestions Tried:

• We have tried setting log_threshold to 4, and looked through the CodeIgniter Log Files which shows no history of a rollback.

• We have checked the mySQL Query Log:

[Query Log]

2018-12-03T15:20:09.452725Z         3 Query     UPDATE `tbl_discounts` SET discount_redeem_count = discount_redeem_count-1 WHERE `discount_id` = '1'
2018-12-03T15:20:09.453673Z         3 Quit

It shows that a QUIT command is being sent directly after the UPDATE query. This would initiate a rollback, however the trans_status is returning TRUE.

I also changed my my.cnf file for mySQL to have innodb_buffer_pool_size=256M and innodb_log_file_size=64M. There was no change in the outcome.

• As @ebcode recommended, I changed UPDATE query to use a simple_query() instead of using default methods from CodeIgniter's Query Builder Class:

[Simple Query]

if(!empty($discount)){
    $this->db->simple_query('UPDATE `tbl_discounts` SET '.
    'discount_redeem_count = discount_redeem_count-1 WHERE '.
    '`discount_id` = \''.$discount['discount_id'].'\'');
}

However, this produced did not affect the outcome any differently.

If you have an idea that I haven't tried yet, or need more information from me, please comment and I will reply promptly.

Question:

Why does trans_status return TRUE if none of my transaction is being committed?

In order to try and bring some clarity to users just finding this question now, the latest updates to the post will appear in italics *

Answer 1

I found my problem. I want to say Thank-you to everyone that tried to help however, this one was my fault.

Earlier in the Controller method that calls this function I called another function that starts a transaction. That transaction was never closed and therefore continued on into this new transaction.

Because the transaction just wasn’t committed and there were no errors, I was not able to find any errors or any history of a rollback. However as soon as I closed the previous transaction everything worked.

There was no evidence of any problems in the mySQL query log, mySQL error log, or the CodeIgniter error logs. I was only able to find this problem by slowly reading through the entire mySQL query log.

For anyone who comes across this problem: Check your other transactions and make sure that they are all closed.

Answer 2

Maybe try replacing your update code with a call to simple_query?

Change:

if(!empty($discount)){
    $this->db->set('discount_redeem_count', 'discount_redeem_count-1', false);
    $this->db->where('discount_id', $discount['discount_id']);
    $this->db->update('tbl_discounts');
}

To:

if(!empty($discount)){
    $this->db->simple_query('UPDATE `tbl_discounts` SET '.
    'discount_redeem_count = discount_redeem_count-1 WHERE '.
    '`discount_id` = \''.$discount['discount_id'].'\'');
}

I poked around the CodeIgniter source code a bit, and it looks like the default query function does a lot of housekeeping that might be messing things up. And the simple_query function has this morsel of documentation:

/**
 * Simple Query
 * This is a simplified version of the query() function. Internally
 * we only use it when running transaction commands since they do
 * not require all the features of the main query() function.
 *
 * @param   string  the sql query
 * @return  mixed
 */

Answer 3

I'm thinking about your database field discount_redeem_count name.

Are you sure discount_redeem_count is not number? because here your trying to push a string value. So database field should be var or text.

Maybe helpful.

Thanks.

Answer 4

First, you should make sure to turn on the Transaction Strict mode before starting transactions.

$this->db->trans_strict(TRUE);
$this->db->trans_start();

Second, please check the $order variable. is this an array? or a class? if this is a class then it probably failed at this line

$this->db->insert('tbl_orders', $order);

Third, if $order variable is a class then this line will succeed. If $order variable is an array then this line will fail.

$discount = $order->get_discount();

Answer 5

Based on EDIT 5:

This

$this->db->set('discount_redeem_count', 'discount_redeem_count-1', false);

should work (the back-ticked one would not.. the whole point of passing the false third parameter is so that CI doesn't escape your parameters with backticks, which would prevent the set statement to be passed as a string.

I did some quick tests in my own development code with an update similar to that one and the only way I got it to fail was to alter the table being updated so that the field (discount_redeem_count in your case) wasn't numeric. If my field was, for example, a VARCHAR it wouldn't work, but when I tried it on an INT field, it worked with no issues.

Are you positive the discount_redeem_count field is numeric?

Answer 6

(Both of these suggestions were tried, to no avail.)

Suggestion 1

Perhaps this is the real answer:

trans_status() must be run when you're inside a transaction. In your example trans_complete() resets the status flag.

(However, this is sad if you are using Galera or Group Replication since a transaction can still fail when running COMMIT.)

Suggestion 2

These are  `== NULL`:  NULL, '', FALSE, 0
These are `!== NULL`:        '', FALSE, 0

Notice how you use the "triple" !== for some tests against NULL, but use the "double" == for others.

Do this to see what you are actually getting:

var_dump($result['webcast_capacity']);