[eluser]Unknown[/eluser]
I admit that i am very new to CI so this could definitely be me. However i was having some problems with using transactions with mysql and codeigniter.
I created this little controller to demo the issue.
Am i doing something wrong or is the problem in CI?
Code:
<?php
class Test extends Controller
{
/*
this is the table creation sql for the test table
all of these examples use this table
CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO test values (1);
*/
function index()
{
$this->db->trans_start();
$this->db->query("update test set id = 2");//seems to run immediately even though it shouldnt sense this is are in a transaction
$this->db->query("create table fail;");//should throw error and cause the rollback
$this->db->trans_complete();
if ($this->db->trans_status() === FALSE)
{
echo("this never gets called");
//all ids in table test are set to 2 and never rolled back
//fail query generates failure on screen
}
}
function fail_two()
{
$this->db->trans_begin();
try
{
$this->db->query("update test set id = 2");//seems to run immediately even though it shouldnt sense this is are in a transaction
$this->db->query("create table fail;");//should throw error and cause the rollback
}
catch(Exception $e)
{
echo("this never gets called");
$this->db->trans_rollback();
//table fail is created in the db and never rolled back.
//fail query generates failure on screen
}
$this->db->trans_commit();
/*
this syntax in this location instead of the above line produces the same result
if ($this->db->trans_status() === FALSE)
{
$this->db->trans_rollback();
}
else
{
$this->db->trans_commit();
}
*/
}
function fail_three()
{
$this->db->simple_query("START TRANSACTION;");//manually calling mysql's transaction methods
$this->db->simple_query("update test set id = 2");//seems to run immediately even though it shouldnt sense this is are in a transaction
$this->db->simple_query("create table fail");//should throw error and cause the rollback
//this one fails too.
$this->db->simple_query("ROLLBACK;");// still fails to rollback the query.
}
function fail_four()
{
mysql_query("START TRANSACTION;");//using php's mysql functions to start transactions
$this->db->simple_query("update test set id = 2");//seems to run immediately even though it shouldnt sense this is are in a transaction
$this->db->simple_query("create table fail");//should throw error and cause the rollback
//this one fails too.
mysql_query("ROLLBACK;");//fails to roll back. i was supprised this one didnt work.
}
function proof()
{
//using only php's mysql_query functions
mysql_query("START TRANSACTION;");
mysql_query("UPDATE TEST SET ID = 5;");
mysql_query("ROLLBACK;");//success the transaction is rolled back -- change to COMMIT; to commit the query.
//this means that the problem has to be somwhere between fail_four and here.
//probably something in simple_query autocommiting the queries.
}
}
?>