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.
