#StackBounty: #php #mysql #codeigniter CodeIgniter Transactions – trans_status and trans_complete return true but nothing being committed

Bounty: 50

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), and mySQL (5.7), Apache 2.4.

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');
  }

  $this->db->trans_complete();

  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.

The UPDATE query (using $this->db->last_query();) outputs:

UPDATE `tbl_discounts` SET discount_redeem_count = discount_redeem_count- 1 WHERE `discount_id` = '1'

$this->db->affected_rows() outputs 1. Which means that the UPDATE query is working. However, still nothing being committed.

Column in mySQL: discount_redeem_count column in mySQL

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 have tried echoing all of the return insert_ids and they all work (However I can’t test the last one because it’s an UPDATE query)

  • 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 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. However, the order_id is still being stored into the $order_id variable before the rollback happens and can be echoed, as well as the order_product_id and the transaction_id. That is why I think that that the problem has something to do with the last UPDATE query.
  • 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. I am unsure if this would be the problem.

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

  • As @ebcode recommended, I changed UPDATE query to use a simple_query() instead of using 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.

  • Tried to change the UPDATE query from:

[Query]

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

To:

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

The reason for trying was because of this question. However, I have reverted the code back to not using back-ticks.

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?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.