Gecko [ www.jdcpatala.org ]

Name	Size	Permission
check_type.inc	2.29 KB	-rw-r--r--
create_recursive_construct.inc	16.61 KB	-rw-r--r--
create_recursive_construct_stm...	16.48 KB	-rw-r--r--
delayed_slave_wait_on_query.in...	1.59 KB	-rw-r--r--
gtid_disconnect_drop_temporary...	3.11 KB	-rw-r--r--
rpl_EE_err.test	1.03 KB	-rw-r--r--
rpl_auto_increment.test	8.63 KB	-rw-r--r--
rpl_auto_increment_insert_view...	1.37 KB	-rw-r--r--
rpl_auto_increment_invoke_trig...	2.47 KB	-rw-r--r--
rpl_autoinc_func_invokes_trigg...	1.78 KB	-rw-r--r--
rpl_binlog_max_cache_size.test	18.23 KB	-rw-r--r--
rpl_blackhole.test	1.03 KB	-rw-r--r--
rpl_change_master.test	10.46 KB	-rw-r--r--
rpl_change_master_bind.inc	2.23 KB	-rw-r--r--
rpl_charset.test	4.45 KB	-rw-r--r--
rpl_check_gtid.inc	4.87 KB	-rw-r--r--
rpl_commit_after_flush.test	272 B	-rw-r--r--
rpl_conflicts.test	5.45 KB	-rw-r--r--
rpl_crash_safe.inc	4.13 KB	-rw-r--r--
rpl_crash_safe.test	7.61 KB	-rw-r--r--
rpl_create_msr_channels.inc	2.53 KB	-rw-r--r--
rpl_db_stmts_ignored.inc	1.7 KB	-rw-r--r--
rpl_ddl.test	21.62 KB	-rw-r--r--
rpl_deadlock.test	6.05 KB	-rw-r--r--
rpl_delete_no_where.test	523 B	-rw-r--r--
rpl_do_table_filter_insensitiv...	492 B	-rw-r--r--
rpl_do_table_filter_sensitive....	491 B	-rw-r--r--
rpl_drop_create_temp_table.inc	29.79 KB	-rw-r--r--
rpl_drop_create_temp_table.tes...	17.8 KB	-rw-r--r--
rpl_drop_multiple_tables.inc	4 KB	-rw-r--r--
rpl_drop_multiple_tables_in_mu...	1.18 KB	-rw-r--r--
rpl_extra_col_master.test	31.93 KB	-rw-r--r--
rpl_extra_col_slave.test	28.76 KB	-rw-r--r--
rpl_failed_optimize.test	666 B	-rw-r--r--
rpl_filters.test	530 B	-rw-r--r--
rpl_flsh_tbls.test	1.86 KB	-rw-r--r--
rpl_foreign_key.test	1.7 KB	-rw-r--r--
rpl_generate_mts_gap.test	1.6 KB	-rw-r--r--
rpl_generate_mts_gap_with_miss...	3.22 KB	-rw-r--r--
rpl_generate_mts_gap_with_miss...	408 B	-rw-r--r--
rpl_get_master_version_and_clo...	2.88 KB	-rw-r--r--
rpl_gtid_drop_table.inc	1.11 KB	-rw-r--r--
rpl_gtid_mixed_rows_and_stmts_...	3.35 KB	-rw-r--r--
rpl_gtid_mts_relay_log_recover...	4.39 KB	-rw-r--r--
rpl_gtid_temp_table_in_func_or...	3.69 KB	-rw-r--r--
rpl_gtids_restart_slave_io_los...	3.48 KB	-rw-r--r--
rpl_heartbeat_2slaves.inc	4.27 KB	-rw-r--r--
rpl_ignore_super_read_only.tes...	6.19 KB	-rw-r--r--
rpl_ignore_table_filter_insens...	508 B	-rw-r--r--
rpl_ignore_table_filter_sensit...	506 B	-rw-r--r--
rpl_implicit_commit_binlog.tes...	17.22 KB	-rw-r--r--
rpl_innodb.test	4.53 KB	-rw-r--r--
rpl_insert_id.test	15.12 KB	-rw-r--r--
rpl_insert_id_pk.test	2.65 KB	-rw-r--r--
rpl_insert_ignore.test	4.9 KB	-rw-r--r--
rpl_kill_query.inc	4.14 KB	-rw-r--r--
rpl_loaddata.test	8.91 KB	-rw-r--r--
rpl_loaddata_s.inc	896 B	-rw-r--r--
rpl_loadfile.test	1.08 KB	-rw-r--r--
rpl_log.test	3.9 KB	-rw-r--r--
rpl_lower_case_table_names.tes...	4.07 KB	-rw-r--r--
rpl_max_relay_size.test	2.58 KB	-rw-r--r--
rpl_mixing_engines.inc	23.15 KB	-rw-r--r--
rpl_mixing_engines.test	54.43 KB	-rw-r--r--
rpl_mts_crash_safe.inc	5.92 KB	-rw-r--r--
rpl_mts_crash_safe.test	6.74 KB	-rw-r--r--
rpl_mts_execute_partial_trx_in...	1.11 KB	-rw-r--r--
rpl_mts_pending_events.inc	4.91 KB	-rw-r--r--
rpl_mts_relay_log_recovery.tes...	2.93 KB	-rw-r--r--
rpl_mts_transaction_retry.inc	7.9 KB	-rw-r--r--
rpl_multi_query.test	782 B	-rw-r--r--
rpl_multi_source_corrupt_repos...	18.31 KB	-rw-r--r--
rpl_multi_source_generate_mts_...	2.11 KB	-rw-r--r--
rpl_multi_update.test	805 B	-rw-r--r--
rpl_multi_update2.test	1.35 KB	-rw-r--r--
rpl_multi_update3.test	4.52 KB	-rw-r--r--
rpl_not_null.test	10.76 KB	-rw-r--r--
rpl_parallel_ddl.test	5.35 KB	-rw-r--r--
rpl_parallel_load.test	6.1 KB	-rw-r--r--
rpl_parallel_load_innodb.test	7 KB	-rw-r--r--
rpl_parallel_recovery_core.tes...	6.74 KB	-rw-r--r--
rpl_partition.test	7.88 KB	-rw-r--r--
rpl_perfschema_applier_xa_stat...	2.96 KB	-rw-r--r--
rpl_perfschema_order_by.test	3.12 KB	-rw-r--r--
rpl_record_compare.test	2.1 KB	-rw-r--r--
rpl_relayrotate.test	2.68 KB	-rw-r--r--
rpl_replication_observers_exam...	2.61 KB	-rw-r--r--
rpl_reset_slave.test	3.82 KB	-rw-r--r--
rpl_reset_slave_all_thread_saf...	1.2 KB	-rw-r--r--
rpl_rollback_to_savepoint.inc	7.54 KB	-rw-r--r--
rpl_row_001.test	2.68 KB	-rw-r--r--
rpl_row_UUID.test	2.61 KB	-rw-r--r--
rpl_row_basic.test	24.58 KB	-rw-r--r--
rpl_row_blob.test	5.57 KB	-rw-r--r--
rpl_row_empty_imgs.test	5.69 KB	-rw-r--r--
rpl_row_event_max_size.inc	4.05 KB	-rw-r--r--
rpl_row_func003.test	3.13 KB	-rw-r--r--
rpl_row_idempotency.test	8.39 KB	-rw-r--r--
rpl_row_img.test	8.23 KB	-rw-r--r--
rpl_row_img_blobs.test	5.82 KB	-rw-r--r--
rpl_row_img_diff_indexes.test	9.11 KB	-rw-r--r--
rpl_row_sp002.test	5.26 KB	-rw-r--r--
rpl_row_sp003.test	2.02 KB	-rw-r--r--
rpl_row_sp006.test	2.78 KB	-rw-r--r--
rpl_row_sp007.test	1.5 KB	-rw-r--r--
rpl_row_tabledefs.test	7.97 KB	-rw-r--r--
rpl_seconds_behind_master_mts_...	12.04 KB	-rw-r--r--
rpl_seconds_behind_master_mts_...	10.2 KB	-rw-r--r--
rpl_semi_sync_ack_thread.inc	676 B	-rw-r--r--
rpl_semi_sync_after_sync.test	4.82 KB	-rw-r--r--
rpl_semi_sync_deadlock.test	1.46 KB	-rw-r--r--
rpl_semi_sync_group_commit_dea...	1.71 KB	-rw-r--r--
rpl_set_null.test	2.85 KB	-rw-r--r--
rpl_sp.test	17.69 KB	-rw-r--r--
rpl_split_statements.test	17.96 KB	-rw-r--r--
rpl_start_stop_slave.test	5.4 KB	-rw-r--r--
rpl_stm_EE_err2.test	1.5 KB	-rw-r--r--
rpl_stm_create_if_not_exists.t...	6.87 KB	-rw-r--r--
rpl_stop_middle_group.test	5.17 KB	-rw-r--r--
rpl_stop_slave.test	1.56 KB	-rw-r--r--
rpl_stress_test.inc	2.21 KB	-rw-r--r--
rpl_sv_relay_space.test	921 B	-rw-r--r--
rpl_sync_relay_log_info.inc	9.02 KB	-rw-r--r--
rpl_sync_relay_log_info_assert...	3.23 KB	-rw-r--r--
rpl_temp_error.test	623 B	-rw-r--r--
rpl_temporary.test	10.6 KB	-rw-r--r--
rpl_test_framework.inc	2.42 KB	-rw-r--r--
rpl_tmp_table_and_DDL.test	4.8 KB	-rw-r--r--
rpl_trig004.test	1.7 KB	-rw-r--r--
rpl_truncate.test	425 B	-rw-r--r--
rpl_truncate_helper.test	976 B	-rw-r--r--
rpl_trx_boundary_parser.inc	1.59 KB	-rw-r--r--
rpl_trx_boundary_parser_all_st...	7.19 KB	-rw-r--r--
rpl_trx_boundary_parser_one_st...	3.69 KB	-rw-r--r--
rpl_trx_boundary_parser_warnin...	4.51 KB	-rw-r--r--
rpl_variables_stm.test	24.26 KB	-rw-r--r--
rpl_xa_gtid_next.inc	2.46 KB	-rw-r--r--
rpl_xa_mixed_engines.inc	1.89 KB	-rw-r--r--
type_conversions.test	23.7 KB	-rw-r--r--

Code Editor : 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;
source include/not_gtid_enabled.inc;

# 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;
eval create table t1 (a varchar(100)) engine=$DEFAULT_ENGINE;
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;

# Change the timestamp back to current
set timestamp = DEFAULT;

# SP with definer's right

drop table t1;
eval create table t1 (a int) engine=$DEFAULT_ENGINE;
create table t2 like t1;

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

# let's create a non-privileged user
set @orig_sql_mode= @@sql_mode;
set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
set sql_mode= @orig_sql_mode;
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;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
alter table t2 add unique (a);
SET sql_mode = default;
drop function fn1;

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

delimiter ;|

--error ER_DUP_ENTRY
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;
eval create table t1 (a int) engine=$DEFAULT_ENGINE;
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.

eval CREATE TABLE t1(col VARCHAR(10)) engine=$DEFAULT_ENGINE;

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...
--echo ---> connection: master
--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

--echo
--echo ---> connection: 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 ---> connection: master

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

SHOW CREATE PROCEDURE p1;
SHOW CREATE FUNCTION f1;

# Cleanup.

--echo
--echo ---> connection: master
--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;
eval create table t ( t integer ) engine=$DEFAULT_ENGINE;
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.
--let $mask_binlog_commit_events= 1
--source include/show_binlog_events.inc
--let $mask_binlog_commit_events= 0

# 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`;
--let $mysqlbinlog_parameters= --short-form $MYSQLD_DATADIR/master-bin.000001
--source include/mysqlbinlog.inc
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 #
--echo # --> connection default
connection default;
--disable_warnings
drop table if exists t1, t2;
drop function if exists t1;
--enable_warnings
eval create table t1 (a int) engine=$DEFAULT_ENGINE;
eval create table t2 (a int) engine=$DEFAULT_ENGINE as select 1 as a;
create function f1() returns int deterministic return (select max(a) from t2);
lock table t2 write;
--echo # --> connection master
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_or_abort.inc
--echo # Sending 'drop function f1'. It will wait till insert finishes.
--send drop function f1;
--echo # --> connection default
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_or_abort.inc
--echo # Now let's let 'insert' go through...
unlock tables;
--echo # --> connection master
connection master;
--echo # Reaping 'insert into t1 (a) values (f1())'...
--reap
--echo # --> connection master1
connection master1;
--echo # Reaping 'drop function f1'
--reap
--echo # --> connection master
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

eval create table t1 (c1 int) engine=$DEFAULT_ENGINE;
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

${this.title}

Code Editor : rpl_sp.test