Fix for BUG#12335 (SP replication) : New binlogging strategy for stored PROCEDUREs/FUNCTIONs.

"Interleaved SPs execution is now binlogged properly, "SELECT spfunc()" is binlogged too.
The known remaining issue is binlogging/replication of "a routine is deleted while it is executed" scenario.


mysql-test/r/rpl_sp.result:
  Fix for BUG#12335: updated test cases/results
mysql-test/t/rpl_sp.test:
  Fix for BUG#12335: updated test cases/results
sql/item.cc:
  Fix for BUG#12335 (SP replication): 
   - Added Item_name_const 'function'
   - Addede 'delete reuse' to call dtor on item reuse
sql/item.h:
  Fix for BUG#12335 (SP replication) : Added Item_name_const 'function' + code cleanup
sql/item_create.cc:
   Fix for BUG#12335 (SP replication) : Added Item_name_const 'function'
sql/item_create.h:
   Fix for BUG#12335 (SP replication) : Added Item_name_const 'function'
sql/item_func.cc:
  Fix for BUG#12335 (SP replication) : binary log is now constrolled from within execute_function.
sql/lex.h:
  Fix for BUG#12335 (SP replication) : Added Item_name_const 'function'
sql/log.cc:
  Fix for BUG#12335 (SP replication) : Added MYSQL_LOG::{start|stop}_union_events to allow
  one to temporary disable binlogging but collect a 'union' information about binlog write
  calls.
sql/mysql_priv.h:
  Fix for BUG#12335 (SP replication)
sql/sp_head.cc:
  Fix for BUG#12335 (SP replication) : Now we use different SP binlogging strategy, grep for 
  StoredRoutinesBinlogging for details
sql/sp_head.h:
  Comments added
sql/sp_pcontext.h:
  Comments added
sql/sp_rcontext.h:
  Comments added
sql/sql_class.cc:
  Fix for BUG#12335 (SP replication) : Now we use different SP binlogging strategy, grep for 
  StoredRoutinesBinlogging for details
sql/sql_class.h:
  Fix for BUG#12335 (SP replication) : Added MYSQL_LOG::{start|stop}_union_events to allow
  one to temporary disable binlogging but collect a 'union' information about binlog write
  calls.
sql/sql_delete.cc:
  Fix for BUG#12335: check THD::query_str_binlog_unsuitable when writing to binlog.
sql/sql_insert.cc:
  Fix for BUG#12335: check THD::query_str_binlog_unsuitable when writing to binlog.
sql/sql_lex.cc:
  Fix for BUG#12335 (SP replication): Add ability to extract previous returned token from
  the tokenizer.
sql/sql_lex.h:
  Fix for BUG#12335 (SP replication): Add ability to extract previous returned token from
  the tokenizer.
sql/sql_parse.cc:
  Fix for BUG#12335 (SP replication) : Now we use different SP binlogging strategy, grep for 
  StoredRoutinesBinlogging for details
sql/sql_update.cc:
  Fix for BUG#12335: check THD::query_str_binlog_unsuitable when writing to binlog.
sql/sql_yacc.yy:
  Fix for BUG#12335 (SP replication) : When creating Item_splocal, remember where it is located
  in the query.

1 files changed, 234 insertions, 0 deletions

diff --git a/mysql-test/t/rpl_sp_effects.test b/mysql-test/t/rpl_sp_effects.test
new file mode 100644
index 00000000000..825fd764f4d
--- /dev/null
+++ b/mysql-test/t/rpl_sp_effects.test
@@ -0,0 +1,234 @@
+# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
+
+source include/master-slave.inc;
+
+# ****************************************************************
+connection master;
+
+# cleanup
+--disable_warnings
+drop procedure if exists p1;
+drop procedure if exists p2;
+drop function if exists f1;
+drop table if exists t1,t2;
+drop view if exists v1;
+--enable_warnings
+create table t1 (a int);
+
+# 1. Test simple variables use.
+delimiter //;
+create procedure p1()
+begin
+  declare spv int default 0;
+  while spv < 5 do
+    insert into t1 values(spv+1);
+    set spv=spv+1;
+  end while;
+end//
+delimiter ;//
+
+call p1();
+
+sync_slave_with_master;
+connection slave;
+select * from t1;
+connection master;
+delete from t1;
+
+# 2. Test SP variable name
+delimiter //;
+create procedure p2()
+begin
+  declare a int default 4;
+  create table t2 as select a;
+end//
+delimiter ;//
+
+call p2();
+select * from t2;
+sync_slave_with_master;
+connection slave;
+select * from t2;
+
+connection master;
+drop procedure p1;
+drop procedure p2;
+drop table t2;
+
+# 3. Test FUNCTIONs in various places
+
+delimiter //;
+create function f1(x int) returns int
+begin
+  insert into t1 values(x);
+  return x+1;
+end//
+
+create procedure p1(a int, b int)
+begin
+  declare v int default f1(5);
+  if (f1(6)) then
+    select 'yes';
+  end if;
+  set v = f1(7);
+  while f1(8) < 1 do 
+    select 'this cant be';
+  end while;
+
+end//
+delimiter ;//
+
+call p1(f1(1), f1(2));
+select * from t1;
+
+create table t2(a int);
+insert into t2 values (10),(11);
+select a,f1(a) from t2;
+
+# This shouldn't put separate 'call f1(3)' into binlog:
+insert into t2 select f1(3);
+select 'master:',a from t1;
+
+sync_slave_with_master;
+connection slave;
+select 'slave:',a from t1;
+
+connection master;
+drop procedure p1;
+delete from t1;
+delete from t2;
+
+# 4. VIEWs
+delete from t1;
+insert into t2 values(1),(2);
+create view v1 as select f1(a) from t2;
+select * from v1;
+select 'master:',a from t1;
+
+sync_slave_with_master;
+connection slave;
+select 'slave:',a from t1;
+
+connection master;
+drop view v1;
+delete from t1;
+
+# 5. Prepared statements.
+prepare s1 from 'select f1(?)';
+set @xx=123;
+execute s1 using @xx;
+select 'master:',a from t1;
+
+sync_slave_with_master;
+connection slave;
+select 'slave:',a from t1;
+
+connection master;
+delete from t1;
+
+# 5. Cursors.
+# t2 has (1),(2);
+delimiter //;
+create procedure p1(spv int)
+begin
+  declare c cursor for select f1(spv) from t2;
+  while (spv > 2) do
+    open c;
+    fetch c into spv;
+    close c;
+    set spv= spv - 10;
+  end while;
+end//
+delimiter ;//
+
+call p1(15);
+select 'master:',a from t1;
+sync_slave_with_master;
+connection slave;
+select 'slave:',a from t1;
+
+connection master;
+drop procedure p1;
+drop function f1;
+drop table t1,t2;
+
+sync_slave_with_master;
+
+# The following will produce incorrect results: 
+connection master;
+create table t1 (a int);
+create table t2 (a char(200));
+
+delimiter //;
+create procedure p1()
+begin
+  declare dummy int;
+  while ((select count(*) from t1) < 1) do
+    set dummy = sleep(1);
+  end while;
+end//
+
+create procedure p2()
+begin
+  select f1();
+  
+  call p1();
+  delete from t1 limit 1;
+  
+  select f1();
+  
+  call p1();
+  delete from t1 limit 1;
+  
+  select f1();
+end//
+
+create function f1() returns int 
+begin
+  insert into t2 values('f1-r1');
+  return 0;
+end// 
+delimiter ;//
+
+connection master;
+send call p2();
+
+connection master1;
+delimiter //;
+
+drop function f1//
+create function f1() returns int 
+begin
+  insert into t2 values('f1-r2');
+  return 0;
+end// 
+insert into t1 values (1) //
+
+call p1()//
+
+drop function f1//
+create function f1() returns int 
+begin
+  insert into t2 values('f1-r3');
+  return 0;
+end// 
+insert into t1 values (1) //
+
+call p1()//
+
+delimiter ;//
+
+connection master;
+reap;
+
+select * from t2;
+
+connection slave;
+select * from t2;
+
+# Clean up
+connection master;
+drop table t1;
+
+sync_slave_with_master;
+