Welcome Guest, Not a member yet? Register   Sign In
Transactions dont work
#1

[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.
        }
        
    }
?>
#2

[eluser]Unknown[/eluser]
It looks like this might be more of a problem with mysql.

running the following sql in phpmyadmin commits the query on the sql error. This is also likely the problem being shown in code igniter.

Code:
START TRANSACTION;
UPDATE TEST SET ID = 15;
create table fail;
ROLLBACK;




Theme © iAndrew 2016 - Forum software by © MyBB