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

[30 Aug 2005 9:37] Sergey Petrunya
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.
[30 Aug 2005 11:42] Jonathan Miller
I think this is a duplicate of Bug #12559
[31 Aug 2005 16:01] Jorge del Conde
Tested under Linux w/5.0 from bk
[13 Sep 2005 21:47] Bugs System
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
[14 Sep 2005 6:26] Bugs System
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
[14 Sep 2005 14:39] Bugs System
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
[14 Sep 2005 18:40] Bugs System
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
[14 Sep 2005 21:47] Elliot Murphy
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
[15 Sep 2005 17:35] Paul Dubois
Noted in 5.0.13 changelog,
and the pages mentioned in the bug report
have been update to include the new
statements.