Bug #2510 Rollback Not Working with innoDB
Submitted: 26 Jan 2004 3:40 Modified: 26 Jan 2004 8:02
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0.0-alpha-max-debug OS:Windows (Windows 2000 SP3)
Assigned to: CPU Architecture:Any

[26 Jan 2004 3:40] [ name withheld ] 
Description:
After deleting all records from an innoDB table, with AUTOCOMMIT=0, ROLLBACK failed to bring back the deleted records.

If the table is repopulated, by using an INSERT INTO <table-name> ( SELECT * FROM <another-table> ); command, and then ROLLBACK issued, the table is emptied, which is correct.

Thus, it seems that ROLLBACK is not working after a DELETE statement is issued, but will work after an INSERT INTO... statement.

How to repeat:
Log on as root, using mySQL database.

CREATE TABLE XXX AS ( SELECT * FROM USER );

ALTER TABLE XXX TYPE=innoDB;

SET AUTOCOMMIT=0;

SELECT COUNT( * ) FROM XXX;   *** at least one record is found: OK ***

DELETE FROM XXX;

SELECT COUNT( * ) FROM XXX;   *** 0 records found OK ***

ROLLBACK;

SELECT COUNT( * ) FROM XXX;   *** 0 records found: NOT OK!!! ***

INSERT INTO XXX ( SELECT * FROM USER );

SELECT COUNT( * ) FROM XXX;   *** at least one record is found: OK ***

ROLLBACK;

SELECT COUNT( * ) FROM XXX;   *** 0 records found: OK ***

Suggested fix:
No idea!!!
[26 Jan 2004 7:04] [ name withheld ] 
Please cancel this bug! There was an error in the setup of the testing.
[26 Jan 2004 7:05] [ name withheld ] 
Please cancel this bug! There was an error in the setup of the testing.
[26 Jan 2004 8:02] Alexander Keremidarski 
Closed upon user request.
[3 May 2011 17:38] Ryan Rivers 
After START TRANSACTION; I UPDATE the information on table by doing UPDATE accounts SET balance=(balance-100) WHERE id=1;
UPDATE accounts SET balance=(balance+100) WHERE id=2;

Then I wanted to undo so I excute ROLLBACK; It said "it's successfully executed" BUT  the figure didn't go back to where it was. 

I put the table type InnoDB. I think it shouldn't be having any problem... I don't know why the figure isn't changing even though it said "it's successfully executed"  

Please help me to find what's wrong..

Thanks in advance. 

You can reply me to my email address: forkhyun@gmail.com