MySQL Bugs: #12870: CREATE PROCEDURE followed by ROLLBACK is not replicated

Bug #12870	CREATE PROCEDURE followed by ROLLBACK is not replicated
Submitted:	30 Aug 2005 9:37	Modified:	15 Sep 2005 17:35
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.13	OS:
Assigned to:	Elliot Murphy	CPU Architecture:	Any

Description:
CREATE PROCEDURE followed by ROLLBACK is not replicated in some cases.

How to repeat:
Run the below test:
---t/rpl_a.test---
source include/master-slave.inc;

create table t1 (a int) engine=innodb;

set autocommit=0;
begin;
insert into t1 values(1); # A
create procedure p1() reads sql data select 'this is p1'; # B
rollback;

select * from t1;
call p1();

save_master_pos;
connection slave;
sync_with_master;

select * from t1;
call p1();
---eof---
And get this error:
/home/psergey/mysql-5.0-rpl-bug/client/.libs/mysqltest: At line 19: query 'call p1()' failed: 1305: PROCEDURE test.p1 does not exist

It's interesting that if one swaps lines marked A and B, the procedure gets created on the slave and the test succeeds.

Suggested fix:
We should consider making CREATE/DROP ROUTINE statements to cause implicit commit.
Discussion about BUG#6883 hints that ppl agree on this, but now CREATE/DROP ROUTINE statements do not cause implicit commit.
Also fix for BUG#12815 depends on fix for this bug.

I think this is a duplicate of Bug #12559

Tested under Linux w/5.0 from bk

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29772

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29792

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29847

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29863

Fixed for 5.0.13. Now the following statements cause implicit commit:

CREATE PROCEDURE
ALTER PROCEDURE
DROP PROCEDURE
CREATE FUNCTION
ALTER FUNCTION
DROP FUNCTION

Probably both these pages need to be updated:
http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html
http://dev.mysql.com/doc/mysql/en/implicit-commit.html

Noted in 5.0.13 changelog,
and the pages mentioned in the bug report
have been update to include the new
statements.