MySQL Transaction+ PHP issue in Mysql

Question

I have a code which was used in an application where I am having a problem in rollback. Even if I 's2' returns false rollback isn't happening i.e. table 'products' is getting droped. Can anyone explain why it isn't working or how should I change it. Note: tables are of Innodb engine..I use mysql 5.0+

    mysql_query('SET AUTOCOMMIT=0;');
    mysql_query('START TRANSACTION;');
    $sql = 'DROP TABLE '.$this->Product->tablePrefix.'products';
    $s1 = mysql_query($sql);
    $sql = 'RENAME TABLE '.$this->Product->tablePrefix.'temp12212 TO '.$this->Product->tablePrefix.'products'; 
    $s2 =mysql_query($sql);
    if($s1 && $s2){
        mysql_query('COMMIT;');
        $this->Session->setFlash('Commit Successful to Database');
    }else{
        mysql_query('ROLLBACK;');
        $this->Session->setFlash('Commit failed due to some errors<br> auto-rollbacked to previous state');
    }

The Scrum Meister · Accepted Answer · 2011-01-25 06:19:51Z

2

DROP TABLE is one of the commands in MySql that cause a implicit commit.

http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html

Use this instead:

'RENAME TABLE '.$this->Product->tablePrefix.'products TO backup_table
, '.$this->Product->tablePrefix.'temp12212 TO '.$this->Product->tablePrefix.'products';

answered Jan 25, 2011 at 6:19

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Libu Over a year ago

rename is also nt working brother!!! :-(.. rename also issues an implicit commit.

The Scrum Meister Over a year ago

I know, use RENAME to rename both tables automicly, instead of a transaction.

Libu Over a year ago

Worked :-) thanks for the reply!!! 'RENAME' cannot be used inside 'START TRANSACTION' & 'COMMIT' but it can be used instead of it due to its atomicity property...

Rich Adams · Accepted Answer · 2011-01-25 06:20:42Z

1

You cannot rollback a DROP TABLE or RENAME TABLE statement as they cause an implicit commit.

answered Jan 25, 2011 at 6:20

Rich Adams

26.7k4 gold badges43 silver badges62 bronze badges

Comments

Libu · Accepted Answer · 2011-01-25 08:04:13Z

I sorted the problem this way instead!!! thanks all for your reply :-)  


 $sql = 'DROP TABLE IF EXISTS '.$this->Product->tablePrefix.'temp_backup';
        mysql_query($sql);
        $sql = 'RENAME TABLE '.$this->Product->tablePrefix.'products TO '.$this->Product->tablePrefix.'temp_backup, '.$this->Product->tablePrefix.'temp TO '.$this->Product->tablePrefix.'products'; 
        $status =mysql_query($sql);
        if($status){
            $sql = 'DROP TABLE '.$this->Product->tablePrefix.'temp_backup';
            mysql_query($sql);
            $this->Session->setFlash('Commit Successful to Database');
        }else{              
            $this->Session->setFlash('Commit failed due to some errors<br> auto-rollbacked to previous state');
        }

Collectives™ on Stack Overflow

MySQL Transaction+ PHP issue in Mysql

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related