MySQL Bugs: #12334: If SP is inside transaction, BEGIN ..ROLLBACK are not written to the binary log

Bug #12334	If SP is inside transaction, BEGIN ..ROLLBACK are not written to the binary log
Submitted:	3 Aug 2005 6:45	Modified:	6 Oct 2005 13:59
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.11	OS:	Linux (linux)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
If SP is inside transaction, BEGIN ..ROLLBACK (START TRANSACTION .. ROLLBACK) are not written to the binary log. As a result MySQL rollbacks all changes made inside SP on the master but not on the slave.

On the master:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> call sp1();
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from tbl1;
+------+------+
| col1 | col2 |
+------+------+
|    1 |  100 |
+------+------+
1 row in set (0.00 sec)

On the slave:
mysql> select * from tbl1;
+------+------+
| col1 | col2 |
+------+------+
|    1 |  200 |
+------+------+
1 row in set (0.00 sec)

How to repeat:
1. set up replication
2. Create table and stored procedure on the master:
CREATE TABLE tbl1 (col1 INTEGER PRIMARY KEY , col2 INTEGER ) ENGINE=InnoDB;
INSERT INTO tbl1 VALUES(1,100);
delimiter //
CREATE PROCEDURE sp1 ()
DETERMINISTIC
BEGIN
UPDATE tbl1 SET col2=200 WHERE col1=1;
END
//
delimiter ;
3. Check they was replicated.
4. 
start transaction;
call sp1();
rollback;

5. Execute the following SELECT on the master and on the slave and compare the results:
select * from tbl1;

This bug is a duplicate of http://bugs.mysql.com/bug.php?id=12559

This has been fixed in 5.0.12. I've verified that with 5.0.12 binary.

Victoria,
I'm have not been involved with this bug at all. Please talk to SergeyP who said it was fixed or to any other people listed in "show progress log", for documentation.

I'm setting this from Closed -> Need Doc Info, because I don't believe the change has been documented anywhere in the 5.0.12 and following change logs.  Please correct me if I'm wrong.

The last I can see, this bug requires some info from SergeyP in order for the docs team to know what to say in the manual.

Regards,

Timothy

This bug has been fixed by fix for BUG#12335 (which is mentioned in the changelog).

Accurate description of this bug:

All calls to PROCEDUREs (not counting calls from FUNCTIONs/TRIGGERS) made
inside transaction were immediately written into the binary log, so any
side-effects would be applied on the slave even if the master rolled the
transaction back.

Fix for BUG#12335 caused CALL statements to never be written to binary log which
resolved this problem.

Noted in 5.0.12 changelog.

Will also modify section on binary logging
of stored routines.