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.