~drizzle-trunk/drizzle/development

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

# 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 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 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...

--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...

--save_master_pos

--connection slave

--sync_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...

--save_master_pos

--connection slave

--sync_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;

--save_master_pos

--connection slave

--sync_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.

set global log_bin_trust_function_creators=0;

connection master;

set global log_bin_trust_function_creators=0;

# Clean up

drop database mysqltest;

drop database mysqltest2;

sync_slave_with_master;

--echo End of 5.0 tests

--echo End of 5.1 tests