Bug #224 ROLLBACK fails for InnoDB table when MyISAM appears in trx
Submitted: 3 Apr 2003 0:24 Modified: 3 Apr 2003 10:50
Reporter: Alexander Keremidarski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:all OS:Any (all)
Assigned to: Heikki Tuuri CPU Architecture:Any

[3 Apr 2003 0:24] Alexander Keremidarski 
Description:
The problem occurs when the 
following are true.

1) Within a single transaction a connection changes InnoDB tables and 
non-InnoDB tables.
2) bin-update logging is on
3) transaction rolls back

When these conditions are true, the InnoDB tables are only partially 
rolled-back.

Looks like binary-log forces such transaction to work in implicit AUTOCOMMIT=1 mode.

How to repeat:
turn on bin-update-log and execute following:

CREATE TABLE T_SEQ (
        NEXT_T         BIGINT NOT NULL PRIMARY KEY
) TYPE=MyISAM;

CREATE TABLE B (
        B_ID           INTEGER NOT NULL PRIMARY KEY
) TYPE=InnoDB;

SET AUTOCOMMIT=0;
INSERT INTO B ( B_ID ) VALUES ( 1 );
INSERT INTO T_SEQ ( NEXT_T ) VALUES ( 1 );
ROLLBACK;

SELECT * FROM B;
+------+
| B_ID |
+------+
|    1 |
+------+
[3 Apr 2003 10:50] Indrek Siitan 
ChangeSet 1.1437
Will be fixed in 4.0.13