path: root/mysql-test/suite/rpl/t/rpl_sp_effects.test

##########################################
# Change Author: JBM
# Change Date: 2006-05-02
##########################################

# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
-- source include/master-slave.inc

# ****************************************************************
connection master;

SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;

# 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);

SET GLOBAL log_bin_trust_function_creators = 1;

# 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 ORDER BY a;
connection master;
SELECT * FROM t1 ORDER BY a;

# 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 ORDER BY a;
sync_slave_with_master;
connection slave;
SELECT * FROM t2 ORDER BY a;

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 ORDER BY a;

create table t2(a int);
insert into t2 values (10),(11);
--disable_ps2_protocol
SELECT a,f1(a) FROM t2 ORDER BY a;
--enable_ps2_protocol

# This shouldn't put separate 'call f1(3)' into binlog:
insert into t2 select f1(3);
SELECT 'master:',a FROM t1 ORDER BY a;

sync_slave_with_master;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;

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) as f from t2;
--disable_ps2_protocol
select * from v1 order by f;
--enable_ps2_protocol
SELECT 'master:',a FROM t1 ORDER BY a;

sync_slave_with_master;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;

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 ORDER BY a;

sync_slave_with_master;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;

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 ORDER BY a;
sync_slave_with_master;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;

connection master;
drop procedure p1;
drop function f1;
drop table t1,t2;

# BUG#12637: User variables + SPs replication
create table t1 (a int);
delimiter //;
create procedure p1()
begin
  insert into t1 values(@x);
  set @x=@x+1;
  insert into t1 values(@x);
  if (f2()) then
    insert into t1 values(1243);
  end if;
end//

create function f2() returns int
begin
  insert into t1 values(@z);
  set @z=@z+1;
  insert into t1 values(@z);
  return 0;
end//

create function f1() returns int
begin
  insert into t1 values(@y);
  call p1();
  return 0;
end//

delimiter ;//

set @x=10;
set @y=20;
set @z=100;
--disable_ps2_protocol
select f1();
--enable_ps2_protocol

set @x=30;
call p1();

SELECT 'master', a FROM t1 ORDER BY a;
sync_slave_with_master;
connection slave;
SELECT 'slave', a FROM t1 ORDER BY a;

#
# cleanup
#

connection master;
drop table t1;
drop function f1;
drop function f2;
drop procedure p1;
sync_slave_with_master;

#
# bug#26199 Replication Failure on Slave when using stored procs
#           with bit-type parameters

connection master;

create table t2 (b BIT(7));
delimiter //;
create procedure sp_bug26199(bitvalue BIT(7))
begin
  insert into t2 set b = bitvalue;
end //

create function sf_bug26199(b BIT(7)) returns int
begin
  insert into t2 values(b);
  return 0;
end//

DELIMITER ;//



call   sp_bug26199(b'1110');
call   sp_bug26199('\0');
--disable_ps2_protocol
select sf_bug26199(b'1111111');
SET STATEMENT sql_mode = '' FOR
select sf_bug26199(b'101111111');
select sf_bug26199('\'');
--enable_ps2_protocol
select hex(b) from t2;

sync_slave_with_master;
#connection slave;
select hex(b) from t2;

#
# cleanup bug#26199
#
connection master;
drop table t2;
drop procedure sp_bug26199;
drop function sf_bug26199;

SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;

sync_slave_with_master;

#
# Bug#16056537: MYSQLD CRASHES IN ITEM_FUNC_GET_USER_VAR::FIX_LENGTH_AND_DEC()
#
set names utf8;
--delimiter |
CREATE FUNCTION f() RETURNS timestamp DETERMINISTIC
BEGIN RETURN '2012-12-21 12:12:12'; END |
CREATE PROCEDURE p(t timestamp) 
BEGIN
  SET @t = t;
  PREPARE stmt FROM " 
  UPDATE t1 SET a = @t WHERE '2012-12-31 08:00:00' < f() ";
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END |
--delimiter ;
create table t1 (a timestamp);
call p('2012-12-31 08:00:00');
drop table t1;
drop procedure p;
drop function f;

--echo end of the tests
--source include/rpl_end.inc