diff options
| author | Alfranio Correia <alfranio.correia@sun.com> | 2009-12-17 21:43:35 +0000 |
|---|---|---|
| committer | Alfranio Correia <alfranio.correia@sun.com> | 2009-12-17 21:43:35 +0000 |
| commit | 3476b15af99b626bd21a835eeab22f3c9c9f307d (patch) | |
| tree | fb25bfc9b6c669b42e70afadf7790f0f8d7db7e3 /mysql-test/extra | |
| parent | 7c2c655ccfb163737ded33559d1942aeddec2970 (diff) | |
| parent | 0758893afe391b14016eacb1fb6fa067eea9a548 (diff) | |
| download | mariadb-git-3476b15af99b626bd21a835eeab22f3c9c9f307d.tar.gz | |
merge mysql-5.1-rep+3 --> mysql-5.1-rep+2-delivery1
Diffstat (limited to 'mysql-test/extra')
21 files changed, 3987 insertions, 789 deletions
diff --git a/mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test b/mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test deleted file mode 100644 index 54f3c538c79..00000000000 --- a/mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test +++ /dev/null @@ -1,300 +0,0 @@ -################################################################################ -# Let -# - B be begin, C commit and R rollback. -# - T a statement that accesses and changes only transactional tables, i.e. -# T-tables -# - N a statement that accesses and changes only non-transactional tables, -# i.e, N-tables. -# - M be a mixed statement, i.e. a statement that updates both T- and -# N-tables. -# - M* be a mixed statement that fails while updating either a T -# or N-table. -# - N* be a statement that fails while updating a N-table. -# -# In this test case, when changes are logged as rows either in the RBR or MIXED -# modes, we check if a M* statement that happens early in a transaction is -# written to the binary log outside the boundaries of the transaction and -# wrapped up in a BEGIN/ROLLBACK. This is done to keep the slave consistent with -# the master as the rollback will keep the changes on N-tables and undo them on -# T-tables. In particular, we expect the following behavior: -# -# 1. B M* T C would generate in the binlog B M* R B T C. -# 2. B M M* C would generate in the binlog B M M* C. -# 3. B M* M* T C would generate in the binlog B M* R B M* R B T C. -# -# SBR is not considered in this test because a failing statement is written to -# the binary along with the error code such that a slave executes and rolls it -# back, thus undoing the effects on T-tables. -# -# Note that, in the first case, we are not preserving history from the master as -# we are introducing a rollback that never happened. However, this seems to be -# more acceptable than making the slave diverge. In the second case, the slave -# will diverge as the changes on T-tables that originated from the M statement -# are rolled back on the master but not on the slave. Unfortunately, we cannot -# simply roll the transaction back as this would undo any uncommitted changes -# on T-tables. -# -# We check two more cases. First, INSERT...SELECT* which produces the following -# results: -# -# 1. B T INSERT M...SELECT* C" with an error in INSERT M...SELECT* generates in -# the binlog the following entries: "Nothing". -# 2. B INSERT M...SELECT* C" with an error in INSERT M...SELECT* generates in -# the binlog the following entries: B INSERT M...SELECT* R. -# -# Finally, we also check if any N statement that happens early in a transaction -# (i.e. before any T or M statement) is written to the binary log outside the -# boundaries of the transaction. In particular, we expect the following -# behavior: -# -# 1. B N N T C would generate in the binlog B N C B N C B T C. -# 2. B N N T R would generate in the binlog B N C B N C B T R. -# 3. B N* N* T C would generate in the binlog B N R B N R B T C. -# 4. B N* N* T R would generate in the binlog B N R B N R B T R. -# 5. B N N T N T C would generate in the binlog B N C B N C B T N T C. -# 6. B N N T N T R would generate in the binlog the B N C B N C B T N T R. -# -# Such issues do not happen in SBR. In RBR and MBR, a full-fledged fix will be -# pushed after the WL#2687. -# -# Please, remove this test case after pushing WL#2687. -################################################################################ - - ---echo ################################################################################### ---echo # CONFIGURATION ---echo ################################################################################### -CREATE TABLE nt_1 (a text, b int PRIMARY KEY) ENGINE = MyISAM; -CREATE TABLE nt_2 (a text, b int PRIMARY KEY) ENGINE = MyISAM; -CREATE TABLE tt_1 (a text, b int PRIMARY KEY) ENGINE = Innodb; -CREATE TABLE tt_2 (a text, b int PRIMARY KEY) ENGINE = Innodb; - -DELIMITER |; - -CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW -BEGIN - INSERT INTO nt_1 VALUES (NEW.a, NEW.b); -END| - -CREATE TRIGGER tr_i_nt_2_to_tt_2 BEFORE INSERT ON nt_2 FOR EACH ROW -BEGIN - INSERT INTO tt_2 VALUES (NEW.a, NEW.b); -END| - -DELIMITER ;| - ---echo ################################################################################### ---echo # CHECK HISTORY IN BINLOG ---echo ################################################################################### ---echo ---echo ---echo ---echo *** "B M* T C" with error in M* generates in the binlog the "B M* R B T C" entries ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -INSERT INTO nt_1 VALUES ("new text 1", 1); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES (USER(), 2), (USER(), 1); -INSERT INTO tt_2 VALUES ("new text 3", 3); -COMMIT; ---source include/show_binlog_events.inc - ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -INSERT INTO tt_2 VALUES ("new text 4", 4); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO nt_2 VALUES (USER(), 5), (USER(), 4); -INSERT INTO tt_2 VALUES ("new text 6", 6); -COMMIT; ---source include/show_binlog_events.inc - ---echo ---echo ---echo ---echo *** "B M M* T C" with error in M* generates in the binlog the "B M M* T C" entries ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -INSERT INTO nt_1 VALUES ("new text 10", 10); -BEGIN; -INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8); ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES (USER(), 9), (USER(), 10); -INSERT INTO tt_2 VALUES ("new text 11", 11); -COMMIT; ---source include/show_binlog_events.inc - ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -INSERT INTO tt_2 VALUES ("new text 15", 15); -BEGIN; -INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13); ---error ER_DUP_ENTRY -INSERT INTO nt_2 VALUES (USER(), 14), (USER(), 15); -INSERT INTO tt_2 VALUES ("new text 16", 16); -COMMIT; ---source include/show_binlog_events.inc - - ---echo ---echo ---echo ---echo *** "B M* M* T C" with error in M* generates in the binlog the "B M* R B M* R B T C" entries ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -INSERT INTO nt_1 VALUES ("new text 18", 18); -INSERT INTO nt_1 VALUES ("new text 20", 20); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES (USER(), 17), (USER(), 18); ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES (USER(), 19), (USER(), 20); -INSERT INTO tt_2 VALUES ("new text 21", 21); -COMMIT; ---source include/show_binlog_events.inc - ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -INSERT INTO tt_2 VALUES ("new text 23", 23); -INSERT INTO tt_2 VALUES ("new text 25", 25); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO nt_2 VALUES (USER(), 22), (USER(), 23); ---error ER_DUP_ENTRY -INSERT INTO nt_2 VALUES (USER(), 24), (USER(), 25); -INSERT INTO tt_2 VALUES ("new text 26", 26); -COMMIT; ---source include/show_binlog_events.inc - ---echo ---echo ---echo ---echo *** "B T INSERT M...SELECT* C" with an error in INSERT M...SELECT* generates ---echo *** in the binlog the following entries: "Nothing". ---echo *** There is a bug in that will be fixed after WL#2687. Please, check BUG#47175 for further details. ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -TRUNCATE TABLE nt_2; -TRUNCATE TABLE tt_2; -INSERT INTO tt_2 VALUES ("new text 7", 7); -BEGIN; -INSERT INTO tt_2 VALUES ("new text 27", 27); ---error ER_DUP_ENTRY -INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1; -INSERT INTO tt_2 VALUES ("new text 28", 28); -ROLLBACK; ---source include/show_binlog_events.inc - ---echo ---echo ---echo ---echo *** "B INSERT M..SELECT* C" with an error in INSERT M...SELECT* generates ---echo *** in the binlog the following entries: "B INSERT M..SELECT* R". ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -TRUNCATE TABLE nt_2; -TRUNCATE TABLE tt_2; -INSERT INTO tt_2 VALUES ("new text 7", 7); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1; -COMMIT; ---source include/show_binlog_events.inc - ---echo ---echo ---echo ---echo *** "B N N T C" generates in the binlog the "B N C B N C B T C" entries ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -TRUNCATE TABLE nt_1; -TRUNCATE TABLE tt_2; -BEGIN; -INSERT INTO nt_1 VALUES (USER(), 1); -INSERT INTO nt_1 VALUES (USER(), 2); -INSERT INTO tt_2 VALUES (USER(), 3); -COMMIT; ---source include/show_binlog_events.inc - ---echo ---echo ---echo ---echo *** "B N N T R" generates in the binlog the "B N C B N C B T R" entries ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -BEGIN; -INSERT INTO nt_1 VALUES (USER(), 4); -INSERT INTO nt_1 VALUES (USER(), 5); -INSERT INTO tt_2 VALUES (USER(), 6); -ROLLBACK; ---source include/show_binlog_events.inc - ---echo ---echo ---echo ---echo *** "B N* N* T C" with error in N* generates in the binlog the "B N R B N R B T C" entries ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO nt_1 VALUES (USER(), 7), (USER(), 1); ---error ER_DUP_ENTRY -INSERT INTO nt_1 VALUES (USER(), 8), (USER(), 1); -INSERT INTO tt_2 VALUES (USER(), 9); -COMMIT; ---source include/show_binlog_events.inc - ---echo ---echo ---echo ---echo *** "B N* N* T R" with error in N* generates in the binlog the "B N R B N R B T R" entries ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO nt_1 VALUES (USER(), 10), (USER(), 1); ---error ER_DUP_ENTRY -INSERT INTO nt_1 VALUES (USER(), 11), (USER(), 1); -INSERT INTO tt_2 VALUES (USER(), 12); -ROLLBACK; ---source include/show_binlog_events.inc - ---echo ---echo ---echo ---echo *** "B N N T N T C" generates in the binlog the "B N C B N C B T N T C" entries ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -BEGIN; -INSERT INTO nt_1 VALUES (USER(), 13); -INSERT INTO nt_1 VALUES (USER(), 14); -INSERT INTO tt_2 VALUES (USER(), 15); -INSERT INTO nt_1 VALUES (USER(), 16); -INSERT INTO tt_2 VALUES (USER(), 17); -COMMIT; ---source include/show_binlog_events.inc - ---echo ---echo ---echo ---echo *** "B N N T N T R" generates in the binlog the "B N C B N C B T N T R" entries ---echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); -BEGIN; -INSERT INTO nt_1 VALUES (USER(), 18); -INSERT INTO nt_1 VALUES (USER(), 19); -INSERT INTO tt_2 VALUES (USER(), 20); -INSERT INTO nt_1 VALUES (USER(), 21); -INSERT INTO tt_2 VALUES (USER(), 22); -ROLLBACK; ---source include/show_binlog_events.inc - ---echo ################################################################################### ---echo # CLEAN ---echo ################################################################################### - -DROP TABLE tt_1; -DROP TABLE tt_2; -DROP TABLE nt_1; -DROP TABLE nt_2; diff --git a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test index da0b77fbc23..4abf21a9930 100644 --- a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test +++ b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test @@ -8,6 +8,7 @@ -- source include/have_log_bin.inc -- source include/have_innodb.inc +call mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT"); --disable_warnings drop table if exists t1, t2; @@ -323,23 +324,24 @@ let $MYSQLD_DATADIR= `select @@datadir`; # and does not make slave to stop) if (`select @@binlog_format = 'ROW'`) { - --exec $MYSQL_BINLOG --start-position=525 $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output + --echo This does not matter in ROW mode as the rolled back changes do not contain transactional changes as these + --echo were previously flushed upon committing/rolling back each statement. } if (`select @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`) { --exec $MYSQL_BINLOG --start-position=556 $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output -} ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR -eval select -(@a:=load_file("$MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output")) -is not null; ---replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR -eval select -@a like "%#%error_code=0%ROLLBACK\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR -@a like "%#%error_code=0%ROLLBACK\\r\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%", -@a not like "%#%error_code=%error_code=%"; + --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR + eval select + (@a:=load_file("$MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output")) + is not null; + --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR + eval select + @a like "%#%error_code=0%ROLLBACK\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR + @a like "%#%error_code=0%ROLLBACK\\r\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%", + @a not like "%#%error_code=%error_code=%"; +} drop table t1, t2; # diff --git a/mysql-test/extra/rpl_tests/create_recursive_construct.inc b/mysql-test/extra/rpl_tests/create_recursive_construct.inc new file mode 100644 index 00000000000..628f1735726 --- /dev/null +++ b/mysql-test/extra/rpl_tests/create_recursive_construct.inc @@ -0,0 +1,400 @@ +# ==== Purpose ==== +# +# Creates a stored routine, stored function, trigger, view, or +# prepared statement (commonly referred to as "recursive construct") +# that invokes a given unsafe statement. +# +# Then, it invokes the created recursive construct several times: +# +# - With SQL_LOG_BIN = 1 and binlog_format = STATEMENT, to verify +# that it gives a warning. +# +# - With SQL_LOG_BIN = 0 and binlog_format = STATEMENT, to verify that +# there is no warning and nothing is logged. +# +# - With SQL_LOG_BIN = 1 and binlog_format = MIXED, to verify that it +# writes row events to the binlog. +# +# - In some cases, the recursive construct can be invoked so that it +# has no side-effects but returns a value that may be +# nondeterministic. An example is a function that returns UUID(). +# The function does not have side effects but its a return value +# that may differ on slave. Such statements are invoked so that +# the return value is discarded (e.g., SELECT func()), with +# SQL_LOG_BIN = 1 and binlog_format = STATEMENT. In this case, no +# warning should be given and nothing should be written to the +# binlog. +# +# This is an auxiliary file particularly targeted to being used by the +# test binlog_unsafe. In this context, the purpose is to check how +# warnings for unsafe statements are propagated in recursive +# constructs. +# +# The statement to invoke ("input") is described using mtr variables, +# and the resulting recursive construct ("output") is stored in mtr +# variables in a similar fashion. To create several levels of nested +# recursive constructs, source this file once, then copy the values of +# appropriate output variables to the input variables, and then source +# this file again. +# +# +# ==== Usage ==== +# +# See binlog_unsafe for an example of how to use this file. +# +# let $CRC_ARG_level= <level>; +# let $CRC_ARG_type= <type>; +# let $CRC_ARG_stmt_sidef= <stmt>; +# let $CRC_ARG_value= <stmt>; +# let $CRC_ARG_sel_retval= <stmt>; +# let $CRC_ARG_sel_sidef= <stmt>; +# let $CRC_ARG_desc= <desc>; +# source extra/rpl_tests/create_recursive_construct.inc; +# let $my_stmt_sidef= $CRC_RET_stmt_sidef; +# let $my_value= $CRC_RET_value; +# let $my_sel_sidef= $CRC_RET_sel_sidef; +# let $my_sel_retval= $CRC_RET_sel_retval; +# let $my_drop= $CRC_RET_drop; +# let $my_is_toplevel= $CRC_RET_top_is_toplevel; +# let $my_desc= $CRC_RET_desc; +# +# $CRC_ARG_* are used as input parameters (arguments) to this file: +# +# $CRC_ARG_level is the recursion depth: 1 for the innermost +# statement created, 2 for a statement that invokes a statement on +# level 1, etc. +# +# $CRC_ARG_type is an integer from 0 to 6, indicating what type of +# statement shall be created: +# 0 - Create a stored function where the return value depends on +# the value of the given statement. +# 1 - Create a stored function that invokes the given statement as +# a side-effect but may not return a value that depends on it. +# 2 - Create a stored routine that invokes the given statement. +# 3 - Create a trigger (on table trigger_table_$CRC_ARG_level) that +# invokes the given statement. +# 4 - Create a view that returns a value that depends on the value +# of the given statement. +# 5 - Create a view that invokes the given statement but may return +# a value that does not depend on it. +# 6 - Create a prepared statement that invokes the given statement. +# +# $CRC_ARG_stmt_sidef is the statement to invoke. It should be a +# statement that can be invoked on its own (not sub-statement), +# which causes something unsafe to be written to the binlog. +# +# $CRC_ARG_value is a sub-statement holding the value of the given +# statement. Can be empty if the given statement does not have a +# value. Typically, this is non-empty if the given statement is a +# function call or user variable, but not if it is a stored routine +# call, INSERT, SELECT, etc (because none of them has a value). +# $CRC_ARG_value is used only when $CRC_ARG_type=6. +# +# $CRC_ARG_sel_sidef is a SELECT sub-statement that invokes the +# statement as a side-effect, but returns a result set that may not +# depend on the statement. Can be empty if the statement cannot +# produce a result set from a SELECT. $CRC_ARG_sel_sidef is used +# only if $CRC_ARG_type=2 +# +# $CRC_ARG_sel_retval is a SELECT sub-statement that does not have +# side-effects, but returns a result set that depends on the unsafe +# statement. Can be empty if the statement cannot be invoked from a +# SELECT. $CRC_ARG_sel_retval is used only if $CRC_ARG_type=3. +# +# $CRC_ARG_desc is a human-readable description of the statement to +# invoke. +# +# $CRC_RET_* are used as output parameters (return values) of this +# file: +# +# $CRC_RET_stmt_sidef is a statement invoking the resulting recursive +# construct. +# +# $CRC_RET_value is a sub-statement invoking the resulting recursive +# construct and returning the value of the recursive construct. +# This is the empty string if the resulting recursive construct does +# not have a value. In particular, this is non-empty only if +# $CRC_ARG_value=7. +# +# $CRC_RET_sel_sidef is a SELECT sub-statement that invokes the +# resulting recursive construct as a side-effect but where the +# result set may not depend on the recursive construct. This is the +# empty string if the recursive construct cannot be invoked from a +# SELECT. In particular, this is non-empty only if $CRC_ARG_value=6 +# or $CRC_ARG_value=2. +# +# $CRC_RET_sel_retval is a SELECT sub-statement that does not have +# side-effects, but returns a result set depending on the unsafe +# statement. This is the empty string if the recursive construct +# cannot produce a result set from a SELECT. In particular, this is +# non-empty only if $CRC_ARG_value=7 or $CRC_ARG_value=3. +# +# $CRC_RET_drop is a statement that drops the created object. I.e., +# it is one of 'DROP FUNCTION <func>', 'DROP PROCEDURE <proc>', etc. +# +# $CRC_RET_top_is_toplevel is 0 normally, or 1 if the resulting +# recursive construct can only be called from a top-level statement. +# In particular, this is 1 only when $CRC_ARG_value=1, because +# prepared statements cannot be invoked from other recursive +# constructs. +# +# $CRC_RET_desc is a text string that describes the invokation of +# the recursive construct in a human-readable fashion. +# +# Assumptions +# +# Before sourcing this file with $CRC_ARG_level=X, you need to +# create three tables: tX, taX and trigger_table_X. These are used +# as auxiliary tables. + + +#--echo debug: >>>>ENTER create_recursive_construct +#--echo debug: level=$CRC_ARG_level +#--echo debug: type=$CRC_ARG_type +#--echo debug: stmt_sidef=$CRC_ARG_stmt_sidef +#--echo debug: value=$CRC_ARG_value +#--echo debug: sel_retval=$CRC_ARG_sel_retval +#--echo debug: sel_sidef=$CRC_ARG_sel_sidef + +--let $CRC_RET_stmt_sidef= +--let $CRC_RET_value= +--let $CRC_RET_sel_retval= +--let $CRC_RET_sel_sidef= +--let $CRC_RET_drop= +--let $CRC_RET_is_toplevel= 1 +--let $CRC_RET_desc= +--let $CRC_name= +--let $CRC_create= + +######## func_retval ######## +if (`SELECT $CRC_ARG_type = 0 AND '$CRC_ARG_value' != ''`) { + # It will be safe to call this function and discard the return + # value, but it will be unsafe to use return value (e.g., in + # INSERT...SELECT). + --let $CRC_name= func_retval_$CRC_ARG_level + --let $CRC_create= CREATE FUNCTION $CRC_name() RETURNS VARCHAR(100) BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); RETURN $CRC_ARG_value; END + --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_level VALUES ($CRC_name()) + --let $CRC_RET_value= $CRC_name() + --let $CRC_RET_sel_sidef= + --let $CRC_RET_sel_retval= SELECT $CRC_name() + --let $CRC_RET_drop= DROP FUNCTION $CRC_name + --let $CRC_RET_is_toplevel= 0 + --let $CRC_RET_desc= function $CRC_name returning value from $CRC_ARG_desc +} + +######## func_sidef ######## +if (`SELECT $CRC_ARG_type = 1`) { + # It will be unsafe to call func even if you discard return value. + --let $CRC_name= func_sidef_$CRC_ARG_level + --let $CRC_create= CREATE FUNCTION $CRC_name() RETURNS VARCHAR(100) BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); $CRC_ARG_stmt_sidef; RETURN 0; END + --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_level SELECT $CRC_name() + --let $CRC_RET_value= + --let $CRC_RET_sel_retval= + --let $CRC_RET_sel_sidef= SELECT $CRC_name() + --let $CRC_RET_drop= DROP FUNCTION $CRC_name + --let $CRC_RET_is_toplevel= 0 + --let $CRC_RET_desc= function $CRC_name invoking $CRC_ARG_desc +} + +######## proc ######## +if (`SELECT $CRC_ARG_type = 2`) { + # It will be unsafe to call this procedure. + --let $CRC_name= proc_$CRC_ARG_level + --let $CRC_create= CREATE PROCEDURE $CRC_name() BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); $CRC_ARG_stmt_sidef; END + --let $CRC_RET_stmt_sidef= CALL $CRC_name() + --let $CRC_RET_value= + --let $CRC_RET_sel_retval= + --let $CRC_RET_sel_sidef= + --let $CRC_RET_drop= DROP PROCEDURE $CRC_name + --let $CRC_RET_is_toplevel= 0 + --let $CRC_RET_desc= procedure $CRC_name invoking $CRC_ARG_desc +} + +######## trig ######## +if (`SELECT $CRC_ARG_type = 3`) { + # It will be unsafe to invoke this trigger. + --let $CRC_name= trig_$CRC_ARG_level + --let $CRC_create= CREATE TRIGGER $CRC_name BEFORE INSERT ON trigger_table_$CRC_ARG_level FOR EACH ROW BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); $CRC_ARG_stmt_sidef; END + --let $CRC_RET_stmt_sidef= INSERT INTO trigger_table_$CRC_ARG_level VALUES (1) + --let $CRC_RET_value= + --let $CRC_RET_sel_retval= + --let $CRC_RET_sel_sidef= + --let $CRC_RET_drop= DROP TRIGGER $CRC_name + --let $CRC_RET_is_toplevel= 0 + --let $CRC_RET_desc= trigger $CRC_name invoking $CRC_ARG_desc +} + +######## view_retval ######## +if (`SELECT $CRC_ARG_type = 4 AND '$CRC_ARG_sel_retval' != ''`) { + # It will be safe to select from this view if you discard the result + # set, but unsafe to use result set (e.g., in INSERT..SELECT). + --let $CRC_name= view_retval_$CRC_ARG_level + --let $CRC_create= CREATE VIEW $CRC_name AS $CRC_ARG_sel_retval + --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_LEVEL SELECT * FROM $CRC_name + --let $CRC_RET_value= + --let $CRC_RET_sel_retval= SELECT * FROM $CRC_name + --let $CRC_RET_sel_sidef= + --let $CRC_RET_drop= DROP VIEW $CRC_name + --let $CRC_RET_is_toplevel= 0 + --let $CRC_RET_desc= view $CRC_name returning value from $CRC_ARG_desc +} + +######## view_sidef ######## +if (`SELECT $CRC_ARG_type = 5 AND '$CRC_ARG_sel_sidef' != ''`) { + # It will be unsafe to select from this view, even if you discard + # the return value. + --let $CRC_name= view_sidef_$CRC_ARG_level + --let $CRC_create= CREATE VIEW $CRC_name AS $CRC_ARG_sel_sidef + --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_level SELECT * FROM $CRC_name + --let $CRC_RET_value= + --let $CRC_RET_sel_retval= + --let $CRC_RET_sel_sidef= SELECT * FROM $CRC_name + --let $CRC_RET_drop= DROP VIEW $CRC_name + --let $CRC_RET_is_toplevel= 0 + --let $CRC_RET_desc= view $CRC_name invoking $CRC_ARG_desc +} + +######## prep ######## +if (`SELECT $CRC_ARG_type = 6`) { + # It will be unsafe to execute this prepared statement + --let $CRC_name= prep_$CRC_ARG_level + --let $CRC_create= PREPARE $CRC_name FROM "$CRC_ARG_stmt_sidef" + --let $CRC_RET_stmt_sidef= EXECUTE $CRC_name + --let $CRC_RET_value= + --let $CRC_RET_sel_retval= + --let $CRC_RET_sel_sidef= + --let $CRC_RET_drop= DROP PREPARE $CRC_name + --let $CRC_RET_is_toplevel= 1 + --let $CRC_RET_desc= prepared statement $CRC_name invoking $CRC_ARG_desc +} + +######## no recursive construct: just return the given statement ######## +if (`SELECT $CRC_ARG_type = 7`) { + # CRC_ARG_type=7 is a special case. We just set $CRC_RET_x = + # $CRC_ARG_x. This way, the $CRC_ARG_stmt gets executed directly + # (below). In binlog_unsafe.test, it is used to invoke the unsafe + # statement created in the outermost loop directly, without + # enclosing it in a recursive construct. + --let $CRC_RET_stmt_sidef= $CRC_ARG_stmt_sidef + --let $CRC_RET_value= $CRC_ARG_value + --let $CRC_RET_sel_retval= $CRC_ARG_sel_retval + --let $CRC_RET_sel_sidef= $CRC_ARG_sel_sidef + --let $CRC_RET_drop= + --let $CRC_RET_is_toplevel= 1 + --let $CRC_RET_desc= $CRC_ARG_desc +} + +######## execute! ######## +if (`SELECT '$CRC_RET_stmt_sidef' != ''`) { + --echo + --echo Invoking $CRC_RET_desc. + if (`SELECT '$CRC_create' != ''`) { + --eval $CRC_create + } + + --echo * binlog_format = STATEMENT: expect $CRC_ARG_expected_number_of_warnings warnings. + --eval $CRC_RET_stmt_sidef + --let $n_warnings= `SHOW COUNT(*) WARNINGS` + if (`SELECT '$n_warnings' != '$CRC_ARG_expected_number_of_warnings'`) { + --echo ******** Failure! Expected $CRC_ARG_expected_number_of_warnings warnings, got $n_warnings warnings. ******** + SHOW WARNINGS; + SHOW BINLOG EVENTS; + --die Wrong number of warnings. + } + + # These queries are run without query log, to make result file more + # readable. Debug info is only printed if something abnormal + # happens. + --disable_query_log + + --echo * SQL_LOG_BIN = 0: expect nothing logged and no warning. + SET SQL_LOG_BIN = 0; + RESET MASTER; + --eval $CRC_RET_stmt_sidef + --let $n_warnings= `SHOW COUNT(*) WARNINGS` + if (`SELECT '$n_warnings' != '0'`) { + --echo ******** Failure! Expected 0 warnings, got $n_warnings warnings. ******** + SHOW WARNINGS; + SHOW BINLOG EVENTS; + --die Wrong number of warnings. + } + --let $binlog_event= query_get_value(SHOW BINLOG EVENTS, Event_type, 2) + if (`SELECT '$binlog_event' != 'No such row'`) { + --enable_query_log + --echo ******** Failure! Something was written to the binlog despite SQL_LOG_BIN=0 ******** + SHOW BINLOG EVENTS; + --die Binlog not empty + } + SET SQL_LOG_BIN = 1; + + --echo * binlog_format = MIXED: expect row events in binlog and no warning. + SET binlog_format = MIXED; + RESET MASTER; + --eval $CRC_RET_stmt_sidef + --let $n_warnings= `SHOW COUNT(*) WARNINGS` + if (`SELECT '$n_warnings' != '0'`) { + --echo ******** Failure! Expected 0 warnings, got $n_warnings warnings. ******** + SHOW WARNINGS; + SHOW BINLOG EVENTS; + --die Warnings printed + } + # The first event is format_description, the second is + # Query_event('BEGIN'), and the third should be our Table_map. + --let $event_type= query_get_value(SHOW BINLOG EVENTS, Event_type, 3) + if (`SELECT '$event_type' != 'Table_map'`) { + --enable_query_log + --echo ******** Failure! Event number 3 was a '$event_type', not a 'Table_map'. ******** + + # Currently, there is a bug causing some statements to be logged + # partially in statement format. Hence, we don't fail here, we + # just print the events (masking out nondeterministic components + # of the output) and continue. When binloggging works perfectly, + # we should instead execute: + #--enable_query_log + #SHOW BINLOG EVENTS; + #--die Wrong events in binlog. + + # Here, we should really source + # include/show_binlog_events.inc. But due to BUG#41913, that + # doesn't work, and we have to inline the entire file here. Sigh + # :-( + --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 107 <binlog_start> + --replace_column 2 # 4 # 5 # + --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /file_id=[0-9]+/file_id=#/ /block_len=[0-9]+/block_len=#/ + --eval SHOW BINLOG EVENTS FROM 107 + --disable_query_log + } + SET binlog_format = STATEMENT; + + --enable_query_log +} + +# Invoke created object, discarding the return value. This should not +# give any warning. +if (`SELECT '$CRC_RET_sel_retval' != ''`) { + --echo * Invoke statement so that return value is dicarded: expect no warning. + --disable_result_log + --eval $CRC_RET_sel_retval + --enable_result_log + + # Currently, due to a bug, we do get warnings here, so we don't + # fail. When the bug is fixed, we should execute the following. + + #--let $n_warnings= `SHOW COUNT(*) WARNINGS` + #if (`SELECT '$n_warnings' != '0'`) { + # --enable_query_log + # --echo Failure! Expected 0 warnings, got $n_warnings warnings. + # SHOW WARNINGS; + # SHOW BINLOG EVENTS; + # --die Wrong number of warnings. + #} +} + +#--echo debug: <<<<EXIT create_recursive_construct +#--echo debug: stmt_sidef=$CRC_RET_stmt_sidef +#--echo debug: value=$CRC_RET_value +#--echo debug: sel_retval=$CRC_RET_sel_retval +#--echo debug: sel_sidef=$CRC_RET_sel_sidef +#--echo debug: drop=$CRC_RET_drop +#--echo debug: is_toplevel=$CRC_RET_is_toplevel +#--echo debug: desc=$CRC_RET_desc diff --git a/mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test b/mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test new file mode 100644 index 00000000000..55dc7ca6514 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test @@ -0,0 +1,423 @@ +######################################################################################## +# This test verifies if the binlog is not corrupted when the cache buffer is not +# big enough to accommodate the changes and is divided in five steps: +# +# 1 - Single Statements: +# 1.1 - Single statement on transactional table. +# 1.2 - Single statement on non-transactional table. +# 1.3 - Single statement on both transactional and non-transactional tables. +# In both 1.2 and 1.3, an incident event is logged to notify the user that the +# master and slave are diverging. +# +# 2 - Transactions ended by an implicit commit. +# +# 3 - Transactions ended by a COMMIT. +# +# 4 - Transactions ended by a ROLLBACK. +# +# 5 - Transactions with a failing statement that updates a non-transactional +# table. In this case, a failure means that the statement does not get into +# the cache and an incident event is logged to notify the user that the master +# and slave are diverging. +# +######################################################################################## + +call mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT"); + +CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb; +CREATE TABLE t2(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MyIsam; +CREATE TABLE t3(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb; + +let $data = `select concat('"', repeat('a',2000), '"')`; + +--echo ######################################################################################## +--echo # 1 - SINGLE STATEMENT +--echo ######################################################################################## + +connection master; + +--echo *** Single statement on transactional table *** +--disable_query_log +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +eval INSERT INTO t1 (a, data) VALUES (1, + CONCAT($data, $data, $data, $data, $data)); +--enable_query_log + +--echo *** Single statement on non-transactional table *** +--disable_query_log +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +eval INSERT INTO t2 (a, data) VALUES (2, + CONCAT($data, $data, $data, $data, $data, $data)); +--enable_query_log + +connection slave; +--source include/wait_for_slave_sql_to_stop.inc +SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; +START SLAVE SQL_THREAD; +--source include/wait_for_slave_sql_to_start.inc + +connection master; + +--disable_query_log +eval INSERT INTO t1 (a, data) VALUES (3, $data); +eval INSERT INTO t1 (a, data) VALUES (4, $data); +eval INSERT INTO t1 (a, data) VALUES (5, $data); +eval INSERT INTO t2 (a, data) VALUES (3, $data); +eval INSERT INTO t2 (a, data) VALUES (4, $data); +eval INSERT INTO t2 (a, data) VALUES (5, $data); +--enable_query_log + +--echo *** Single statement on both transactional and non-transactional tables. *** +--disable_query_log +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +eval UPDATE t2, t1 SET t2.data = CONCAT($data, $data, $data, $data), + t1.data = CONCAT($data, $data, $data, $data); +--enable_query_log + +connection slave; +--source include/wait_for_slave_sql_to_stop.inc + +if (`SELECT @@binlog_format = 'STATEMENT'`) +{ + SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; +} +if (`SELECT @@binlog_format = 'ROW' || @@binlog_format = 'MIXED'`) +{ + SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; +} +START SLAVE SQL_THREAD; +--source include/wait_for_slave_sql_to_start.inc +connection master; + +let $diff_statement= SELECT * FROM t1; +--source include/diff_master_slave.inc + +--echo ######################################################################################## +--echo # 2 - BEGIN - IMPLICIT COMMIT by DDL +--echo ######################################################################################## + +connection master; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; + +BEGIN; +--disable_query_log +--eval INSERT INTO t1 (a, data) VALUES (1, $data); +--eval INSERT INTO t1 (a, data) VALUES (2, $data); +--eval INSERT INTO t1 (a, data) VALUES (3, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (4, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (5, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (6, $data); +--eval INSERT INTO t1 (a, data) VALUES (7, 's'); +--eval INSERT INTO t2 (a, data) VALUES (8, 's'); +--eval INSERT INTO t1 (a, data) VALUES (9, 's'); +--enable_query_log + +--disable_query_log +ALTER TABLE t3 ADD COLUMN d int; +--enable_query_log + +--disable_query_log +--eval INSERT INTO t2 (a, data) VALUES (10, $data); +--eval INSERT INTO t2 (a, data) VALUES (11, $data); +--eval INSERT INTO t2 (a, data) VALUES (12, $data); +--eval INSERT INTO t2 (a, data) VALUES (13, $data); +--enable_query_log + +BEGIN; +--disable_query_log +--eval INSERT INTO t1 (a, data) VALUES (14, $data); +--eval INSERT INTO t1 (a, data) VALUES (15, $data); +--eval INSERT INTO t1 (a, data) VALUES (16, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (17, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (18, $data); +--eval INSERT INTO t1 (a, data) VALUES (19, 's'); +--eval INSERT INTO t2 (a, data) VALUES (20, 's'); +--eval INSERT INTO t1 (a, data) VALUES (21, 's'); +--enable_query_log + +if (`SELECT @@binlog_format = 'STATEMENT'`) +{ + --disable_query_log + CREATE TABLE t4 SELECT * FROM t1; + --enable_query_log +} +if (`SELECT @@binlog_format = 'ROW' || @@binlog_format = 'MIXED'`) +{ + --disable_query_log + --error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE + CREATE TABLE t4 SELECT * FROM t1; + --enable_query_log +} + +--disable_query_log +--eval INSERT INTO t2 (a, data) VALUES (15, $data); +--enable_query_log + +BEGIN; +--disable_query_log +--eval INSERT INTO t1 (a, data) VALUES (22, $data); +--eval INSERT INTO t1 (a, data) VALUES (23, $data); +--eval INSERT INTO t1 (a, data) VALUES (24, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (25, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (26, $data); +--eval INSERT INTO t1 (a, data) VALUES (27, 's'); +--eval INSERT INTO t2 (a, data) VALUES (28, 's'); +--eval INSERT INTO t1 (a, data) VALUES (29, 's'); +--enable_query_log + +--disable_query_log +CREATE TABLE t5 (a int); +--enable_query_log + +if (`SELECT @@binlog_format = 'ROW' || @@binlog_format = 'MIXED'` ) +{ + connection slave; + --source include/wait_for_slave_sql_to_stop.inc + + SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; + START SLAVE SQL_THREAD; + --source include/wait_for_slave_sql_to_start.inc + connection master; +} + +let $diff_statement= SELECT * FROM t1; +--source include/diff_master_slave.inc + +--echo ######################################################################################## +--echo # 3 - BEGIN - COMMIT +--echo ######################################################################################## + +connection master; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; + +BEGIN; +--disable_query_log +--eval INSERT INTO t1 (a, data) VALUES (1, $data); +--eval INSERT INTO t1 (a, data) VALUES (2, $data); +--eval INSERT INTO t1 (a, data) VALUES (3, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (4, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (5, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (6, $data); +--eval INSERT INTO t1 (a, data) VALUES (7, 's'); +--eval INSERT INTO t2 (a, data) VALUES (8, 's'); +--eval INSERT INTO t1 (a, data) VALUES (9, 's'); +--enable_query_log +COMMIT; + +let $diff_statement= SELECT * FROM t1; +--source include/diff_master_slave.inc + +--echo ######################################################################################## +--echo # 4 - BEGIN - ROLLBACK +--echo ######################################################################################## + +connection master; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; + +BEGIN; +--disable_query_log +--eval INSERT INTO t1 (a, data) VALUES (1, $data); +--eval INSERT INTO t1 (a, data) VALUES (2, $data); +--eval INSERT INTO t1 (a, data) VALUES (3, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (4, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (5, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (6, $data); +--eval INSERT INTO t1 (a, data) VALUES (7, 's'); +--eval INSERT INTO t2 (a, data) VALUES (8, 's'); +--eval INSERT INTO t1 (a, data) VALUES (9, 's'); +--enable_query_log +ROLLBACK; + +let $diff_statement= SELECT * FROM t1; +--source include/diff_master_slave.inc + +--echo ######################################################################################## +--echo # 5 - PROCEDURE +--echo ######################################################################################## + +connection master; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; + +DELIMITER //; + +CREATE PROCEDURE p1(pd VARCHAR(30000)) +BEGIN + INSERT INTO t1 (a, data) VALUES (1, pd); + INSERT INTO t1 (a, data) VALUES (2, pd); + INSERT INTO t1 (a, data) VALUES (3, pd); + INSERT INTO t1 (a, data) VALUES (4, pd); + INSERT INTO t1 (a, data) VALUES (5, 's'); +END// + +DELIMITER ;// + +TRUNCATE TABLE t1; + +--disable_query_log +eval CALL p1($data); +--enable_query_log + +TRUNCATE TABLE t1; + +BEGIN; +--disable_query_log +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +eval CALL p1($data); +--enable_query_log +COMMIT; + +TRUNCATE TABLE t1; + +BEGIN; +--disable_query_log +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +eval CALL p1($data); +--enable_query_log +ROLLBACK; + +let $diff_statement= SELECT * FROM t1; +--source include/diff_master_slave.inc + +--echo ######################################################################################## +--echo # 6 - XID +--echo ######################################################################################## + +connection master; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; + +BEGIN; +--disable_query_log +--eval INSERT INTO t1 (a, data) VALUES (1, $data); +--eval INSERT INTO t1 (a, data) VALUES (2, $data); +--eval INSERT INTO t1 (a, data) VALUES (3, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (4, $data); +SAVEPOINT sv; +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (5, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (6, $data); +--eval INSERT INTO t1 (a, data) VALUES (7, 's'); +--eval INSERT INTO t2 (a, data) VALUES (8, 's'); +--eval INSERT INTO t1 (a, data) VALUES (9, 's'); +--enable_query_log +ROLLBACK TO sv; +COMMIT; + +let $diff_statement= SELECT * FROM t1; +--source include/diff_master_slave.inc + +--echo ######################################################################################## +--echo # 7 - NON-TRANS TABLE +--echo ######################################################################################## + +connection master; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; + +BEGIN; +--disable_query_log +--eval INSERT INTO t1 (a, data) VALUES (1, $data); +--eval INSERT INTO t1 (a, data) VALUES (2, $data); +--eval INSERT INTO t2 (a, data) VALUES (3, $data); +if (`SELECT @@binlog_format = 'STATEMENT'`) +{ + --error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE + --eval INSERT INTO t1 (a, data) VALUES (4, $data); +} +if (`SELECT @@binlog_format = 'ROW' || @@binlog_format = 'MIXED'`) +{ + --eval INSERT INTO t1 (a, data) VALUES (4, $data); +} +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (5, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (6, $data); +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (7, $data); +if (`SELECT @@binlog_format = 'STATEMENT'`) +{ + --error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE + --eval UPDATE t2 SET data= CONCAT($data, $data); +} +if (`SELECT @@binlog_format = 'ROW' || @@binlog_format = 'MIXED'`) +{ + --eval UPDATE t2 SET data= CONCAT($data, $data); +} +--eval INSERT INTO t1 (a, data) VALUES (8, 's'); +--eval INSERT INTO t1 (a, data) VALUES (9, 's'); +--eval INSERT INTO t2 (a, data) VALUES (10, 's'); +--eval INSERT INTO t1 (a, data) VALUES (11, 's'); +--enable_query_log +COMMIT; + +BEGIN; +--disable_query_log +--eval INSERT INTO t1 (a, data) VALUES (15, $data); +--eval INSERT INTO t1 (a, data) VALUES (16, $data); +--eval INSERT INTO t2 (a, data) VALUES (17, $data); +if (`SELECT @@binlog_format = 'STATEMENT'`) +{ + --error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE + --eval INSERT INTO t1 (a, data) VALUES (18, $data); +} +if (`SELECT @@binlog_format = 'ROW' || @@binlog_format = 'MIXED'`) +{ + --eval INSERT INTO t1 (a, data) VALUES (18, $data); +} +--error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--eval INSERT INTO t1 (a, data) VALUES (19, $data); +--enable_query_log +COMMIT; + +if (`SELECT @@binlog_format = 'STATEMENT'`) +{ + connection slave; + --source include/wait_for_slave_sql_to_stop.inc + + SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; + START SLAVE SQL_THREAD; + --source include/wait_for_slave_sql_to_start.inc + connection master; +} + +let $diff_statement= SELECT * FROM t1; +--source include/diff_master_slave.inc + +--echo ######################################################################################## +--echo # CLEAN +--echo ######################################################################################## + +connection master; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE IF EXISTS t4; +DROP TABLE IF EXISTS t5; +DROP TABLE IF EXISTS t6; +DROP PROCEDURE p1; +sync_slave_with_master; diff --git a/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test b/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test index 69b375677b6..20f7afbae0d 100644 --- a/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test +++ b/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test @@ -102,9 +102,8 @@ SELECT * FROM t2 ORDER BY a; connection slave; START SLAVE; source include/wait_for_slave_sql_to_stop.inc; ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS +--let $errno= query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +--echo Slave failed with Error $errno STOP SLAVE; RESET SLAVE; SELECT * FROM t2 ORDER BY a; @@ -155,9 +154,8 @@ INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TEST --echo ******************************************** connection slave; --source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS +--let $errno= query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +--echo Slave failed with Error $errno SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -200,9 +198,8 @@ INSERT INTO t4 () VALUES(100.22,2,'Kyle, TEX'),(200.26,1,'JOE AUSTIN'), --echo ******************************************** connection slave; --source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS +--let $errno= query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +--echo Slave failed with Error $errno SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -245,9 +242,8 @@ INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), --echo ******************************************** connection slave; --source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS +--let $errno= query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +--echo Slave failed with Error $errno SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -291,9 +287,8 @@ INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), --echo ******************************************** connection slave; --source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS +--let $errno= query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +--echo Slave failed with Error $errno SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; #START SLAVE; @@ -500,9 +495,8 @@ INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); --echo ******************************************** connection slave; --source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS +--let $errno= query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +--echo Slave failed with Error $errno SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -544,9 +538,8 @@ INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); --echo ******************************************** connection slave; --source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS +--let $errno= query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +--echo Slave failed with Error $errno SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -816,9 +809,8 @@ ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5; --echo ******************************************** connection slave; --source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS +--let $errno= query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +--echo Slave failed with Error $errno SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; @@ -927,9 +919,8 @@ INSERT INTO t17 () VALUES(9223372036854775807,2,'Kyle, TEX'); --echo ******************************************** connection slave; --source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS +--let $errno= query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1) +--echo Slave failed with Error $errno SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/extra/rpl_tests/rpl_flsh_tbls.test b/mysql-test/extra/rpl_tests/rpl_flsh_tbls.test index 0baf49c9fac..6de4a538e72 100644 --- a/mysql-test/extra/rpl_tests/rpl_flsh_tbls.test +++ b/mysql-test/extra/rpl_tests/rpl_flsh_tbls.test @@ -20,19 +20,15 @@ rename table t1 to t5, t2 to t1; # first don't write it to the binlog, to test the NO_WRITE_TO_BINLOG keyword. flush no_write_to_binlog tables; # Check that it's not in the binlog. ---replace_result $SERVER_VERSION SERVER_VERSION ---replace_column 2 # 5 # ---replace_regex /table_id: [0-9]+/table_id: #/ -eval SHOW BINLOG EVENTS FROM $rename_event_pos ; +let $binlog_start= $rename_event_pos; +source include/show_binlog_events.inc; # Check that the master is not confused. select * from t3; # This FLUSH should go into the binlog to not confuse the slave. flush tables; # Check that it's in the binlog. ---replace_result $SERVER_VERSION SERVER_VERSION ---replace_column 2 # 5 # ---replace_regex /table_id: [0-9]+/table_id: #/ -eval SHOW BINLOG EVENTS FROM $rename_event_pos ; +let $binlog_start= $rename_event_pos; +source include/show_binlog_events.inc; sync_slave_with_master; # Check that the slave is not confused. diff --git a/mysql-test/extra/rpl_tests/rpl_foreign_key.test b/mysql-test/extra/rpl_tests/rpl_foreign_key.test index 8755bf5aa87..ea16237435c 100644 --- a/mysql-test/extra/rpl_tests/rpl_foreign_key.test +++ b/mysql-test/extra/rpl_tests/rpl_foreign_key.test @@ -47,7 +47,6 @@ insert into t1 set b=1; insert into t2 set a=1, b=1; set foreign_key_checks=0; -set @@session.binlog_format=row; delete from t1; --echo must sync w/o a problem (could not with the buggy code) diff --git a/mysql-test/extra/rpl_tests/rpl_implicit_commit_binlog.test b/mysql-test/extra/rpl_tests/rpl_implicit_commit_binlog.test new file mode 100644 index 00000000000..39d18b7403d --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_implicit_commit_binlog.test @@ -0,0 +1,656 @@ +################################################################################ +# In this test case, we verify if some DDL statements implicitly commit a +# transaction and are written directly to the binary log without going +# through either the Statement- or Transactional-Cache. +# +# As any statement that goes through a cache is written to the binary log +# wrapped in a BEGIN...COMMIT, we proceed as follows: +# +# - create a transaction and insert some values into a transactional table. +# - execute a DDL statement that is supposed to implicitly commit the previous +# transaction. +# - Check in the binary log for a COMMIT mark which is supposed to be written +# before the DDL statement. +# - Check in the binary log if the DDL is not wrapped by a BEGIN..COMMIT. +# +# For further details, please, read WL#2687 and WL#5072. +################################################################################ + +--echo ######################################################################### +--echo # CONFIGURATION +--echo ######################################################################### +connection master; + +eval CREATE TABLE tt_1 (ddl_case INT, PRIMARY KEY(ddl_case)) ENGINE = $engine; +eval CREATE TABLE tt_2 (ddl_case INT, PRIMARY KEY(ddl_case)) ENGINE = $engine; +eval CREATE TABLE nt_1 (ddl_case INT, PRIMARY KEY(ddl_case)) ENGINE = MyIsam; + +INSERT INTO tt_1(ddl_case) VALUES(0); +INSERT INTO tt_2(ddl_case) VALUES(0); + +--echo ######################################################################### +--echo # CHECK IMPLICT COMMIT +--echo ######################################################################### +SET AUTOCOMMIT= 0; +let $ddl_cases= 41; +while (`SELECT $ddl_cases >= 1`) +{ + --echo -b-b-b-b-b-b-b-b-b-b-b- >> << -b-b-b-b-b-b-b-b-b-b-b- + let $in_temporary= "no"; + let $ok= "yes"; + # + # In SBR and MIXED modes, the commit event is usually the third event in the + # binary log: + # + # 1: BEGIN + # 2: INSERT + # 3: COMMIT + # 4: DDL EVENT which triggered the previous commmit. + # + if (`select @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`) + { + let $commit_event_row_number= 3; + } + # + # In RBR mode, the commit event is usually the fourth event in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: ROW EVENT + # 4: COMMIT + # 5: DDL EVENT which triggered the previous commmit. + # + if (`select @@binlog_format = 'ROW'`) + { + let $commit_event_row_number= 4; + } + # + # In NDB (RBR and MIXED modes), the commit event is usually the seventh event + # in the binary log: + # + # 1: COMMAND + # 2: BEGIN + # 3: TABLE MAP EVENT + # 4: TABLE MAP EVENT (ndb_apply_status) + # 5: ROW EVENT + # 6: ROW EVENT + # 7: COMMIT + # + if (`select '$engine' = 'NDB'`) + { + let $commit_event_row_number= 7; + } + + let $first_binlog_position= query_get_value("SHOW MASTER STATUS", Position, 1); + --enable_query_log + eval INSERT INTO tt_1(ddl_case) VALUES ($ddl_cases); + if (`SELECT $ddl_cases = 41`) + { + let $cmd= LOAD INDEX INTO CACHE nt_1 IGNORE LEAVES; + if (`SELECT '$engine' = 'NDB'`) + { + # This seems to be related to epochs. + # We need to check this against an updated version or avoid it. + let $ok= "no"; + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 40`) + { + let $cmd= LOAD INDEX INTO CACHE tt_1, tt_2 IGNORE LEAVES; + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 39`) + { + let $cmd= ANALYZE TABLE nt_1; + } + if (`SELECT $ddl_cases = 38`) + { + let $cmd= CHECK TABLE nt_1; + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 37`) + { + let $cmd= OPTIMIZE TABLE nt_1; + } + if (`SELECT $ddl_cases = 36`) + { + let $cmd= REPAIR TABLE nt_1; + } + if (`SELECT $ddl_cases = 35`) + { + let $cmd= LOCK TABLES tt_1 WRITE; + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 34`) + { + let $cmd= UNLOCK TABLES; + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 33`) + { + let $cmd= CREATE USER 'user'@'localhost'; + } + if (`SELECT $ddl_cases = 32`) + { + let $cmd= GRANT ALL ON *.* TO 'user'@'localhost'; + } + if (`SELECT $ddl_cases = 31`) + { + let $cmd= SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpass'); + # + # In NDB (RBR mode), the commit event is the eleventh event + # in the binary log: + # + # 1: DDL EVENT which triggered the previous commmit. + # 2: BEGIN + # 3: TABLE MAP EVENT + # 4: ROW EVENT + # 5: COMMIT + # 6: BEGIN + # 7: TABLE MAP EVENT + # 8: TABLE MAP EVENT (ndb_apply_status) + # 9: ROW EVENT + # 10: ROW EVENT + # 11: COMMIT + # + if (`SELECT '$engine' = 'NDB' && @@binlog_format = 'ROW'`) + { + let $commit_event_row_number= 11; + } + # + # In NDB (MIXED mode), the commit event is the eighth event + # in the binary log: + # + # 1: DDL EVENT which triggered the previous commmit. + # 2: COMMIT + # 3: BEGIN + # 4: TABLE MAP EVENT + # 5: TABLE MAP EVENT (ndb_apply_status) + # 6: ROW EVENT + # 7: ROW EVENT + # 8: COMMIT + # + if (`SELECT '$engine' = 'NDB' && @@binlog_format != 'ROW'`) + { + let $commit_event_row_number= 7; + } + } + if (`SELECT $ddl_cases = 30`) + { + let $cmd= REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost'; + } + if (`SELECT $ddl_cases = 29`) + { + let $cmd= RENAME USER 'user'@'localhost' TO 'user_new'@'localhost'; + } + if (`SELECT $ddl_cases = 28`) + { + let $cmd= DROP USER 'user_new'@'localhost'; + } + if (`SELECT $ddl_cases = 27`) + { + let $cmd= CREATE EVENT evt ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO SELECT * FROM tt_1; + } + if (`SELECT $ddl_cases = 26`) + { + let $cmd= ALTER EVENT evt COMMENT 'evt'; + } + if (`SELECT $ddl_cases = 25`) + { + let $cmd= DROP EVENT evt; + } + if (`SELECT $ddl_cases = 24`) + { + let $cmd= CREATE TRIGGER tr AFTER INSERT ON tt_1 FOR EACH ROW UPDATE tt_2 SET ddl_case = ddl_case WHERE ddl_case= NEW.ddl_case; + } + if (`SELECT $ddl_cases = 23`) + { + let $cmd= DROP TRIGGER tr; + # + # In RBR mode, due to the trigger the tt_2 is also updated: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT + # 4: ROW EVENT + # 5: COMMIT + # 6: DDL EVENT which triggered the previous commmit. + # + if (`select @@binlog_format = 'ROW' && '$engine' != 'NDB'`) + { + let $commit_event_row_number= 5; + } + } + if (`SELECT $ddl_cases = 22`) + { + let $cmd= CREATE FUNCTION fc () RETURNS VARCHAR(64) RETURN "fc"; + } + if (`SELECT $ddl_cases = 21`) + { + let $cmd= ALTER FUNCTION fc COMMENT 'fc'; + } + if (`SELECT $ddl_cases = 20`) + { + let $cmd= DROP FUNCTION fc; + } + if (`SELECT $ddl_cases = 19`) + { + let $cmd= CREATE PROCEDURE pc () UPDATE tt_2 SET ddl_case = ddl_case WHERE ddl_case= NEW.ddl_case; + } + if (`SELECT $ddl_cases = 18`) + { + let $cmd= ALTER PROCEDURE pc COMMENT 'pc'; + } + if (`SELECT $ddl_cases = 17`) + { + let $cmd= DROP PROCEDURE pc; + } + if (`SELECT $ddl_cases = 16`) + { + let $cmd= CREATE VIEW v AS SELECT * FROM tt_1; + } + if (`SELECT $ddl_cases = 15`) + { + let $cmd= ALTER VIEW v AS SELECT * FROM tt_1; + } + if (`SELECT $ddl_cases = 14`) + { + let $cmd= DROP VIEW v; + } + if (`SELECT $ddl_cases = 13`) + { + let $cmd= CREATE INDEX ix ON tt_1(ddl_case); + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # 7: DDL EVENT which triggered the previous commmit. + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 12`) + { + let $cmd= DROP INDEX ix ON tt_1; + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # 7: DDL EVENT which triggered the previous commmit. + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 11`) + { + let $cmd= CREATE TEMPORARY TABLE tt_xx (a int); + let $in_temporary= "yes"; + # In SBR and MIXED modes, the DDL statement is written to the binary log but + # does not commit the current transaction. + # + # 1: BEGIN + # 2: INSERT + # 3: CREATE TEMPORARY + # + # In RBR the transaction is not committed either and the statement is not + # written to the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: ROW EVENT + # + if (`select @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`) + { + let $commit_event_row_number= 4; + } + # + # In NDB (RBR mode), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # + if (`SELECT '$engine' = 'NDB' && @@binlog_format = 'ROW'` ) + { + let $commit_event_row_number= 6; + } + # + # In NDB (MIXED mode), the commit event is the nineth event + # in the binary log: + # + # 1: BEGIN + # 2: DDL EVENT which triggered the previous commmit. + # 3: COMMIT + # 4: BEGIN + # 5: TABLE MAP EVENT + # 6: TABLE MAP EVENT (ndb_apply_status) + # 7: ROW EVENT + # 8: ROW EVENT + # 9: COMMIT + # + if (`SELECT '$engine' = 'NDB' && @@binlog_format != 'ROW'` ) + { + let $commit_event_row_number= 9; + } + } + if (`SELECT $ddl_cases = 10`) + { + let $cmd= ALTER TABLE tt_xx ADD COLUMN (b int); + # + # In MIXED mode, the changes are logged as rows and we have what follows: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: ROW EVENT + # 4: COMMIT + # 5: DDL EVENT which triggered the previous commmit. + # + if (`select @@binlog_format = 'MIXED'`) + { + let $commit_event_row_number= 4; + } + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 9`) + { + let $cmd= ALTER TABLE tt_xx RENAME new_tt_xx; + # + # In MIXED mode, the changes are logged as rows and we have what follows: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: ROW EVENT + # 4: COMMIT + # 5: DDL EVENT which triggered the previous commmit. + # + if (`select @@binlog_format = 'MIXED'`) + { + let $commit_event_row_number= 4; + } + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 8`) + { + let $cmd= DROP TEMPORARY TABLE IF EXISTS new_tt_xx; + let $in_temporary= "yes"; + # + # In SBR and MIXED modes, the DDL statement is written to the binary log + # but does not commit the current transaction: + # + # 1: BEGIN + # 2: INSERT + # 3: DROP TEMPORARY + # + # In RBR the transaction is not committed either and the statement is not + # written to the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: ROW EVENT + # + if (`select @@binlog_format = 'STATEMENT'`) + { + let $commit_event_row_number= 4; + } + # In MIXED mode, the changes are logged as rows and we have what follows: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: ROW EVENT + # 4: DROP TEMPORARY table IF EXISTS + # + if (`select @@binlog_format = 'MIXED'`) + { + let $commit_event_row_number= 5; + } + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + # + # In NDB (MIXED mode), the commit event is the nineth event + # in the binary log: + # + # 1: BEGIN + # 2: DDL EVENT which triggered the previous commmit. + # 3: COMMIT + # 4: BEGIN + # 5: TABLE MAP EVENT + # 6: TABLE MAP EVENT (ndb_apply_status) + # 7: ROW EVENT + # 8: ROW EVENT + # 9: COMMIT + # + if (`SELECT '$engine' = 'NDB' && @@binlog_format != 'ROW'` ) + { + let $commit_event_row_number= 9; + } + } + if (`SELECT $ddl_cases = 7`) + { + let $cmd= CREATE TABLE tt_xx (a int); + } + if (`SELECT $ddl_cases = 6`) + { + let $cmd= ALTER TABLE tt_xx ADD COLUMN (b int); + } + if (`SELECT $ddl_cases = 5`) + { + let $cmd= RENAME TABLE tt_xx TO new_tt_xx; + } + if (`SELECT $ddl_cases = 4`) + { + let $cmd= TRUNCATE TABLE new_tt_xx; + } + if (`SELECT $ddl_cases = 3`) + { + let $cmd= DROP TABLE IF EXISTS tt_xx, new_tt_xx; + } + if (`SELECT $ddl_cases = 2`) + { + let $cmd= CREATE DATABASE db; + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # 7: DDL EVENT which triggered the previous commmit. + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + if (`SELECT $ddl_cases = 1`) + { + let $cmd= DROP DATABASE IF EXISTS db; + # + # In NDB (RBR and MIXED modes), the commit event is the sixth event + # in the binary log: + # + # 1: BEGIN + # 2: TABLE MAP EVENT + # 3: TABLE MAP EVENT (ndb_apply_status) + # 4: ROW EVENT + # 5: ROW EVENT + # 6: COMMIT + # 7: DDL EVENT which triggered the previous commmit. + # + if (`SELECT '$engine' = 'NDB'`) + { + let $commit_event_row_number= 6; + } + } + --eval $cmd + --disable_query_log + # + # When a temporary table is either created or dropped, there is no implicit + # commit. The flag in_temporary is used to avoid aborting the test in such + # cases. Thus we force the commit. + # + if (`SELECT $in_temporary = "yes"`) + { + --eval COMMIT + } + let $event_commit= query_get_value("SHOW BINLOG EVENTS FROM $first_binlog_position", Info, $commit_event_row_number); + if (`SELECT SUBSTRING("$event_commit",1,6) != "COMMIT"`) + { + if (`SELECT $ok = "yes"`) + { + --echo it *does not* commit the current transaction. + --echo $cmd + --echo $event_commit + SHOW BINLOG EVENTS; + exit; + } + } + --echo -e-e-e-e-e-e-e-e-e-e-e- >> << -e-e-e-e-e-e-e-e-e-e-e- + let $binlog_start= $first_binlog_position; + --echo -b-b-b-b-b-b-b-b-b-b-b- >> << -b-b-b-b-b-b-b-b-b-b-b- + --source include/show_binlog_events.inc + --echo -e-e-e-e-e-e-e-e-e-e-e- >> << -e-e-e-e-e-e-e-e-e-e-e- + --echo + dec $ddl_cases; +} +SET AUTOCOMMIT= 1; + +--echo ################################################################################### +--echo # CHECK CONSISTENCY +--echo ################################################################################### +--sync_slave_with_master + +--let $diff_table_1= master:test.tt_1 +--let $diff_table_2= slave:test.tt_1 +--source include/diff_tables.inc + +--echo ################################################################################### +--echo # CLEAN +--echo ################################################################################### +connection master; + +DROP TABLE tt_1; +DROP TABLE tt_2; +DROP TABLE nt_1; + +sync_slave_with_master; diff --git a/mysql-test/extra/rpl_tests/rpl_innodb.test b/mysql-test/extra/rpl_tests/rpl_innodb.test new file mode 100644 index 00000000000..c866b68466d --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_innodb.test @@ -0,0 +1,123 @@ +# +# Bug#11401: Load data infile 'REPLACE INTO' fails on slave. +# +connection master; +CREATE TABLE t4 ( + id INT(5) unsigned NOT NULL auto_increment, + name varchar(15) NOT NULL default '', + number varchar(35) NOT NULL default 'default', + PRIMARY KEY (id), + UNIQUE KEY unique_rec (name,number) +) ENGINE=InnoDB; + +--disable_warnings +LOAD DATA + INFILE '../../std_data/loaddata_pair.dat' + REPLACE INTO TABLE t4 + (name,number); +--enable_warnings +SELECT * FROM t4; + +sync_slave_with_master; +SELECT * FROM t4; + +connection master; +--disable_warnings +LOAD DATA + INFILE '../../std_data/loaddata_pair.dat' + REPLACE INTO TABLE t4 + (name,number); +--enable_warnings +SELECT * FROM t4; + +sync_slave_with_master; +SELECT * FROM t4; + +connection master; +--disable_query_log +DROP TABLE t4; +--enable_query_log +sync_slave_with_master; +connection master; + +# End of 4.1 tests + +# +# Bug #26418: Slave out of sync after CREATE/DROP TEMPORARY TABLE + ROLLBACK +# on master +# +#Note Matthias: to be merged to rpl_ddl.test + +--source include/not_ndb_default.inc + +FLUSH LOGS; +sync_slave_with_master; +FLUSH LOGS; +connection master; +let $engine_type= "InnoDB"; + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +--enable_warnings + +CREATE DATABASE mysqltest1; +CREATE TEMPORARY TABLE mysqltest1.tmp (f1 BIGINT); +eval CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE=$engine_type; +SET AUTOCOMMIT = 0; + +sync_slave_with_master; +--echo -------- switch to slave -------- +connection slave; + +# We want to verify that the following transactions are written to the +# binlog, despite the transaction is rolled back. (They should be +# written to the binlog since they contain non-transactional DROP +# TEMPORARY TABLE). To see that, we use the auxiliary table t1, which +# is transactional (InnoDB) on master and MyISAM on slave. t1 should +# be transactional on master so that the insert into t1 does not cause +# the transaction to be logged. Since t1 is non-transactional on +# slave, the change will not be rolled back, so the inserted rows will +# stay in t1 and we can verify that the transaction was replicated. +# +# Note, however, that the previous explanation is not true for ROW and +# MIXED modes as rollback on a transactional table is not written to +# the binary log. +ALTER TABLE mysqltest1.t1 ENGINE = MyISAM; +SHOW CREATE TABLE mysqltest1.t1; + +--echo -------- switch to master -------- +connection master; +INSERT INTO mysqltest1.t1 SET f1= 1; +DROP TEMPORARY TABLE mysqltest1.tmp; +ROLLBACK; +--error ER_NO_SUCH_TABLE +SHOW CREATE TABLE mysqltest1.tmp; +# Must return no rows here +SELECT COUNT(*) FROM mysqltest1.t1; + +INSERT INTO mysqltest1.t1 SET f1= 2; +CREATE TEMPORARY TABLE mysqltest1.tmp2(a INT); +ROLLBACK; +SHOW CREATE TABLE mysqltest1.tmp2; +# Must return no rows here +SELECT COUNT(*) FROM mysqltest1.t1; + +sync_slave_with_master; +--echo -------- switch to slave -------- +connection slave; +--error ER_NO_SUCH_TABLE +SHOW CREATE TABLE mysqltest1.tmp; +--error ER_NO_SUCH_TABLE +SHOW CREATE TABLE mysqltest1.tmp2; +# t1 has two rows here: the transaction not rolled back since t1 uses MyISAM +SELECT COUNT(*) FROM mysqltest1.t1; +FLUSH LOGS; + +--echo -------- switch to master -------- +connection master; +FLUSH LOGS; + +DROP DATABASE mysqltest1; +-- source include/master-slave-end.inc + +--echo End of 5.1 tests diff --git a/mysql-test/extra/rpl_tests/rpl_insert_delayed.test b/mysql-test/extra/rpl_tests/rpl_insert_delayed.test index e492903afad..a6bf2b294d1 100644 --- a/mysql-test/extra/rpl_tests/rpl_insert_delayed.test +++ b/mysql-test/extra/rpl_tests/rpl_insert_delayed.test @@ -38,18 +38,19 @@ connection master; truncate table t1; # first scenario: duplicate on first row insert delayed into t1 values(10, "my name"); -if ($binlog_format_statement) +flush table t1; +if (`SELECT @@global.binlog_format = 'STATEMENT'`) { # statement below will be converted to non-delayed INSERT and so # will stop at first error, guaranteeing replication. --error ER_DUP_ENTRY insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); } -if (!$binlog_format_statement) +if (`SELECT @@global.binlog_format != 'STATEMENT'`) { insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); } -flush table t1; # to wait for INSERT DELAYED to be done +flush table t1; select * from t1; sync_slave_with_master; # when bug existed in statement-based binlogging, t1 on slave had @@ -59,7 +60,7 @@ select * from t1; # second scenario: duplicate on second row connection master; delete from t1 where id!=10; -if ($binlog_format_statement) +if (`SELECT @@global.binlog_format = 'STATEMENT'`) { # statement below will be converted to non-delayed INSERT and so # will be binlogged with its ER_DUP_ENTRY error code, guaranteeing @@ -67,7 +68,7 @@ if ($binlog_format_statement) --error ER_DUP_ENTRY insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); } -if (!$binlog_format_statement) +if (`SELECT @@global.binlog_format != 'STATEMENT'`) { insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); } @@ -108,6 +109,7 @@ if (`SELECT @@global.binlog_format != 'ROW'`) { #must show two INSERT DELAYED --replace_column 1 x 2 x 3 x 4 x 5 x + --replace_regex /table_id: [0-9]+/table_id: #/ show binlog events in 'master-bin.000002' LIMIT 2,2; } select * from t1; @@ -118,6 +120,7 @@ if (`SELECT @@global.binlog_format != 'ROW'`) { #must show two INSERT DELAYED --replace_column 1 x 2 x 3 x 4 x 5 x + --replace_regex /table_id: [0-9]+/table_id: #/ show binlog events in 'slave-bin.000002' LIMIT 2,2; } select * from t1; diff --git a/mysql-test/extra/rpl_tests/rpl_insert_id.test b/mysql-test/extra/rpl_tests/rpl_insert_id.test index b076e73a215..c8793559b07 100644 --- a/mysql-test/extra/rpl_tests/rpl_insert_id.test +++ b/mysql-test/extra/rpl_tests/rpl_insert_id.test @@ -9,6 +9,8 @@ # column and index but without primary key. ############################################################## +call mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT"); + --echo # --echo # Setup --echo # @@ -187,7 +189,9 @@ drop trigger t1_bi; # Check that nested call doesn't affect outer context. select last_insert_id(); +--disable_warnings select bug15728_insert(); +--enable_warnings select last_insert_id(); insert into t1 (last_id) values (bug15728()); # This should be exactly one greater than in the previous call. @@ -440,7 +444,9 @@ delimiter ;| INSERT INTO t1 VALUES (NULL, -1); CALL p1(); +--disable_warnings SELECT f1(); +--enable_warnings INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2()); INSERT INTO t1 VALUES (NULL, f2()); @@ -509,7 +515,9 @@ insert into t2 (id) values(1),(2),(3); delete from t2; set sql_log_bin=1; #inside SELECT, then inside INSERT +--disable_warnings select insid(); +--enable_warnings set sql_log_bin=0; insert into t2 (id) values(5),(6),(7); delete from t2 where id>=5; diff --git a/mysql-test/extra/rpl_tests/rpl_insert_ignore.test b/mysql-test/extra/rpl_tests/rpl_insert_ignore.test index 43d45ef6c60..ee09c316354 100644 --- a/mysql-test/extra/rpl_tests/rpl_insert_ignore.test +++ b/mysql-test/extra/rpl_tests/rpl_insert_ignore.test @@ -55,7 +55,9 @@ INSERT INTO t1 VALUES (2, 2); INSERT INTO t1 VALUES (3, 3); INSERT INTO t1 VALUES (4, 4); +--disable_warnings INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a; +--enable_warnings SELECT * FROM t1 ORDER BY a; diff --git a/mysql-test/extra/rpl_tests/rpl_loaddata.test b/mysql-test/extra/rpl_tests/rpl_loaddata.test index e159242ed10..e65973dec8b 100644 --- a/mysql-test/extra/rpl_tests/rpl_loaddata.test +++ b/mysql-test/extra/rpl_tests/rpl_loaddata.test @@ -1,6 +1,3 @@ -# Requires statement logging --- source include/have_binlog_format_mixed_or_statement.inc - # See if replication of a "LOAD DATA in an autoincrement column" # Honours autoincrement values # i.e. if the master and slave have the same sequence @@ -71,7 +68,7 @@ set global sql_slave_skip_counter=1; start slave; sync_with_master; --replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 16 # 23 # 33 # +--replace_column 1 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # --query_vertical show slave status; # Trigger error again to test CHANGE MASTER @@ -93,7 +90,7 @@ stop slave; change master to master_user='test'; change master to master_user='root'; --replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 16 # 23 # 33 # +--replace_column 1 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # --query_vertical show slave status; # Trigger error again to test RESET SLAVE @@ -115,7 +112,7 @@ connection slave; stop slave; reset slave; --replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 16 # 23 # 33 # +--replace_column 1 # 8 # 9 # 16 # 22 # 23 # 33 # --query_vertical show slave status; # Finally, see if logging is done ok on master for a failing LOAD DATA INFILE diff --git a/mysql-test/extra/rpl_tests/rpl_log.test b/mysql-test/extra/rpl_tests/rpl_log.test index 0517fea1be3..7efcb67b033 100644 --- a/mysql-test/extra/rpl_tests/rpl_log.test +++ b/mysql-test/extra/rpl_tests/rpl_log.test @@ -43,7 +43,7 @@ show binlog events from 107 limit 1; show binlog events from 107 limit 2; --replace_column 2 # 5 # --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ -show binlog events from 107 limit 2,1; +show binlog events from 107 limit 1,4; flush logs; # We need an extra update before doing save_master_pos. @@ -104,7 +104,7 @@ show binlog events in 'slave-bin.000001' from 4; --replace_column 2 # 5 # --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ show binlog events in 'slave-bin.000002' from 4; -source include/show_slave_status.inc; +source include/show_slave_status2.inc; # Need to recode the following diff --git a/mysql-test/extra/rpl_tests/rpl_mixing_engines.inc b/mysql-test/extra/rpl_tests/rpl_mixing_engines.inc new file mode 100644 index 00000000000..6dde3e079a1 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_mixing_engines.inc @@ -0,0 +1,554 @@ +################################################################################ +# This is an auxiliary file used by rpl_mixing_engines.test, and that it +# executes SQL statements according to a format string, as specified in +# rpl_mixing_engines.test. In addition, it accepts the special format +# strings 'configure' and 'clean', used before and after everything else. +################################################################################ + +if (`SELECT HEX(@commands) = HEX('configure')`) +{ + connection master; + + SET SQL_LOG_BIN=0; + eval CREATE TABLE nt_1 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_2 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_3 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_4 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_5 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_6 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE tt_1 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_2 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_3 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_4 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_5 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_6 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval SET SQL_LOG_BIN=1; + + connection slave; + + SET SQL_LOG_BIN=0; + eval CREATE TABLE nt_1 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_2 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_3 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_4 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_5 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE nt_6 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = MyISAM; + eval CREATE TABLE tt_1 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_2 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_3 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_4 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_5 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + eval CREATE TABLE tt_6 (trans_id INT, stmt_id INT, info VARCHAR(64), PRIMARY KEY(trans_id, stmt_id)) ENGINE = $engine_type; + SET SQL_LOG_BIN=1; + + connection master; + + INSERT INTO nt_1(trans_id, stmt_id) VALUES(1,1); + INSERT INTO nt_2(trans_id, stmt_id) VALUES(1,1); + INSERT INTO nt_3(trans_id, stmt_id) VALUES(1,1); + INSERT INTO nt_4(trans_id, stmt_id) VALUES(1,1); + INSERT INTO nt_5(trans_id, stmt_id) VALUES(1,1); + INSERT INTO nt_6(trans_id, stmt_id) VALUES(1,1); + + INSERT INTO tt_1(trans_id, stmt_id) VALUES(1,1); + INSERT INTO tt_2(trans_id, stmt_id) VALUES(1,1); + INSERT INTO tt_3(trans_id, stmt_id) VALUES(1,1); + INSERT INTO tt_4(trans_id, stmt_id) VALUES(1,1); + INSERT INTO tt_5(trans_id, stmt_id) VALUES(1,1); + INSERT INTO tt_6(trans_id, stmt_id) VALUES(1,1); + + DELIMITER |; + + CREATE PROCEDURE pc_i_tt_5_suc (IN p_trans_id INTEGER, IN p_stmt_id INTEGER) + BEGIN + DECLARE in_stmt_id INTEGER; + SELECT max(stmt_id) INTO in_stmt_id FROM tt_5 WHERE trans_id= p_trans_id; + SELECT COALESCE(greatest(in_stmt_id + 1, p_stmt_id), 1) INTO in_stmt_id; + INSERT INTO tt_5(trans_id, stmt_id) VALUES (p_trans_id, in_stmt_id); + INSERT INTO tt_5(trans_id, stmt_id) VALUES (p_trans_id, in_stmt_id + 1); + END| + + CREATE PROCEDURE pc_i_nt_5_suc (IN p_trans_id INTEGER, IN p_stmt_id INTEGER) + BEGIN + DECLARE in_stmt_id INTEGER; + SELECT max(stmt_id) INTO in_stmt_id FROM nt_5 WHERE trans_id= p_trans_id; + SELECT COALESCE(greatest(in_stmt_id + 1, p_stmt_id), 1) INTO in_stmt_id; + INSERT INTO nt_5(trans_id, stmt_id) VALUES (p_trans_id, in_stmt_id); + INSERT INTO nt_5(trans_id, stmt_id) VALUES (p_trans_id, in_stmt_id + 1); + END| + + CREATE FUNCTION fc_i_tt_5_suc (p_trans_id INTEGER, p_stmt_id INTEGER) RETURNS VARCHAR(64) + BEGIN + DECLARE in_stmt_id INTEGER; + SELECT max(stmt_id) INTO in_stmt_id FROM tt_5 WHERE trans_id= p_trans_id; + SELECT COALESCE(greatest(in_stmt_id + 1, p_stmt_id), 1) INTO in_stmt_id; + INSERT INTO tt_5(trans_id, stmt_id) VALUES (p_trans_id, in_stmt_id); + INSERT INTO tt_5(trans_id, stmt_id) VALUES (p_trans_id, in_stmt_id + 1); + RETURN "fc_i_tt_5_suc"; + END| + + CREATE FUNCTION fc_i_nt_5_suc (p_trans_id INTEGER, p_stmt_id INTEGER) RETURNS VARCHAR(64) + BEGIN + DECLARE in_stmt_id INTEGER; + SELECT max(stmt_id) INTO in_stmt_id FROM nt_5 WHERE trans_id= p_trans_id; + SELECT COALESCE(greatest(in_stmt_id + 1, p_stmt_id), 1) INTO in_stmt_id; + INSERT INTO nt_5(trans_id, stmt_id) VALUES (p_trans_id, in_stmt_id); + INSERT INTO nt_5(trans_id, stmt_id) VALUES (p_trans_id, in_stmt_id + 1); + RETURN "fc_i_nt_5_suc"; + END| + + CREATE TRIGGER tr_i_tt_3_to_nt_3 AFTER INSERT ON tt_3 FOR EACH ROW + BEGIN + DECLARE in_stmt_id INTEGER; + SELECT max(stmt_id) INTO in_stmt_id FROM nt_3 WHERE trans_id= NEW.trans_id; + SELECT COALESCE(greatest(in_stmt_id + 1, NEW.stmt_id), 1) INTO in_stmt_id; + INSERT INTO nt_3(trans_id, stmt_id) VALUES (NEW.trans_id, in_stmt_id); + INSERT INTO nt_3(trans_id, stmt_id) VALUES (NEW.trans_id, in_stmt_id + 1); + END| + + CREATE TRIGGER tr_i_nt_4_to_tt_4 AFTER INSERT ON nt_4 FOR EACH ROW + BEGIN + DECLARE in_stmt_id INTEGER; + SELECT max(stmt_id) INTO in_stmt_id FROM tt_4 WHERE trans_id= NEW.trans_id; + SELECT COALESCE(greatest(in_stmt_id + 1, NEW.stmt_id), 1) INTO in_stmt_id; + INSERT INTO tt_4(trans_id, stmt_id) VALUES (NEW.trans_id, in_stmt_id); + INSERT INTO tt_4(trans_id, stmt_id) VALUES (NEW.trans_id, in_stmt_id + 1); + END| + + CREATE TRIGGER tr_i_tt_5_to_tt_6 AFTER INSERT ON tt_5 FOR EACH ROW + BEGIN + DECLARE in_stmt_id INTEGER; + SELECT max(stmt_id) INTO in_stmt_id FROM tt_6 WHERE trans_id= NEW.trans_id; + SELECT COALESCE(greatest(in_stmt_id + 1, NEW.stmt_id, 1), 1) INTO in_stmt_id; + INSERT INTO tt_6(trans_id, stmt_id) VALUES (NEW.trans_id, in_stmt_id); + INSERT INTO tt_6(trans_id, stmt_id) VALUES (NEW.trans_id, in_stmt_id + 1); + END| + + CREATE TRIGGER tr_i_nt_5_to_nt_6 AFTER INSERT ON nt_5 FOR EACH ROW + BEGIN + DECLARE in_stmt_id INTEGER; + SELECT max(stmt_id) INTO in_stmt_id FROM nt_6 WHERE trans_id= NEW.trans_id; + SELECT COALESCE(greatest(in_stmt_id + 1, NEW.stmt_id), 1) INTO in_stmt_id; + INSERT INTO nt_6(trans_id, stmt_id) VALUES (NEW.trans_id, in_stmt_id); + INSERT INTO nt_6(trans_id, stmt_id) VALUES (NEW.trans_id, in_stmt_id + 1); + END| + + DELIMITER ;| + + let $pos_trans_command= query_get_value("SHOW MASTER STATUS", Position, 1); + + let $trans_id= 7; + let $tb_id= 1; + let $stmt_id= 1; + let $commands= ''; + + SET @commands= ''; +} + +if (`SELECT HEX(@commands) = HEX('clean')`) +{ + connection master; + + DROP TABLE tt_1; + DROP TABLE tt_2; + DROP TABLE tt_3; + DROP TABLE tt_4; + DROP TABLE tt_5; + DROP TABLE tt_6; + + DROP TABLE nt_1; + DROP TABLE nt_2; + DROP TABLE nt_3; + DROP TABLE nt_4; + DROP TABLE nt_5; + DROP TABLE nt_6; + + DROP PROCEDURE pc_i_tt_5_suc; + DROP PROCEDURE pc_i_nt_5_suc; + DROP FUNCTION fc_i_tt_5_suc; + DROP FUNCTION fc_i_nt_5_suc; + + sync_slave_with_master; + + SET @commands= ''; +} + +while (`SELECT HEX(@commands) != HEX('')`) +{ + --disable_query_log + SET @command= SUBSTRING_INDEX(@commands, ' ', 1); + let $command= `SELECT @command`; + --eval SET @check_commands= '$commands' + if (`SELECT HEX(@check_commands) = HEX('''')`) + { + let $commands= `SELECT @commands`; + } + --echo -b-b-b-b-b-b-b-b-b-b-b- >> $command << -b-b-b-b-b-b-b-b-b-b-b- + let $pos_command= query_get_value("SHOW MASTER STATUS", Position, 1); + --enable_query_log + if (`SELECT HEX(@command) = HEX('B')`) + { + eval BEGIN; + } + if (`SELECT HEX(@command) = HEX('T')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO tt_1(trans_id, stmt_id) VALUES ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('T-trig')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO tt_5(trans_id, stmt_id) VALUES ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('T-func')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval SELECT fc_i_tt_5_suc ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('T-proc')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval CALL pc_i_tt_5_suc ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('eT')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from tt_1`; + let $old_stmt_id= `SELECT max(stmt_id) from tt_1 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO tt_1(trans_id, stmt_id) VALUES ($old_trans_id, $old_stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('Te')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from tt_1`; + let $old_stmt_id= `SELECT max(stmt_id) from tt_1 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO tt_1(trans_id, stmt_id) VALUES ($trans_id, $stmt_id), ($old_trans_id, $old_stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('Te-trig')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from tt_5`; + let $old_stmt_id= `SELECT max(stmt_id) from tt_5 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO tt_5(trans_id, stmt_id) VALUES ($trans_id, $stmt_id), ($old_trans_id, $old_stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('Te-func')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from tt_1`; + let $old_stmt_id= `SELECT max(stmt_id) from tt_1 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO tt_1(trans_id, stmt_id, info) VALUES ($trans_id, $stmt_id, ''), ($old_trans_id, $old_stmt_id, fc_i_tt_5_suc ($trans_id, $stmt_id)); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('N')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO nt_1(trans_id, stmt_id) VALUES ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('N-trig')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO nt_5(trans_id, stmt_id) VALUES ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('N-func')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval SELECT fc_i_nt_5_suc ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('N-proc')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval CALL pc_i_nt_5_suc ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('eN')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from nt_1`; + let $old_stmt_id= `SELECT max(stmt_id) from nt_1 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO nt_1(trans_id, stmt_id) VALUES ($old_trans_id, $old_stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('Ne')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from nt_1`; + let $old_stmt_id= `SELECT max(stmt_id) from nt_1 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO nt_1(trans_id, stmt_id) VALUES ($trans_id, $stmt_id), ($old_trans_id, $old_stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('Ne-trig')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from nt_5`; + let $old_stmt_id= `SELECT max(stmt_id) from nt_5 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO nt_5(trans_id, stmt_id) VALUES ($trans_id, $stmt_id), ($old_trans_id, $old_stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('Ne-func')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from nt_1`; + let $old_stmt_id= `SELECT max(stmt_id) from nt_1 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO nt_1(trans_id, stmt_id, info) VALUES ($trans_id, $stmt_id, ''), ($old_trans_id, $old_stmt_id, fc_i_nt_5_suc ($trans_id, $stmt_id)); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('tN')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO nt_1(trans_id, stmt_id, info) SELECT $trans_id, $stmt_id, COUNT(*) FROM tt_1; + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('tNe')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from nt_1`; + let $old_stmt_id= `SELECT max(stmt_id) from nt_1 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO nt_1(trans_id, stmt_id, info) SELECT $trans_id, $stmt_id, COUNT(*) FROM tt_1 UNION SELECT $old_trans_id, $old_stmt_id, COUNT(*) FROM tt_1; + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('nT')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO tt_1(trans_id, stmt_id, info) SELECT $trans_id, $stmt_id, COUNT(*) FROM nt_1; + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('nTe')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from tt_1`; + let $old_stmt_id= `SELECT max(stmt_id) from tt_1 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO tt_1(trans_id, stmt_id, info) SELECT $trans_id, $stmt_id, COUNT(*) FROM nt_1 UNION SELECT $old_trans_id, $old_stmt_id, COUNT(*) FROM nt_1; + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('NT')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval UPDATE nt_3, tt_3 SET nt_3.info= "new text $trans_id --> $stmt_id", tt_3.info= "new text $trans_id --> $stmt_id" where nt_3.trans_id = tt_3.trans_id and tt_3.trans_id = 1; + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('NT-trig')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO nt_4(trans_id, stmt_id) VALUES ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('NT-func')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO nt_5(trans_id, stmt_id, info) VALUES ($trans_id, $stmt_id, fc_i_tt_5_suc($trans_id, $stmt_id)); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('NeT-trig')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from nt_4`; + let $old_stmt_id= `SELECT max(stmt_id) from nt_4 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO nt_4(trans_id, stmt_id) VALUES ($trans_id, $stmt_id), ($old_trans_id, $old_stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('NeT-func')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from nt_5`; + let $old_stmt_id= `SELECT max(stmt_id) from nt_5 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO nt_5(trans_id, stmt_id, info) VALUES ($trans_id, $stmt_id, ''), ($old_trans_id, $old_stmt_id, fc_i_tt_5_suc ($trans_id, $stmt_id)); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('TN')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval UPDATE tt_4, nt_4 SET tt_4.info= "new text $trans_id --> $stmt_id", nt_4.info= "new text $trans_id --> $stmt_id" where nt_4.trans_id = tt_4.trans_id and tt_4.trans_id = 1; + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('TN-trig')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO tt_3(trans_id, stmt_id) VALUES ($trans_id, $stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('TN-func')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + eval INSERT INTO tt_5(trans_id, stmt_id, info) VALUES ($trans_id, $stmt_id, fc_i_nt_5_suc($trans_id, $stmt_id)); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('TeN-trig')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from tt_3`; + let $old_stmt_id= `SELECT max(stmt_id) from tt_3 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO tt_3(trans_id, stmt_id) VALUES ($trans_id, $stmt_id), ($old_trans_id, $old_stmt_id); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('TeN-func')`) + { + #--echo DEBUG-- (trans_id, stmt_id) --> ($trans_id, $stmt_id) + let $old_trans_id= `SELECT max(trans_id) from tt_5`; + let $old_stmt_id= `SELECT max(stmt_id) from tt_5 where trans_id= $old_trans_id`; + --error ER_DUP_ENTRY, ER_DUP_KEY + eval INSERT INTO tt_5(trans_id, stmt_id, info) VALUES ($trans_id, $stmt_id, ''), ($old_trans_id, $old_stmt_id, fc_i_nt_5_suc ($trans_id, $stmt_id)); + inc $stmt_id; + } + if (`SELECT HEX(@command) = HEX('CS-T->T')`) + { + --eval CREATE TABLE tt_xx_$tb_id (PRIMARY KEY(trans_id, stmt_id)) engine=$engine_type SELECT * FROM tt_1; + } + if (`SELECT HEX(@command) = HEX('CS-N->N')`) + { + --eval CREATE TABLE nt_xx_$tb_id (PRIMARY KEY(trans_id, stmt_id)) engine=MyIsam SELECT * FROM nt_1; + } + if (`SELECT HEX(@command) = HEX('CS-T->N')`) + { + --eval CREATE TABLE tt_xx_$tb_id (PRIMARY KEY(trans_id, stmt_id)) engine=$engine_type SELECT * FROM nt_1; + } + if (`SELECT HEX(@command) = HEX('CS-N->T')`) + { + --eval CREATE TABLE nt_xx_$tb_id (PRIMARY KEY(trans_id, stmt_id)) engine=MyIsam SELECT * FROM tt_1; + } + if (`SELECT HEX(@command) = HEX('CSe-T->T')`) + { + --error ER_DUP_ENTRY, ER_DUP_KEY + --eval CREATE TABLE tt_xx_$tb_id (PRIMARY KEY (stmt_id)) engine=$engine_type SELECT stmt_id FROM tt_1; + } + if (`SELECT HEX(@command) = HEX('CSe-N->N')`) + { + --error ER_DUP_ENTRY, ER_DUP_KEY + --eval CREATE TABLE nt_xx_$tb_id (PRIMARY KEY (stmt_id)) engine=MyIsam SELECT stmt_id FROM nt_1; + } + if (`SELECT HEX(@command) = HEX('CSe-T->N')`) + { + --error ER_DUP_ENTRY, ER_DUP_KEY + --eval CREATE TABLE tt_xx_$tb_id (PRIMARY KEY (stmt_id)) engine=$engine_type SELECT stmt_id FROM nt_1; + } + if (`SELECT HEX(@command) = HEX('CSe-N->T')`) + { + --error ER_DUP_ENTRY, ER_DUP_KEY + --eval CREATE TABLE nt_xx_$tb_id (PRIMARY KEY (stmt_id)) engine=MyIsam SELECT stmt_id FROM tt_1; + } + if (`SELECT HEX(@command) = HEX('CT')`) + { + --eval CREATE TEMPORARY TABLE tt_xx_$tb_id (a int) engine=$engine_type; + } + if (`SELECT HEX(@command) = HEX('IS-T<-N')`) + { + --eval INSERT INTO tt_xx_$tb_id(trans_id, stmt_id, info) SELECT trans_id, stmt_id, USER() FROM nt_1; + } + if (`SELECT HEX(@command) = HEX('ISe-T<-N')`) + { + --error ER_DUP_ENTRY, ER_DUP_KEY + --eval INSERT INTO tt_xx_$tb_id(trans_id, stmt_id, info) SELECT trans_id, trans_id, USER() FROM nt_1; + } + if (`SELECT HEX(@command) = HEX('IS-N<-T')`) + { + --eval INSERT INTO nt_xx_$tb_id(trans_id, stmt_id, info) SELECT trans_id, stmt_id, USER() FROM tt_1; + } + if (`SELECT HEX(@command) = HEX('ISe-N<-T')`) + { + --error ER_DUP_ENTRY, ER_DUP_KEY + --eval INSERT INTO nt_xx_$tb_id(trans_id, stmt_id, info) SELECT trans_id, trans_id, USER() FROM tt_1; + } + if (`SELECT HEX(@command) = HEX('IS-T<-T')`) + { + --eval INSERT INTO tt_xx_$tb_id(trans_id, stmt_id, info) SELECT trans_id, stmt_id, USER() FROM tt_1; + } + if (`SELECT HEX(@command) = HEX('ISe-T<-T')`) + { + --error ER_DUP_ENTRY, ER_DUP_KEY + --eval INSERT INTO tt_xx_$tb_id(trans_id, stmt_id, info) SELECT trans_id, trans_id, USER() FROM tt_1; + } + if (`SELECT HEX(@command) = HEX('IS-N<-N')`) + { + --eval INSERT INTO nt_xx_$tb_id(trans_id, stmt_id, info) SELECT trans_id, stmt_id, USER() FROM nt_1; + } + if (`SELECT HEX(@command) = HEX('ISe-N<-N')`) + { + --error ER_DUP_ENTRY, ER_DUP_KEY + --eval INSERT INTO nt_xx_$tb_id(trans_id, stmt_id, info) SELECT trans_id, trans_id, USER() FROM nt_1; + } + if (`SELECT HEX(@command) = HEX('trunc-CS-T')`) + { + eval TRUNCATE TABLE tt_xx_$tb_id; + } + if (`SELECT HEX(@command) = HEX('trunc-CS-N')`) + { + eval TRUNCATE TABLE nt_xx_$tb_id; + } + if (`SELECT HEX(@command) = HEX('trunc-CT')`) + { + eval TRUNCATE TABLE tt_xx_$tb_id; + } + if (`SELECT HEX(@command) = HEX('drop-CS')`) + { + --disable_warnings + eval DROP TABLE IF EXISTS tt_xx_$tb_id, nt_xx_$tb_id; + inc $tb_id; + --enable_warnings + } + if (`SELECT HEX(@command) = HEX('drop-CT')`) + { + --disable_warnings + eval DROP TEMPORARY TABLE IF EXISTS tt_xx_$tb_id; + inc $tb_id; + --enable_warnings + } + if (`SELECT HEX(@command) = HEX('C')`) + { + --error 0, ER_GET_ERRMSG + eval COMMIT; + } + if (`SELECT HEX(@command) = HEX('R')`) + { + --error 0, ER_GET_ERRMSG + eval ROLLBACK; + } + if (`SELECT HEX(@command) = HEX('S1')`) + { + eval SAVEPOINT s1; + } + if (`SELECT HEX(@command) = HEX('R1')`) + { + eval ROLLBACK TO s1; + } + --disable_query_log + SET @commands= LTRIM(SUBSTRING(@commands, LENGTH(@command) + 1)); + inc $stmt_id; + + let $binlog_start= $pos_command; + --source include/show_binlog_events.inc + --echo -e-e-e-e-e-e-e-e-e-e-e- >> $command << -e-e-e-e-e-e-e-e-e-e-e- + if (`SELECT HEX(@commands) = HEX('')`) + { + let $binlog_start= $pos_trans_command; + --echo -b-b-b-b-b-b-b-b-b-b-b- >> $commands << -b-b-b-b-b-b-b-b-b-b-b- + --source include/show_binlog_events.inc + --echo -e-e-e-e-e-e-e-e-e-e-e- >> $commands << -e-e-e-e-e-e-e-e-e-e-e- + --echo + let $pos_trans_command= query_get_value("SHOW MASTER STATUS", Position, 1); + let $stmt_id= 1; + inc $trans_id; + let $commands= ''; + } +} diff --git a/mysql-test/extra/rpl_tests/rpl_mixing_engines.test b/mysql-test/extra/rpl_tests/rpl_mixing_engines.test index cbda85527f1..b8b2b1bc01b 100644 --- a/mysql-test/extra/rpl_tests/rpl_mixing_engines.test +++ b/mysql-test/extra/rpl_tests/rpl_mixing_engines.test @@ -1,710 +1,1776 @@ -################################################################################### -# This test checks if transactions that mixes transactional and non-transactional -# tables are correctly handled in statement mode. In an nutshell, we have what -# follows: +################################################################################ +# - Introduction +# This checks if transactions that mixes transactional and non-transactional +# are correctly handled. There are several types of statements that require +# attention because of their special behavior in transactions: # -# 1) "B T T C" generates in binlog the "B T T C" entries. +# * Non-transactional updates that take place inside a transaction present +# problems for logging because (1) they are visible to other clients before +# the transaction is committed, and (2) they are not rolled back even if the +# transaction is rolled back. It is not always possible to log correctly in +# statement format when both transactional and non-transactional tables are +# used in the same transaction. # -# 2) "B T T R" generates in binlog an "empty" entry. +# * Statements that do an implicit commit (i.e., most but not all DDL, and +# some utility commands) are logged specially due to unspecified requirements by +# NDB. +# +# * Statements that update temporary tables need special treatment since they +# are not logged in row format. # -# 3) "B T N C" generates in binlog the "B T N C" entries. +# - Definitions # -# 4) "B T N R" generates in binlog the "B T N R" entries. +# To reason about logging different table types, we make some preliminary +# definitions. # -# 5) "T" generates in binlog the "B T C" entry. +# * A table that has a transactional engine is called a T-table. # -# 6) "N" generates in binlog the "N" entry. +# * A table that has a non-transactional engine is called an N-table. # -# 7) "M" generates in binglog the "B M C" entries. +# * A statement makes an N-write if it makes any type of change to the server +# state that will not be changed by a ROLLBACK. # -# 8) "B N N T C" generates in binglog the "N N B T C" entries. +# * Events are either appended to the Transaction Cache (TC) or to the +# Statement Cache (SC) or written directly to the binlog. # -# 9) "B N N T R" generates in binlog the "N N B T R" entries. +# - Preliminary Rules # -# 10) "B N N C" generates in binglog the "N N" entries. +# The following preliminary rules are actually consequences of the principle +# that statements shall be correctly logged when binlog_format=MIXED or ROW. +# They also apply when binlog_format=STATEMENT: this makes statement format +# work in many practical cases. # -# 11) "B N N R" generates in binlog the "N N" entries. +# * (Causality) If statement A is executed before statement B, and B is logged +# in statement format, and B reads tables that A may modifies, then B shall be +# logged after A. # -# 12) "B M T C" generates in the binlog the "B M T C" entries. +# * (Durability) Events shall be written to the binary log at the moment they +# take effect. In particular, changes to N-tables shall be written to the +# binary log when they have been executed, and changes to T-tables shall be +# written to the binary log on commit. If --sync-binlog has been specified, +# then it suffices that events are be written to the binary log at the next +# synchronization point. # -# 13) "B M T R" generates in the binlog the "B M T R" entries. -################################################################################### - ---echo ################################################################################### ---echo # CONFIGURATION ---echo ################################################################################### -connection master; +# * (causality-precedence) If causality and durability cannot both be +# fulfilled, then casuality is considered more important. +# +# - Rules for non-committing statements, except CREATE TEMPORARY TABLE...SELECT +# +# The preliminary rules above, together with the principles for logging format +# , have been used to construct the following rules. +# +# CALL statements are unrolled, so that each statement executed by the stored +# procedure is logged separately. (If a stored procedure A invokes a stored +# procedure B, then B is unrolled recursively). In the following, we assume +# that unrolling has already been done, and the word "statement" refers to a +# non-CALL top-level statement or a non-CALL sub-statement. +# +# Let S be a logged statement that does not have an implicit commit, except +# CREATE TEMPORARY TABLE...SELECT (This includes all "pure DML": INSERT, +# UPDATE, DELETE, REPLACE, TRUNCATE, SELECT, DO, CALL, EXECUTE, LOAD DATA +# INFILE, and BINLOG. It also includes CREATE TEMPORARY TABLE without SELECT, +# and DROP TEMPORARY TABLE. CREATE TEMPORARY TABLE...SELECT is handled in the +# next subsection). +# +# Before executing S, determine unsafeness: +# +# * If S either makes N-writes or reads from an N-table, and either S or a +# previous statement in the same transaction reads or writes to a T-table, +# then S is marked as unsafe. +# +# When logging S, determine where to log it by applying the following rules in +# order: +# +# * If S is to be logged in statement format (i.e., if one of the following +# holds: (1) STATEMENT; (2) MIXED and S is safe; (3) S is of DDL type, i.e., +# CREATE TEMPORARY TABLE): +# 1. If S produces an error and does not do any N-write, do not log. +# 2. Otherwise, if either S or any previous statement in the same +# transaction reads or writes in any T-tables, log to TC. +# 3. Otherwise, log to SC. +# +# * If S is to be logged in row format (i.e., if S is DML and one of the +# following holds: (1) ROW; (2) MIXED and S is unsafe): +# 1. Do not log row events that write to temporary tables. +# 2. Log row events that write to non-temporary N-tables to SC. +# 3. Log row events that write to non-temporary T-tables to TC, except +# rows that are rolled back due to an error. (Note: if there is an error, +# rows written to a T-table are kept if there are subsequent rows written +# to an N-table.) +# +# * At the end of S, write BEGIN + SC + COMMIT to the binlog and clear the +# SC. +# +# At end of transaction: +# +# * At COMMIT or implicit commit, where all XA tables in the transaction +# succeed in the "prepare" phase: +# 1. If the TC is non-empty, write BEGIN + TC + COMMIT to the binlog. +# 2. If the TC is empty, do nothing. +# +# * At ROLLBACK; or at COMMIT or implicit commit where some XA table fails +# in the "prepare" phase: +# 1. If the TC contains any N-write, write BEGIN + TC + ROLLBACK to the +# binlog. +# 2. If the TC does not contain any N-write, do nothing. +# +# * At ROLLBACK TO SAVEPOINT: +# 1. If the TC contains any N-write after the savepoint, write ROLLBACK TO +# SAVEPOINT to the TC. +# 2. Otherwise, clear the part of the TC that starts at the savepoint and +# extends to the end of the TC. (Bug#47327 breaks this rule) +# +# * Clear the TC at the end of the transaction. +# +# - Rules for CREATE [TEMPORARY] TABLE...SELECT +# +# First, unsafeness is determined as above (R-unsafe-transaction). Then the +# logging format is decided. Then the following rules apply. +# +# * If logging in statement format (i.e., one of the following holds: (1) +# STATEMENT; (2) MIXED and statement is safe): +# 1. If there is an error, do not write anything. +# 2. If there is no error and the TEMPORARY keyword is used, write the +# entire CREATE...SELECT statement to the TC. +# 3. If there is no error and the TEMPORARY keyword is not used, write the +# entire CREATE...SELECT directly to the binlog. +# +# * If logging in row format (i.e., one of the following holds: (1) ROW; (2) +# MIXED and statement is unsafe): +# 1. If the TEMPORARY keyword is used, do not write anything. +# 2. If the TEMPORARY keyword is not used, write CREATE TABLE (without +# select) + BEGIN + row events + COMMIT to the TC. If there is an error, +# clear the TC; otherwise flush the TC to the binlog at the end of the +# statement and then clear the TC. (Note: currently Bug#47899 breaks this +# rule) +# +# - Rules for committing statements, except CREATE [TEMPORARY] TABLE...SELECT +# +# * All other statements that have a pre-commit are written directly to the +# binlog. (Note: this is semantically equivalent to writing it to the SC and +# flushing the SC. However, due to requirements by NDB (which have not been +# clarified), we write directly to the binlog.) +# +# We use the include file rpl_mixing_engines.inc to generate sql commands from a +# format string. The format string consists of a sequence of 'codes' separated +# by spaces. The following codes exist: +# +# - Define the scope of a transaction: +# B - Begin. +# C - Commit. +# R - Rollback. +# S1 - Savepoint. +# R1 - Rollback to S1. +# +# - Change only T-Tables: +# T - Updates a T-Table. +# T-trig - Updates T-Tables through a trigger. +# T-func - Updates T-Tables through a function. +# T-proc - Updates T-Tables through a procedure. +# eT - Fails while updating the first tuple in a T-Table. +# Te - Fails while updating an n-tuple (n > 1) in a T-Table. +# Te-trig - Fails while updating an n-tuple (n > 1) in a T-Table. +# Te-func - Fails while updating an n-tuple (n > 1) in a T-Table. +# +# - Change only N-Tables +# N - Updates a N-Table. +# N-trig - Updates N-Tables through a trigger. +# N-func - Updates N-Tables through a function. +# N-proc - Updates N-Tables through a procedure. +# eN - Fails while updating the first tuple in a N-Table. +# Ne - Fails while updating an n-tuple (n > 1) in a N-Table. +# Ne-trig - Fails while updating an n-tuple (n > 1) in a N-Table. +# Ne-func - Fails while updating an n-tuple (n > 1) in a N-Table. +# +# - Read T-table and write N-table: +# tN - Updates a N-Table +# tNe - Fails while updating an n-tuple (n > 1) in a N-Table. +# +# - Read N-table and write T-table: +# nT - Updates a T-Table. +# nTe - Fails while updating an n-tuple (n > 1) in a T-Table. +# +# - Update both types of tables. First a N-Table and the a T-Table: +# NT - Upates both types of tables through an update statement. +# NT-trig - Updates both types of tables through a trigger. +# NT-func - Updates both types of tables through a procedure. +# NeT-trig - Fails while updating an n-tuple (n > 1) in a T-Table. +# NeT-func - Fails while updating an n-tuple (n > 1) in a T-Table. +# +# - Update both types of tables. First a T-Table and the a N-Table: +# TN - Upates both types of tables through an update statement. +# TN-trig - Updates both types of tables through a trigger. +# TN-func - Updates both types of tables through a procedure. +# TeN-trig - Fails while updating an n-tuple (n > 1) in a N-Table. +# TeN-func - Fails while updating an n-tuple (n > 1) in a N-Table. +# +# - This is CREATE...SELECT: +# CS-T->T - Creates a T-table selecting from a T-table. +# CS-N->N - Creates a N-table selecting from a N-table. +# CS-T->N - Creates a T-table selecting form a N-table. +# CS-N->T - Creates a N-table selecting from a T-table. +# CSe-T->T - Fails while creating a T-table selecting from a T-table. +# CSe-N->N - Fails while creating a N-table selecting from a N-table. +# CSe-T->N - Fails while creating a T-table selecting from a a N-table. +# CSe-N->T - Fails while creating a N-table selecting from a T-table. +# drop-CS - Drops any of the tables previously created. +# trunc-CS-T - Truncates a T-table previously created. +# trunc-CS-N - Truncates a N-table previously created. +# CT - Creates a temporary T-table. +# drop-CT - Drops a temporary T-table. +# +# - This is INSERT...SELECT: +# IS-T<-T - Inserts data from a T-table into a T-table. +# IS-T<-N - Inserts data from a N-table into a T-table. +# IS-N<-T - Inserts data from a T-table into a N-table. +# IS-N<-N - Inserts data from a N-table into a N-table. +# ISe-T<-T - Fails while inserting data from a T-table into a T-table. +# ISe-T<-N - Fails while inserting data from a N-table into a T-table. +# ISe-N<-T - Fails while inserting data from a T-table into a N-table. +# ISe-N<-N - Fails while inserting data from a N-table into a N-table. +# +# For the CREATE...SELECT and INSERT...SELECT, the table names are defined based +# on the variable $tb_id which is automatically incremented after each drop. +# This indirectly means that two tables cannot co-exist unless we manually keep +# the variable $tb_id. +# +# The format of the entries in the binlog depends on the mode and on the type +# statements: S - statement and R - row. And when it is clear from the context +# which statement is referred to, we sometimes use "M" to denote a "Mixed" +# statement, i.e., one that accesses both a T-table and an N-table. +# +# For further details, please, read WL#2687 and WL#5072. +################################################################################ +--echo ######################################################################### +--echo # CONFIGURATION +--echo ######################################################################### -SET SQL_LOG_BIN=0; -CREATE TABLE nt_1 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM; -CREATE TABLE nt_2 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM; -CREATE TABLE nt_3 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM; -CREATE TABLE nt_4 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM; -CREATE TABLE tt_1 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb; -CREATE TABLE tt_2 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb; -CREATE TABLE tt_3 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb; -CREATE TABLE tt_4 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb; -SET SQL_LOG_BIN=1; - -connection slave; - -SET SQL_LOG_BIN=0; -CREATE TABLE nt_1 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM; -CREATE TABLE nt_2 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM; -CREATE TABLE nt_3 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM; -CREATE TABLE nt_4 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM; -CREATE TABLE tt_1 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb; -CREATE TABLE tt_2 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb; -CREATE TABLE tt_3 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb; -CREATE TABLE tt_4 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb; -SET SQL_LOG_BIN=1; +call mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT"); +SET @commands= 'configure'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +--echo ######################################################################### +--echo # 1 - MIXING TRANSACTIONAL and NON-TRANSACTIONAL TABLES +--echo ######################################################################### connection master; -DELIMITER |; +--echo +--echo +--echo +--echo +--echo # +--echo #1) Generates in the binlog what follows: +--echo # --> STMT "B T C" entries, format S. +--echo # --> ROW "B T C" entries, format R. +--echo # --> MIXED "B T C" entries, format S. +--echo # +SET @commands= 'T'; +--source extra/rpl_tests/rpl_mixing_engines.inc -CREATE FUNCTION f1 () RETURNS VARCHAR(64) -BEGIN - RETURN "Testing..."; -END| +SET @commands= 'T-trig'; +--source extra/rpl_tests/rpl_mixing_engines.inc -CREATE FUNCTION f2 () RETURNS VARCHAR(64) -BEGIN - RETURN f1(); -END| +SET @commands= 'T-func'; +--source extra/rpl_tests/rpl_mixing_engines.inc -CREATE PROCEDURE pc_i_tt_3 (IN x INT, IN y VARCHAR(64)) -BEGIN - INSERT INTO tt_3 VALUES (y,x,x); -END| +SET @commands= 'T-proc'; +--source extra/rpl_tests/rpl_mixing_engines.inc -CREATE TRIGGER tr_i_tt_3_to_nt_3 BEFORE INSERT ON tt_3 FOR EACH ROW -BEGIN - INSERT INTO nt_3 VALUES (NEW.a, NEW.b, NEW.c); -END| -CREATE TRIGGER tr_i_nt_4_to_tt_4 BEFORE INSERT ON nt_4 FOR EACH ROW -BEGIN - INSERT INTO tt_4 VALUES (NEW.a, NEW.b, NEW.c); -END| +--echo +--echo +--echo +--echo +--echo # +--echo #1.e) Generates in the binlog what follows: +--echo # --> STMT empty. +--echo # --> ROW empty. +--echo # --> MIXED empty. +--echo # +SET @commands= 'eT'; +--source extra/rpl_tests/rpl_mixing_engines.inc -DELIMITER ;| +SET @commands= 'Te'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---echo ################################################################################### ---echo # MIXING TRANSACTIONAL and NON-TRANSACTIONAL TABLES ---echo ################################################################################### -connection master; +SET @commands= 'Te-trig'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'Te-func'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +--echo +--echo +--echo +--echo +--echo # +--echo #2) Generates in the binlog what follows: +--echo # --> STMT "B N C" entry, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format S. +--echo # +SET @commands= 'N'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'N-trig'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'N-func'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'N-proc'; +--source extra/rpl_tests/rpl_mixing_engines.inc + + +--echo +--echo +--echo +--echo +--echo # +--echo #2.e) Generates in the binlog what follows if a N-table is changed: +--echo # --> STMT "B N C" entry, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format S. +--echo # +SET @commands= 'eN'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'Ne'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'Ne-trig'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'Ne-func'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +--echo +--echo +--echo +--echo +--echo # +--echo #3) Generates in the binlog what follows: +--echo # --> STMT "B M C" entry if only N-Table is changed, format S. +--echo # --> STMT "B M C" entries, format S. +--echo # --> ROW "B N T B T C" entries, format R. +--echo # --> MIXED "B N T B T C" entries, format R. +--echo # +SET @commands= 'tN'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'nT'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'NT'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'NT-trig'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'NT-func'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'TN'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'TN-trig'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'TN-func'; +--source extra/rpl_tests/rpl_mixing_engines.inc + + +--echo +--echo +--echo +--echo +--echo # +--echo #3.e) Generates in the binlog what follows: +--echo # --> STMT "B M C" entry if only N-Table is changed, format S. +--echo # --> STMT "B M R" entries, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format R. +SET @commands= 'tNe'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'nTe'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'NeT-trig'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'NeT-func'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'TeN-trig'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'TeN-func'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +--echo +--echo +--echo +--echo +--echo # +--echo #4) Generates in the binlog what follows: +--echo # --> STMT "B T T C" entries, format S. +--echo # --> ROW "B T T C" entries, format R. +--echo # --> MIXED "B T T C" entries, format S +--echo # +SET @commands= 'B T T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T T-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T T-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T T-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig T-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig T-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig T-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +SET @commands= 'B T-func T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func T-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func T-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func T-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc T-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc T-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc T-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + + +--echo +--echo +--echo +--echo --echo # ---echo #1) "B T T C" generates in binlog the "B T T C" entries. +--echo #4.e) Generates in the binlog what follows: +--echo # --> STMT "B T C" entries, format S. +--echo # --> ROW "B T C" entries, format R. +--echo # --> MIXED "B T C" entries, format S. --echo # -BEGIN; -INSERT INTO tt_1 VALUES ("new text 4", 4, "new text 4"); -INSERT INTO tt_2 VALUES ("new text 4", 4, "new text 4"); -COMMIT; +SET @commands= 'B T eT C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T Te C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T Te-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T Te-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B eT T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Te T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Te-trig T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Te-func T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #1.e) "B T T C" with error in T generates in binlog the "B T T C" entries. +--echo #5) Generates in the binlog what follows: +--echo # --> STMT empty. +--echo # --> ROW empty. +--echo # --> MIXED empty. --echo # -INSERT INTO tt_1 VALUES ("new text -2", -2, "new text -2"); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES ("new text -1", -1, "new text -1"), ("new text -2", -2, "new text -2"); -INSERT INTO tt_2 VALUES ("new text -3", -3, "new text -3"); -COMMIT; +SET @commands= 'B T T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T T-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T T-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T T-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig T-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_2 VALUES ("new text -5", -5, "new text -5"); ---error ER_DUP_ENTRY -INSERT INTO tt_2 VALUES ("new text -4", -4, "new text -4"), ("new text -5", -5, "new text -5"); -COMMIT; +SET @commands= 'B T-trig T-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig T-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func T-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func T-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func T-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc T-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc T-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc T-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #2) "B T T R" generates in binlog an "empty" entry. +--echo #5.e) Generates in the binlog what follows: +--echo # --> STMT empty. +--echo # --> ROW empty. +--echo # --> MIXED empty. --echo # -BEGIN; -INSERT INTO tt_1 VALUES ("new text 5", 5, "new text 5"); -INSERT INTO tt_2 VALUES ("new text 5", 5, "new text 5"); -ROLLBACK; +SET @commands= 'B T eT R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T Te R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T Te-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T Te-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B eT T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Te T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Te-trig T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Te-func T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #2.e) "B T T R" with error in T generates in binlog an "empty" entry. +--echo #6) Generates in the binlog what follows: +--echo # --> STMT "B N C B N C" entries, format S. +--echo # --> ROW "B N C B N C" entries, format R. +--echo # --> MIXED "B N C B N C" entries, format S. --echo # -INSERT INTO tt_1 VALUES ("new text -7", -7, "new text -7"); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES ("new text -6", -6, "new text -6"), ("new text -7", -7, "new text -7"); -INSERT INTO tt_2 VALUES ("new text -8", -8, "new text -8"); -ROLLBACK; +SET @commands= 'B N N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N N-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N N-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N N-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig N-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig N-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig N-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func N-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_2 VALUES ("new text -10", -10, "new text -10"); ---error ER_DUP_ENTRY -INSERT INTO tt_2 VALUES ("new text -9", -9, "new text -9"), ("new text -10", -10, "new text -10"); -ROLLBACK; +SET @commands= 'B N-func N-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func N-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc N-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc N-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc N-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #3) "B T N C" generates in binlog the "B T N C" entries. +--echo #6.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B N C B N C" entries, format S. +--echo # --> ROW "B N C B N C" entries, format R. +--echo # --> MIXED "B N C B N C" entries, format S. --echo # -BEGIN; -INSERT INTO tt_1 VALUES ("new text 6", 6, "new text 6"); -INSERT INTO nt_1 VALUES ("new text 6", 6, "new text 6"); -COMMIT; +SET @commands= 'B N eN C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N Ne C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N Ne-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N Ne-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B eN N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Ne N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Ne-trig N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Ne-func N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #3.e) "B T N C" with error in either T or N generates in binlog the "B T N C" entries. +--echo #7) Generates in the binlog what follows: +--echo # --> STMT "B N C B N C" entries, format S. +--echo # --> ROW "B N C B N C" entries, format R. +--echo # --> MIXED "B N C B N C" entries, format S. --echo # -INSERT INTO tt_1 VALUES ("new text -12", -12, "new text -12"); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES ("new text -11", -11, "new text -11"), ("new text -12", -12, "new text -12"); -INSERT INTO nt_1 VALUES ("new text -13", -13, "new text -13"); -COMMIT; +SET @commands= 'B N N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_1 VALUES ("new text -14", -14, "new text -14"); -INSERT INTO nt_1 VALUES ("new text -16", -16, "new text -16"); ---error ER_DUP_ENTRY -INSERT INTO nt_1 VALUES ("new text -15", -15, "new text -15"), ("new text -16", -16, "new text -16"); -COMMIT; +SET @commands= 'B N N-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N N-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N N-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig N-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig N-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig N-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func N-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func N-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func N-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc N-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc N-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc N-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #4) "B T N R" generates in binlog the "B T N R" entries. +--echo #7.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B N C B N C" entries, format S. +--echo # --> ROW "B N C B N C" entries, format R. +--echo # --> MIXED "B N C B N C" entries, format S. --echo # -BEGIN; -INSERT INTO tt_1 VALUES ("new text 7", 7, "new text 7"); -INSERT INTO nt_1 VALUES ("new text 7", 7, "new text 7"); -ROLLBACK; +SET @commands= 'B N eN R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N Ne R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N Ne-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N Ne-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B eN N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Ne N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Ne-trig N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Ne-func N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #4.e) "B T N R" with error in either T or N generates in binlog the "B T N R" entries. +--echo #8) Generates in the binlog what follows: +--echo # --> STMT "B T N C" entries, format S. +--echo # --> ROW "B N C B T C" entries, format R. +--echo # --> MIXED "B N C B T C" entries, format R in N and S in T. --echo # -INSERT INTO tt_1 VALUES ("new text -17", -17, "new text -17"); -BEGIN; ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES ("new text -16", -16, "new text -16"), ("new text -17", -17, "new text -17"); -INSERT INTO nt_1 VALUES ("new text -18", -18, "new text -18"); -ROLLBACK; +SET @commands= 'B T N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T N-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T N-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T N-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_1 VALUES ("new text -19", -19, "new text -19"); -INSERT INTO nt_1 VALUES ("new text -21", -21, "new text -21"); ---error ER_DUP_ENTRY -INSERT INTO nt_1 VALUES ("new text -20", -20, "new text -20"), ("new text -21", -21, "new text -21"); -ROLLBACK; +SET @commands= 'B T-trig N-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig N-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig N-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func N-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func N-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func N-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc N-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc N-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc N-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #5) "T" generates in binlog the "B T C" entry. +--echo #8.e) Generates in the binlog what follows if T-* fails: +--echo # --> STMT "B N C" entry, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format R. +--echo # Otherwise, what follows if N-* fails and a N-Table is changed: +--echo # --> STMT "B T N C" entries, format S. +--echo # --> ROW "B N C B T C" entries, format R. +--echo # --> MIXED "B N C B T C" entries, format R in N and S in T. --echo # -INSERT INTO tt_1 VALUES ("new text 8", 8, "new text 8"); +SET @commands= 'B eT N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Te N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T eN C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T Ne C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #5.e) "T" with error in T generates in binlog an "empty" entry. +--echo #9) Generates in the binlog what follows: +--echo # --> STMT "B T N R" entries, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format R. --echo # -INSERT INTO tt_1 VALUES ("new text -1", -1, "new text -1"); ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES ("new text -1", -1, "new text -1"), ("new text -22", -22, "new text -22"); ---error ER_DUP_ENTRY -INSERT INTO tt_1 VALUES ("new text -23", -23, "new text -23"), ("new text -1", -1, "new text -1"); +SET @commands= 'B T N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T N-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T N-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T N-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig N-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig N-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-trig N-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func N-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func N-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-func N-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc N-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc N-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T-proc N-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #6) "N" generates in binlog the "N" entry. +--echo #9.e) Generates in the binlog what follows if T* fails: +--echo # --> STMT "B N C" entry, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format R. +--echo # Otherwise, what follows if N* fails and a N-Table is changed: +--echo # --> STMT "B T N R" entries, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format R. --echo # -INSERT INTO nt_1 VALUES ("new text 9", 9, "new text 9"); +SET @commands= 'B eT N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B Te N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T eN R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T Ne R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #6.e) "N" with error in N generates in binlog an empty entry if the error ---echo # happens in the first tuple. Otherwise, generates the "N" entry and ---echo # the error is appended. +--echo #10) Generates in the binlog: +--echo # --> STMT "B N C B T C" entries, format S. +--echo # --> ROW "B N C B T C" entries, format R. +--echo # --> MIXED "B N C B T C" entries, format S. --echo # -INSERT INTO nt_1 VALUES ("new text -1", -1, "new text -1"); ---error ER_DUP_ENTRY -INSERT INTO nt_1 VALUES ("new text -1", -1, "new text -1"); ---error ER_DUP_ENTRY -INSERT INTO nt_1 VALUES ("new text -24", -24, "new text -24"), ("new text -1", -1, "new text -1"); +SET @commands= 'B N T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N T-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N T-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N T-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig T-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig T-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-trig T-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func T-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func T-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-func T-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc T-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc T-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N-proc T-proc C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #7) "M" generates in binglog the "B M C" entries. +--echo #11) Generates in the binlog what follows: +--echo # --> STMT "B N C" entries, format S. +--echo # --> ROW "B N C" entries, format R. +--echo # --> MIXED "B N C" entries, format S. --echo # -DELETE FROM nt_1; +SET @commands= 'B N T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO nt_1 SELECT * FROM tt_1; +SET @commands= 'B N T-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -DELETE FROM tt_1; +SET @commands= 'B N T-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO tt_1 SELECT * FROM nt_1; +SET @commands= 'B N T-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO tt_3 VALUES ("new text 000", 000, ''); +SET @commands= 'B N-trig T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO tt_3 VALUES("new text 100", 100, f1()); +SET @commands= 'B N-trig T-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO nt_4 VALUES("new text 100", 100, f1()); +SET @commands= 'B N-trig T-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO tt_3 VALUES("new text 200", 200, f2()); +SET @commands= 'B N-trig T-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO nt_4 VALUES ("new text 300", 300, ''); +SET @commands= 'B N-func T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO nt_4 VALUES ("new text 400", 400, f1()); +SET @commands= 'B N-func T-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO nt_4 VALUES ("new text 500", 500, f2()); +SET @commands= 'B N-func T-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -CALL pc_i_tt_3(600, "Testing..."); +SET @commands= 'B N-func T-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a= "new text 1", nt_4.a= "new text 1", tt_3.a= "new text 1", tt_4.a= "new text 1" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; +SET @commands= 'B N-proc T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a= "new text 2", tt_4.a= "new text 2", nt_3.a= "new text 2", nt_4.a = "new text 2" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; +SET @commands= 'B N-proc T-proc R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 3", nt_3.a= "new text 3", nt_4.a= "new text 3", tt_4.a = "new text 3" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; +SET @commands= 'B N-proc T-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 4", nt_3.a= "new text 4", nt_4.a= "new text 4", tt_4.a = "new text 4" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; +SET @commands= 'B N-proc T-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #7.e) "M" with error in M generates in binglog the "B M R" entries. +--echo #12) Generates in the binlog what follows: +--echo # --> STMT "B M C B T C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B M T C" entries, format S. +--echo # --> ROW "B N C B T T C" entries, format R. +--echo # --> MIXED "B N C B T T C" entries, format R in N/T and format S in T. +--echo # +SET @commands= 'B tN T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B nT T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NT T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NT-trig T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NT-func T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TN T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TN-trig T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TN-func T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + + +--echo +--echo +--echo +--echo --echo # +--echo #12.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B M C B T C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B M T C" entries, format S. +--echo # --> ROW "B N C B T T C" entries, format R. +--echo # --> MIXED "B N C B T T C" entries, format R in N/T and format S in T. + --echo # +SET @commands= 'B tNe T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B nTe T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NeT-trig T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO nt_3 VALUES ("new text -26", -26, ''); -SELECT * FROM tt_3; ---error ER_DUP_ENTRY -INSERT INTO tt_3 VALUES ("new text -25", -25, ''), ("new text -26", -26, ''); -SELECT * FROM tt_3; +SET @commands= 'B NeT-func T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -INSERT INTO tt_4 VALUES ("new text -26", -26, ''); -SELECT * FROM nt_4; ---error ER_DUP_ENTRY -INSERT INTO nt_4 VALUES ("new text -25", -25, ''), ("new text -26", -26, ''); -SELECT * FROM nt_4; +SET @commands= 'B TeN-trig T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TeN-func T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #8) "B N N T C" generates in binglog the "N N B T C" entries. +--echo #13) "B M T R" generates in the binlog: +--echo # --> STMT "B M C B T R" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B M T R" entries, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format R. --echo # -BEGIN; -INSERT INTO nt_1 VALUES ("new text 10", 10, "new text 10"); -INSERT INTO nt_2 VALUES ("new text 10", 10, "new text 10"); -INSERT INTO tt_1 VALUES ("new text 10", 10, "new text 10"); -COMMIT; +SET @commands= 'B tN T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B nT T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NT T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NT-trig T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NT-func T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TN T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TN-trig T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TN-func T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo --echo # ---echo #8.e) "B N N T R" See 6.e and 9.e. +--echo #13.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B M C B T R" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B M T R" entries, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format R. --echo # +SET @commands= 'B tNe T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B nTe T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NeT-trig T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NeT-func T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TeN-trig T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TeN-func T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #9) "B N N T R" generates in binlog the "N N B T R" entries. +--echo #14) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B T M C" entries, format S. +--echo # --> ROW "B N C B T T C" entries, format R. +--echo # --> MIXED "B N C B T T C" entries, format R in N/T and format S in T. --echo # -BEGIN; -INSERT INTO nt_1 VALUES ("new text 11", 11, "new text 11"); -INSERT INTO nt_2 VALUES ("new text 11", 11, "new text 11"); -INSERT INTO tt_1 VALUES ("new text 11", 11, "new text 11"); -ROLLBACK; +SET @commands= 'B T tN C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T nT C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NT C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NT-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NT-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T TN C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T TN-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T TN-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #9.e) "B N N T R" with error in N generates in binlog the "N N B T R" entries. +--echo #14.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B T M C" entries, format S. +--echo # --> ROW "B N C B T C" entry, format R. +--echo # --> MIXED "B N C B T C" entry, format R. --echo # -BEGIN; -INSERT INTO nt_1 VALUES ("new text -25", -25, "new text -25"); -INSERT INTO nt_2 VALUES ("new text -25", -25, "new text -25"); ---error ER_DUP_ENTRY -INSERT INTO nt_2 VALUES ("new text -26", -26, "new text -26"), ("new text -25", -25, "new text -25"); -INSERT INTO tt_1 VALUES ("new text -27", -27, "new text -27"); -ROLLBACK; +SET @commands= 'B T tNe C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T nTe C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NeT-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NeT-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T TeN-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc +SET @commands= 'B T TeN-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #10) "B N N C" generates in binglog the "N N" entries. +--echo #15) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B T M R" entries, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format R. --echo # -BEGIN; -INSERT INTO nt_1 VALUES ("new text 12", 12, "new text 12"); -INSERT INTO nt_2 VALUES ("new text 12", 12, "new text 12"); -COMMIT; +SET @commands= 'B T tN R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T nT R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NT R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NT-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NT-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T TN R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T TN-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T TN-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo --echo # ---echo #10.e) "B N N C" See 6.e and 9.e. +--echo #15.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B T M R" entries, format S. +--echo # --> ROW "B N C" entry, format R. +--echo # --> MIXED "B N C" entry, format R. --echo # +SET @commands= 'B T tNe R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T nTe R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NeT-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T NeT-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T TeN-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T TeN-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #11) "B N N R" generates in binlog the "N N" entries. +--echo #16) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B M C B N C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B M N C" entries, format S. +--echo # --> ROW "B N C B N C B T C" entries, format R. +--echo # --> MIXED "B M C B N C" entries if in M only N-Table is changed, format S. +--echo # --> MIXED "B N C B N C B T C" entries, format R. --echo # -BEGIN; -INSERT INTO nt_1 VALUES ("new text 13", 13, "new text 13"); -INSERT INTO nt_2 VALUES ("new text 13", 13, "new text 13"); -ROLLBACK; +SET @commands= 'B tN N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B nT N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NT N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NT-trig N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NT-func N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TN N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TN-trig N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TN-func N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo --echo # ---echo #11.e) "B N N R" See 6.e and 9.e. +--echo #16.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B M C B N C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B M N C" entries, format S. +--echo # --> ROW "B N C B N C B T C" entries, format R. +--echo # --> MIXED "B M C B N C" entries if in M only N-Table is changed, format S. +--echo # --> MIXED "B N C B N C B T C" entries, format R. --echo # +SET @commands= 'B tNe N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B nTe N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NeT-trig N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B NeT-func N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TeN-trig N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TeN-func N C'; +--source extra/rpl_tests/rpl_mixing_engines.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #12) "B M T C" generates in the binlog the "B M T C" entries. +--echo #17) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B M C B N C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B M N R" entries, format S. +--echo # --> ROW "B N C B N C" entries, format R. +--echo # --> MIXED "B M C B N C" entries if in M only N-Table is changed, format S. +--echo # --> MIXED "B N C B N C" entries, format R. --echo # -DELETE FROM nt_1; -BEGIN; -INSERT INTO nt_1 SELECT * FROM tt_1; -INSERT INTO tt_2 VALUES ("new text 14", 14, "new text 14"); -COMMIT; +SET @commands= 'B tN N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -DELETE FROM tt_1; -BEGIN; -INSERT INTO tt_1 SELECT * FROM nt_1; -INSERT INTO tt_2 VALUES ("new text 15", 15, "new text 15"); -COMMIT; +SET @commands= 'B nT N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_3 VALUES ("new text 700", 700, ''); -INSERT INTO tt_1 VALUES ("new text 800", 800, ''); -COMMIT; +SET @commands= 'B NT N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_3 VALUES("new text 900", 900, f1()); -INSERT INTO tt_1 VALUES ("new text 1000", 1000, ''); -COMMIT; +SET @commands= 'B NT-trig N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_3 VALUES(1100, 1100, f2()); -INSERT INTO tt_1 VALUES ("new text 1200", 1200, ''); -COMMIT; +SET @commands= 'B NT-func N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO nt_4 VALUES ("new text 1300", 1300, ''); -INSERT INTO tt_1 VALUES ("new text 1400", 1400, ''); -COMMIT; +SET @commands= 'B TN N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO nt_4 VALUES("new text 1500", 1500, f1()); -INSERT INTO tt_1 VALUES ("new text 1600", 1600, ''); -COMMIT; +SET @commands= 'B TN-trig N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO nt_4 VALUES("new text 1700", 1700, f2()); -INSERT INTO tt_1 VALUES ("new text 1800", 1800, ''); -COMMIT; +SET @commands= 'B TN-func N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -CALL pc_i_tt_3(1900, "Testing..."); -INSERT INTO tt_1 VALUES ("new text 2000", 2000, ''); -COMMIT; -BEGIN; -UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a= "new text 5", nt_4.a= "new text 5", tt_3.a= "new text 5", tt_4.a= "new text 5" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; -INSERT INTO tt_1 VALUES ("new text 2100", 2100, ''); -COMMIT; +--echo +--echo +--echo +--echo +--echo # +--echo #17.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B M C B N C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B M N R" entries, format S. +--echo # --> ROW "B N C B N C" entries, format R. +--echo # --> MIXED "B M C B N C" entries if in M only N-Table is changed, format S. +--echo # --> MIXED "B N C B N C" entries, format R. +--echo # +SET @commands= 'B tNe N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a= "new text 6", tt_4.a= "new text 6", nt_3.a= "new text 6", nt_4.a = "new text 6" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; -INSERT INTO tt_1 VALUES ("new text 2200", 2200, ''); -COMMIT; +SET @commands= 'B nTe N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 7", nt_3.a= "new text 7", nt_4.a= "new text 7", tt_4.a = "new text 7" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; -INSERT INTO tt_1 VALUES ("new text 2300", 2300, ''); -COMMIT; +SET @commands= 'B NeT-trig N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 8", nt_3.a= "new text 8", nt_4.a= "new text 8", tt_4.a = "new text 8" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; -INSERT INTO tt_1 VALUES ("new text 2400", 2400, ''); -COMMIT; +SET @commands= 'B NeT-func N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TeN-trig N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B TeN-func N R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #12.e) "B M T C" with error in M generates in the binlog the "B M T C" entries. ---echo # +--echo #18) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B N C B M C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B N C B M C" entries, format S. +--echo # --> ROW "B N C B N C B T C" entries, format R. +--echo # --> MIXED "B N C B N C B T C" entries, format S in first N and format R in the other. +--echo # + +SET @commands= 'B N tN C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N nT C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N NT C'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---echo # There is a bug in the slave that needs to be fixed before enabling ---echo # this part of the test. A bug report will be filed referencing this ---echo # test case. +SET @commands= 'B N NT-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO nt_3 VALUES ("new text -28", -28, ''); ---error ER_DUP_ENTRY -INSERT INTO tt_3 VALUES ("new text -27", -27, ''), ("new text -28", -28, ''); -INSERT INTO tt_1 VALUES ("new text -27", -27, ''); -COMMIT; +SET @commands= 'B N NT-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N TN C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N TN-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N TN-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_4 VALUES ("new text -28", -28, ''); ---error ER_DUP_ENTRY -INSERT INTO nt_4 VALUES ("new text -27", -27, ''), ("new text -28", -28, ''); -INSERT INTO tt_1 VALUES ("new text -28", -28, ''); -COMMIT; ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #13) "B M T R" generates in the binlog the "B M T R" entries +--echo #18.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B N C B M C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B N C B M C" entries, format S. +--echo # --> ROW "B N C B N C" entries, format R. +--echo # --> MIXED "B N C B N C" entries, format S in first N and format R in the other. --echo # +SET @commands= 'B N tNe C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -DELETE FROM nt_1; -BEGIN; -INSERT INTO nt_1 SELECT * FROM tt_1; -INSERT INTO tt_2 VALUES ("new text 17", 17, "new text 17"); -ROLLBACK; +SET @commands= 'B N nTe C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -DELETE FROM tt_1; -BEGIN; -INSERT INTO tt_1 SELECT * FROM nt_1; -INSERT INTO tt_2 VALUES ("new text 18", 18, "new text 18"); -ROLLBACK; -INSERT INTO tt_1 SELECT * FROM nt_1; +SET @commands= 'B N NeT-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_3 VALUES ("new text 2500", 2500, ''); -INSERT INTO tt_1 VALUES ("new text 2600", 2600, ''); -ROLLBACK; +SET @commands= 'B N NeT-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_3 VALUES("new text 2700", 2700, f1()); -INSERT INTO tt_1 VALUES ("new text 2800", 2800, ''); -ROLLBACK; +SET @commands= 'B N TeN-trig C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_3 VALUES(2900, 2900, f2()); -INSERT INTO tt_1 VALUES ("new text 3000", 3000, ''); -ROLLBACK; +SET @commands= 'B N TeN-func C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO nt_4 VALUES ("new text 3100", 3100, ''); -INSERT INTO tt_1 VALUES ("new text 3200", 3200, ''); -ROLLBACK; -BEGIN; -INSERT INTO nt_4 VALUES("new text 3300", 3300, f1()); -INSERT INTO tt_1 VALUES ("new text 3400", 3400, ''); -ROLLBACK; +--echo +--echo +--echo +--echo +--echo # +--echo #19) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B N C B M C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B N C B M R" entries, format S. +--echo # --> ROW "B N C B N C" entries, format R. +--echo # --> MIXED "B N C B N C" entries, format S in first N and format R in the other. +--echo # + +SET @commands= 'B N tN R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO nt_4 VALUES("new text 3500", 3500, f2()); -INSERT INTO tt_1 VALUES ("new text 3600", 3600, ''); -ROLLBACK; +SET @commands= 'B N nT R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -CALL pc_i_tt_3(3700, "Testing..."); -INSERT INTO tt_1 VALUES ("new text 3700", 3700, ''); -ROLLBACK; +SET @commands= 'B N NT R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a= "new text 9", nt_4.a= "new text 9", tt_3.a= "new text 9", tt_4.a= "new text 9" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; -INSERT INTO tt_1 VALUES ("new text 3800", 3800, ''); -ROLLBACK; +SET @commands= 'B N NT-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a= "new text 10", tt_4.a= "new text 10", nt_3.a= "new text 10", nt_4.a = "new text 10" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; -INSERT INTO tt_1 VALUES ("new text 3900", 3900, ''); -ROLLBACK; +SET @commands= 'B N NT-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 11", nt_3.a= "new text 11", nt_4.a= "new text 11", tt_4.a = "new text 11" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; -INSERT INTO tt_1 VALUES ("new text 4000", 4000, ''); -ROLLBACK; +SET @commands= 'B N TN R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 12", nt_3.a= "new text 12", nt_4.a= "new text 12", tt_4.a = "new text 12" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100; -INSERT INTO tt_1 VALUES ("new text 4100", 4100, ''); -ROLLBACK; +SET @commands= 'B N TN-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N TN-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc --echo --echo --echo --echo -let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); --echo # ---echo #13.e) "B M T R" with error in M generates in the binlog the "B M T R" entries. +--echo #19.e) Generates in the binlog what follows if a N-Table is changed: +--echo # --> STMT "B N C B M C" entries if in M only N-Table is changed, format S. +--echo # --> STMT "B N C B M R" entries, format S. +--echo # --> ROW "B N C B N C" entries, format R. +--echo # --> MIXED "B N C B N C" entries, format S in first N and format R in the other. --echo # +SET @commands= 'B N tNe R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO nt_3 VALUES ("new text -30", -30, ''); ---error ER_DUP_ENTRY -INSERT INTO tt_3 VALUES ("new text -29", -29, ''), ("new text -30", -30, ''); -INSERT INTO tt_1 VALUES ("new text -30", -30, ''); -ROLLBACK; +SET @commands= 'B N nTe R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -BEGIN; -INSERT INTO tt_4 VALUES ("new text -30", -30, ''); ---error ER_DUP_ENTRY -INSERT INTO nt_4 VALUES ("new text -29", -29, ''), ("new text -30", -30, ''); -INSERT INTO tt_1 VALUES ("new text -31", -31, ''); -ROLLBACK; +SET @commands= 'B N NeT-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---source include/show_binlog_events.inc +SET @commands= 'B N NeT-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc -connection master; -sync_slave_with_master; +SET @commands= 'B N TeN-trig R'; +--source extra/rpl_tests/rpl_mixing_engines.inc ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/test-master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/test-slave.sql ---diff_files $MYSQLTEST_VARDIR/tmp/test-master.sql $MYSQLTEST_VARDIR/tmp/test-slave.sql +SET @commands= 'B N TeN-func R'; +--source extra/rpl_tests/rpl_mixing_engines.inc --echo ################################################################################### ---echo # CLEAN +--echo # 2 - SAVEPOINT --echo ################################################################################### +SET @commands= 'B T S1 T R1 C'; +--source extra/rpl_tests/rpl_mixing_engines.inc -connection master; -DROP TABLE tt_1; -DROP TABLE tt_2; -DROP TABLE tt_3; -DROP TABLE tt_4; -DROP TABLE nt_1; -DROP TABLE nt_2; -DROP TABLE nt_3; -DROP TABLE nt_4; -DROP PROCEDURE pc_i_tt_3; -DROP FUNCTION f1; -DROP FUNCTION f2; +SET @commands= 'B N T S1 T R1 C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T N S1 T R1 C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T S1 N T R1 C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +--echo ################################################################################### +--echo # 3 - CREATE TABLE...SELECT +--echo ################################################################################### +SET @commands= 'CSe-T->T CS-T->T drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'CSe-N->N CS-N->N drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'CSe-T->N CS-T->N drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'CSe-N->T CS-N->T drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'CSe-N->T CS-N->T drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'CSe-N->T CS-N->T drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +--echo ################################################################################### +--echo # 4 - INSERT TABLE...SELECT +--echo ################################################################################### + +SET @commands= 'CS-T->T'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-T B T IS-T<-N T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-T B T ISe-T<-N T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-T B IS-T<-N T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-T B ISe-T<-N T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'CS-T->T'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-T B T IS-T<-T T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-T B T ISe-T<-T T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-T B IS-T<-T T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-T B ISe-T<-T T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'CS-N->N'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-N B T IS-N<-T T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-N B T ISe-N<-T T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-N B IS-N<-T T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-N B ISe-N<-T T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'CS-N->N'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-N B T IS-N<-N T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-N B T ISe-N<-N T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-N B IS-N<-N T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'trunc-CS-N B ISe-N<-N T C'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CS'; +--source extra/rpl_tests/rpl_mixing_engines.inc + + +--echo ################################################################################### +--echo # 5 - ROLLBACK TEMPORARY TABLE +--echo ################################################################################### +SET @commands= 'B T CT R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CT'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T S1 T CT R1 R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CT'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B T CT T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CT'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B tN CT T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CT'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B CT T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CT'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'B N CT T R'; +--source extra/rpl_tests/rpl_mixing_engines.inc + +SET @commands= 'drop-CT'; +--source extra/rpl_tests/rpl_mixing_engines.inc + + +--echo ################################################################################### +--echo # CHECK CONSISTENCY +--echo ################################################################################### +connection master; sync_slave_with_master; + +--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/test-nmt-master.sql +--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/test-nmt-slave.sql +--diff_files $MYSQLTEST_VARDIR/tmp/test-nmt-master.sql $MYSQLTEST_VARDIR/tmp/test-nmt-slave.sql + +--echo ################################################################################### +--echo # CLEAN +--echo ################################################################################### +SET @commands= 'clean'; +--source extra/rpl_tests/rpl_mixing_engines.inc diff --git a/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test b/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test index 4677f6da25d..7ab99ef65dd 100644 --- a/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test +++ b/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test @@ -141,7 +141,9 @@ let $run= 5; while ($run) { START TRANSACTION; + --disable_warnings --eval CALL tpcb.trans($rpl_format); + --enable_warnings eval SET @my_errno= $mysql_errno; let $run_good= `SELECT @my_errno = 0`; let $run_bad= `SELECT @my_errno <> 0`; @@ -190,7 +192,9 @@ let $run= 5; while ($run) { START TRANSACTION; + --disable_warnings --eval CALL tpcb.trans($rpl_format); + --enable_warnings eval SET @my_errno= $mysql_errno; let $run_good= `SELECT @my_errno = 0`; let $run_bad= `SELECT @my_errno <> 0`; @@ -240,7 +244,9 @@ let $run= 5; while ($run) { START TRANSACTION; + --disable_warnings --eval CALL tpcb.trans($rpl_format); + --enable_warnings eval SET @my_errno= $mysql_errno; let $run_good= `SELECT @my_errno = 0`; let $run_bad= `SELECT @my_errno <> 0`; diff --git a/mysql-test/extra/rpl_tests/rpl_show_relaylog_events.inc b/mysql-test/extra/rpl_tests/rpl_show_relaylog_events.inc index 50036e564a7..cf68ba53caf 100644 --- a/mysql-test/extra/rpl_tests/rpl_show_relaylog_events.inc +++ b/mysql-test/extra/rpl_tests/rpl_show_relaylog_events.inc @@ -23,7 +23,7 @@ let $binary_log_limit_row= 3; -- echo [MASTER] ********* SOW BINLOG EVENTS ... LIMIT offset,rows ********* let $binary_log_file= ; -let $binary_log_limit_row= 3; +let $binary_log_limit_row= 4; let $binary_log_limit_offset= 1; -- source include/show_binlog_events.inc @@ -49,7 +49,7 @@ let $binary_log_limit_row= 3; -- echo [SLAVE] ********* SOW BINLOG EVENTS ... LIMIT offset,rows ********* let $binary_log_file= ; -let $binary_log_limit_row= 3; +let $binary_log_limit_row= 4; let $binary_log_limit_offset= 1; -- source include/show_binlog_events.inc diff --git a/mysql-test/extra/rpl_tests/rpl_start_stop_slave.test b/mysql-test/extra/rpl_tests/rpl_start_stop_slave.test new file mode 100644 index 00000000000..e548e691429 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_start_stop_slave.test @@ -0,0 +1,125 @@ +# +# Bug#6148 () +# +# Let the master do lots of insertions + +connection master; +call mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT"); + +connection slave; +call mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT"); + +connection master; +create table t1(n int); +sync_slave_with_master; +stop slave; +--source include/wait_for_slave_to_stop.inc +connection master; +let $1=5000; +disable_query_log; +while ($1) +{ + eval insert into t1 values($1); + dec $1; +} +enable_query_log; +save_master_pos; + +connection slave; +start slave; +let $wait_condition= SELECT COUNT(*) > 0 FROM t1; +source include/wait_condition.inc; +stop slave io_thread; +start slave io_thread; +source include/wait_for_slave_to_start.inc; +sync_with_master; + +connection master; +drop table t1; +sync_slave_with_master; + + +# +# Bug#38205 Row-based Replication (RBR) causes inconsistencies... +# Bug#319 if while a non-transactional slave is replicating a transaction... +# +# Verifying that STOP SLAVE does not interrupt excution of a group +# execution of events if the group can not roll back. +# Killing the sql thread continues to provide a "hard" stop (the +# part II, moved to the bugs suite as it's hard to make it +# deterministic with KILL). +# + +# +# Part I. The being stopped sql thread finishes first the current group of +# events if the group contains an event on a non-transaction table. + +connection master; +create table t1i(n int primary key) engine=innodb; +create table t2m(n int primary key) engine=myisam; +begin; +insert into t1i values (1); +insert into t1i values (2); +insert into t1i values (3); +commit; + +sync_slave_with_master; +connection slave; +begin; +insert into t1i values (5); + +connection master; +let $pos0_master= query_get_value(SHOW MASTER STATUS, Position, 1); +begin; +insert into t1i values (4); +insert into t2m values (1); # non-ta update to process +insert into t1i values (5); # to block at. to be played with stopped +commit; + +connection slave; +# slave sql thread must be locked out by the conn `slave' explicit lock +let $pos0_slave= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1); +--disable_query_log +eval select $pos0_master - $pos0_slave as zero; +--enable_query_log + +connection slave1; +let $count= 1; +let $table= t2m; +source include/wait_until_rows_count.inc; +send stop slave; + +connection slave; +rollback; # release the sql thread + +connection slave1; +reap; +source include/wait_for_slave_to_stop.inc; +let $sql_status= query_get_value(SHOW SLAVE STATUS, Slave_SQL_Running, 1); +--echo *** sql thread is *not* running: $sql_status *** + + +connection master; +let $pos1_master= query_get_value(SHOW MASTER STATUS, Position, 1); + +connection slave; + +let $pos1_slave= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1); + +--echo *** the prove: the stopped slave has finished the current transaction *** + +--disable_query_log +select count(*) as five from t1i; +eval select $pos1_master - $pos1_slave as zero; +eval select $pos1_slave > $pos0_slave as one; +--enable_query_log + +source include/start_slave.inc; + +# clean-up +connection master; +drop table t1i, t2m; + +sync_slave_with_master; + +# End of tests diff --git a/mysql-test/extra/rpl_tests/rpl_stm_000001.test b/mysql-test/extra/rpl_tests/rpl_stm_000001.test index 1276b00d882..07ca6f6a38f 100644 --- a/mysql-test/extra/rpl_tests/rpl_stm_000001.test +++ b/mysql-test/extra/rpl_tests/rpl_stm_000001.test @@ -3,7 +3,7 @@ -- source include/have_binlog_format_statement.inc -- source include/master-slave.inc -CALL mtr.add_suppression("Statement may not be safe to log in statement format."); +CALL mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT"); # Load some data into t1 create table t1 (word char(20) not null); diff --git a/mysql-test/extra/rpl_tests/rpl_stop_middle_group.test b/mysql-test/extra/rpl_tests/rpl_stop_middle_group.test new file mode 100644 index 00000000000..96ae314bee5 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_stop_middle_group.test @@ -0,0 +1,147 @@ +# Proving that stopping in the middle of applying a group of events +# does not have immediate effect if a non-transaction table has been changed. +# The slave sql thread has to try to finish applying first. +# The tests rely on simulation of the killed status. +# The matter of testing correlates to some of `rpl_start_stop_slave' that does +# not require `have_debug'. + +connection master; + +call mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT"); + +create table tm (a int auto_increment primary key) engine=myisam; +create table ti (a int auto_increment primary key) engine=innodb; + +sync_slave_with_master; +set @@global.debug="+d,stop_slave_middle_group"; + +connection master; + +begin; +insert into ti set a=null; +insert into tm set a=null; # to simulate killed status on the slave +commit; + +connection slave; + +# slave will catch the killed status but won't shut down immediately +# only after the whole group has done (commit) + +source include/wait_for_slave_sql_to_stop.inc; + +# checking: no error and the group is finished + +let $error= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); +let $read = query_get_value("SHOW SLAVE STATUS", Read_Master_Log_Pos, 1); +let $exec = query_get_value("SHOW SLAVE STATUS", Exec_Master_Log_Pos, 1); +--disable_query_log +eval SELECT $read = $exec into @check; +--enable_query_log +eval SELECT "NO$error" AS Last_SQL_Error, @check as `true`; +select count(*) as one from tm; +select count(*) as one from ti; + +set @@global.debug="-d"; + +# +# bug#45940 issues around rli->last_event_start_time +# Testing of slave stopped after it had waited (in vain) for +# the group be finished. +# It could not be finished because of simulation of failure to +# receive the terminal part +# The test relay on simulation of the incomplete group in the relay log + +# Two cases are verified: a mixed transacton and a mixed multi-table update. +# +# The mixed transacton. +# +source include/start_slave.inc; + +connection master; + +truncate table tm; # cleanup of former tests +truncate table ti; + +#connection slave; +sync_slave_with_master; + +set @@global.debug="+d,stop_slave_middle_group"; +set @@global.debug="+d,incomplete_group_in_relay_log"; + +connection master; + +begin; +insert into ti set a=null; +insert into tm set a=null; +commit; + +connection slave; + +# slave will catch the killed status, won't shut down immediately +# but does it eventually having the whole group unfinished (not committed) + +source include/wait_for_slave_sql_to_stop.inc; + +# checking: the error and group unfinished + +let $error= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); +let $read = query_get_value("SHOW SLAVE STATUS", Read_Master_Log_Pos, 1); +let $exec = query_get_value("SHOW SLAVE STATUS", Exec_Master_Log_Pos, 1); +--disable_query_log +eval SELECT $read - $exec > 0 into @check; +--enable_query_log +eval SELECT "$error" AS Last_SQL_Error, @check as `true`; +select count(*) as one from tm; +select count(*) as zero from ti; + +set @@global.debug="-d"; + +# +# The mixed multi-table update +# +stop slave; +truncate table tm; +source include/start_slave.inc; + +connection master; + +#connection slave; +sync_slave_with_master; +set @@global.debug="+d,stop_slave_middle_group"; +set @@global.debug="+d,incomplete_group_in_relay_log"; + +connection master; +update tm as t1, ti as t2 set t1.a=t1.a * 2, t2.a=t2.a * 2; + +connection slave; + +# slave will catch the killed status, won't shut down immediately +# but does it eventually having the whole group unfinished (not committed) +# + +source include/wait_for_slave_sql_to_stop.inc; + +# checking: the error and group unfinished + +let $error= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); +let $read = query_get_value("SHOW SLAVE STATUS", Read_Master_Log_Pos, 1); +let $exec = query_get_value("SHOW SLAVE STATUS", Exec_Master_Log_Pos, 1); +--disable_query_log +eval SELECT $read - $exec > 0 into @check; +--enable_query_log +eval SELECT "$error" AS Last_SQL_Error, @check as `true`; +select max(a) as two from tm; +select max(a) as one from ti; + +set @@global.debug="-d"; + +# +# clean-up +# + +connection master; +drop table tm, ti; + +connection slave; # slave SQL thread is stopped +source include/stop_slave.inc; +drop table tm, ti; |