Below is the list of changes that have just been committed into a local
5.0 repository of guilhem. When guilhem does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.2003 05/10/03 14:07:27 guilhem@stripped +16 -0
WL#1012: companion of my previous cset
1) fix for a memory corruption; such problem occurs frequently (see BUG#13360) so I add
an assertion to catch it in a more frank manner (without adding any false positive).
2) if a routine fails in the middle of its execution, no warning should be issued in RBR.
3) reorganization of tests which test replication of routines: they were quite outdated (old
comments now wrong since binlogging of procedure calls was changed in 5.0 in August). We now do
one big SHOW BINLOG EVENTS at the end of the test, instead of many small ones, this is easier
to maintain and has no drawback. We have to use mysqltest as the database to test against.
mysql-test/t/rpl_row_sp000-slave.opt
1.1 05/10/03 14:07:22 guilhem@stripped +1 -0
New BitKeeper file ``mysql-test/t/rpl_row_sp000-slave.opt''
mysql-test/t/rpl_row_sp000-master.opt
1.1 05/10/03 14:07:22 guilhem@stripped +1 -0
New BitKeeper file ``mysql-test/t/rpl_row_sp000-master.opt''
mysql-test/r/rpl_row_sp000.result
1.1 05/10/03 14:07:22 guilhem@stripped +325 -0
New BitKeeper file ``mysql-test/r/rpl_row_sp000.result''
wl1012-review-pending-comments.txt
1.12 05/10/03 14:07:22 guilhem@stripped +4 -0
more TODO.
sql/sql_parse.cc
1.468 05/10/03 14:07:22 guilhem@stripped +1 -1
If a routine fails in the middle of its execution, and it's RBR, no warning should be issued (because no problem).
sql/slave.cc
1.262 05/10/03 14:07:22 guilhem@stripped +1 -1
method needs rli now.
mysql-test/t/rpl_row_sp000-slave.opt
1.0 05/10/03 14:07:22 guilhem@stripped +0 -0
BitKeeper file /home/mysql_src/mysql-5.0-wl1012/mysql-test/t/rpl_row_sp000-slave.opt
mysql-test/t/rpl_row_sp000-master.opt
1.0 05/10/03 14:07:22 guilhem@stripped +0 -0
BitKeeper file /home/mysql_src/mysql-5.0-wl1012/mysql-test/t/rpl_row_sp000-master.opt
mysql-test/r/rpl_row_sp000.result
1.0 05/10/03 14:07:22 guilhem@stripped +0 -0
BitKeeper file /home/mysql_src/mysql-5.0-wl1012/mysql-test/r/rpl_row_sp000.result
sql/log_event.h
1.128 05/10/03 14:07:21 guilhem@stripped +1 -1
method needs rli now
sql/log_event.cc
1.212 05/10/03 14:07:21 guilhem@stripped +31 -3
An assertion which will help RBR debugging (do crash instead of corrupting memory).
A fix for a crash I got a few times (due to lock_tables() relying on thd->lex): init lex.
As we close_thread_tables() at end of slave SQL thread (because we must), we also must
clear table mappings because they are stale.
mysql-test/t/sp.test
1.154 05/10/03 14:07:21 guilhem@stripped +4 -0
Between statement-based and row-based binlogging we now have a minor difference in the result:
a warning is in the first case only. As this warning is already tested in rpl_stm_sp.test,
it is preferrable to hide it in sp.test, thus avoiding a duplication of this 4200-line test
into stm_sp.test and row_sp.test.
mysql-test/t/rpl_stm_sp.test
1.9 05/10/03 14:07:21 guilhem@stripped +47 -44
Cleanup in this test: out-of-date comments, doing one single big SHOW BINLOG EVENTS instead
of many small ones. Using database mysqltest instead of mtest (as usual: because the testsuite
can be run on a server with important data so don't use whatever name). Testing one more case
of "function failing in the middle".
Soon I'll backport this test to the main 5.0 (because that one has the same out-of-date comments,
ideally it should have been updated when the binlogging of procedure calls was changed).
mysql-test/t/rpl_row_sp000.test
1.5 05/10/03 14:07:21 guilhem@stripped +176 -191
Cleanup in this test: out-of-date comments, doing one single big SHOW BINLOG EVENTS instead
of many small ones. Using database mysqltest instead of mtest (as usual: because the testsuite
can be run on a server with important data so don't use whatever name). Testing one more case
of "function failing in the middle".
mysql-test/t/rpl_row_UUID.test
1.6 05/10/03 14:07:21 guilhem@stripped +34 -10
this test used to test a procedure, now I make it test a function too.
mysql-test/t/disabled.def
1.29 05/10/03 14:07:21 guilhem@stripped +0 -1
test enabled as bug is fixed
mysql-test/r/sp.result
1.158 05/10/03 14:07:21 guilhem@stripped +0 -6
result update
mysql-test/r/rpl_stm_sp.result
1.10 05/10/03 14:07:21 guilhem@stripped +103 -67
result update
mysql-test/r/rpl_row_UUID.result
1.2 05/10/03 14:07:21 guilhem@stripped +18 -4
result update
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: guilhem
# Host: gbichot3.local
# Root: /home/mysql_src/mysql-5.0-wl1012
--- 1.211/sql/log_event.cc 2005-09-30 18:00:15 +02:00
+++ 1.212/sql/log_event.cc 2005-10-03 14:07:21 +02:00
@@ -1578,6 +1578,23 @@
clear_all_errors(thd, rli);
/*
+ If the condition of this assertion is false, slave would do wrong things
+ (at least reading freed memory), so we prefer to detect it early (helps
+ debugging, adds no false positive).
+ Why would it do wrong things: because it means that prior to this
+ Query_log_event, there has been a Table_map_log_event, and there has not
+ been the last Rows_log_event (the one with TRANS_END_F) corresponding to
+ this Table_map_log_event (if there had been,
+ rli->m_table_map->clear_tables() would have been called and so assertion
+ would be true). So we know that this Rows_log_event is going to come after
+ us. But Query_log_event is going to call close_thread_tables() which
+ will make the 'table' pointer recorded in rli->m_table_map point to freed
+ memory. This freed memory will be used by the Rows_log_event => problem.
+ See BUG#13360.
+ */
+ DBUG_ASSERT(!rli->m_table_map.count());
+
+ /*
Note: We do not need to execute reset_one_shot_variables() if this
db_ok() test fails.
Reason: The db stored in binlog events is the same for SET and for
@@ -2717,6 +2734,9 @@
thd->query_length= 0; // Should not be needed
thd->query_error= 0;
clear_all_errors(thd, rli);
+
+ /* see Query_log_event::exec_event() and BUG#13360 */
+ DBUG_ASSERT(!rli->m_table_map.count());
/*
Usually mysql_init_query() is called by mysql_parse(), but we need it here
as the present method does not call mysql_parse().
@@ -5084,6 +5104,13 @@
*/
DBUG_ASSERT(rli->sql_thd == thd);
+ /*
+ lock_tables() reads the contents of thd->lex, so they must be
+ initialized, so we should call lex_start(); to be even safer, we call
+ mysql_init_query() which does a more complete set of inits.
+ */
+ mysql_init_query(thd, NULL, 0);
+
if (table)
{
/*
@@ -5704,16 +5731,17 @@
#endif
#if !defined(MYSQL_CLIENT) && defined(HAVE_REPLICATION)
-void Table_map_log_event::cleanup(THD *thd)
+void Table_map_log_event::cleanup(THD *thd_arg, struct st_relay_log_info *rli)
{
/*
Instances of Table_map_log_event, if ::exec_event() was called on them,
may have opened tables, which we cannot be sure have been closed (because
maybe the Rows_log_event have not been found or will not be, because slave
SQL thread is stopping, or relay log has a missing tail etc). So we close
- all thread's tables.
+ all thread's tables. And so the table mappings have to be cancelled.
*/
- close_thread_tables(thd);
+ rli->m_table_map.clear_tables();
+ close_thread_tables(thd_arg);
}
#endif
--- 1.127/sql/log_event.h 2005-09-30 18:00:15 +02:00
+++ 1.128/sql/log_event.h 2005-10-03 14:07:21 +02:00
@@ -1679,7 +1679,7 @@
your main function. Mats: if you have a better place for this function...
*/
#if !defined(MYSQL_CLIENT) && defined(HAVE_REPLICATION) && defined(HAVE_ROW_BASED_REPLICATION)
- static void cleanup(THD *thd);
+ static void cleanup(THD *thd, struct st_relay_log_info *rli);
#endif
private:
--- 1.261/sql/slave.cc 2005-09-29 09:49:11 +02:00
+++ 1.262/sql/slave.cc 2005-10-03 14:07:22 +02:00
@@ -3536,7 +3536,7 @@
request is detected only by the present function, not by events), so we
must "proactively" clear playgrounds:
*/
- Table_map_log_event::cleanup(thd);
+ Table_map_log_event::cleanup(thd, rli);
#endif
VOID(pthread_mutex_lock(&LOCK_thread_count));
--- 1.467/sql/sql_parse.cc 2005-09-30 18:00:16 +02:00
+++ 1.468/sql/sql_parse.cc 2005-10-03 14:07:22 +02:00
@@ -4245,7 +4245,7 @@
So just execute the statement.
*/
res= sp->execute_procedure(thd, &lex->value_list);
- if (mysql_bin_log.is_open() &&
+ if (!binlog_row_based && mysql_bin_log.is_open() &&
(sp->m_chistics->daccess == SP_CONTAINS_SQL ||
sp->m_chistics->daccess == SP_MODIFIES_SQL_DATA))
{
--- 1.9/mysql-test/r/rpl_stm_sp.result 2005-09-27 22:13:20 +02:00
+++ 1.10/mysql-test/r/rpl_stm_sp.result 2005-10-03 14:07:21 +02:00
@@ -4,16 +4,11 @@
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
-create database if not exists mysqltest1;
+drop database if exists mysqltest1;
+create database mysqltest1;
use mysqltest1;
create table t1 (a varchar(100));
use mysqltest1;
-drop procedure if exists foo;
-drop procedure if exists foo2;
-drop procedure if exists foo3;
-drop procedure if exists foo4;
-drop procedure if exists bar;
-drop function if exists fn1;
create procedure foo()
begin
declare b int;
@@ -22,10 +17,6 @@
insert into t1 values (unix_timestamp());
end|
ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)
-show binlog events from 102|
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query 1 # create database if not exists mysqltest1
-master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100))
create procedure foo() deterministic
begin
declare b int;
@@ -51,17 +42,6 @@
end @ # #
set timestamp=1000000000;
call foo();
-show binlog events from 312;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo() deterministic
-begin
-declare b int;
-set b = 8;
-insert into t1 values (b);
-insert into t1 values (unix_timestamp());
-end
-master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
-master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp())
select * from t1;
a
8
@@ -77,15 +57,6 @@
select * from mysqltest1.t1;
call foo2();
a
-show binlog events from 522;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
-master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp())
-master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
-master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo2()
-not deterministic
-reads sql data
-select * from mysqltest1.t1
alter procedure foo2 contains sql;
ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)
drop table t1;
@@ -109,7 +80,7 @@
insert into t1 values (5);
end|
call foo4();
-Got one of the listed errors
+ERROR 42000: INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
show warnings;
Level Code Message
Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
@@ -118,7 +89,7 @@
show warnings;
Level Code Message
call foo4();
-Got one of the listed errors
+ERROR 42000: INSERT command denied to user 'zedjzlcsjhd'@'127.0.0.1' for table 't1'
show warnings;
Level Code Message
Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'127.0.0.1' for table 't1'
@@ -127,29 +98,6 @@
call foo4();
show warnings;
Level Code Message
-show binlog events from 994;
-Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1
-master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int)
-master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 like t1
-master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo3()
-deterministic
-insert into t1 values (15)
-master-bin.000001 # Query 1 # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
-master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
-master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
-master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4()
-deterministic
-begin
-insert into t2 values(3);
-insert into t1 values (5);
-end
-master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3)
-master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (15)
-master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3)
-master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo4 sql security invoker
-master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3)
-master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (5)
select * from t1;
a
15
@@ -168,11 +116,30 @@
3
3
3
+delete from t2;
+alter table t2 add unique (a);
+drop procedure foo4;
+create procedure foo4()
+deterministic
+begin
+insert into t2 values(20),(20);
+end|
+call foo4();
+ERROR 23000: Duplicate entry '20' for key 1
+show warnings;
+Level Code Message
+Error 1062 Duplicate entry '20' for key 1
+Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes
+select * from t2;
+a
+20
+select * from t2;
+a
+20
select * from mysql.proc where name="foo4" and db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
-mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES INVOKER begin
-insert into t2 values(3);
-insert into t1 values (5);
+mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin
+insert into t2 values(20),(20);
end @ # #
drop procedure foo4;
select * from mysql.proc where name="foo4" and db='mysqltest1';
@@ -237,7 +204,6 @@
end @ # #
create trigger trg before insert on t1 for each row set new.a= 10;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
-flush logs;
delete from t1;
create trigger trg before insert on t1 for each row set new.a= 10;
insert into t1 values (1);
@@ -253,14 +219,84 @@
select * from t1;
a
1
-show binlog events in 'master-bin.000002' from 102;
+show binlog events from 102;
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1
-master-bin.000002 # Query 1 # use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10
-master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1)
-master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1
-master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger trg
-master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1)
+master-bin.000001 # Query 1 # drop database if exists mysqltest1
+master-bin.000001 # Query 1 # create database mysqltest1
+master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100))
+master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo() deterministic
+begin
+declare b int;
+set b = 8;
+insert into t1 values (b);
+insert into t1 values (unix_timestamp());
+end
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp())
+master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
+master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo2()
+not deterministic
+reads sql data
+select * from mysqltest1.t1
+master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1
+master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int)
+master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 like t1
+master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo3()
+deterministic
+insert into t1 values (15)
+master-bin.000001 # Query 1 # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
+master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
+master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
+master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4()
+deterministic
+begin
+insert into t2 values(3);
+insert into t1 values (5);
+end
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3)
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (15)
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3)
+master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo4 sql security invoker
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3)
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (5)
+master-bin.000001 # Query 1 # use `mysqltest1`; delete from t2
+master-bin.000001 # Query 1 # use `mysqltest1`; alter table t2 add unique (a)
+master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4
+master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4()
+deterministic
+begin
+insert into t2 values(20),(20);
+end
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(20),(20)
+master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4
+master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo
+master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo2
+master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo3
+master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1(x int)
+returns int
+deterministic
+begin
+insert into t1 values (x);
+return x+2;
+end
+master-bin.000001 # Query 1 # use `mysqltest1`; delete t1,t2 from t1,t2
+master-bin.000001 # Query 1 # use `mysqltest1`; DO `fn1`(20)
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(fn1(21))
+master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1
+master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1()
+returns int
+deterministic
+begin
+return unix_timestamp();
+end
+master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(fn1())
+master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
+master-bin.000001 # Query 1 # use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1)
+master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1
+master-bin.000001 # Query 1 # use `mysqltest1`; drop trigger trg
+master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1)
select * from t1;
a
1
--- 1.8/mysql-test/t/rpl_stm_sp.test 2005-09-10 13:42:29 +02:00
+++ 1.9/mysql-test/t/rpl_stm_sp.test 2005-10-03 14:07:21 +02:00
@@ -1,13 +1,15 @@
-# row-based and statement binlog difference in result files
+# row-based and statement have expected binlog difference in result files
-- source include/have_binlog_format_statement.inc
# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
-- source include/master-slave.inc
-# First let's test replication of current_user() (that's a related thing)
# we need a db != test, where we don't have automatic grants
-create database if not exists mysqltest1;
+--disable_warnings
+drop database if exists mysqltest1;
+--enable_warnings
+create database mysqltest1;
use mysqltest1;
create table t1 (a varchar(100));
sync_slave_with_master;
@@ -16,20 +18,13 @@
# ********************** PART 1 : STORED PROCEDURES ***************
# Does the same proc as on master get inserted into mysql.proc ?
-# (same definer, same properties...)
+# (almost all properties should be same; definer is an exception
+# (known problem - Alik@stripped is working on it - Oct 1st 2005))
connection master;
-# cleanup
---disable_warnings
-drop procedure if exists foo;
-drop procedure if exists foo2;
-drop procedure if exists foo3;
-drop procedure if exists foo4;
-drop procedure if exists bar;
-drop function if exists fn1;
---enable_warnings
delimiter |;
+
--error 1418 # not deterministic
create procedure foo()
begin
@@ -39,9 +34,6 @@
insert into t1 values (unix_timestamp());
end|
---replace_column 2 # 5 #
-show binlog events from 102| # check that not there
-
create procedure foo() deterministic
begin
declare b int;
@@ -61,21 +53,16 @@
--replace_column 13 # 14 #
select * from mysql.proc where name='foo' and db='mysqltest1';
-# Now when we call it, does the CALL() get into binlog,
-# or the substatements?
connection master;
# see if timestamp used in SP on slave is same as on master
set timestamp=1000000000;
call foo();
---replace_column 2 # 5 #
-show binlog events from 312;
select * from t1;
sync_slave_with_master;
select * from t1;
-# Now a SP which is supposed to not update tables (CALL should not be
-# binlogged) as it's "read sql data", so should not give error even if
-# non-deterministic.
+# Now a SP which is supposed to not update tables (it's "read sql
+# data") , so should not give error even if non-deterministic.
connection master;
delete from t1;
@@ -84,9 +71,6 @@
reads sql data
select * from mysqltest1.t1;
call foo2();
-# verify CALL is not in binlog
---replace_column 2 # 5 #
-show binlog events from 522;
--error 1418
alter procedure foo2 contains sql;
@@ -118,6 +102,7 @@
set global log_bin_trust_routine_creators=1;
connection con1;
+# this routine will fail in the second INSERT because of privileges
delimiter |;
create procedure foo4()
deterministic
@@ -128,18 +113,15 @@
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 => failure
+--error 1142
+call foo4(); # invoker has no INSERT grant on table t1 => failure
show warnings;
connection master;
call foo3(); # success (definer == root)
show warnings;
---replace_result localhost.localdomain localhost 127.0.0.1 localhost
---error 1142,0
+--error 1142
call foo4(); # definer's rights => failure
show warnings;
@@ -148,12 +130,9 @@
call foo4(); # invoker's rights => success
show warnings;
-# Check that only successful CALLs are in binlog
---replace_column 2 # 5 #
-show binlog events from 994;
-
-# Note that half-failed CALLs are not in binlog, which is a known
-# bug. If we compare t2 on master and slave we see they differ:
+# Thanks to the statement-based replication of CALL (binlogging
+# substatements) done in Aug 2005 by SergeyP, half-failed CALL does
+# not break replication anymore:
select * from t1;
select * from t2;
@@ -161,6 +140,30 @@
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 1062
+call foo4();
+show warnings;
+
+select * from t2;
+sync_slave_with_master;
+# did all these failed-in-the-middle replicate right:
+select * from t2;
+
# Test of DROP PROCEDURE
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
@@ -233,17 +236,13 @@
create trigger trg before insert on t1 for each row set new.a= 10;
connection master;
-# fn1() above uses timestamps, so in !ps-protocol, the timezone will be
-# binlogged, but in --ps-protocol it will not be (BUG#9359) so
-# the binlog offsets get shifted which spoils SHOW BINLOG EVENTS.
-# To be immune, we take a new binlog.
-flush logs;
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;
@@ -255,8 +254,12 @@
drop trigger trg;
insert into t1 values (1);
select * from t1;
+
+# now inspect binlog
+
--replace_column 2 # 5 #
-show binlog events in 'master-bin.000002' from 102;
+show binlog events from 102;
+
sync_slave_with_master;
select * from t1;
--- New file ---
+++ mysql-test/r/rpl_row_sp000.result 05/10/03 14:07:22
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
drop database if exists mysqltest1;
create database mysqltest1;
use mysqltest1;
create table t1 (a varchar(100));
use mysqltest1;
create procedure foo()
begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end|
select * from mysql.proc where name='foo' and db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end root@localhost # #
select * from mysql.proc where name='foo' and db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end root@localhost # #
set timestamp=1000000000;
call foo();
select * from t1;
a
8
1000000000
select * from t1;
a
8
1000000000
delete from t1;
create procedure foo2()
not deterministic
select * from mysqltest1.t1;
call foo2();
a
alter procedure foo2 contains sql;
drop table t1;
create table t1 (a int);
create table t2 like t1;
create procedure foo3()
deterministic
insert into t1 values (15);
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;
create procedure foo4()
deterministic
begin
insert into t2 values(3);
insert into t1 values (5);
end|
call foo4();
ERROR 42000: INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
show warnings;
Level Code Message
Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
call foo3();
show warnings;
Level Code Message
call foo4();
ERROR 42000: INSERT command denied to user 'zedjzlcsjhd'@'127.0.0.1' for table 't1'
show warnings;
Level Code Message
Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'127.0.0.1' for table 't1'
alter procedure foo4 sql security invoker;
call foo4();
show warnings;
Level Code Message
select * from t1;
a
15
5
select * from t2;
a
3
3
3
select * from t1;
a
15
5
select * from t2;
a
3
3
3
delete from t2;
alter table t2 add unique (a);
drop procedure foo4;
create procedure foo4()
deterministic
begin
insert into t2 values(20),(20);
end|
call foo4();
ERROR 23000: Duplicate entry '20' for key 1
show warnings;
Level Code Message
Error 1062 Duplicate entry '20' for key 1
select * from t2;
a
20
select * from t2;
a
20
select * from mysql.proc where name="foo4" and db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin
insert into t2 values(20),(20);
end root@localhost # #
drop procedure foo4;
select * from mysql.proc where name="foo4" and db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
select * from mysql.proc where name="foo4" and db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
drop procedure foo;
drop procedure foo2;
drop procedure foo3;
create function fn1(x int)
returns int
deterministic
begin
insert into t1 values (x);
return x+2;
end|
delete t1,t2 from t1,t2;
select fn1(20);
fn1(20)
22
insert into t2 values(fn1(21));
select * from t1;
a
21
20
select * from t2;
a
23
select * from t1;
a
21
20
select * from t2;
a
23
drop function fn1;
create function fn1()
returns int
begin
return unix_timestamp();
end|
delete from t1;
set timestamp=1000000000;
insert into t1 values(fn1());
select * from mysql.proc where db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL NO DEFINER int(11) begin
return unix_timestamp();
end root@localhost # #
select * from t1;
a
1000000000
use mysqltest1;
select * from t1;
a
1000000000
select * from mysql.proc where db='mysqltest1';
db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment
mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL NO DEFINER int(11) begin
return unix_timestamp();
end root@localhost # #
delete from t2;
alter table t2 add unique (a);
drop function fn1;
create function fn1()
returns int
begin
insert into t2 values(20),(20);
return 10;
end|
select fn1();
ERROR 23000: Duplicate entry '20' for key 1
show warnings;
Level Code Message
Error 1062 Duplicate entry '20' for key 1
Error 1105 Unknown error
select * from t2;
a
20
select * from t2;
a
20
create trigger trg before insert on t1 for each row set new.a= 10;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
delete from t1;
create trigger trg before insert on t1 for each row set new.a= 10;
insert into t1 values (1);
select * from t1;
a
10
select * from t1;
a
10
delete from t1;
drop trigger trg;
insert into t1 values (1);
select * from t1;
a
1
show binlog events from 102;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Update_rows 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Update_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Delete_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Query 1 # #
master-bin.000001 # Table_map 1 # #
master-bin.000001 # Write_rows 1 # #
select * from t1;
a
1
drop function fn1;
drop database mysqltest1;
drop user "zedjzlcsjhd"@127.0.0.1;
--- New file ---
+++ mysql-test/t/rpl_row_sp000-master.opt 05/10/03 14:07:22
--log_bin_trust_routine_creators=0
--- New file ---
+++ mysql-test/t/rpl_row_sp000-slave.opt 05/10/03 14:07:22
--log_bin_trust_routine_creators=0
--- 1.11/wl1012-review-pending-comments.txt 2005-09-30 18:00:16 +02:00
+++ 1.12/wl1012-review-pending-comments.txt 2005-10-03 14:07:22 +02:00
@@ -9838,3 +9838,7 @@
[G2TODO] instead of
[G2TODO] /*
[G2TODO] Hi (problem caused by trailing space after /* usually)
+
+[G2TODO] When we have a solution ("replace-regexp" in mysqltest?), we
+[G2TODO] should not entirely replace the 6th column of SHOW BINLOG
+[G2TODO] EVENTS because this is losing too much info.
--- 1.5/mysql-test/t/rpl_row_UUID.test 2005-09-30 15:54:14 +02:00
+++ 1.6/mysql-test/t/rpl_row_UUID.test 2005-10-03 14:07:21 +02:00
@@ -19,40 +19,64 @@
# Section 1 test
-CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, PRIMARY KEY(a));
-INSERT INTO test.t1 VALUES(1,UUID());
+CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, vchar_column VARCHAR(100), PRIMARY KEY(a)) engine=myisam;
+INSERT INTO test.t1 VALUES(1,UUID(),UUID());
delimiter |;
create procedure test.p1()
begin
- INSERT INTO test.t1 VALUES(2,UUID());
+ INSERT INTO test.t1 VALUES(2,UUID(),UUID());
+ INSERT INTO test.t1 VALUES(3,UUID(),UUID());
end|
delimiter ;|
CALL test.p1();
-#SELECT * FROM test.t1;
+
+# Now the same thing with a function
+
+delimiter |;
+create function test.fn1(x int)
+ returns int
+begin
+ insert into t1 values (4+x,UUID(),UUID());
+ insert into t1 values (5+x,UUID(),UUID());
+ return 0;
+end|
+
+delimiter ;|
+# test both in SELECT and in INSERT
+select fn1(0);
+create table t2 (a int);
+insert into t2 values(fn1(2));
+
sync_slave_with_master;
SHOW CREATE TABLE test.t1;
-#SELECT * FROM test.t1;
# Dump the databases and so we can see if they match
+# Another method would be to use SELECT INTO OUTFILE on master,
+# then LOAD DATA INFILE in slave, and use a query to compare.
+# This would have the advantage that it would not assume
+# the system has a 'diff'
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > rpl_row_UUID_master.sql
--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > rpl_row_UUID_slave.sql
connection master;
-# Lets cleanup
-#show binlog events;
+# Let's cleanup
-DROP PROCEDURE IF EXISTS test.p1;
+DROP PROCEDURE test.p1;
DROP TABLE test.t1;
-# Lets compare. Note: If they match test will pass, if they do not match
+# Let's compare. Note: If they match test will pass, if they do not match
# the test will show that the diff statement failed and not reject file
# will be created. You will need to go to the mysql-test dir and diff
# the files your self to see what is not matching :-)
--exec diff ./rpl_row_UUID_master.sql ./rpl_row_UUID_slave.sql;
-# Cleanup dump files
+# Cleanup dump files.
+# Long-term "system rm" is not portable; we could live without
+# this cleanup as no other test will use these files and they'll
+# be removed at next testsuite run.
+exit;
system rm ./rpl_row_UUID_master.sql ./rpl_row_UUID_slave.sql;
# End of 5.0 test case
--- 1.4/mysql-test/t/rpl_row_sp000.test 2005-09-19 17:56:44 +02:00
+++ 1.5/mysql-test/t/rpl_row_sp000.test 2005-10-03 14:07:21 +02:00
@@ -1,292 +1,277 @@
-# row-based and statement binlog difference in result files
-# TODO: Come back and figure out the right positions for show binlog events from ### once 13325 is patched.
-
+# row-based and statement have expected binlog difference in result files
-- source include/have_binlog_format_row.inc
-# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
+# Test of replication of stored procedures in row-based replication.
+# Initially copied from the statement-based version rpl_stm_sp.test.
-- source include/master-slave.inc
-connection master;
-
-# cleanup
+# we need a db != test, where we don't have automatic grants
--disable_warnings
-DROP DATABASE IF EXISTS mtest;
-DROP PROCEDURE IF EXISTS mtest.p1;
-DROP PROCEDURE IF EXISTS mtest.p2;
-DROP PROCEDURE IF EXISTS mtest.p3;
-DROP PROCEDURE IF EXISTS mtest.p4;
-DROP PROCEDURE IF EXISTS bar;
-DROP FUNCTION IF EXISTS fn1;
+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 ***************
+# ********************** PART 1 : STORED PROCEDURES ***************
# Does the same proc as on master get inserted into mysql.proc ?
-# (same definer, same properties...)
+# (all same properties)
-# First let's test replication of current_user() (that's a related thing)
-# we need a db != test, where we don't have automatic grants
-CREATE DATABASE IF NOT EXISTS mtest;
-USE mtest;
-CREATE TABLE mtest.t1 (a VARCHAR(100), PRIMARY KEY(a));
-sync_slave_with_master;
-USE mtest;
connection master;
delimiter |;
---error 0,1418 # not deterministic
-CREATE PROCEDURE mtest.p1()
-BEGIN
- DECLARE b INT;
- SET b = 8;
- INSERT INTO mtest.t1 VALUES (b);
- INSERT INTO mtest.t1 VALUES (unix_timestamp());
-END|
-
-#--replace_column 2 # 5 #
-#show binlog events from 102| # check that not there
-
-#create procedure foo() deterministic
-#begin
-# declare b int;
-# set b = 8;
-# insert into t1 values (b);
-# insert into t1 values (unix_timestamp());
-#end|
+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='p1' AND db='mtest';
+select * from mysql.proc where name='foo' and db='mysqltest1';
sync_slave_with_master;
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
-SELECT * FROM mysql.proc WHERE name='p1' AND db='mtest';
+select * from mysql.proc where name='foo' and db='mysqltest1';
-# Now when we call it, does the CALL() get into binlog,
-# or the substatements?
connection master;
# see if timestamp used in SP on slave is same as on master
-SET TIMESTAMP=1000000000;
-CALL mtest.p1();
-#--replace_column 2 # 5 #
-#show binlog events from 620;
-show binlog events;
-SELECT * FROM mtest.t1;
-sync_slave_with_master;
-SELECT * FROM mtest.t1;
-
-# Now a SP which is supposed to not update tables (CALL should not be
-# binlogged) as it's "read sql data", so should not give error even if
-# non-deterministic.
-
-connection master;
-DELETE FROM mtest.t1;
-CREATE PROCEDURE mtest.p2()
- NOT DETERMINISTIC
- READS SQL DATA
- SELECT * FROM mtest.t1;
-CALL mtest.p2();
-# verify CALL is not in binlog
-#--replace_column 2 # 5 #
-#show binlog events from 747;
-show binlog events;
---error 0,1418
-ALTER PROCEDURE mtest.p2 CONTAINS SQL;
-
-# SP with definer's right
+set timestamp=1000000000;
+call foo();
+select * from t1;
+sync_slave_with_master;
+select * from t1;
-DROP TABLE mtest.t1;
-CREATE TABLE mtest.t1 (a INT,PRIMARY KEY(a));
-CREATE TABLE mtest.t2 LIKE mtest.t1;
-
-CREATE PROCEDURE mtest.p3()
- DETERMINISTIC
- INSERT INTO mtest.t1 VALUES (15);
+# Now a SP which is not updating tables
-# let's create a non-privileged user
-GRANT CREATE ROUTINE, EXECUTE ON mtest.* TO "zedjzlcsjhd"@127.0.0.1;
-GRANT SELECT ON mtest.t1 TO "zedjzlcsjhd"@127.0.0.1;
-GRANT SELECT, INSERT on mtest.t2 TO "zedjzlcsjhd"@127.0.0.1;
+connection master;
+delete from t1;
+create procedure foo2()
+ not deterministic
+ select * from mysqltest1.t1;
+call foo2();
-connect (con1,127.0.0.1,zedjzlcsjhd,,mtest,$MASTER_MYPORT,);
-connection con1;
+alter procedure foo2 contains sql;
-#As of MySQL 5.0.3, to create a routine, it is necessary to have the CREATE ROUTINE privilege, and the ALTER ROUTINE and EXECUTE privileges are granted automatically to its creator.
+# SP with definer's right
-#There for this test is no-longer vailid
+drop table t1;
+create table t1 (a int);
+create table t2 like t1;
+
+create procedure foo3()
+ deterministic
+ insert into t1 values (15);
---error 0,1419 # only full-global-privs user can create a routine
-CREATE PROCEDURE mtest.p4()
- DETERMINISTIC
- INSERT INTO mtest.t1 VALUES (10);
+# 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;
-connection master;
-SET GLOBAL log_bin_trust_routine_creators=1;
+connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,);
connection con1;
-DROP PROCEDURE IF EXISTS mtest.p4;
-
+# this routine will fail in the second INSERT because of privileges
delimiter |;
-CREATE PROCEDURE mtest.p4()
- DETERMINISTIC
- BEGIN
- INSERT INTO mtest.t2 VALUES (3);
- INSERT INTO mtest.t1 VALUES (5);
- END|
-connection master|
-CREATE PROCEDURE mtest.p5()
- BEGIN
- DELETE FROM mtest.t2;
- DELETE FROM mtest.t1;
- END|
-connection con1|
+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 mtest.p4(); # invoker has no INSERT grant on table => failure
+--error 1142
+call foo4(); # invoker has no INSERT grant on table t1 => failure
+# we should not see the warnings we expect to see in statement-based binlogging
show warnings;
connection master;
-CALL mtest.p3(); # success (definer == root)
+call foo3(); # success (definer == root)
show warnings;
-CALL mtest.p5();
---replace_result localhost.localdomain localhost 127.0.0.1 localhost
---error 1142,0
-CALL mtest.p4(); # definer's rights => failure
+--error 1142
+call foo4(); # definer's rights => failure
show warnings;
-CALL mtest.p5();
# we test replication of ALTER PROCEDURE
-ALTER PROCEDURE mtest.p4 SQL SECURITY INVOKER;
-CALL mtest.p4(); # invoker's rights => success
+alter procedure foo4 sql security invoker;
+call foo4(); # invoker's rights => success
show warnings;
-# Check that only successful CALLs are in binlog
-#--replace_column 2 # 5 #
-#show binlog events from 894;
-show binlog events;
+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 ;|
-# Note that half-failed CALLs are not in binlog, which is a known
-# bug. If we compare t2 on master and slave we see they differ:
+--error 1062
+call foo4();
+show warnings;
-SELECT * FROM mtest.t1;
-SELECT * FROM mtest.t2;
+select * from t2;
sync_slave_with_master;
-SELECT * FROM mtest.t1;
-SELECT * FROM mtest.t2;
+# did all these failed-in-the-middle replicate 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="p4" AND db='mtest';
+select * from mysql.proc where name="foo4" and db='mysqltest1';
connection master;
-DROP PROCEDURE mtest.p4;
-SELECT * FROM mysql.proc WHERE name="p4" AND db='mtest';
+drop procedure foo4;
+select * from mysql.proc where name="foo4" and db='mysqltest1';
sync_slave_with_master;
-SELECT * FROM mysql.proc WHERE name="p4" AND db='mtest';
+select * from mysql.proc where name="foo4" and db='mysqltest1';
+
+# Test of a procedure and function containing UUID() is done in
+# rpl_row_UUID.
+
# ********************** PART 2 : FUNCTIONS ***************
connection master;
-DROP PROCEDURE mtest.p1;
-DROP PROCEDURE mtest.p2;
-DROP PROCEDURE mtest.p3;
+drop procedure foo;
+drop procedure foo2;
+drop procedure foo3;
delimiter |;
-CREATE FUNCTION fn1(x INT)
- RETURNS INT
- DETERMINISTIC
-BEGIN
- INSERT INTO mtest.t1 VALUES (x);
- RETURN x+2;
-END|
-delimiter ;|
+create function fn1(x int)
+ returns int
+ deterministic
+begin
+ insert into t1 values (x);
+ return x+2;
+end|
-DELETE t1,t2 FROM mtest.t1, mtest.t2;
-SELECT fn1(20);
-INSERT INTO mtest.t2 VALUES (fn1(21));
-SELECT * FROM mtest.t1;
-SELECT * FROM mtest.t2;
+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 mtest.t1;
-SELECT * FROM mtest.t2;
+select * from t1;
+select * from t2;
connection master;
+delimiter |;
-DROP FUNCTION fn1;
+drop function fn1;
-delimiter |;
-CREATE FUNCTION fn1()
- RETURNS INT
- DETERMINISTIC
-BEGIN
- RETURN unix_timestamp();
-END|
+create function fn1()
+ returns int
+begin
+ return unix_timestamp();
+end|
delimiter ;|
-
-DELETE FROM mtest.t1;
-SET TIMESTAMP=1000000000;
-INSERT INTO mtest.t1 VALUES(fn1());
+delete from t1;
+set timestamp=1000000000;
+insert into t1 values(fn1());
--replace_result localhost.localdomain localhost 127.0.0.1 localhost
--replace_column 13 # 14 #
-SELECT * FROM mysql.proc WHERE db='mtest';
-SELECT * FROM mtest.t1;
+select * from mysql.proc where db='mysqltest1';
+select * from t1;
sync_slave_with_master;
-SELECT * FROM mtest.t1;
+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='mtest';
+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()
+ returns int
+begin
+ insert into t2 values(20),(20);
+ return 10;
+end|
+
+delimiter ;|
+
+--error 1062
+select fn1();
+show warnings;
+
+select * from t2;
+sync_slave_with_master;
+# did all these failed-in-the-middle replicate right:
+select * from t2;
+
# And now triggers
connection con1;
--error 1227
-CREATE TRIGGER trg BEFORE INSERT ON mtest.t1 FOR EACH ROW SET new.a= 10;
+create trigger trg before insert on t1 for each row set new.a= 10;
connection master;
-# fn1() above uses timestamps, so in !ps-protocol, the timezone will be
-# binlogged, but in --ps-protocol it will not be (BUG#9359) so
-# the binlog offsets get shifted which spoils SHOW BINLOG EVENTS.
-# To be immune, we take a new binlog.
-FLUSH LOGS;
-DELETE FROM mtest.t1;
+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 mtest.t1 FOR EACH ROW SET new.a= 10;
-#*********** NOTE: Triggers currently cause core on slave
-#insert into t1 values (1);
+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 mtest.t1;
-DROP TRIGGER trg;
-INSERT INTO mtest.t1 VALUES (1);
-SELECT * FROM mtest.t1;
---replace_column 2 # 5 #
-show binlog events in 'master-bin.000002'from 102;
+delete from t1;
+drop trigger trg;
+insert into t1 values (1);
+select * from t1;
+
+# now inspect binlog
+
+--replace_column 2 # 5 # 6 #
+show binlog events from 102;
+
sync_slave_with_master;
-SELECT * FROM mtest.t1;
+select * from t1;
# Clean up
connection master;
-DROP FUNCTION fn1;
-DROP DATABASE mtest;
-DROP USER "zedjzlcsjhd"@127.0.0.1;
-#sync_slave_with_master;
+drop function fn1;
+drop database mysqltest1;
+drop user "zedjzlcsjhd"@127.0.0.1;
+sync_slave_with_master;
--- 1.28/mysql-test/t/disabled.def 2005-09-30 17:08:44 +02:00
+++ 1.29/mysql-test/t/disabled.def 2005-10-03 14:07:21 +02:00
@@ -21,7 +21,6 @@
rpl_row_relayrotate:Transaction Rollbacks not replicated Bug 12559
rpl_row_timezone:Timestamp incorrectly replicated Bug 12443
rpl_row_000002:create table from temporary Bug 12345
-rpl_row_sp000:Bug 13325 Slave Core
#rpl_row_sp001:Bug 12461
rpl_row_sp002:Bug 12554
rpl_row_sp003:Bug 12335
--- 1.1/mysql-test/r/rpl_row_UUID.result 2005-09-30 15:54:15 +02:00
+++ 1.2/mysql-test/r/rpl_row_UUID.result 2005-10-03 14:07:21 +02:00
@@ -6,19 +6,33 @@
start slave;
DROP PROCEDURE IF EXISTS test.p1;
DROP TABLE IF EXISTS test.t1;
-CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, PRIMARY KEY(a));
-INSERT INTO test.t1 VALUES(1,UUID());
+CREATE TABLE test.t1 (a INT, blob_column LONGBLOB, vchar_column VARCHAR(100), PRIMARY KEY(a)) engine=myisam;
+INSERT INTO test.t1 VALUES(1,UUID(),UUID());
create procedure test.p1()
begin
-INSERT INTO test.t1 VALUES(2,UUID());
+INSERT INTO test.t1 VALUES(2,UUID(),UUID());
+INSERT INTO test.t1 VALUES(3,UUID(),UUID());
end|
CALL test.p1();
+create function test.fn1(x int)
+returns int
+begin
+insert into t1 values (4+x,UUID(),UUID());
+insert into t1 values (5+x,UUID(),UUID());
+return 0;
+end|
+select fn1(0);
+fn1(0)
+0
+create table t2 (a int);
+insert into t2 values(fn1(2));
SHOW CREATE TABLE test.t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL default '0',
`blob_column` longblob,
+ `vchar_column` varchar(100) default NULL,
PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
-DROP PROCEDURE IF EXISTS test.p1;
+DROP PROCEDURE test.p1;
DROP TABLE test.t1;
--- 1.157/mysql-test/r/sp.result 2005-09-26 18:21:53 +02:00
+++ 1.158/mysql-test/r/sp.result 2005-10-03 14:07:21 +02:00
@@ -3312,17 +3312,11 @@
bug12379()
42
42
-Warnings:
-Error 1062 Duplicate entry 'X' for key 1
-Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes
select 2|
2
2
call bug12379_2()|
bug12379()
-Warnings:
-Error 1062 Duplicate entry 'X' for key 1
-Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes
select 3|
3
3
--- 1.153/mysql-test/t/sp.test 2005-09-26 18:21:53 +02:00
+++ 1.154/mysql-test/t/sp.test 2005-10-03 14:07:21 +02:00
@@ -4170,9 +4170,13 @@
--error 1062
select bug12379()|
select 1|
+# statement-based binlogging will show warning which row-based won't;
+# so we hide it (this warning is already tested in rpl_stm_sp.test)
+--disable_warnings
call bug12379_1()|
select 2|
call bug12379_2()|
+--enable_warnings
select 3|
--error 1062
call bug12379_3()|
| Thread |
|---|
| • bk commit into 5.0 tree (guilhem:1.2003) BUG#13360 | guilhem | 3 Oct |
