Database consecutive transactions issue

Some context:

I am tasked with maintaining a few projects that were created with Codeigniter about 5 years ago. I've never had any issue with it but decided it was time for a bigger update and started the move to Codeigniter v3. The update was mostly ok so I started running some tests. That's when I ran into this issue.

The issue:

I have a controller (to manage sites) and multiple models. My app allows a site to participate to a study, the record for that is stored in a participation table. When such a participation is recorded, an empty survey is created first and a reference to that survey is added to the participation.

Goes like this (simplified):

PHP Code:

$participation = new Participation();
//Set some variables

$survey = new Survey();
//Set some variables

$savedSurvey = $this->Surveymodel->persist($survey);

if ($savedSurvey ) 
    $participation->survey_id = $savedSurvey->id; 
                    
$savedParticipation = $this->Participationmodel->persist($participation); 


The persist method in both cases is similar to this (again simplified):

PHP Code:

$sql = "INSERT INTO survey (a, b, c) VALUES (?, ?, ?)";
$params = array($object->a, $object->b, $object->c);
               
$this->db->trans_start();
$this->db->query($sql, $params);
$object->id = $this->db->insert_id();
$this->db->trans_complete();

if ($this->db->trans_status() === FALSE) {
    error_log('SURVEY INSERT KO');
    return false;
} else {
    error_log('SURVEY INSERT OK');
    return $object;
} 


In my PHP error log file I indeed have confirmation that the transactions were OK in both cases and that there were no errors. The survey is indeed saved in the survey table but surprisingly the participation is not.

So after some hair pulling I decided to check the mysql logs. I noticed this interesting thing:

Code:

55 Query    set autocommit=0
55 Query    START TRANSACTION
55 Query    INSERT INTO survey (survey_class_id, status, created_at)
                       VALUES ('2', 1, NOW())
55 Query    COMMIT
55 Query    INSERT INTO participation (study_id, site_id, fs_folder_id, survey_id, reference, created_at)
                       VALUES (1, '33', NULL, 49, 'REDVILLE', NOW())
55 Quit

It seems that CI does start the first transaction, executes the query and commits but then only executes the second query without startint a new transaction and committing.

If I get rid if the transactions, everything works fine and both the survey and the participation are saved in the database. If I used transactions, only the survey is saved EVEN THOUGH the code tells me that everything is fine with the second transaction.

What is going on? I admit I haven't messed with this before so maybe there's something obvious that I am missing but I don't see what it could be. 

Thanks in advance for any help.

To add to what is already said above:

• I tried with a delay between both call to persist and it didn't change anything, still only the first transaction/query actually having an impact
  
• It's not a bug with my models. Tried to create object 1 & object 2 of the same type, with transactions and only for the first save do I get a "START TRANSACTION" + Query Insert + "COMMIT". Second save only runs the query without a start or a commit, returns a successful execution but no data is actually recorded in the table.
  
• If I try more than 2 it's the same result. Only the first operation actually shows a start transaction and a commit in the mysql logs, all subsequent operations only show the query in the logs and no data is actually added to the table.
  
• If I log the query, copy it and execute it manually (phpmyadmin or mysql workbench) it does work.
  
• I tried both with automatic transaction and manual ones (trans begin and then commit or rollback based on results). Again CI returns a successful transaction status but it's not actually done.
  
• Interesting to note, while the query doesn't actually add the record in the table (even though it returns that it did) the next autoindex value is incremented.
  

Would be nice to get any feedback.

Posted on Stackoverflow to hopefully get more visibility. Someone commented that they can't reproduce the bug so I tried with a clean copy of CI V3.0.4 and get the same bug. Full code below. As for config all I have changed is the log report level to 4 (to get everything) and of coruse the database.php with db name, login & password.

Welcome.php

PHP Code:

defined('BASEPATH') OR exit('No direct script access allowed');

class Welcome extends CI_Controller {
    public function index() {
        $this->load->view('welcome_message');

        $this->load->model(array('Mytestmodel'));

        $item = new stdClass();

        for ($i=0;$i<3;$i++) {
            $length = 10;

            $randomString = substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"), 0, $length);

            $item->name = $randomString;

            $this->Mytestmodel->insertItem($item);
        }


    }
} 


Mytestmodel.php

PHP Code:

Class Mytestmodel extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function insertItem($item) {

        $sql = 'INSERT INTO TEST (name) VALUES (?)';
        $params = array($item->name);

        $this->db->trans_start();
        $this->db->query($sql, $params);
        $item->id = $this->db->insert_id();
        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            error_log('ITEM INSERT KO');
            return false;
        } else {
            error_log('ITEM INSERT OK');
            return $item;
        }
    }
} 


EDIT:

• Tried without a model and doing everything in the controller, same result.
  
• Copied the same code in a CI v2 and I don't get the bug.
  
• Other person has tried the same thing with V3 and can't reproduce the bug.
  

Bug disappeared when upgrading my version of php from 5.5.10 to 5.6.10

Upgrading my version of PHP fixed the bug. In order to try to get a more accurate estimate of the version fixing the bug I tried PHP 5.5.22 which works (couldn't get anything between that and 5.5.10 for MAMP).

I checked the PHP ChangeLog and noticed this for version 5.5.12:

> mysqli: Fixed problem in mysqli_commit()/mysqli_rollback() with second parameter (extra comma) and third parameters (lack of escaping).

Which doesn't link to any further details but is the only thing in the change logs that seems related to my issue and that matches the versions for which I have the bug (before 5.5.10 & after 5.5.22).

So the issue exists in PHP 5.5.10, but I don't quite understand your reference 5.5.22 - is the bug still present in there or is that the earliest version you've got available that fixed it?

As a side note - I'm that guy from StackOverflow, and I could reproduce it the issue on 5.5.9 (that's just the version shipped with Ubuntu 14.04 and its derivatives), so 5.5.10 seems to be the version that actually introduced the problem.