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

# row-based and statement have expected binlog difference in result files

# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
# Modified by WL#2971.

source include/have_binlog_format_mixed.inc;
source include/master-slave.inc;

set local sql_mode='';
set global sql_mode='';

# we need a db != test, where we don't have automatic grants
--disable_warnings
drop database if exists mysqltest1;
--enable_warnings
create database mysqltest1;
use mysqltest1;
create table t1 (a varchar(100));
sync_slave_with_master;
use mysqltest1;

# ********************** PART 1 : STORED PROCEDURES ***************

# Does the same proc as on master get inserted into mysql.proc ?
# (same definer, same properties...)

connection master;

delimiter |;

# Stored procedures don't have the limitations that functions have
# regarding binlogging: it's ok to create a procedure as not
# deterministic and updating data, while it's not ok to create such a
# function. We test this.

create procedure foo()
begin
  declare b int;
  set b = 8;
  insert into t1 values (b);
  insert into t1 values (unix_timestamp());
end|
delimiter ;|

# we replace columns having times
# (even with fixed timestamp displayed time may changed based on TZ)
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where name='foo' and db='mysqltest1';
sync_slave_with_master;
# You will notice in the result that the definer does not match what
# it is on master, it is a known bug on which Alik is working
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where name='foo' and db='mysqltest1';

connection master;
# see if timestamp used in SP on slave is same as on master
set timestamp=1000000000;
call foo();
select * from t1;
sync_slave_with_master;
select * from t1;

# Now a SP which is not updating tables

connection master;
delete from t1;
create procedure foo2()
  select * from mysqltest1.t1;
call foo2();

# check that this is allowed (it's not for functions):
alter procedure foo2 contains sql;

# SP with definer's right

drop table t1;
create table t1 (a int);
create table t2 like t1;

create procedure foo3()
  deterministic
  insert into t1 values (15);

# let's create a non-privileged user
grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;

# ToDo: BUG#14931: There is a race between the last grant binlogging, and
# the binlogging in the new connection made below, causing sporadic test
# failures due to switched statement order in binlog. To fix this we do
# SELECT 1 in the first connection before starting the second, ensuring
# that binlogging is done in the expected order.
# Please remove this SELECT 1 when BUG#14931 is fixed.
SELECT 1;

connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,);
connection con1;

# this routine will fail in the second INSERT because of privileges
delimiter |;
create procedure foo4()
  deterministic
  begin
  insert into t2 values(3);
  insert into t1 values (5);
  end|

delimiter ;|

# I add ,0 so that it does not print the error in the test output,
# because this error is hostname-dependent
--error 1142,0
call foo4(); # invoker has no INSERT grant on table t1 => failure

connection master;
call foo3(); # success (definer == root)
show warnings;

--error 1142,0
call foo4(); # definer's rights => failure

# we test replication of ALTER PROCEDURE
alter procedure foo4 sql security invoker;
call foo4(); # invoker's rights => success
show warnings;

# Note that half-failed procedure calls are ok with binlogging;
# if we compare t2 on master and slave we see they are identical:

select * from t1;
select * from t2;
sync_slave_with_master;
select * from t1;
select * from t2;

# Let's check another failing-in-the-middle procedure
connection master;
delete from t2;
alter table t2 add unique (a);

drop procedure foo4;
delimiter |;
create procedure foo4()
  deterministic
  begin
  insert into t2 values(20),(20);
  end|

delimiter ;|

--error ER_DUP_ENTRY
call foo4();
show warnings;

select * from t2;
sync_slave_with_master;
# check that this failed-in-the-middle replicated right:
select * from t2;

# Test of DROP PROCEDURE

--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where name="foo4" and db='mysqltest1';
connection master;
drop procedure foo4;
select * from mysql.proc where name="foo4" and db='mysqltest1';
sync_slave_with_master;
select * from mysql.proc where name="foo4" and db='mysqltest1';

# ********************** PART 2 : FUNCTIONS ***************

connection master;
drop procedure foo;
drop procedure foo2;
drop procedure foo3;

delimiter |;
# check that needs "deterministic"
--error 1418
create function fn1(x int)
       returns int
begin
       insert into t1 values (x);
       return x+2;
end|
create function fn1(x int)
       returns int
       deterministic
begin
       insert into t1 values (x);
       return x+2;
end|

delimiter ;|
delete t1,t2 from t1,t2;
select fn1(20);
insert into t2 values(fn1(21));
select * from t1;
select * from t2;
sync_slave_with_master;
select * from t1;
select * from t2;

connection master;
delimiter |;

drop function fn1;

create function fn1()
       returns int
       no sql
begin
       return unix_timestamp();
end|

delimiter ;|
# check that needs "deterministic"
--error 1418
alter function fn1 contains sql;

delete from t1;
set timestamp=1000000000;
insert into t1 values(fn1()); 

connection con1;

delimiter |;
--error 1419 # only full-global-privs user can create a function
create function fn2()
       returns int
       no sql
begin
       return unix_timestamp();
end|
delimiter ;|
connection master;
set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
set global log_bin_trust_function_creators=0;
set global log_bin_trust_function_creators=1;
# slave needs it too otherwise will not execute what master allowed:
connection slave;
set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
set global log_bin_trust_function_creators=1;

connection con1;

delimiter |;
create function fn2()
       returns int
       no sql
begin
       return unix_timestamp();
end|
delimiter ;|

connection master;

# Now a function which is supposed to not update tables
# as it's "reads sql data", so should not give error even if
# non-deterministic.

delimiter |;
create function fn3()
       returns int
       not deterministic
       reads sql data
begin
  return 0;
end|
delimiter ;|

select fn3();
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where db='mysqltest1';
select * from t1;

sync_slave_with_master;
use mysqltest1;
select * from t1;
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
select * from mysql.proc where db='mysqltest1';

# Let's check a failing-in-the-middle function
connection master;
delete from t2;
alter table t2 add unique (a);

drop function fn1;

delimiter |;
create function fn1(x int)
       returns int
begin
  insert into t2 values(x),(x);
  return 10;
end|

delimiter ;|

do fn1(100);

--error ER_DUP_ENTRY
select fn1(20);

select * from t2;
sync_slave_with_master;

# check that this failed-in-the-middle replicated right:
select * from t2;

# ********************** PART 3 : TRIGGERS ***************

connection con1;
# now fails due to missing trigger grant (err 1142 i/o 1227) due to new
# check in sql_trigger.cc (v1.44) by anozdrin on 2006/02/01  --azundris
--error ER_TABLEACCESS_DENIED_ERROR
create trigger trg before insert on t1 for each row set new.a= 10;

connection master;
delete from t1;
# TODO: when triggers can contain an update, test that this update
# does not go into binlog.
# I'm not setting user vars in the trigger, because replication of user vars
# would take care of propagating the user var's value to slave, so even if
# the trigger was not executed on slave it would not be discovered.
create trigger trg before insert on t1 for each row set new.a= 10;
insert into t1 values (1);
select * from t1;
sync_slave_with_master;
select * from t1;

connection master;
delete from t1;
drop trigger trg;
insert into t1 values (1);
select * from t1;
sync_slave_with_master;
select * from t1;


# ********************** PART 4 : RELATED FIXED BUGS ***************


#
# Test for bug #13969 "Routines which are replicated from master can't be
# executed on slave".
# 
connection master;
create procedure foo()
  not deterministic
  reads sql data
  select * from t1;
sync_slave_with_master;
# This should not fail
call foo();
connection master;
drop procedure foo;
sync_slave_with_master;


# Clean up
connection master;
drop function fn1;
drop database mysqltest1;
drop user "zedjzlcsjhd"@127.0.0.1;
use test;
sync_slave_with_master;
use test;

#
# Bug#14077 "Failure to replicate a stored function with a cursor":
# verify that stored routines with cursors work on slave. 
#
connection master;
--disable_warnings
drop function if exists f1;
--enable_warnings
delimiter |;
create function f1() returns int reads sql data
begin
  declare var integer;
  declare c cursor for select a from v1;
  open c;
  fetch c into var;
  close c;
  return var;
end|
delimiter ;|
create view v1 as select 1 as a;
create table t1 (a int);
insert into t1 (a) values (f1());
select * from t1;
drop view v1;
drop function f1;
sync_slave_with_master;
connection slave;
select * from t1;

#
# Bug#16621 "INSERTs in Stored Procedures causes data corruption in the Binary
# Log for 5.0.18"
#

# Prepare environment.

connection master;

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
--enable_warnings

# Test case.

CREATE TABLE t1(col VARCHAR(10));

CREATE PROCEDURE p1(arg VARCHAR(10))
  INSERT INTO t1 VALUES(arg);

CALL p1('test');

SELECT * FROM t1;

sync_slave_with_master;
SELECT * FROM t1;

# Cleanup
connection master;
DROP PROCEDURE p1;


#
# BUG#20438: CREATE statements for views, stored routines and triggers can be
# not replicable.
#

--echo
--echo ---> Test for BUG#20438

# Prepare environment.

--echo
--echo ---> Preparing environment...
--connection master

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
--enable_warnings

--echo
--echo ---> Synchronizing slave with master...

--sync_slave_with_master

--connection master

# Test.

--echo
--echo ---> Creating procedure...

/*!50003 CREATE PROCEDURE p1() SET @a = 1 */;

/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;

--echo
--echo ---> Checking on master...

SHOW CREATE PROCEDURE p1;
SHOW CREATE FUNCTION f1;

--echo
--echo ---> Synchronizing slave with master...

--sync_slave_with_master

--echo
--echo ---> Checking on slave...

SHOW CREATE PROCEDURE p1;
SHOW CREATE FUNCTION f1;

# Cleanup.

--connection master

--echo
--echo ---> Cleaning up...

DROP PROCEDURE p1;
DROP FUNCTION f1;

--sync_slave_with_master
--connection master


# cleanup
connection master;
drop table t1;
sync_slave_with_master;

#
# Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS"
#

connection master;
--disable_warnings
drop database if exists mysqltest;
drop database if exists mysqltest2;
--enable_warnings
create database mysqltest;
create database mysqltest2;
use mysqltest2;
create table t ( t integer );
create procedure mysqltest.test() begin end;
insert into t values ( 1 );
--error ER_BAD_DB_ERROR
create procedure `\\`.test() begin end;

#
# BUG#19725: Calls to stored function in other database are not
# replicated correctly in some cases
#

connection master;
delimiter |;
create function f1 () returns int
begin
  insert into t values (1);
  return 0;
end|
delimiter ;|
sync_slave_with_master;
# Let us test if we don't forget to binlog the function's database
connection master;
use mysqltest;
set @a:= mysqltest2.f1();
sync_slave_with_master;
connection master;

# Final inspection which verifies how all statements of this test file
# were written to the binary log.
--source include/show_binlog_events.inc


# Restore log_bin_trust_function_creators to its original value.
# This is a cleanup for all parts above where we tested stored
# functions and triggers.
connection slave;
set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
connection master;
set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;

# Clean up
drop database mysqltest;
drop database mysqltest2;
sync_slave_with_master;

#
# Bug#36570: Parse error of CREATE PROCEDURE stmt with comments on slave
#
connection master;
use test;
delimiter |;

/*!50001 create procedure `mysqltestbug36570_p1`() */
begin
	select 1;
end|

use mysql|
create procedure test.` mysqltestbug36570_p2`(/*!50001 a int*/)`label`:
begin
	select a;
end|

/*!50001 create function test.mysqltestbug36570_f1() */
	returns int
	/*!50001 deterministic */
begin
	return 3;
end|
use test|

delimiter ;|

--replace_column 5 t 6 t
show procedure status like '%mysqltestbug36570%';
show create procedure ` mysqltestbug36570_p2`;

sync_slave_with_master;
connection slave;

--replace_column 5 t 6 t
show procedure status like '%mysqltestbug36570%';
show create procedure ` mysqltestbug36570_p2`;
call ` mysqltestbug36570_p2`(42);

--replace_column 5 t 6 t
show function status like '%mysqltestbug36570%';

connection master;
flush logs;
let $MYSQLD_DATADIR= `select @@datadir`;
--replace_regex /$MYSQL_TEST_DIR/MYSQL_TEST_DIR/ /TIMESTAMP=[0-9]*/TIMESTAMP=t/
--exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000001
use test;
drop procedure mysqltestbug36570_p1;
drop procedure ` mysqltestbug36570_p2`;
drop function mysqltestbug36570_f1;
--echo End of 5.0 tests
--echo # End of 5.1 tests
--echo #
--echo # Test Bug#30977 Concurrent statement using stored
--echo # function and DROP FUNCTION breaks SBR.
--echo #
--echo # Demonstrate that stored function DDL can not go through,
--echo # or, worse yet, make its way into the binary log, while
--echo # the stored function is in use.
--echo # For that, try to insert a result of a stored function
--echo # into a table. Block the insert in the beginning, waiting
--echo # on a table lock. While insert is blocked, attempt to
--echo # drop the routine. Verify that this attempt 
--echo # blocks and waits for INSERT to complete. Commit and 
--echo # reap the chain of events. Master and slave must contain
--echo # identical data. Statements in the binrary log must be
--echo # consistent with data in the table.
--echo #
connection default;
--disable_warnings
drop table if exists t1, t2;
drop function if exists t1;
--enable_warnings
create table t1 (a int);
create table t2 (a int) as select 1 as a;
create function f1() returns int deterministic return (select max(a) from t2);
lock table t2 write;
connection master;
--echo # Sending 'insert into t1 (a) values (f1())'...
--send insert into t1 (a) values (f1())
connection master1;
--echo # Waitng for 'insert into t1 ...' to get blocked on table lock...
let $wait_condition=select count(*)=1 from information_schema.processlist
where state='Waiting for table metadata lock' and
      info='insert into t1 (a) values (f1())';
--source include/wait_condition.inc
--echo # Sending 'drop function f1'. It will wait till insert finishes.
--send drop function f1;
connection default;
--echo # Check that 'drop function f1' gets blocked.
let $wait_condition=select count(*)=1 from information_schema.processlist
where state='Waiting for stored function metadata lock' and info='drop function f1';
--source include/wait_condition.inc
--echo # Now let's let 'insert' go through...
unlock tables;
connection master;
--echo # Reaping 'insert into t1 (a) values (f1())'...
--reap
connection master1;
--echo # Reaping 'drop function f1'
--reap
connection master;
select * from t1;
sync_slave_with_master;
connection slave;
select * from  t1;
# Cleanup
connection master;
drop table t1, t2;
--error ER_SP_DOES_NOT_EXIST
drop function f1;


--echo #
--echo # Bug #11918 Can't use a declared variable in LIMIT clause
--echo #
--source include/rpl_reset.inc

create table t1 (c1 int);
insert into t1 (c1) values
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");

create procedure p1(p1 integer)
  delete from t1 limit p1; 

set @save_binlog_format=@@session.binlog_format;
set @@session.binlog_format=STATEMENT;

--disable_warnings
call p1(NULL);
call p1(0);
call p1(1);
call p1(2);
call p1(3);
--enable_warnings

select * from t1;
sync_slave_with_master;
connection slave;
select * from t1;
connection master;
--disable_warnings
call p1(-1);
--enable_warnings
select * from t1;
sync_slave_with_master;
connection slave;
select * from t1;
connection master;

--echo # Cleanup
set @@session.binlog_format=@save_binlog_format;
drop table t1;
drop procedure p1;

--echo # End of 5.5 tests.


# Cleanup
sync_slave_with_master;
--source include/rpl_end.inc
set global sql_mode=default;