diff options
Diffstat (limited to 'mysql-test/suite')
60 files changed, 3056 insertions, 427 deletions
diff --git a/mysql-test/suite/binlog/r/binlog_killed.result b/mysql-test/suite/binlog/r/binlog_killed.result index ba4f38fb4c1..72fda535b6f 100644 --- a/mysql-test/suite/binlog/r/binlog_killed.result +++ b/mysql-test/suite/binlog/r/binlog_killed.result @@ -9,4 +9,135 @@ insert into t2 values (null, null), (null, get_lock("a", 10)); select @result /* must be zero either way */; @result 0 +select RELEASE_LOCK("a"); +RELEASE_LOCK("a") +1 +delete from t1; +delete from t2; +insert into t1 values (1,1),(2,2); +begin; +update t1 set b=11 where a=2; +begin; +update t1 set b=b+10; +kill query ID; +rollback; +rollback; +select * from t1 order by a /* must be the same as before (1,1),(2,2) */; +a b +1 1 +2 2 +begin; +delete from t1 where a=2; +begin; +delete from t1 where a=2; +kill query ID; +rollback; +rollback; +select * from t1 order by a /* must be the same as before (1,1),(2,2) */; +a b +1 1 +2 2 +drop table if exists t4; +create table t4 (a int, b int) engine=innodb; +insert into t4 values (3, 3); +begin; +insert into t1 values (3, 3); +begin; +insert into t1 select * from t4 for update; +kill query ID; +rollback; +rollback; +select * from t1 /* must be the same as before (1,1),(2,2) */; +a b +1 1 +2 2 +drop table t4; +create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */; +create function bug27563(n int) +RETURNS int(11) +DETERMINISTIC +begin +if @b > 0 then +select get_lock("a", 20) into @a; +else +set @b= 1; +end if; +return n; +end| +delete from t4; +insert into t4 values (1,1), (1,1); +reset master; +select get_lock("a", 20); +get_lock("a", 20) +1 +set @b= 0; +update t4 set b=b + bug27563(b); +select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock'; +count(*) +1 +kill query ID; +ERROR 70100: Query execution was interrupted +select * from t4 order by b /* must be (1,1), (1,2) */; +a b +1 1 +1 2 +select @b /* must be 1 at the end of a stmt calling bug27563() */; +@b +1 +must have the update query event more to FD +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # User var # # @`b`=0 +master-bin.000001 # Query # # use `test`; update t4 set b=b + bug27563(b) +select +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null +1 +select 0 /* must return 0 to mean the killed query is in */; +0 +0 +select RELEASE_LOCK("a"); +RELEASE_LOCK("a") +1 +delete from t4; +insert into t4 values (1,1), (2,2); +reset master; +select get_lock("a", 20); +get_lock("a", 20) +1 +set @b= 0; +delete from t4 where b=bug27563(1) or b=bug27563(2); +select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock'; +count(*) +1 +kill query ID; +ERROR 70100: Query execution was interrupted +select count(*) from t4 /* must be 1 */; +count(*) +1 +select @b /* must be 1 at the end of a stmt calling bug27563() */; +@b +1 +must have the delete query event more to FD +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # User var # # @`b`=0 +master-bin.000001 # Query # # use `test`; delete from t4 where b=bug27563(1) or b=bug27563(2) +select +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null +1 +select 0 /* must return 0 to mean the killed query is in */; +0 +0 +select RELEASE_LOCK("a"); +RELEASE_LOCK("a") +1 +drop table t4; +drop function bug27563; drop table t1,t2,t3; +end of the tests diff --git a/mysql-test/suite/binlog/r/binlog_killed_simulate.result b/mysql-test/suite/binlog/r/binlog_killed_simulate.result new file mode 100644 index 00000000000..f6a5ddade51 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_killed_simulate.result @@ -0,0 +1,33 @@ +drop table if exists t1,t2; +create table t1 (a int) engine=MyISAM; +insert into t1 set a=1; +reset master; +update t1 set a=2 /* will be "killed" after work has been done */; +select +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null +1 +select 1 /* must return 1 as query completed before got killed*/; +1 +1 +create table t2 (a int, b int) ENGINE=MyISAM; +reset master; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */; +ERROR 70100: Query execution was interrupted +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Begin_load_query # # ;file_id=1;block_len=12 +master-bin.000001 # Execute_load_query # # use `test`; load data infile '../std_data_ln/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */ ;file_id=1 +select +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +(@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null +1 +select 0 /* must return 0 to mean the killed query is in */; +0 +0 +drop table t1,t2; +end of the tests diff --git a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result index 6ac942176c7..f69d5717a1f 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result @@ -413,3 +413,236 @@ select @a like "%#%error_code=0%ROLLBACK/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" @a not like "%#%error_code=%error_code=%" 1 1 drop table t1, t2; +create temporary table tt (a int unique); +create table ti (a int) engine=innodb; +reset master; +show master status; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 106 +begin; +insert into ti values (1); +insert into ti values (2) ; +insert into tt select * from ti; +rollback; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +select count(*) from tt /* 2 */; +count(*) +2 +show master status; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 395 +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; BEGIN +master-bin.000001 # Table_map # # table_id: # (test.ti) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.ti) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from ti /* zero */; +count(*) +0 +insert into ti select * from tt; +select * from ti /* that is what slave would miss - bug#28960 */; +a +1 +2 +delete from ti; +delete from tt where a=1; +reset master; +show master status; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 106 +begin; +insert into ti values (1); +insert into ti values (2) /* to make the dup error in the following */; +insert into tt select * from ti /* one affected and error */; +ERROR 23000: Duplicate entry '2' for key 'a' +rollback; +show master status; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 106 +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +select count(*) from ti /* zero */; +count(*) +0 +insert into ti select * from tt; +select * from tt /* that is what otherwise slave missed - the bug */; +a +1 +2 +drop table ti; +drop function if exists bug27417; +drop table if exists t1,t2; +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; +CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); +create function bug27417(n int) +RETURNS int(11) +begin +insert into t1 values (null); +return n; +end| +reset master; +insert into t2 values (bug27417(1)); +insert into t2 select bug27417(2); +reset master; +insert into t2 values (bug27417(2)); +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(2)) +select count(*) from t1 /* must be 3 */; +count(*) +3 +reset master; +select count(*) from t2; +count(*) +2 +delete from t2 where a=bug27417(3); +select count(*) from t2 /* nothing got deleted */; +count(*) +2 +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=4 +master-bin.000001 # Query # # use `test`; delete from t2 where a=bug27417(3) +select count(*) from t1 /* must be 5 */; +count(*) +5 +delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */; +affected rows: 0 +select count(*) from t1 /* must be 7 */; +count(*) +7 +drop table t1,t2; +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; +CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; +CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; +CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; +CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; +insert into t2 values (1); +reset master; +insert into t2 values (bug27417(1)); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(1)) +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 1 */; +count(*) +1 +delete from t1; +delete from t2; +insert into t2 values (2); +reset master; +insert into t2 select bug27417(1) union select bug27417(2); +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=2 +master-bin.000001 # Query # # use `test`; insert into t2 select bug27417(1) union select bug27417(2) +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 2 */; +count(*) +2 +delete from t1; +insert into t3 values (1,1),(2,3),(3,4); +reset master; +update t3 set b=b+bug27417(1); +ERROR 23000: Duplicate entry '4' for key 'b' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=4 +master-bin.000001 # Query # # use `test`; update t3 set b=b+bug27417(1) +select count(*) from t1 /* must be 2 */; +count(*) +2 +delete from t3; +delete from t4; +insert into t3 values (1,1); +insert into t4 values (1,1),(2,2); +reset master; +UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=6 +master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */ +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 4 */; +count(*) +4 +delete from t1; +delete from t3; +delete from t4; +insert into t3 values (1,1),(2,2); +insert into t4 values (1,1),(2,2); +reset master; +UPDATE t3,t4 SET t3.a=t4.a + bug27417(1); +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +select count(*) from t1 /* must be 1 */; +count(*) +1 +drop table t4; +delete from t1; +delete from t2; +delete from t3; +insert into t2 values (1); +insert into t3 values (1,1); +create trigger trg_del before delete on t2 for each row +insert into t3 values (bug27417(1), 2); +reset master; +delete from t2; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=9 +master-bin.000001 # Query # # use `test`; delete from t2 +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 1 */; +count(*) +1 +drop trigger trg_del; +delete from t1; +delete from t2; +delete from t5; +create trigger trg_del_t2 after delete on t2 for each row +insert into t1 values (1); +insert into t2 values (2),(3); +insert into t5 values (1),(2); +reset master; +delete t2.* from t2,t5 where t2.a=t5.a + 1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; delete t2.* from t2,t5 where t2.a=t5.a + 1 +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 1 */; +count(*) +1 +delete from t1; +create table t4 (a int default 0, b int primary key) engine=innodb; +insert into t4 values (0, 17); +reset master; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2); +ERROR 23000: Duplicate entry '17' for key 'PRIMARY' +select * from t4; +a b +0 17 +select count(*) from t1 /* must be 2 */; +count(*) +2 +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=10 +master-bin.000001 # Begin_load_query # # ;file_id=1;block_len=12 +master-bin.000001 # Intvar # # INSERT_ID=10 +master-bin.000001 # Execute_load_query # # use `test`; load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=1 +master-bin.000001 # Query # # use `test`; ROLLBACK +drop trigger trg_del_t2; +drop table t1,t2,t3,t4,t5; +drop function bug27417; diff --git a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result index 5ef36861c30..c15478bc826 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result @@ -380,7 +380,8 @@ select @a like "%#%error_code=0%ROLLBACK/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" @a not like "%#%error_code=%error_code=%" 1 1 drop table t1, t2; -create table tt (a int unique); +set @@session.binlog_format=statement; +create temporary table tt (a int unique); create table ti (a int) engine=innodb; reset master; show master status; @@ -399,18 +400,18 @@ count(*) show master status; File Position Binlog_Do_DB Binlog_Ignore_DB master-bin.000001 515 -show binlog events from 106; +show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `test`; BEGIN -master-bin.000001 # Query 1 # use `test`; insert into ti values (1) -master-bin.000001 # Query 1 # use `test`; insert into ti values (2) -master-bin.000001 # Query 1 # use `test`; insert into tt select * from ti -master-bin.000001 # Query 1 # use `test`; ROLLBACK +master-bin.000001 # Query # # use `test`; BEGIN +master-bin.000001 # Query # # use `test`; insert into ti values (1) +master-bin.000001 # Query # # use `test`; insert into ti values (2) +master-bin.000001 # Query # # use `test`; insert into tt select * from ti +master-bin.000001 # Query # # use `test`; ROLLBACK select count(*) from ti /* zero */; count(*) 0 insert into ti select * from tt; -select * from ti /* that is what slave would miss - a bug */; +select * from ti /* that is what slave would miss - bug#28960 */; a 1 2 @@ -431,13 +432,13 @@ Warning 1196 Some non-transactional changed tables couldn't be rolled back show master status; File Position Binlog_Do_DB Binlog_Ignore_DB master-bin.000001 589 -show binlog events from 106; +show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `test`; BEGIN -master-bin.000001 # Query 1 # use `test`; insert into ti values (1) -master-bin.000001 # Query 1 # use `test`; insert into ti values (2) /* to make the dup error in the following */ -master-bin.000001 # Query 1 # use `test`; insert into tt select * from ti /* one affected and error */ -master-bin.000001 # Query 1 # use `test`; ROLLBACK +master-bin.000001 # Query # # use `test`; BEGIN +master-bin.000001 # Query # # use `test`; insert into ti values (1) +master-bin.000001 # Query # # use `test`; insert into ti values (2) /* to make the dup error in the following */ +master-bin.000001 # Query # # use `test`; insert into tt select * from ti /* one affected and error */ +master-bin.000001 # Query # # use `test`; ROLLBACK select count(*) from ti /* zero */; count(*) 0 @@ -446,7 +447,7 @@ select * from tt /* that is what otherwise slave missed - the bug */; a 1 2 -drop table ti,tt; +drop table ti; drop function if exists bug27417; drop table if exists t1,t2; CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; @@ -463,6 +464,10 @@ insert into t2 select bug27417(2); reset master; insert into t2 values (bug27417(2)); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(2)) select count(*) from t1 /* must be 3 */; count(*) 3 @@ -474,6 +479,10 @@ delete from t2 where a=bug27417(3); select count(*) from t2 /* nothing got deleted */; count(*) 2 +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=4 +master-bin.000001 # Query # # use `test`; delete from t2 where a=bug27417(3) select count(*) from t1 /* must be 5 */; count(*) 5 @@ -482,6 +491,134 @@ affected rows: 0 select count(*) from t1 /* must be 7 */; count(*) 7 -drop function bug27417; drop table t1,t2; +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; +CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; +CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; +CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; +CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; +insert into t2 values (1); +reset master; +insert into t2 values (bug27417(1)); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(1)) +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 1 */; +count(*) +1 +delete from t1; +delete from t2; +insert into t2 values (2); +reset master; +insert into t2 select bug27417(1) union select bug27417(2); +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=2 +master-bin.000001 # Query # # use `test`; insert into t2 select bug27417(1) union select bug27417(2) +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 2 */; +count(*) +2 +delete from t1; +insert into t3 values (1,1),(2,3),(3,4); +reset master; +update t3 set b=b+bug27417(1); +ERROR 23000: Duplicate entry '4' for key 'b' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=4 +master-bin.000001 # Query # # use `test`; update t3 set b=b+bug27417(1) +select count(*) from t1 /* must be 2 */; +count(*) +2 +delete from t3; +delete from t4; +insert into t3 values (1,1); +insert into t4 values (1,1),(2,2); +reset master; +UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=6 +master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */ +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 4 */; +count(*) +4 +delete from t1; +delete from t3; +delete from t4; +insert into t3 values (1,1),(2,2); +insert into t4 values (1,1),(2,2); +reset master; +UPDATE t3,t4 SET t3.a=t4.a + bug27417(1); +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +select count(*) from t1 /* must be 1 */; +count(*) +1 +drop table t4; +delete from t1; +delete from t2; +delete from t3; +insert into t2 values (1); +insert into t3 values (1,1); +create trigger trg_del before delete on t2 for each row +insert into t3 values (bug27417(1), 2); +reset master; +delete from t2; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=9 +master-bin.000001 # Query # # use `test`; delete from t2 +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 1 */; +count(*) +1 +drop trigger trg_del; +delete from t1; +delete from t2; +delete from t5; +create trigger trg_del_t2 after delete on t2 for each row +insert into t1 values (1); +insert into t2 values (2),(3); +insert into t5 values (1),(2); +reset master; +delete t2.* from t2,t5 where t2.a=t5.a + 1; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; delete t2.* from t2,t5 where t2.a=t5.a + 1 +master-bin.000001 # Query # # use `test`; ROLLBACK +select count(*) from t1 /* must be 1 */; +count(*) +1 +delete from t1; +create table t4 (a int default 0, b int primary key) engine=innodb; +insert into t4 values (0, 17); +reset master; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2); +ERROR 23000: Duplicate entry '17' for key 'PRIMARY' +select * from t4; +a b +0 17 +select count(*) from t1 /* must be 2 */; +count(*) +2 +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Intvar # # INSERT_ID=10 +master-bin.000001 # Begin_load_query # # ;file_id=1;block_len=12 +master-bin.000001 # Intvar # # INSERT_ID=10 +master-bin.000001 # Execute_load_query # # use `test`; load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=1 +master-bin.000001 # Query # # use `test`; ROLLBACK +drop trigger trg_del_t2; +drop table t1,t2,t3,t4,t5; +drop function bug27417; +set @@session.binlog_format=@@global.binlog_format; end of tests diff --git a/mysql-test/suite/binlog/t/binlog_killed.test b/mysql-test/suite/binlog/t/binlog_killed.test index 6c0b4b46a4e..e5f7288b17c 100644 --- a/mysql-test/suite/binlog/t/binlog_killed.test +++ b/mysql-test/suite/binlog/t/binlog_killed.test @@ -55,194 +55,277 @@ enable_result_log; select @result /* must be zero either way */; -# the functions are either *insensitive* to killing or killing can cause -# strange problmes with the error propagation out of SF's stack -# Bug#27563, Bug#27565, BUG#24971 -# -# TODO: use if's block as regression test for the bugs or remove -# -if (0) -{ -delimiter |; -create function bug27563() -RETURNS int(11) -DETERMINISTIC -begin - select get_lock("a", 10) into @a; - return 1; -end| -delimiter ;| -# the function is sensitive to killing requiring innodb though with wrong client error -# TO FIX in BUG#27565; TODO: remove --error 1105 afterwards -delimiter |; -create function bug27565() -RETURNS int(11) -DETERMINISTIC -begin - select a from t1 where a=1 into @a for update; - return 1; -end| -delimiter ;| +--remove_file $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog +connection con1; +select RELEASE_LOCK("a"); -reset master; +# +# bug#27571 asynchronous setting mysql_`query`::error and Query_log_e::error_code +# +# checking that killing inside of select loops is safe as before +# killing after the loop can be only simulated - another test -### ta table case: killing causes rollback +delete from t1; +delete from t2; +insert into t1 values (1,1),(2,2); -# A. autocommit ON +# +# simple update +# connection con1; -select get_lock("a", 20); +begin; update t1 set b=11 where a=2; connection con2; let $ID= `select connection_id()`; -send insert into t1 values (bug27563(),1); +begin; +send update t1 set b=b+10; connection con1; +--replace_result $ID ID eval kill query $ID; +rollback; + +# Bug #32148 killi query may be ineffective +# forced to comment out the test's outcome +# and mask out ineffective ER_QUERY_INTERRUPTED +# todo1: revert back upon fixing bug#32148 +# todo2: the tests need refining in that +# killing should wait till the victim requested +# its lock (wait_condition available in 5.1 tests) connection con2; -# todo (re-record test): after bugs 27563,27565 got fixed affected rows will report zero ---enable_info -# todo: remove 0 return after fixing Bug#27563 --error 0,ER_QUERY_INTERRUPTED -reap; ### pb: wrong error ---disable_info -###--replace_column 2 # 5 # -### show binlog events from 98 /* nothing in binlog unless Bug#27563 */; -show master status /* must be only FD event unless Bug#27563 */; -select count(*) from t1 /* must be zero unless Bug#27563 */; - -# M. multi-statement-ta +reap; +rollback; +select * from t1 order by a /* must be the same as before (1,1),(2,2) */; + +# +# multi update +# commented out as Bug #31807 multi-update,delete killing does not report with ER_QUERY_INTERRUPTED +# in the way +# +# connection con1; +# begin; update t1 set b=b+10; + +# connection con2; +# send update t1 as t_1,t1 as t_2 set t_1.b=11 where t_2.a=2; + +# connection con1; +# --replace_result $ID ID +# eval kill query $ID; +# rollback; + +# disable_abort_on_error; + +# connection con2; +# --error HY000,ER_QUERY_INTERRUPTED +# reap; +# select * from t1 /* must be the same as before (1,1),(2,2) */; + +# enable_abort_on_error; +# +# simple delete +# +connection con1; +begin; delete from t1 where a=2; + connection con2; let $ID= `select connection_id()`; begin; -send insert into t1 values (bug27563(),1); +send delete from t1 where a=2; connection con1; +--replace_result $ID ID eval kill query $ID; +rollback; + connection con2; -# todo (re-record test): after bugs 27563,27565 got fixed affected rows will report zero ---enable_info -# todo: remove 0 return after fixing Bug#27563 --error 0,ER_QUERY_INTERRUPTED reap; ---disable_info -select count(*) from t1 /* must be zero unless Bug#27563 */; -commit; +rollback; +# todo1,2 above +select * from t1 order by a /* must be the same as before (1,1),(2,2) */; -### non-ta table case: killing must be recorded in binlog +# +# multi delete +# the same as for multi-update +# +# connection con1; +# begin; delete from t1 where a=2; -reset master; +# connection con2; +# send delete t1 from t1 where t1.a=2; + +# connection con1; +# --replace_result $ID ID +# eval kill query $ID; +# rollback; + +# connection con2; +# --error 0,ER_QUERY_INTERRUPTED +# reap; +# select * from t1 /* must be the same as before (1,1),(2,2) */; +# +# insert select +# +connection con1; +--disable_warnings +drop table if exists t4; +--enable_warnings +create table t4 (a int, b int) engine=innodb; +insert into t4 values (3, 3); +begin; insert into t1 values (3, 3); connection con2; let $ID= `select connection_id()`; -send insert into t2 values (bug27563(),1); +begin; +send insert into t1 select * from t4 for update; connection con1; +--replace_result $ID ID eval kill query $ID; +rollback; connection con2; -# todo: remove 0 return after fixing Bug#27563 --error 0,ER_QUERY_INTERRUPTED reap; -select count(*) from t2 /* must be one */; -#show binlog events from 98 /* must have the insert on non-ta table */; -show master status /* must have the insert event more to FD */; -# the value of the error flag of KILLED_QUERY is tested further +# todo 1,2 above +rollback; +select * from t1 /* must be the same as before (1,1),(2,2) */; -connection con1; -select RELEASE_LOCK("a"); +drop table t4; # cleanup for the sub-case -### test with effective killing of SF() +### +## non-ta table case: killing must be recorded in binlog +### +create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */; -delete from t1; -delete from t2; -insert into t1 values (1,1); -insert into t2 values (1,1); +delimiter |; +create function bug27563(n int) +RETURNS int(11) +DETERMINISTIC +begin + if @b > 0 then + select get_lock("a", 20) into @a; + else + set @b= 1; + end if; + return n; +end| +delimiter ;| + +# +# update +# -# -# Bug#27565 -# test where KILL is propagated as error to the top level -# still another bug with the error message to the user -# todo: fix reexecute the result file after fixing -# -begin; update t1 set b=0 where a=1; +delete from t4; +insert into t4 values (1,1), (1,1); +reset master; +connection con1; +select get_lock("a", 20); connection con2; let $ID= `select connection_id()`; -send update t2 set b=bug27565()-1 where a=1; +set @b= 0; +send update t4 set b=b + bug27563(b); connection con1; +let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock'; +source include/wait_condition.inc; +select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock'; + +--replace_result $ID ID eval kill query $ID; -commit; connection con2; -# todo: fix Bug #27565 killed query of SF() is not reported correctly and -# remove 1105 (wrong) -#--error ER_QUERY_INTERRUPTED ---error 1105,ER_QUERY_INTERRUPTED -reap; ### pb: wrong error -select * from t1 /* must be: (1,0) */; -select * from t2 /* must be as before: (1,1) */; +--error ER_QUERY_INTERRUPTED +reap; +select * from t4 order by b /* must be (1,1), (1,2) */; +select @b /* must be 1 at the end of a stmt calling bug27563() */; +--echo must have the update query event more to FD +source include/show_binlog_events.inc; -## bug#22725 with effective and propagating killing -# -# top-level ta-table +# a proof the query is binlogged with an error + +--exec $MYSQL_BINLOG --start-position=106 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval select +(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR +let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`; +eval select $error_code /* must return 0 to mean the killed query is in */; + +# cleanup for the sub-case connection con1; -delete from t3; +select RELEASE_LOCK("a"); +--remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog + +# +# delete +# + +delete from t4; +insert into t4 values (1,1), (2,2); reset master; -begin; update t1 set b=0 where a=1; +connection con1; +select get_lock("a", 20); connection con2; let $ID= `select connection_id()`; -# the query won't perform completely since the function gets interrupted -send insert into t3 values (0,0),(1,bug27565()); +set @b= 0; +send delete from t4 where b=bug27563(1) or b=bug27563(2); connection con1; +let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock'; +source include/wait_condition.inc; +select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock'; +--replace_result $ID ID eval kill query $ID; -rollback; connection con2; -# todo: fix Bug #27565 killed query of SF() is not reported correctly and -# remove 1105 (wrong) -#--error ER_QUERY_INTERRUPTED ---error 1105,ER_QUERY_INTERRUPTED -reap; ### pb: wrong error -select count(*) from t3 /* must be zero */; -show master status /* nothing in binlog */; - -# top-level non-ta-table -connection con1; -delete from t2; -reset master; -begin; update t1 set b=0 where a=1; +--error ER_QUERY_INTERRUPTED +reap; +select count(*) from t4 /* must be 1 */; +select @b /* must be 1 at the end of a stmt calling bug27563() */; +--echo must have the delete query event more to FD +source include/show_binlog_events.inc; -connection con2; -let $ID= `select connection_id()`; -# the query won't perform completely since the function gets intrurrupted -send insert into t2 values (0,0),(1,bug27565()) /* non-ta t2 */; +# a proof the query is binlogged with an error + +--exec $MYSQL_BINLOG --start-position=106 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval select +(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR +let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`; +eval select $error_code /* must return 0 to mean the killed query is in */; +# cleanup for the sub-case connection con1; -eval kill query $ID; -rollback; +select RELEASE_LOCK("a"); +--remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog -connection con2; -# todo: fix Bug #27565 killed query of SF() is not reported correctly and -# remove 1105 (wrong) -#--error ER_QUERY_INTERRUPTED ---error 1105,ER_QUERY_INTERRUPTED -reap; ### pb: wrong error +drop table t4; -select count(*) from t2 /* count must be one */; -show master status /* insert into non-ta must be in binlog */; +# +# load data - see simulation tests +# + + +# bug#27571 cleanup drop function bug27563; -drop function bug27565; -} -system rm $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog ; + +# +# common cleanup +# drop table t1,t2,t3; +--echo end of the tests diff --git a/mysql-test/suite/binlog/t/binlog_killed_simulate-master.opt b/mysql-test/suite/binlog/t/binlog_killed_simulate-master.opt new file mode 100644 index 00000000000..90c70ecee29 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_killed_simulate-master.opt @@ -0,0 +1 @@ +--loose-debug=d,simulate_kill_bug27571 diff --git a/mysql-test/suite/binlog/t/binlog_killed_simulate.test b/mysql-test/suite/binlog/t/binlog_killed_simulate.test new file mode 100644 index 00000000000..2121a90dc8c --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_killed_simulate.test @@ -0,0 +1,69 @@ +-- source include/have_debug.inc +-- source include/have_binlog_format_mixed_or_statement.inc +# +# bug#27571 asynchronous setting mysql_$query()'s local error and +# Query_log_event::error_code +# + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +# +# Checking that killing upon successful row-loop does not affect binlogging +# + +create table t1 (a int) engine=MyISAM; +insert into t1 set a=1; +reset master; + +update t1 set a=2 /* will be "killed" after work has been done */; + +# a proof the query is binlogged with no error +#todo: introduce a suite private macro that provides numeric values +# for some constants like the offset of the first real event +# that is different between severs versions. +--exec $MYSQL_BINLOG --start-position=106 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval select +(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR +let $error_code= `select @a like "%#%error_code=0%" /* must return 1 */`; +eval select $error_code /* must return 1 as query completed before got killed*/; + +# cleanup for the sub-case +system rm $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog; + + +# +# Checking that killing inside of row-loop for LOAD DATA into +# non-transactional table affects binlogging +# + +create table t2 (a int, b int) ENGINE=MyISAM; +reset master; +--error ER_QUERY_INTERRUPTED +load data infile '../std_data_ln/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */; + + +# a proof the query is binlogged with an error + +source include/show_binlog_events.inc; + +--exec $MYSQL_BINLOG --start-position=98 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval select +(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) +is not null; +--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR +let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`; +eval select $error_code /* must return 0 to mean the killed query is in */; + +# cleanup for the sub-case +system rm $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog; + + +drop table t1,t2; + +--echo end of the tests diff --git a/mysql-test/suite/binlog/t/binlog_row_mix_innodb_myisam.test b/mysql-test/suite/binlog/t/binlog_row_mix_innodb_myisam.test index 7b7753a487e..3148cc50fd0 100644 --- a/mysql-test/suite/binlog/t/binlog_row_mix_innodb_myisam.test +++ b/mysql-test/suite/binlog/t/binlog_row_mix_innodb_myisam.test @@ -31,3 +31,5 @@ eval select @a like "%#%error_code=0%ROLLBACK/*!*/;%ROLLBACK /* added by mysqlbinlog */;%", @a not like "%#%error_code=%error_code=%"; drop table t1, t2; + +-- source extra/binlog_tests/mix_innodb_myisam_side_effects.test diff --git a/mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test b/mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test index 1815f3deb34..e7149e03b87 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test +++ b/mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test @@ -24,123 +24,9 @@ eval select drop table t1, t2; -# -# Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack -# bug #28960 non-trans temp table changes with insert .. select -# not binlogged after rollback -# -# testing appearence of insert into temp_table in binlog. -# There are two branches of execution that require different setup. +set @@session.binlog_format=statement; +-- source extra/binlog_tests/mix_innodb_myisam_side_effects.test +set @@session.binlog_format=@@global.binlog_format; -## send_eof() branch - -# prepare - -create table tt (a int unique); -create table ti (a int) engine=innodb; -reset master; -show master status; - -# action - -begin; -insert into ti values (1); -insert into ti values (2) ; -insert into tt select * from ti; -rollback; - -# check - -select count(*) from tt /* 2 */; -show master status; ---replace_column 2 # 5 # -show binlog events from 106; -select count(*) from ti /* zero */; -insert into ti select * from tt; -select * from ti /* that is what slave would miss - a bug */; - - -## send_error() branch -delete from ti; -delete from tt where a=1; -reset master; -show master status; - -# action - -begin; -insert into ti values (1); -insert into ti values (2) /* to make the dup error in the following */; ---error ER_DUP_ENTRY -insert into tt select * from ti /* one affected and error */; -rollback; - -# check - -show master status; ---replace_column 2 # 5 # -show binlog events from 106; -select count(*) from ti /* zero */; -insert into ti select * from tt; -select * from tt /* that is what otherwise slave missed - the bug */; - -drop table ti,tt; - - -# -# Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack -# -# Testing asserts: if there is a side effect of modifying non-transactional -# table thd->no_trans_update.stmt must be TRUE; -# the assert is active with debug build -# - ---disable_warnings -drop function if exists bug27417; -drop table if exists t1,t2; ---enable_warnings -# side effect table -CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; -# target tables -CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); - -delimiter |; -create function bug27417(n int) -RETURNS int(11) -begin - insert into t1 values (null); - return n; -end| -delimiter ;| - -reset master; - -# execute - -insert into t2 values (bug27417(1)); -insert into t2 select bug27417(2); -reset master; - ---error ER_DUP_ENTRY -insert into t2 values (bug27417(2)); -#TODO: Andrei: enable this test after 23333 is pushed -#show master status; /* only (!) with fixes for #23333 will show there is the query */; -select count(*) from t1 /* must be 3 */; - -reset master; -select count(*) from t2; -delete from t2 where a=bug27417(3); -select count(*) from t2 /* nothing got deleted */; -#TODO: Andrei: enable this test after 23333 is pushed -#show master status; /* the query must be in regardless of #23333 */; -select count(*) from t1 /* must be 5 */; - ---enable_info -delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */; ---disable_info -select count(*) from t1 /* must be 7 */; - -drop function bug27417; -drop table t1,t2; --echo end of tests diff --git a/mysql-test/suite/manual/r/rpl_replication_delay.result b/mysql-test/suite/manual/r/rpl_replication_delay.result new file mode 100644 index 00000000000..a8fa6ce8265 --- /dev/null +++ b/mysql-test/suite/manual/r/rpl_replication_delay.result @@ -0,0 +1,136 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +show slave status /* Second_behind reports 0 */;; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port 9306 +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 106 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 106 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master 0 +Master_SSL_Verify_Server_Cert No +Last_IO_Errno 0 +Last_IO_Error +Last_SQL_Errno 0 +Last_SQL_Error +drop table if exists t1; +Warnings: +Note 1051 Unknown table 't1' +create table t1 (f1 int); +flush logs /* contaminate rli->last_master_timestamp */; +lock table t1 write; +insert into t1 values (1); +show slave status /* bug emulated: reports slave threads starting time about 3*3 not 3 secs */;; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port 9306 +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 367 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 279 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master 9 +Master_SSL_Verify_Server_Cert No +Last_IO_Errno 0 +Last_IO_Error +Last_SQL_Errno 0 +Last_SQL_Error +unlock tables; +flush logs /* this time rli->last_master_timestamp is not affected */; +lock table t1 write; +insert into t1 values (2); +show slave status /* reports the correct diff with master query time about 3+3 secs */;; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port 9306 +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 455 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 367 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master 7 +Master_SSL_Verify_Server_Cert No +Last_IO_Errno 0 +Last_IO_Error +Last_SQL_Errno 0 +Last_SQL_Error +unlock tables; +drop table t1; diff --git a/mysql-test/suite/manual/t/rpl_replication_delay-slave.opt b/mysql-test/suite/manual/t/rpl_replication_delay-slave.opt new file mode 100644 index 00000000000..24a4c5952fe --- /dev/null +++ b/mysql-test/suite/manual/t/rpl_replication_delay-slave.opt @@ -0,0 +1 @@ +--loose-debug=d,let_first_flush_log_change_timestamp diff --git a/mysql-test/suite/manual/t/rpl_replication_delay.test b/mysql-test/suite/manual/t/rpl_replication_delay.test new file mode 100644 index 00000000000..8230698c8f9 --- /dev/null +++ b/mysql-test/suite/manual/t/rpl_replication_delay.test @@ -0,0 +1,71 @@ +# +# Testing replication delay reporting (bug#29309) +# there is an unavoidable non-determinism in the test +# please compare the results with the comments +# + + +source include/master-slave.inc; + +connection master; +#connection slave; +sync_slave_with_master; +--replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT +--replace_column 1 # 8 # 9 # 23 # +--query_vertical show slave status /* Second_behind reports 0 */; +sleep 3; + +### bug emulation + +connection master; +drop table if exists t1; +create table t1 (f1 int); +sleep 3; + +#connection slave; +sync_slave_with_master; +flush logs /* contaminate rli->last_master_timestamp */; + +connection slave; +lock table t1 write; + +connection master; +insert into t1 values (1); + +sleep 3; + +connection slave; +--replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT +--replace_column 1 # 8 # 9 # 23 # +--query_vertical show slave status /* bug emulated: reports slave threads starting time about 3*3 not 3 secs */; +unlock tables; + +connection master; +sync_slave_with_master; + +### bugfix + + +connection slave; +flush logs /* this time rli->last_master_timestamp is not affected */; +lock table t1 write; + +connection master; +insert into t1 values (2); +sleep 3; + +connection slave; +--replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT +--replace_column 1 # 8 # 9 # 23 # +--query_vertical show slave status /* reports the correct diff with master query time about 3+3 secs */; +unlock tables; + +connection master; +drop table t1; + +#connection slave; +sync_slave_with_master; + + +# End of tests + diff --git a/mysql-test/suite/rpl/data/rpl_bug28618.dat b/mysql-test/suite/rpl/data/rpl_bug28618.dat new file mode 100644 index 00000000000..b800c4dd39d --- /dev/null +++ b/mysql-test/suite/rpl/data/rpl_bug28618.dat @@ -0,0 +1,3 @@ +1|master only +2|master only +3|master only diff --git a/mysql-test/suite/rpl/include/rpl_mixed_check_select.inc b/mysql-test/suite/rpl/include/rpl_mixed_check_select.inc index 5d3b80e077b..b3e0cefbbd7 100644 --- a/mysql-test/suite/rpl/include/rpl_mixed_check_select.inc +++ b/mysql-test/suite/rpl/include/rpl_mixed_check_select.inc @@ -7,15 +7,15 @@ --echo ==========MASTER========== SELECT COUNT(*) FROM t1; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; SELECT COUNT(*) FROM t2; -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; sync_slave_with_master; --echo ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; SELECT COUNT(*) FROM t2; -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; connection master; diff --git a/mysql-test/suite/rpl/include/rpl_mixed_check_view.inc b/mysql-test/suite/rpl/include/rpl_mixed_check_view.inc index 43feedfe64a..a9f7ad8cd68 100644 --- a/mysql-test/suite/rpl/include/rpl_mixed_check_view.inc +++ b/mysql-test/suite/rpl/include/rpl_mixed_check_view.inc @@ -7,11 +7,11 @@ --echo ==========MASTER========== SHOW CREATE VIEW v1; -SELECT * FROM v1; +SELECT * FROM v1 ORDER BY a; sync_slave_with_master; --echo ==========SLAVE=========== USE test_rpl; SHOW CREATE VIEW v1; -SELECT * FROM v1; +SELECT * FROM v1 ORDER BY a; connection master; diff --git a/mysql-test/suite/rpl/include/rpl_mixed_dml.inc b/mysql-test/suite/rpl/include/rpl_mixed_dml.inc index a3ff022c43c..70db5b6cf83 100644 --- a/mysql-test/suite/rpl/include/rpl_mixed_dml.inc +++ b/mysql-test/suite/rpl/include/rpl_mixed_dml.inc @@ -54,7 +54,7 @@ DELETE FROM t2 WHERE a = 2; --exec cp ./suite/rpl/data/rpl_mixed.dat $MYSQLTEST_VARDIR/tmp/ LOAD DATA INFILE '../tmp/rpl_mixed.dat' INTO TABLE t1 FIELDS TERMINATED BY '|' ; --remove_file $MYSQLTEST_VARDIR/tmp/rpl_mixed.dat -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; --source suite/rpl/include/rpl_mixed_check_select.inc --source suite/rpl/include/rpl_mixed_clear_tables.inc @@ -75,7 +75,7 @@ DELETE FROM t1 WHERE a = 2; --echo --echo ******************** SELECT ******************** INSERT INTO t1 VALUES(1, 't1, text 1'); -SELECT * FROM t1 WHERE b <> UUID(); +SELECT * FROM t1 WHERE b <> UUID() ORDER BY a; --source suite/rpl/include/rpl_mixed_clear_tables.inc # JOIN @@ -85,8 +85,8 @@ INSERT INTO t1 VALUES(1, 'CCC'); INSERT INTO t1 VALUES(2, 'DDD'); INSERT INTO t2 VALUES(1, 'DDD'); INSERT INTO t2 VALUES(2, 'CCC'); -SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a; -SELECT * FROM t1 INNER JOIN t2 ON t1.b = t2.b; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a ORDER BY t1.a,t2.a; +SELECT * FROM t1 INNER JOIN t2 ON t1.b = t2.b ORDER BY t1.a,t2.a; --source suite/rpl/include/rpl_mixed_clear_tables.inc # UNION diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result index fe5a5b28682..1c21a80acb1 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result @@ -615,6 +615,66 @@ c1 c2 c3 c4 c5 c6 c7 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP +*** Create t14a on slave *** +STOP SLAVE; +RESET SLAVE; +CREATE TABLE t14a (c1 INT KEY, c4 BLOB, c5 CHAR(5), +c6 INT DEFAULT '1', +c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP +)ENGINE='InnoDB'; +*** Create t14a on Master *** +CREATE TABLE t14a (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) +) ENGINE='InnoDB'; +RESET MASTER; +*** Start Slave *** +START SLAVE; +*** Master Data Insert *** +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t14a () VALUES(1,@b1,'Kyle'), +(2,@b1,'JOE'), +(3,@b1,'QA'); +SELECT * FROM t14a ORDER BY c1; +c1 c4 c5 +1 b1b1b1b1b1b1b1b1 Kyle +2 b1b1b1b1b1b1b1b1 JOE +3 b1b1b1b1b1b1b1b1 QA +*** Select on Slave **** +SELECT * FROM t14a ORDER BY c1; +c1 c4 c5 c6 c7 +1 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP +2 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP +3 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP +STOP SLAVE; +RESET SLAVE; +*** Master Drop c5 *** +ALTER TABLE t14a DROP COLUMN c5; +RESET MASTER; +*** Start Slave *** +START SLAVE; +*** Master Data Insert *** +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t14a () VALUES(4,@b1), +(5,@b1), +(6,@b1); +SELECT * FROM t14a ORDER BY c1; +c1 c4 +1 b1b1b1b1b1b1b1b1 +2 b1b1b1b1b1b1b1b1 +3 b1b1b1b1b1b1b1b1 +4 b1b1b1b1b1b1b1b1 +5 b1b1b1b1b1b1b1b1 +6 b1b1b1b1b1b1b1b1 +*** Select on Slave **** +SELECT * FROM t14a ORDER BY c1; +c1 c4 c5 c6 c7 +1 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP +2 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP +3 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP +4 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP +5 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP +6 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP *** connect to master and drop columns *** ALTER TABLE t14 DROP COLUMN c2; ALTER TABLE t14 DROP COLUMN c4; @@ -707,7 +767,7 @@ Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1060 Last_SQL_Error Error 'Duplicate column name 'c6'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5' -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; *** Try to insert in master **** INSERT INTO t15 () VALUES(5,2.00,'Replication Testing',@b1,'Buda',2); @@ -723,6 +783,7 @@ c1 c2 c3 c4 c5 c6 c7 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP +5 2.00 Replication Testing b1b1b1b1b1b1b1b1 Buda 2 CURRENT_TIMESTAMP *** DROP TABLE t15 *** DROP TABLE t15; *** Create t16 on slave *** diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result index e70a2efaf29..db0eb81fb0f 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result @@ -615,6 +615,66 @@ c1 c2 c3 c4 c5 c6 c7 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP +*** Create t14a on slave *** +STOP SLAVE; +RESET SLAVE; +CREATE TABLE t14a (c1 INT KEY, c4 BLOB, c5 CHAR(5), +c6 INT DEFAULT '1', +c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP +)ENGINE='MyISAM'; +*** Create t14a on Master *** +CREATE TABLE t14a (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) +) ENGINE='MyISAM'; +RESET MASTER; +*** Start Slave *** +START SLAVE; +*** Master Data Insert *** +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t14a () VALUES(1,@b1,'Kyle'), +(2,@b1,'JOE'), +(3,@b1,'QA'); +SELECT * FROM t14a ORDER BY c1; +c1 c4 c5 +1 b1b1b1b1b1b1b1b1 Kyle +2 b1b1b1b1b1b1b1b1 JOE +3 b1b1b1b1b1b1b1b1 QA +*** Select on Slave **** +SELECT * FROM t14a ORDER BY c1; +c1 c4 c5 c6 c7 +1 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP +2 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP +3 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP +STOP SLAVE; +RESET SLAVE; +*** Master Drop c5 *** +ALTER TABLE t14a DROP COLUMN c5; +RESET MASTER; +*** Start Slave *** +START SLAVE; +*** Master Data Insert *** +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t14a () VALUES(4,@b1), +(5,@b1), +(6,@b1); +SELECT * FROM t14a ORDER BY c1; +c1 c4 +1 b1b1b1b1b1b1b1b1 +2 b1b1b1b1b1b1b1b1 +3 b1b1b1b1b1b1b1b1 +4 b1b1b1b1b1b1b1b1 +5 b1b1b1b1b1b1b1b1 +6 b1b1b1b1b1b1b1b1 +*** Select on Slave **** +SELECT * FROM t14a ORDER BY c1; +c1 c4 c5 c6 c7 +1 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP +2 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP +3 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP +4 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP +5 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP +6 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP *** connect to master and drop columns *** ALTER TABLE t14 DROP COLUMN c2; ALTER TABLE t14 DROP COLUMN c4; @@ -707,7 +767,7 @@ Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1060 Last_SQL_Error Error 'Duplicate column name 'c6'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5' -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; *** Try to insert in master **** INSERT INTO t15 () VALUES(5,2.00,'Replication Testing',@b1,'Buda',2); @@ -723,6 +783,7 @@ c1 c2 c3 c4 c5 c6 c7 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP +5 2.00 Replication Testing b1b1b1b1b1b1b1b1 Buda 2 CURRENT_TIMESTAMP *** DROP TABLE t15 *** DROP TABLE t15; *** Create t16 on slave *** diff --git a/mysql-test/suite/rpl/r/rpl_found_rows.result b/mysql-test/suite/rpl/r/rpl_found_rows.result new file mode 100644 index 00000000000..7e757a1d141 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_found_rows.result @@ -0,0 +1,233 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +==== 0. Setting it all up ==== +SET BINLOG_FORMAT=STATEMENT; +**** On Master **** +CREATE TABLE t1 (a INT); +CREATE TABLE logtbl (sect INT, test INT, count INT); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; +#### 1. Using statement mode #### +==== 1.1. Simple test ==== +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +a +7 +SELECT FOUND_ROWS() INTO @a; +INSERT INTO logtbl VALUES(1,1,@a); +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; +a +1 +SELECT FOUND_ROWS() INTO @a; +INSERT INTO logtbl VALUES(1,2,@a); +SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; +sect test count +1 1 183 +1 2 3 +**** On Slave **** +SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; +sect test count +1 1 183 +1 2 3 +==== 1.2. Stored procedure ==== +**** On Master **** +CREATE PROCEDURE calc_and_log(sect INT, test INT) BEGIN +DECLARE cnt INT; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; +SELECT FOUND_ROWS() INTO cnt; +INSERT INTO logtbl VALUES(sect,test,cnt); +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +SELECT FOUND_ROWS() INTO cnt; +INSERT INTO logtbl VALUES(sect,test+1,cnt); +END $$ +CALL calc_and_log(2,1); +a +1 +a +7 +CREATE PROCEDURE just_log(sect INT, test INT, found_rows INT) BEGIN +INSERT INTO logtbl VALUES (sect,test,found_rows); +END $$ +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +a +7 +SELECT FOUND_ROWS() INTO @found_rows; +CALL just_log(2,3,@found_rows); +SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; +sect test count +2 1 3 +2 2 183 +2 3 183 +**** On Slave **** +SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; +sect test count +2 1 3 +2 2 183 +2 3 183 +==== 1.3. Stored functions ==== +**** On Master **** +CREATE FUNCTION log_rows(sect INT, test INT, found_rows INT) +RETURNS INT +BEGIN +INSERT INTO logtbl VALUES(sect,test,found_rows); +RETURN found_rows; +END $$ +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +a +7 +SELECT FOUND_ROWS() INTO @found_rows; +SELECT log_rows(3,1,@found_rows), log_rows(3,2,@found_rows); +log_rows(3,1,@found_rows) log_rows(3,2,@found_rows) +183 183 +SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test; +sect test count +3 1 183 +3 2 183 +**** On Slave **** +SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test; +sect test count +3 1 183 +3 2 183 +==== 1.9. Cleanup ==== +**** On Master **** +DELETE FROM logtbl; +DROP PROCEDURE just_log; +DROP PROCEDURE calc_and_log; +DROP FUNCTION log_rows; +**** Resetting master and slave **** +STOP SLAVE; +RESET SLAVE; +RESET MASTER; +START SLAVE; +#### 2. Using mixed mode #### +==== 2.1. Checking a procedure ==== +**** On Master **** +SET BINLOG_FORMAT=MIXED; +CREATE PROCEDURE just_log(sect INT, test INT) BEGIN +INSERT INTO logtbl VALUES (sect,test,FOUND_ROWS()); +END $$ +**** On Master 1 **** +SET BINLOG_FORMAT=MIXED; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +a +7 +CALL just_log(1,1); +**** On Master **** +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +a +7 +CALL just_log(1,2); +**** On Master 1 **** +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; +a +1 +CALL just_log(1,3); +**** On Master **** +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +a +7 +CALL just_log(1,4); +SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; +sect test count +1 1 183 +1 2 183 +1 3 3 +1 4 183 +**** On Slave **** +SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; +sect test count +1 1 183 +1 2 183 +1 3 3 +1 4 183 +==== 2.1. Checking a stored function ==== +**** On Master **** +CREATE FUNCTION log_rows(sect INT, test INT) +RETURNS INT +BEGIN +DECLARE found_rows INT; +SELECT FOUND_ROWS() INTO found_rows; +INSERT INTO logtbl VALUES(sect,test,found_rows); +RETURN found_rows; +END $$ +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; +a +1 +SELECT log_rows(2,1), log_rows(2,2); +log_rows(2,1) log_rows(2,2) +3 3 +CREATE TABLE t2 (a INT, b INT); +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW +BEGIN +INSERT INTO logtbl VALUES (NEW.a, NEW.b, FOUND_ROWS()); +END $$ +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; +a +1 +INSERT INTO t2 VALUES (2,3), (2,4); +DROP TRIGGER t2_tr; +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW +BEGIN +DECLARE dummy INT; +SELECT log_rows(NEW.a, NEW.b) INTO dummy; +END $$ +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +a +7 +INSERT INTO t2 VALUES (2,5), (2,6); +DROP TRIGGER t2_tr; +CREATE PROCEDURE log_me_inner(sect INT, test INT) +BEGIN +DECLARE dummy INT; +SELECT log_rows(sect, test) INTO dummy; +SELECT log_rows(sect, test+1) INTO dummy; +END $$ +CREATE PROCEDURE log_me(sect INT, test INT) +BEGIN +CALL log_me_inner(sect,test); +END $$ +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW +BEGIN +CALL log_me(NEW.a, NEW.b); +END $$ +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +a +7 +INSERT INTO t2 VALUES (2,5), (2,6); +SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; +sect test count +2 1 3 +2 2 3 +2 3 3 +2 4 3 +2 5 183 +2 5 183 +2 6 183 +2 6 0 +2 6 183 +2 7 0 +SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; +sect test count +2 1 3 +2 2 3 +2 3 3 +2 4 3 +2 5 183 +2 5 183 +2 6 183 +2 6 0 +2 6 183 +2 7 0 +DROP TABLE t1, logtbl; +DROP PROCEDURE just_log; +DROP PROCEDURE log_me; +DROP PROCEDURE log_me_inner; +DROP FUNCTION log_rows; diff --git a/mysql-test/suite/rpl/r/rpl_idempotency.result b/mysql-test/suite/rpl/r/rpl_idempotency.result new file mode 100644 index 00000000000..f17fbd82c44 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_idempotency.result @@ -0,0 +1,71 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (-1),(-2),(-3); +INSERT INTO t2 VALUES (-1),(-2),(-3); +DELETE FROM t1 WHERE a = -2; +DELETE FROM t2 WHERE a = -2; +DELETE FROM t1 WHERE a = -2; +DELETE FROM t2 WHERE a = -2; +SELECT * FROM t1 ORDER BY a; +a +-3 +-1 +SELECT * FROM t2 ORDER BY a; +a +-3 +-1 +SELECT * FROM t1 ORDER BY a; +a +-3 +-1 +SELECT * FROM t2 ORDER BY a; +a +-3 +-1 +Last_SQL_Error +0 +INSERT IGNORE INTO t1 VALUES (-2); +INSERT IGNORE INTO t1 VALUES (-2); +SELECT * FROM t1 ORDER BY a; +a +-3 +-2 +-1 +SELECT * FROM t1 ORDER BY a; +a +-3 +-2 +-1 +Last_SQL_Error +0 +UPDATE t1 SET a = 1 WHERE a = -1; +UPDATE t2 SET a = 1 WHERE a = -1; +UPDATE t1 SET a = 1 WHERE a = -1; +UPDATE t2 SET a = 1 WHERE a = -1; +SELECT * FROM t1 ORDER BY a; +a +-3 +-2 +1 +SELECT * FROM t2 ORDER BY a; +a +-3 +1 +SELECT * FROM t1 ORDER BY a; +a +-3 +-2 +1 +SELECT * FROM t2 ORDER BY a; +a +-3 +1 +Last_SQL_Error +0 +DROP TABLE t1, t2; diff --git a/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result b/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result index fb2782ed9f4..c46b4016715 100644 --- a/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result +++ b/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result @@ -114,30 +114,30 @@ Create Table CREATE TABLE `byrange_tbl` ( `filler` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) SUBPARTITION BY HASH (id) SUBPARTITIONS 2 (PARTITION pa1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION pa2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION pa3 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION pa4 VALUES LESS THAN (40) ENGINE = InnoDB, PARTITION pa5 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION pa6 VALUES LESS THAN (60) ENGINE = InnoDB, PARTITION pa7 VALUES LESS THAN (70) ENGINE = InnoDB, PARTITION pa8 VALUES LESS THAN (80) ENGINE = InnoDB, PARTITION pa9 VALUES LESS THAN (90) ENGINE = InnoDB, PARTITION pa10 VALUES LESS THAN (100) ENGINE = InnoDB, PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ -show slave status; -Slave_IO_State Waiting for master to send event +SHOW SLAVE STATUS; +Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos 945470 -Relay_Log_File slave-relay-bin.000003 -Relay_Log_Pos 945616 +Relay_Log_File # +Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running Yes Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table -Replicate_Ignore_Table +Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 0 Last_Error Skip_Counter 0 Exec_Master_Log_Pos 945470 -Relay_Log_Space 945771 +Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 @@ -149,8 +149,8 @@ Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No -Last_IO_Errno 0 -Last_IO_Error +Last_IO_Errno # +Last_IO_Error # Last_SQL_Errno 0 Last_SQL_Error SELECT count(*) "Slave norm" FROM test.regular_tbl; diff --git a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result index 19c5299df25..0e11d132cc4 100644 --- a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result +++ b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result @@ -41,26 +41,26 @@ DELETE FROM t2 WHERE b <> UUID(); SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 2 t1, text 2 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 2 t1, text 2 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b DELETE FROM t1; DELETE FROM t2; @@ -76,13 +76,13 @@ DELETE FROM t2 WHERE a = 2; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 t1, text 1 SELECT COUNT(*) FROM t2; COUNT(*) 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 t2, text 1 ==========SLAVE=========== @@ -90,13 +90,13 @@ USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 t1, text 1 SELECT COUNT(*) FROM t2; COUNT(*) 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 t2, text 1 DELETE FROM t1; @@ -104,7 +104,7 @@ DELETE FROM t2; ******************** LOAD DATA INFILE ******************** LOAD DATA INFILE '../tmp/rpl_mixed.dat' INTO TABLE t1 FIELDS TERMINATED BY '|' ; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 10 line A 20 line B @@ -113,7 +113,7 @@ a b SELECT COUNT(*) FROM t1; COUNT(*) 3 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 10 line A 20 line B @@ -121,14 +121,14 @@ a b SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 3 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 10 line A 20 line B @@ -136,7 +136,7 @@ a b SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b DELETE FROM t1; DELETE FROM t2; @@ -153,35 +153,35 @@ DELETE FROM t1 WHERE a = 2; SELECT COUNT(*) FROM t1; COUNT(*) 2 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 t1, text 11 3 t1, text 33 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 2 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 t1, text 11 3 t1, text 33 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b DELETE FROM t1; DELETE FROM t2; ******************** SELECT ******************** INSERT INTO t1 VALUES(1, 't1, text 1'); -SELECT * FROM t1 WHERE b <> UUID(); +SELECT * FROM t1 WHERE b <> UUID() ORDER BY a; a b 1 t1, text 1 DELETE FROM t1; @@ -192,11 +192,11 @@ INSERT INTO t1 VALUES(1, 'CCC'); INSERT INTO t1 VALUES(2, 'DDD'); INSERT INTO t2 VALUES(1, 'DDD'); INSERT INTO t2 VALUES(2, 'CCC'); -SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a ORDER BY t1.a,t2.a; a b a b 1 CCC 1 DDD 2 DDD 2 CCC -SELECT * FROM t1 INNER JOIN t2 ON t1.b = t2.b; +SELECT * FROM t1 INNER JOIN t2 ON t1.b = t2.b ORDER BY t1.a,t2.a; a b a b 1 CCC 2 CCC 2 DDD 1 DDD @@ -219,50 +219,50 @@ INSERT INTO t1 VALUES(1, 't1, text 1'); SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 t1, text 1 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 t1, text 1 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b TRUNCATE t1; ==========MASTER========== SELECT COUNT(*) FROM t1; COUNT(*) 0 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 0 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b DELETE FROM t1; DELETE FROM t2; @@ -275,13 +275,13 @@ UPDATE t1 SET b = 't1, text 1 updated' WHERE a = 1; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 t1, text 1 updated SELECT COUNT(*) FROM t2; COUNT(*) 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 t2, text 1 ==========SLAVE=========== @@ -289,13 +289,13 @@ USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 t1, text 1 updated SELECT COUNT(*) FROM t2; COUNT(*) 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 t2, text 1 UPDATE t1, t2 SET t1.b = 'test', t2.b = 'test'; @@ -303,13 +303,13 @@ UPDATE t1, t2 SET t1.b = 'test', t2.b = 'test'; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test SELECT COUNT(*) FROM t2; COUNT(*) 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 test ==========SLAVE=========== @@ -317,13 +317,13 @@ USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test SELECT COUNT(*) FROM t2; COUNT(*) 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 test DELETE FROM t1; @@ -349,26 +349,26 @@ COMMIT; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 start SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 start SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b START TRANSACTION; INSERT INTO t1 VALUES (2, 'rollback'); @@ -377,26 +377,26 @@ ROLLBACK; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 start SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 start SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b START TRANSACTION; INSERT INTO t1 VALUES (3, 'before savepoint s1'); @@ -407,27 +407,27 @@ ROLLBACK TO SAVEPOINT s1; SELECT COUNT(*) FROM t1; COUNT(*) 2 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 start 3 before savepoint s1 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 start SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b START TRANSACTION; INSERT INTO t1 VALUES (5, 'before savepoint s2'); @@ -441,7 +441,7 @@ DELETE FROM t1 WHERE a = 7; SELECT COUNT(*) FROM t1; COUNT(*) 4 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 start 3 before savepoint s1 @@ -450,14 +450,14 @@ a b SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 4 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 start 3 before savepoint s1 @@ -466,7 +466,7 @@ a b SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b DELETE FROM t1; DELETE FROM t2; @@ -610,28 +610,28 @@ DELETE FROM t1 WHERE a = 202; SELECT COUNT(*) FROM t1; COUNT(*) 2 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 100 test 201 test SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 2 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 100 test 201 test SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ALTER PROCEDURE p1 COMMENT 'p1'; DROP PROCEDURE p1; @@ -649,13 +649,13 @@ INSERT INTO t1 VALUES (1, 'test'); SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test SELECT COUNT(*) FROM t2; COUNT(*) 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 test ==========SLAVE=========== @@ -663,13 +663,13 @@ USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test SELECT COUNT(*) FROM t2; COUNT(*) 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 test DELETE FROM t1; @@ -694,51 +694,51 @@ test_rpl e1 @ SYSTEM RECURRING NULL 1 # # NULL SLAVESIDE_DISABLED 1 latin1 latin SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test1 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test1 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========MASTER========== SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test1 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test1 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ALTER EVENT e1 RENAME TO e2; ==========MASTER========== @@ -754,26 +754,26 @@ test_rpl e2 @ SYSTEM RECURRING NULL 1 # # NULL SLAVESIDE_DISABLED 1 latin1 latin SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test1 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b ==========SLAVE=========== USE test_rpl; SELECT COUNT(*) FROM t1; COUNT(*) 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 test1 SELECT COUNT(*) FROM t2; COUNT(*) 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b DROP EVENT e2; ==========MASTER========== @@ -795,7 +795,7 @@ CREATE VIEW v2 AS SELECT * FROM t1 WHERE b <> UUID(); SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` = 1) latin1 latin1_swedish_ci -SELECT * FROM v1; +SELECT * FROM v1 ORDER BY a; a b 1 test1 ==========SLAVE=========== @@ -803,7 +803,7 @@ USE test_rpl; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` = 1) latin1 latin1_swedish_ci -SELECT * FROM v1; +SELECT * FROM v1 ORDER BY a; a b 1 test1 ALTER VIEW v1 AS SELECT * FROM t1 WHERE a = 2; @@ -811,7 +811,7 @@ ALTER VIEW v1 AS SELECT * FROM t1 WHERE a = 2; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` = 2) latin1 latin1_swedish_ci -SELECT * FROM v1; +SELECT * FROM v1 ORDER BY a; a b 2 test2 ==========SLAVE=========== @@ -819,7 +819,7 @@ USE test_rpl; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` = 2) latin1 latin1_swedish_ci -SELECT * FROM v1; +SELECT * FROM v1 ORDER BY a; a b 2 test2 DROP VIEW v1; diff --git a/mysql-test/suite/rpl/r/rpl_invoked_features.result b/mysql-test/suite/rpl/r/rpl_invoked_features.result index 3bcef762497..502bb040218 100644 --- a/mysql-test/suite/rpl/r/rpl_invoked_features.result +++ b/mysql-test/suite/rpl/r/rpl_invoked_features.result @@ -17,13 +17,13 @@ DROP EVENT IF EXISTS e11; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=myisam; INSERT INTO t1 VALUES (1,1,'1'); INSERT INTO t1 VALUES (2,2,UUID()); -CREATE TABLE t2 (a INT, b INT, c VARCHAR(64)) ENGINE=myisam; +CREATE TABLE t2 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=myisam; INSERT INTO t2 VALUES (1,1,'1'); INSERT INTO t2 VALUES (2,2,UUID()); CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=innodb; INSERT INTO t11 VALUES (1,1,'1'); INSERT INTO t11 VALUES (2,2,UUID()); -CREATE TABLE t12 (a INT, b INT, c VARCHAR(64)) ENGINE=innodb; +CREATE TABLE t12 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=innodb; INSERT INTO t12 VALUES (1,1,'1'); INSERT INTO t12 VALUES (2,2,UUID()); @@ -49,21 +49,15 @@ BEGIN UPDATE t12 SET c = ''; UPDATE t13 SET c = ''; END| -CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND ENABLE DO +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO BEGIN -DECLARE c INT; -SELECT a INTO c FROM t1 WHERE a < 11 ORDER BY a DESC LIMIT 1; -IF c = 7 THEN +ALTER EVENT e1 DISABLE; CALL p1(10, ''); -END IF; END| -CREATE EVENT e11 ON SCHEDULE EVERY 1 SECOND ENABLE DO +CREATE EVENT e11 ON SCHEDULE EVERY 1 SECOND DISABLE DO BEGIN -DECLARE c INT; -SELECT a INTO c FROM t11 WHERE a < 11 ORDER BY a DESC LIMIT 1; -IF c = 7 THEN +ALTER EVENT e11 DISABLE; CALL p11(10, ''); -END IF; END| CREATE FUNCTION f1 (x INT) RETURNS VARCHAR(64) BEGIN @@ -78,11 +72,11 @@ RETURN f1(x); END| CREATE PROCEDURE p1 (IN x INT, IN y VARCHAR(64)) BEGIN -INSERT INTO t1 VALUES (x,x,y); +INSERT IGNORE INTO t1 VALUES (x,x,y); END| CREATE PROCEDURE p11 (IN x INT, IN y VARCHAR(64)) BEGIN -INSERT INTO t11 VALUES (x,x,y); +INSERT IGNORE INTO t11 VALUES (x,x,y); END| CREATE TABLE t3 SELECT * FROM v1; @@ -110,6 +104,8 @@ INSERT INTO t11 VALUES(7,7,f2(7)); INSERT INTO t11 VALUES (103,103,''); SET GLOBAL EVENT_SCHEDULER = on; +ALTER EVENT e1 ENABLE; +ALTER EVENT e11 ENABLE; SET GLOBAL EVENT_SCHEDULER = off; SHOW TABLES LIKE 't%'; @@ -138,8 +134,8 @@ PROCEDURE p1 PROCEDURE p11 SELECT event_name, status FROM information_schema.events WHERE event_schema='test'; event_name status -e1 ENABLED -e11 ENABLED +e1 DISABLED +e11 DISABLED SELECT COUNT(*) FROM t1; COUNT(*) @@ -438,6 +434,8 @@ UPDATE t3 SET c=''; UPDATE t11 SET c=''; UPDATE t12 SET c=''; UPDATE t13 SET c=''; +ALTER TABLE t3 ORDER BY a; +ALTER TABLE t13 ORDER BY a; diff --git a/mysql-test/suite/rpl/r/rpl_packet.result b/mysql-test/suite/rpl/r/rpl_packet.result index 981c234d380..dd56eb0471c 100644 --- a/mysql-test/suite/rpl/r/rpl_packet.result +++ b/mysql-test/suite/rpl/r/rpl_packet.result @@ -27,6 +27,42 @@ STOP SLAVE; START SLAVE; CREATE TABLe `t1` (`f1` LONGTEXT) ENGINE=MyISAM; INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2048'); -SHOW STATUS LIKE 'Slave_running'; -Variable_name Value -Slave_running OFF +show slave status; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_MYPORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos # +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running No +Slave_SQL_Running # +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos # +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +Master_SSL_Verify_Server_Cert No +Last_IO_Errno 0 +Last_IO_Error +Last_SQL_Errno 0 +Last_SQL_Error diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result b/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result index 7dc9926522b..c3fd663fad8 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result @@ -242,3 +242,34 @@ a b 3 1 4 4 drop table t1,t2; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +**** On Master **** +SET SESSION BINLOG_FORMAT=ROW; +CREATE TABLE t1 (a INT PRIMARY KEY, b SET('master','slave')); +INSERT INTO t1 VALUES (1,'master,slave'), (2,'master,slave'); +**** On Slave **** +UPDATE t1 SET a = 5, b = 'slave' WHERE a = 1; +SELECT * FROM t1 ORDER BY a; +a b +2 master,slave +5 slave +**** On Master **** +UPDATE t1 SET a = 5, b = 'master' WHERE a = 1; +SELECT * FROM t1 ORDER BY a; +a b +2 master,slave +5 master +**** On Slave **** +Last_SQL_Error + +SELECT * FROM t1 ORDER BY a; +a b +2 master,slave +5 slave +DROP TABLE t1; +**** On Master **** +DROP TABLE t1; diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result index a6877b27b95..2efe3a3e486 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result @@ -415,4 +415,23 @@ a b c 2 4 8 3 6 9 99 99 99 +**** Test for BUG#31552 **** +**** On Master **** +DELETE FROM t1; +**** Resetting master and slave **** +STOP SLAVE; +RESET SLAVE; +RESET MASTER; +START SLAVE; +**** On Master **** +INSERT INTO t1 VALUES ('K','K'), ('L','L'), ('M','M'); +**** On Master **** +DELETE FROM t1 WHERE C1 = 'L'; +DELETE FROM t1; +SELECT COUNT(*) FROM t1 ORDER BY c1,c2; +COUNT(*) 0 +Last_SQL_Error +0 +SELECT COUNT(*) FROM t1 ORDER BY c1,c2; +COUNT(*) 0 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8; diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result index 4c6ec627db5..fc78abfbe2e 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result @@ -415,4 +415,23 @@ a b c 2 4 8 3 6 9 99 99 99 +**** Test for BUG#31552 **** +**** On Master **** +DELETE FROM t1; +**** Resetting master and slave **** +STOP SLAVE; +RESET SLAVE; +RESET MASTER; +START SLAVE; +**** On Master **** +INSERT INTO t1 VALUES ('K','K'), ('L','L'), ('M','M'); +**** On Master **** +DELETE FROM t1 WHERE C1 = 'L'; +DELETE FROM t1; +SELECT COUNT(*) FROM t1 ORDER BY c1,c2; +COUNT(*) 0 +Last_SQL_Error +0 +SELECT COUNT(*) FROM t1 ORDER BY c1,c2; +COUNT(*) 0 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8; diff --git a/mysql-test/suite/rpl/r/rpl_skip_error.result b/mysql-test/suite/rpl/r/rpl_skip_error.result index 525909387b3..ed4c4a6b3bb 100644 --- a/mysql-test/suite/rpl/r/rpl_skip_error.result +++ b/mysql-test/suite/rpl/r/rpl_skip_error.result @@ -29,8 +29,7 @@ select * from t1; a 1 2 -3 show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error -# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 786 # # master-bin.000001 Yes Yes 0 0 786 # None 0 No # No 0 0 +# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 851 # # master-bin.000001 Yes Yes 0 0 851 # None 0 No # No 0 0 drop table t1; diff --git a/mysql-test/suite/rpl/r/rpl_slave_skip.result b/mysql-test/suite/rpl/r/rpl_slave_skip.result index 8e492fe4732..f89fa34e319 100644 --- a/mysql-test/suite/rpl/r/rpl_slave_skip.result +++ b/mysql-test/suite/rpl/r/rpl_slave_skip.result @@ -142,3 +142,211 @@ Last_SQL_Errno 0 Last_SQL_Error **** On Master **** DROP TABLE t1, t2; +SET SESSION BINLOG_FORMAT=ROW; +SET AUTOCOMMIT=0; +CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam; +CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam; +CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam; +INSERT INTO t1 VALUES (1,'master/slave'); +INSERT INTO t2 VALUES (1,'master/slave'); +INSERT INTO t3 VALUES (1,'master/slave'); +CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW +BEGIN +INSERT INTO t2 VALUES (NEW.a,NEW.b); +DELETE FROM t2 WHERE a < NEW.a; +END| +CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW +BEGIN +UPDATE t3 SET a =2, b = 'master only'; +END| +**** On Slave **** +STOP SLAVE; +**** On Master **** +UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +INSERT INTO t1 VALUES (3,'master/slave'); +INSERT INTO t2 VALUES (3,'master/slave'); +INSERT INTO t3 VALUES (3,'master/slave'); +SELECT * FROM t1 ORDER BY a; +a b +2 master only +3 master/slave +SELECT * FROM t2 ORDER BY a; +a b +2 master only +3 master/slave +SELECT * FROM t3 ORDER BY a; +a b +2 master only +3 master/slave +*** On Slave *** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +SELECT * FROM t1 ORDER BY a; +a b +1 master/slave +3 master/slave +SELECT * FROM t2 ORDER BY a; +a b +1 master/slave +3 master/slave +SELECT * FROM t3 ORDER BY a; +a b +1 master/slave +3 master/slave +DROP TABLE t1, t2, t3; +**** Case 2: Row binlog format and transactional tables **** +*** On Master *** +CREATE TABLE t4 (a INT, b VARCHAR(20)) ENGINE=innodb; +CREATE TABLE t5 (a INT, b VARCHAR(20)) ENGINE=innodb; +CREATE TABLE t6 (a INT, b VARCHAR(20)) ENGINE=innodb; +**** On Slave **** +STOP SLAVE; +*** On Master *** +BEGIN; +INSERT INTO t4 VALUES (2, 'master only'); +INSERT INTO t5 VALUES (2, 'master only'); +INSERT INTO t6 VALUES (2, 'master only'); +COMMIT; +BEGIN; +INSERT INTO t4 VALUES (3, 'master/slave'); +INSERT INTO t5 VALUES (3, 'master/slave'); +INSERT INTO t6 VALUES (3, 'master/slave'); +COMMIT; +SELECT * FROM t4 ORDER BY a; +a b +2 master only +3 master/slave +SELECT * FROM t5 ORDER BY a; +a b +2 master only +3 master/slave +SELECT * FROM t6 ORDER BY a; +a b +2 master only +3 master/slave +*** On Slave *** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +SELECT * FROM t4 ORDER BY a; +a b +3 master/slave +SELECT * FROM t5 ORDER BY a; +a b +3 master/slave +SELECT * FROM t6 ORDER BY a; +a b +3 master/slave +**** On Slave **** +STOP SLAVE; +*** On Master *** +BEGIN; +INSERT INTO t4 VALUES (6, 'master only'); +INSERT INTO t5 VALUES (6, 'master only'); +INSERT INTO t6 VALUES (6, 'master only'); +COMMIT; +BEGIN; +INSERT INTO t4 VALUES (7, 'master only'); +INSERT INTO t5 VALUES (7, 'master only'); +INSERT INTO t6 VALUES (7, 'master only'); +COMMIT; +SELECT * FROM t4 ORDER BY a; +a b +2 master only +3 master/slave +6 master only +7 master only +SELECT * FROM t5 ORDER BY a; +a b +2 master only +3 master/slave +6 master only +7 master only +SELECT * FROM t6 ORDER BY a; +a b +2 master only +3 master/slave +6 master only +7 master only +*** On Slave *** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=10; +START SLAVE; +SELECT * FROM t4 ORDER BY a; +a b +3 master/slave +SELECT * FROM t5 ORDER BY a; +a b +3 master/slave +SELECT * FROM t6 ORDER BY a; +a b +3 master/slave +STOP SLAVE; +SET AUTOCOMMIT=0; +INSERT INTO t4 VALUES (4, 'master only'); +INSERT INTO t5 VALUES (4, 'master only'); +INSERT INTO t6 VALUES (4, 'master only'); +COMMIT; +INSERT INTO t4 VALUES (5, 'master/slave'); +INSERT INTO t5 VALUES (5, 'master/slave'); +INSERT INTO t6 VALUES (5, 'master/slave'); +COMMIT; +SELECT * FROM t4 ORDER BY a; +a b +2 master only +3 master/slave +4 master only +5 master/slave +6 master only +7 master only +SELECT * FROM t5 ORDER BY a; +a b +2 master only +3 master/slave +4 master only +5 master/slave +6 master only +7 master only +SELECT * FROM t6 ORDER BY a; +a b +2 master only +3 master/slave +4 master only +5 master/slave +6 master only +7 master only +*** On Slave *** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +SELECT * FROM t4 ORDER BY a; +a b +3 master/slave +5 master/slave +SELECT * FROM t5 ORDER BY a; +a b +3 master/slave +5 master/slave +SELECT * FROM t6 ORDER BY a; +a b +3 master/slave +5 master/slave +DROP TABLE t4, t5, t6; +**** Case 3: Statement logging format and LOAD DATA with non-transactional table **** +*** On Master *** +CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam; +*** On Slave *** +STOP SLAVE; +*** On Master *** +SET SESSION BINLOG_FORMAT=STATEMENT; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|'; +SELECT * FROM t10 ORDER BY a; +a b +1 master only +2 master only +3 master only +*** On Slave *** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +SELECT * FROM t10 ORDER BY a; +a b +DROP TABLE t10; diff --git a/mysql-test/suite/rpl/r/rpl_sp_effects.result b/mysql-test/suite/rpl/r/rpl_sp_effects.result index c2c44b06972..a39d189aa3a 100644 --- a/mysql-test/suite/rpl/r/rpl_sp_effects.result +++ b/mysql-test/suite/rpl/r/rpl_sp_effects.result @@ -235,4 +235,45 @@ drop table t1; drop function f1; drop function f2; drop procedure p1; +create table t2 (b BIT(7)); +create procedure sp_bug26199(bitvalue BIT(7)) +begin +insert into t2 set b = bitvalue; +end // +create function sf_bug26199(b BIT(7)) returns int +begin +insert into t2 values(b); +return 0; +end// +call sp_bug26199(b'1110'); +call sp_bug26199('\0'); +select sf_bug26199(b'1111111'); +sf_bug26199(b'1111111') +0 +select sf_bug26199(b'101111111'); +sf_bug26199(b'101111111') +0 +Warnings: +Warning 1264 Out of range value for column 'b' at row 1 +select sf_bug26199('\''); +sf_bug26199('\'') +0 +select hex(b) from t2; +hex(b) +E +0 +7F +7F +27 +select hex(b) from t2; +hex(b) +E +0 +7F +7F +27 +drop table t2; +drop procedure sp_bug26199; +drop function sf_bug26199; SET GLOBAL log_bin_trust_function_creators = 0; +end of the tests diff --git a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result index c3f0c07b92c..8ed9ff5dc2f 100644 --- a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result +++ b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result @@ -405,6 +405,26 @@ CREATE TABLE t12 (data LONG); LOCK TABLES t12 WRITE; INSERT INTO t12 VALUES(UUID()); UNLOCK TABLES; +CREATE FUNCTION my_user() +RETURNS CHAR(64) +BEGIN +DECLARE user CHAR(64); +SELECT USER() INTO user; +RETURN user; +END $$ +CREATE FUNCTION my_current_user() +RETURNS CHAR(64) +BEGIN +DECLARE user CHAR(64); +SELECT CURRENT_USER() INTO user; +RETURN user; +END $$ +DROP TABLE IF EXISTS t13; +CREATE TABLE t13 (data CHAR(64)); +INSERT INTO t13 VALUES (USER()); +INSERT INTO t13 VALUES (my_user()); +INSERT INTO t13 VALUES (CURRENT_USER()); +INSERT INTO t13 VALUES (my_current_user()); show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # drop database if exists mysqltest1 @@ -709,6 +729,30 @@ master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t12 master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t12 (data LONG) master-bin.000001 # Table_map # # table_id: # (mysqltest1.t12) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION my_user() +RETURNS CHAR(64) +BEGIN +DECLARE user CHAR(64); +SELECT USER() INTO user; +RETURN user; +END +master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION my_current_user() +RETURNS CHAR(64) +BEGIN +DECLARE user CHAR(64); +SELECT CURRENT_USER() INTO user; +RETURN user; +END +master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t13 +master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t13 (data CHAR(64)) +master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # drop database if exists mysqltest1 @@ -1013,5 +1057,29 @@ master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t12 master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t12 (data LONG) master-bin.000001 # Table_map # # table_id: # (mysqltest1.t12) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION my_user() +RETURNS CHAR(64) +BEGIN +DECLARE user CHAR(64); +SELECT USER() INTO user; +RETURN user; +END +master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION my_current_user() +RETURNS CHAR(64) +BEGIN +DECLARE user CHAR(64); +SELECT CURRENT_USER() INTO user; +RETURN user; +END +master-bin.000001 # Query # # use `mysqltest1`; DROP TABLE IF EXISTS t13 +master-bin.000001 # Query # # use `mysqltest1`; CREATE TABLE t13 (data CHAR(64)) +master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (mysqltest1.t13) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F drop database mysqltest1; set global binlog_format =@my_binlog_format; diff --git a/mysql-test/suite/rpl/r/rpl_temporary_errors.result b/mysql-test/suite/rpl/r/rpl_temporary_errors.result new file mode 100644 index 00000000000..5c681683b08 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_temporary_errors.result @@ -0,0 +1,81 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +**** On Master **** +SET SESSION BINLOG_FORMAT=ROW; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); +**** On Slave **** +SHOW STATUS LIKE 'Slave_retried_transactions'; +Variable_name Value +Slave_retried_transactions 0 +UPDATE t1 SET a = 5, b = 47 WHERE a = 1; +SELECT * FROM t1; +a b +5 47 +2 2 +3 3 +4 4 +**** On Master **** +UPDATE t1 SET a = 5, b = 5 WHERE a = 1; +SELECT * FROM t1; +a b +5 5 +2 2 +3 3 +4 4 +**** On Slave **** +SHOW STATUS LIKE 'Slave_retried_transactions'; +Variable_name Value +Slave_retried_transactions 0 +SELECT * FROM t1; +a b +5 47 +2 2 +3 3 +4 4 +SHOW SLAVE STATUS; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port MASTER_PORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos 408 +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table # +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos 408 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +Master_SSL_Verify_Server_Cert No +Last_IO_Errno # +Last_IO_Error # +Last_SQL_Errno 0 +Last_SQL_Error +DROP TABLE t1; +**** On Master **** +DROP TABLE t1; diff --git a/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result b/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result index c7ef28ba56b..7eee31dab7a 100644 --- a/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_truncate_2myisam.result @@ -4,6 +4,11 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=STATEMENT; SET GLOBAL BINLOG_FORMAT=STATEMENT; @@ -31,10 +36,17 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=MIXED; SET GLOBAL BINLOG_FORMAT=MIXED; @@ -62,10 +74,17 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=ROW; SET GLOBAL BINLOG_FORMAT=ROW; @@ -93,11 +112,18 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=STATEMENT; SET GLOBAL BINLOG_FORMAT=STATEMENT; @@ -125,10 +151,17 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) master-bin.000001 # Query # # use `test`; DELETE FROM t1 master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=MIXED; SET GLOBAL BINLOG_FORMAT=MIXED; @@ -156,10 +189,17 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) master-bin.000001 # Query # # use `test`; DELETE FROM t1 master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=ROW; SET GLOBAL BINLOG_FORMAT=ROW; @@ -188,9 +228,11 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; diff --git a/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result b/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result index 7ce48c2e983..a6580a5685b 100644 --- a/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_truncate_3innodb.result @@ -4,6 +4,11 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=STATEMENT; SET GLOBAL BINLOG_FORMAT=STATEMENT; @@ -31,12 +36,19 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=MIXED; SET GLOBAL BINLOG_FORMAT=MIXED; @@ -64,12 +76,19 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=ROW; SET GLOBAL BINLOG_FORMAT=ROW; @@ -97,6 +116,7 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F @@ -104,6 +124,12 @@ master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=STATEMENT; SET GLOBAL BINLOG_FORMAT=STATEMENT; @@ -131,12 +157,19 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; DELETE FROM t1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=MIXED; SET GLOBAL BINLOG_FORMAT=MIXED; @@ -164,12 +197,19 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1), (2,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; DELETE FROM t1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; +STOP SLAVE; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; **** On Master **** SET SESSION BINLOG_FORMAT=ROW; SET GLOBAL BINLOG_FORMAT=ROW; @@ -198,6 +238,7 @@ a b DROP TABLE t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F @@ -206,3 +247,4 @@ master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; DROP TABLE t1 +RESET MASTER; diff --git a/mysql-test/suite/rpl/t/disabled.def b/mysql-test/suite/rpl/t/disabled.def index 426bf93ae97..e9aeb31efe9 100644 --- a/mysql-test/suite/rpl/t/disabled.def +++ b/mysql-test/suite/rpl/t/disabled.def @@ -11,7 +11,7 @@ ############################################################################## rpl_ddl : BUG#26418 2007-03-01 mleich Slave out of sync after CREATE/DROP TEMPORARY TABLE + ROLLBACK on master -rpl_invoked_features : BUG#29020 2007-06-21 Lars Non-deterministic test case rpl_auto_increment_11932 : Bug#29809 2007-07-16 ingo Slave SQL errors in warnings file +rpl_stm_extraColmaster_ndb : WL#3915 : Statement-based replication not supported in ndb. Enable test when supported. rpl_extraColmaster_innodb : BUG#30854 : Tables name show as binary in slave err msg on vm-win2003-64-b and Solaris rpl_extraColmaster_myisam : BUG#30854 diff --git a/mysql-test/suite/rpl/t/rpl_bug31076.test b/mysql-test/suite/rpl/t/rpl_bug31076.test index cc8b26ac8f4..1c7b0ca0fd1 100644 --- a/mysql-test/suite/rpl/t/rpl_bug31076.test +++ b/mysql-test/suite/rpl/t/rpl_bug31076.test @@ -114,4 +114,5 @@ SELECT * FROM visits_events; # Cleanup DROP DATABASE track; +sync_slave_with_master; --echo End of 5.1 tests diff --git a/mysql-test/suite/rpl/t/rpl_dual_pos_advance.test b/mysql-test/suite/rpl/t/rpl_dual_pos_advance.test index 27c920a3186..83c15d691be 100644 --- a/mysql-test/suite/rpl/t/rpl_dual_pos_advance.test +++ b/mysql-test/suite/rpl/t/rpl_dual_pos_advance.test @@ -106,9 +106,3 @@ connection slave; sync_with_master; # End of 4.1 tests - -# Cleanup -# The A->B->A replication causes the master to start writing relay logs -# in var/run, remove them -remove_file $MYSQLTEST_VARDIR/run/master-relay-bin.000001; -remove_file $MYSQLTEST_VARDIR/run/master-relay-bin.index; diff --git a/mysql-test/suite/rpl/t/rpl_found_rows.test b/mysql-test/suite/rpl/t/rpl_found_rows.test new file mode 100644 index 00000000000..f868061c951 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_found_rows.test @@ -0,0 +1,256 @@ +source include/master-slave.inc; + +# It is not possible to replicate FOUND_ROWS() using statement-based +# replication, but there is a workaround that stores the result of +# FOUND_ROWS() into a user variable and then replicates this instead. + +# The purpose of this test case is to test that the workaround +# function properly even when inside stored programs (i.e., stored +# routines and triggers). + +--echo ==== 0. Setting it all up ==== + +SET BINLOG_FORMAT=STATEMENT; + +--echo **** On Master **** +connection master; +CREATE TABLE t1 (a INT); +CREATE TABLE logtbl (sect INT, test INT, count INT); + +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; +INSERT INTO t1 SELECT 2*a+3 FROM t1; + +--echo #### 1. Using statement mode #### + +--echo ==== 1.1. Simple test ==== + +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; + +# Instead of +# INSERT INTO logtbl VALUES(1, 1, FOUND_ROWS()); +# we write +SELECT FOUND_ROWS() INTO @a; +INSERT INTO logtbl VALUES(1,1,@a); + +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; +# Instead of +# INSERT INTO logtbl VALUES(1, 2, FOUND_ROWS()); +# we write +SELECT FOUND_ROWS() INTO @a; +INSERT INTO logtbl VALUES(1,2,@a); + +SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; +--echo **** On Slave **** +sync_slave_with_master; +SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; + +--echo ==== 1.2. Stored procedure ==== + +# Here we do both the calculation and the logging. We also do it twice +# to make sure that there are no limitations on how many times it can +# be used. + +--echo **** On Master **** +connection master; +--delimiter $$ +CREATE PROCEDURE calc_and_log(sect INT, test INT) BEGIN + DECLARE cnt INT; + SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; + SELECT FOUND_ROWS() INTO cnt; + INSERT INTO logtbl VALUES(sect,test,cnt); + SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; + SELECT FOUND_ROWS() INTO cnt; + INSERT INTO logtbl VALUES(sect,test+1,cnt); +END $$ +--delimiter ; + +CALL calc_and_log(2,1); + +--delimiter $$ +CREATE PROCEDURE just_log(sect INT, test INT, found_rows INT) BEGIN + INSERT INTO logtbl VALUES (sect,test,found_rows); +END $$ +--delimiter ; + +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +SELECT FOUND_ROWS() INTO @found_rows; +CALL just_log(2,3,@found_rows); + +SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; +--echo **** On Slave **** +sync_slave_with_master; +SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; + +--echo ==== 1.3. Stored functions ==== +--echo **** On Master **** +connection master; +--delimiter $$ +CREATE FUNCTION log_rows(sect INT, test INT, found_rows INT) + RETURNS INT +BEGIN + INSERT INTO logtbl VALUES(sect,test,found_rows); + RETURN found_rows; +END $$ +--delimiter ; + +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +SELECT FOUND_ROWS() INTO @found_rows; +SELECT log_rows(3,1,@found_rows), log_rows(3,2,@found_rows); + +SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test; +--echo **** On Slave **** +sync_slave_with_master; +SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test; + +--echo ==== 1.9. Cleanup ==== +--echo **** On Master **** +connection master; +DELETE FROM logtbl; +DROP PROCEDURE just_log; +DROP PROCEDURE calc_and_log; +DROP FUNCTION log_rows; +sync_slave_with_master; + +source include/reset_master_and_slave.inc; + +--echo #### 2. Using mixed mode #### + +--echo ==== 2.1. Checking a procedure ==== + +--echo **** On Master **** +connection master; +SET BINLOG_FORMAT=MIXED; + +# We will now check some stuff that will not work in statement-based +# replication, but which should cause the binary log to switch to +# row-based logging. + +--delimiter $$ +CREATE PROCEDURE just_log(sect INT, test INT) BEGIN + INSERT INTO logtbl VALUES (sect,test,FOUND_ROWS()); +END $$ +--delimiter ; +sync_slave_with_master; + +--echo **** On Master 1 **** +connection master1; +SET BINLOG_FORMAT=MIXED; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +CALL just_log(1,1); + +--echo **** On Master **** +connection master; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +CALL just_log(1,2); + +--echo **** On Master 1 **** + +connection master1; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; +CALL just_log(1,3); +sync_slave_with_master; + +--echo **** On Master **** +connection master; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +CALL just_log(1,4); +sync_slave_with_master; + +connection master; +SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; +--echo **** On Slave **** +sync_slave_with_master; +SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; + +--echo ==== 2.1. Checking a stored function ==== +--echo **** On Master **** +connection master; +--delimiter $$ +CREATE FUNCTION log_rows(sect INT, test INT) + RETURNS INT +BEGIN + DECLARE found_rows INT; + SELECT FOUND_ROWS() INTO found_rows; + INSERT INTO logtbl VALUES(sect,test,found_rows); + RETURN found_rows; +END $$ +--delimiter ; + +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; +SELECT log_rows(2,1), log_rows(2,2); + +CREATE TABLE t2 (a INT, b INT); + +# Trying with referencing FOUND_ROWS() directly in the trigger. + +--delimiter $$ +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW +BEGIN + INSERT INTO logtbl VALUES (NEW.a, NEW.b, FOUND_ROWS()); +END $$ +--delimiter ; + +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; +INSERT INTO t2 VALUES (2,3), (2,4); + +# Referencing FOUND_ROWS() indirectly. + +DROP TRIGGER t2_tr; + +--delimiter $$ +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW +BEGIN + DECLARE dummy INT; + SELECT log_rows(NEW.a, NEW.b) INTO dummy; +END $$ +--delimiter ; + +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +INSERT INTO t2 VALUES (2,5), (2,6); + +# Putting FOUND_ROWS() even lower in the call chain. + +connection master; +DROP TRIGGER t2_tr; + +--delimiter $$ +CREATE PROCEDURE log_me_inner(sect INT, test INT) +BEGIN + DECLARE dummy INT; + SELECT log_rows(sect, test) INTO dummy; + SELECT log_rows(sect, test+1) INTO dummy; +END $$ + +CREATE PROCEDURE log_me(sect INT, test INT) +BEGIN + CALL log_me_inner(sect,test); +END $$ +--delimiter ; + +--delimiter $$ +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW +BEGIN + CALL log_me(NEW.a, NEW.b); +END $$ +--delimiter ; + +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; +INSERT INTO t2 VALUES (2,5), (2,6); + +SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; +sync_slave_with_master; +SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; + +connection master; +DROP TABLE t1, logtbl; +DROP PROCEDURE just_log; +DROP PROCEDURE log_me; +DROP PROCEDURE log_me_inner; +DROP FUNCTION log_rows; +sync_slave_with_master; + diff --git a/mysql-test/suite/rpl/t/rpl_idempotency.test b/mysql-test/suite/rpl/t/rpl_idempotency.test new file mode 100644 index 00000000000..44956b7b459 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_idempotency.test @@ -0,0 +1,79 @@ +# Testing various forms of idempotency for replication that should +# work the same way under statement based as under row based. + +source include/master-slave.inc; + +connection master; +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (-1),(-2),(-3); +INSERT INTO t2 VALUES (-1),(-2),(-3); +sync_slave_with_master; + +# A delete for a row that does not exist, the statement is +# deliberately written to be idempotent for statement-based +# replication as well. We test this towards both a table with a +# primary key and without a primary key. + +connection slave; +DELETE FROM t1 WHERE a = -2; +DELETE FROM t2 WHERE a = -2; +connection master; +DELETE FROM t1 WHERE a = -2; +DELETE FROM t2 WHERE a = -2; +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t2 ORDER BY a; +sync_slave_with_master; +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t2 ORDER BY a; +let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); +disable_query_log; +eval SELECT "$last_error" AS Last_SQL_Error; +enable_query_log; + +# An insert of a row that already exists. Since we are replacing the +# row if it already exists, the most apropriate representation is +# INSERT IGNORE. We only test this towards a table with a primary key, +# since the other case does not make sense. + +INSERT IGNORE INTO t1 VALUES (-2); +connection master; +INSERT IGNORE INTO t1 VALUES (-2); +SELECT * FROM t1 ORDER BY a; +sync_slave_with_master; +SELECT * FROM t1 ORDER BY a; +let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); +disable_query_log; +eval SELECT "$last_error" AS Last_SQL_Error; +enable_query_log; + +# BUG#19958: RBR idempotency issue for UPDATE and DELETE + +# Statement-based and row-based replication have different behaviour +# when updating a row with an explicit WHERE-clause that matches +# exactly one row (or no row at all). For statement-based replication, +# the statement is idempotent since the first time it is executed, it +# will update exactly one row, and the second time it will not update +# any row at all. This was not the case for row-based replication, so +# we test under both row-based and statement-based replication both +# for tables with and without primary keys. + +connection slave; +UPDATE t1 SET a = 1 WHERE a = -1; +UPDATE t2 SET a = 1 WHERE a = -1; +connection master; +UPDATE t1 SET a = 1 WHERE a = -1; +UPDATE t2 SET a = 1 WHERE a = -1; +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t2 ORDER BY a; +sync_slave_with_master; +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t2 ORDER BY a; +let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); +disable_query_log; +eval SELECT "$last_error" AS Last_SQL_Error; +enable_query_log; + +connection master; +DROP TABLE t1, t2; +sync_slave_with_master; diff --git a/mysql-test/suite/rpl/t/rpl_innodb_bug28430.test b/mysql-test/suite/rpl/t/rpl_innodb_bug28430.test index 74954299920..eb828f07415 100644 --- a/mysql-test/suite/rpl/t/rpl_innodb_bug28430.test +++ b/mysql-test/suite/rpl/t/rpl_innodb_bug28430.test @@ -136,8 +136,7 @@ SELECT count(*) as "Master byrange" FROM test.byrange_tbl; --sync_slave_with_master connection slave; show create table test.byrange_tbl; ---replace_column 4 MASTER_PORT 33 # -show slave status; +source include/show_slave_status.inc; SELECT count(*) "Slave norm" FROM test.regular_tbl; SELECT count(*) "Slave bykey" FROM test.bykey_tbl; SELECT count(*) "Slave byrange" FROM test.byrange_tbl; diff --git a/mysql-test/suite/rpl/t/rpl_invoked_features.test b/mysql-test/suite/rpl/t/rpl_invoked_features.test index e797e0552ef..2e69c0fabd9 100644 --- a/mysql-test/suite/rpl/t/rpl_invoked_features.test +++ b/mysql-test/suite/rpl/t/rpl_invoked_features.test @@ -8,10 +8,9 @@ --source include/master-slave.inc --source include/have_innodb.inc - -# -# Define variables used by test case -# +# --disable_warnings/--enable_warnings added before/after query +# if one uses UUID() function because we need to avoid warnings +# for STATEMENT binlog format # Non-transactional engine --let $engine_type= myisam @@ -45,20 +44,24 @@ DROP EVENT IF EXISTS e11; --echo eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type; ---disable_warnings INSERT INTO t1 VALUES (1,1,'1'); +--disable_warnings INSERT INTO t1 VALUES (2,2,UUID()); -eval CREATE TABLE t2 (a INT, b INT, c VARCHAR(64)) ENGINE=$engine_type; +--enable_warnings +eval CREATE TABLE t2 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type; INSERT INTO t2 VALUES (1,1,'1'); +--disable_warnings INSERT INTO t2 VALUES (2,2,UUID()); --enable_warnings eval CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=$engine_type2; ---disable_warnings INSERT INTO t11 VALUES (1,1,'1'); +--disable_warnings INSERT INTO t11 VALUES (2,2,UUID()); -eval CREATE TABLE t12 (a INT, b INT, c VARCHAR(64)) ENGINE=$engine_type2; +--enable_warnings +eval CREATE TABLE t12 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=$engine_type2; INSERT INTO t12 VALUES (1,1,'1'); +--disable_warnings INSERT INTO t12 VALUES (2,2,UUID()); --enable_warnings @@ -96,22 +99,16 @@ BEGIN END| # Create events which will run every 1 sec -CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND ENABLE DO +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO BEGIN - DECLARE c INT; - SELECT a INTO c FROM t1 WHERE a < 11 ORDER BY a DESC LIMIT 1; - IF c = 7 THEN - CALL p1(10, ''); - END IF; + ALTER EVENT e1 DISABLE; + CALL p1(10, ''); END| -CREATE EVENT e11 ON SCHEDULE EVERY 1 SECOND ENABLE DO +CREATE EVENT e11 ON SCHEDULE EVERY 1 SECOND DISABLE DO BEGIN - DECLARE c INT; - SELECT a INTO c FROM t11 WHERE a < 11 ORDER BY a DESC LIMIT 1; - IF c = 7 THEN - CALL p11(10, ''); - END IF; + ALTER EVENT e11 DISABLE; + CALL p11(10, ''); END| # Create functions and procedures used for events @@ -130,12 +127,12 @@ END| CREATE PROCEDURE p1 (IN x INT, IN y VARCHAR(64)) BEGIN - INSERT INTO t1 VALUES (x,x,y); + INSERT IGNORE INTO t1 VALUES (x,x,y); END| CREATE PROCEDURE p11 (IN x INT, IN y VARCHAR(64)) BEGIN - INSERT INTO t11 VALUES (x,x,y); + INSERT IGNORE INTO t11 VALUES (x,x,y); END| DELIMITER ;| @@ -147,17 +144,24 @@ DELIMITER ;| # Do some actions for non-transactional tables --echo ---disable_warnings CREATE TABLE t3 SELECT * FROM v1; INSERT INTO t1 VALUES (3,3,''); UPDATE t1 SET c='2' WHERE a = 1; +--disable_warnings INSERT INTO t1 VALUES(4,4,f1(4)); +--enable_warnings INSERT INTO t1 VALUES (100,100,''); +--disable_warnings CALL p1(5, UUID()); +--enable_warnings INSERT INTO t1 VALUES (101,101,''); +--disable_warnings INSERT INTO t1 VALUES(6,6,f1(6)); +--enable_warnings INSERT INTO t1 VALUES (102,102,''); +--disable_warnings INSERT INTO t1 VALUES(7,7,f2(7)); +--enable_warnings INSERT INTO t1 VALUES (103,103,''); # Do some actions for transactional tables @@ -165,21 +169,34 @@ INSERT INTO t1 VALUES (103,103,''); CREATE TABLE t13 SELECT * FROM v11; INSERT INTO t11 VALUES (3,3,''); UPDATE t11 SET c='2' WHERE a = 1; +--disable_warnings INSERT INTO t11 VALUES(4,4,f1(4)); +--enable_warnings INSERT INTO t11 VALUES (100,100,''); +--disable_warnings CALL p11(5, UUID()); +--enable_warnings INSERT INTO t11 VALUES (101,101,''); +--disable_warnings INSERT INTO t11 VALUES(6,6,f1(6)); +--enable_warnings INSERT INTO t11 VALUES (102,102,''); +--disable_warnings INSERT INTO t11 VALUES(7,7,f2(7)); -INSERT INTO t11 VALUES (103,103,''); --enable_warnings +INSERT INTO t11 VALUES (103,103,''); # Scheduler is on --echo +# Temporally events fire sequentally due Bug#29020. SET GLOBAL EVENT_SCHEDULER = on; -# Wait 2 sec while events will executed ---sleep 2 +# Wait while events will executed +ALTER EVENT e1 ENABLE; +let $wait_condition= SELECT COUNT(*) = 1 FROM t1 WHERE t1.a = 10; +--source include/wait_condition.inc +ALTER EVENT e11 ENABLE; +let $wait_condition= SELECT COUNT(*) = 1 FROM t11 WHERE t11.a = 10; +--source include/wait_condition.inc SET GLOBAL EVENT_SCHEDULER = off; # Check original objects @@ -234,7 +251,7 @@ SELECT COUNT(*) FROM t13; SELECT a,b FROM t13 ORDER BY a; SELECT a,b FROM v11 ORDER BY a; -# Remove UUID() before comparing +# Remove UUID() before comparing and sort tables --connection master --echo @@ -245,6 +262,9 @@ UPDATE t11 SET c=''; UPDATE t12 SET c=''; UPDATE t13 SET c=''; +ALTER TABLE t3 ORDER BY a; +ALTER TABLE t13 ORDER BY a; + --sync_slave_with_master slave # Compare a data from master and slave @@ -260,13 +280,12 @@ UPDATE t13 SET c=''; # Remove dumps --echo ---exec rm $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql ---exec rm $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql +--remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_master.sql +--remove_file $MYSQLTEST_VARDIR/tmp/rpl_invoked_features_slave.sql # Remove tables,views,procedures,functions --connection master --echo ---disable_warnings DROP VIEW IF EXISTS v1,v11; DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13; DROP PROCEDURE IF EXISTS p1; @@ -275,7 +294,6 @@ DROP FUNCTION IF EXISTS f1; DROP FUNCTION IF EXISTS f2; DROP EVENT IF EXISTS e1; DROP EVENT IF EXISTS e11; ---enable_warnings --sync_slave_with_master slave diff --git a/mysql-test/suite/rpl/t/rpl_packet.test b/mysql-test/suite/rpl/t/rpl_packet.test index 316278cb75d..0e17ae3144c 100644 --- a/mysql-test/suite/rpl/t/rpl_packet.test +++ b/mysql-test/suite/rpl/t/rpl_packet.test @@ -66,16 +66,11 @@ CREATE TABLe `t1` (`f1` LONGTEXT) ENGINE=MyISAM; INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2048'); # The slave I/O thread must stop after trying to read the above event -connection slave; -sleep 2; ---source include/wait_for_slave_io_to_stop.inc -SHOW STATUS LIKE 'Slave_running'; - -# cleanup -#connection master; -#drop table t1; -#connection slave; -#drop table t1; - +connection slave; +--source include/wait_for_slave_io_to_stop.inc +--replace_result $MASTER_MYPORT MASTER_MYPORT +# import is only the 11th column Slave_IO_Running +--replace_column 1 # 7 # 8 # 9 # 12 # 22 # 23 # 33 # +query_vertical show slave status; # End of tests diff --git a/mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test b/mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test index fb43664f121..07fe763eb3c 100644 --- a/mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test +++ b/mysql-test/suite/rpl/t/rpl_row_basic_11bugs.test @@ -223,3 +223,38 @@ connection master; drop table t1,t2; sync_slave_with_master; + +# +# BUG#31702: Missing row on slave causes assertion failure under +# row-based replication +# + +disable_query_log; +source include/master-slave-reset.inc; +enable_query_log; + +--echo **** On Master **** +connection master; +SET SESSION BINLOG_FORMAT=ROW; +CREATE TABLE t1 (a INT PRIMARY KEY, b SET('master','slave')); +INSERT INTO t1 VALUES (1,'master,slave'), (2,'master,slave'); +--echo **** On Slave **** +sync_slave_with_master; +UPDATE t1 SET a = 5, b = 'slave' WHERE a = 1; +SELECT * FROM t1 ORDER BY a; +--echo **** On Master **** +connection master; +UPDATE t1 SET a = 5, b = 'master' WHERE a = 1; +SELECT * FROM t1 ORDER BY a; +--echo **** On Slave **** +sync_slave_with_master; +let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); +disable_query_log; +eval SELECT "$last_error" AS Last_SQL_Error; +enable_query_log; +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + +--echo **** On Master **** +connection master; +DROP TABLE t1; diff --git a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test index dd46d64f684..be76ac9f3f6 100644 --- a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test +++ b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test @@ -344,5 +344,6 @@ FLUSH LOGS; --exec rm $MYSQLTEST_VARDIR/tmp/local.sql DROP TABLE IF EXISTS t1, t2, t3, t04, t05, t4, t5; +sync_slave_with_master; # End of 4.1 tests diff --git a/mysql-test/suite/rpl/t/rpl_row_until.test b/mysql-test/suite/rpl/t/rpl_row_until.test index 9464e5cfadd..610eec305df 100644 --- a/mysql-test/suite/rpl/t/rpl_row_until.test +++ b/mysql-test/suite/rpl/t/rpl_row_until.test @@ -13,6 +13,8 @@ save_master_pos; connection slave; sync_with_master; stop slave; +# Make sure the slave sql and io thread has stopped +--source include/wait_for_slave_to_stop.inc connection master; # create some events on master @@ -52,6 +54,8 @@ save_master_pos; connection slave; sync_with_master; stop slave; +# Make sure the slave sql and io thread has stopped +--source include/wait_for_slave_to_stop.inc # this should stop immediately as we are already there start slave until master_log_file='master-bin.000001', master_log_pos=740; diff --git a/mysql-test/suite/rpl/t/rpl_skip_error-slave.opt b/mysql-test/suite/rpl/t/rpl_skip_error-slave.opt index c84171976a1..a8f5deaa30b 100644 --- a/mysql-test/suite/rpl/t/rpl_skip_error-slave.opt +++ b/mysql-test/suite/rpl/t/rpl_skip_error-slave.opt @@ -1 +1 @@ ---slave-skip-error=1062,1582 +--slave-skip-error=1062 diff --git a/mysql-test/suite/rpl/t/rpl_skip_error.test b/mysql-test/suite/rpl/t/rpl_skip_error.test index b68b637b3b0..72d434d9c9b 100644 --- a/mysql-test/suite/rpl/t/rpl_skip_error.test +++ b/mysql-test/suite/rpl/t/rpl_skip_error.test @@ -3,6 +3,14 @@ ######################################### # Note that errors are ignored by opt file. source include/master-slave.inc; +source include/have_binlog_format_mixed_or_statement.inc; + +# +# Bug #30594 +# Skipping error due to applying Row-based repliation events +# should be checked with another test file +# consider names like rpl_row_skip_error +# create table t1 (n int not null primary key); save_master_pos; diff --git a/mysql-test/suite/rpl/t/rpl_slave_skip-slave.opt b/mysql-test/suite/rpl/t/rpl_slave_skip-slave.opt new file mode 100644 index 00000000000..627becdbfb5 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_slave_skip-slave.opt @@ -0,0 +1 @@ +--innodb diff --git a/mysql-test/suite/rpl/t/rpl_slave_skip.test b/mysql-test/suite/rpl/t/rpl_slave_skip.test index b19d6a2730b..6783098fd7c 100644 --- a/mysql-test/suite/rpl/t/rpl_slave_skip.test +++ b/mysql-test/suite/rpl/t/rpl_slave_skip.test @@ -1,7 +1,9 @@ source include/master-slave.inc; +source include/have_innodb.inc; --echo **** On Slave **** connection slave; +source include/have_innodb.inc; STOP SLAVE; --echo **** On Master **** @@ -69,3 +71,240 @@ query_vertical SHOW SLAVE STATUS; connection master; DROP TABLE t1, t2; sync_slave_with_master; + +# +# More tests for BUG#28618 +# +# Case 1. +# ROW binlog format and non-transactional tables. +# Create the group of events via triggers and try to skip +# some items of that group. +# + +connection master; +SET SESSION BINLOG_FORMAT=ROW; +SET AUTOCOMMIT=0; + +CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam; +CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam; +CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam; + +INSERT INTO t1 VALUES (1,'master/slave'); +INSERT INTO t2 VALUES (1,'master/slave'); +INSERT INTO t3 VALUES (1,'master/slave'); + +DELIMITER |; + +CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW +BEGIN + INSERT INTO t2 VALUES (NEW.a,NEW.b); + DELETE FROM t2 WHERE a < NEW.a; +END| + +CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW +BEGIN + UPDATE t3 SET a =2, b = 'master only'; +END| + +DELIMITER ;| + +--echo **** On Slave **** +sync_slave_with_master; +STOP SLAVE; +source include/wait_for_slave_to_stop.inc; + +--echo **** On Master **** +connection master; +UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1; +DROP TRIGGER tr1; +DROP TRIGGER tr2; +INSERT INTO t1 VALUES (3,'master/slave'); +INSERT INTO t2 VALUES (3,'master/slave'); +INSERT INTO t3 VALUES (3,'master/slave'); + +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t2 ORDER BY a; +SELECT * FROM t3 ORDER BY a; + +save_master_pos; + +--echo *** On Slave *** +connection slave; +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +source include/wait_for_slave_to_start.inc; +sync_with_master; + +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t2 ORDER BY a; +SELECT * FROM t3 ORDER BY a; + +connection master; +DROP TABLE t1, t2, t3; +sync_slave_with_master; + +--echo **** Case 2: Row binlog format and transactional tables **** + +# Create the transaction and try to skip some +# queries from one. + +--echo *** On Master *** +connection master; +CREATE TABLE t4 (a INT, b VARCHAR(20)) ENGINE=innodb; +CREATE TABLE t5 (a INT, b VARCHAR(20)) ENGINE=innodb; +CREATE TABLE t6 (a INT, b VARCHAR(20)) ENGINE=innodb; + +--echo **** On Slave **** +sync_slave_with_master; +STOP SLAVE; +source include/wait_for_slave_to_stop.inc; + +--echo *** On Master *** +connection master; +BEGIN; +INSERT INTO t4 VALUES (2, 'master only'); +INSERT INTO t5 VALUES (2, 'master only'); +INSERT INTO t6 VALUES (2, 'master only'); +COMMIT; + +BEGIN; +INSERT INTO t4 VALUES (3, 'master/slave'); +INSERT INTO t5 VALUES (3, 'master/slave'); +INSERT INTO t6 VALUES (3, 'master/slave'); +COMMIT; + +SELECT * FROM t4 ORDER BY a; +SELECT * FROM t5 ORDER BY a; +SELECT * FROM t6 ORDER BY a; + +save_master_pos; + +--echo *** On Slave *** +connection slave; +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +source include/wait_for_slave_to_start.inc; +sync_with_master; + +SELECT * FROM t4 ORDER BY a; +SELECT * FROM t5 ORDER BY a; +SELECT * FROM t6 ORDER BY a; + +# Test skipping two groups + +--echo **** On Slave **** +connection slave; +STOP SLAVE; +source include/wait_for_slave_to_stop.inc; + +--echo *** On Master *** +connection master; +BEGIN; +INSERT INTO t4 VALUES (6, 'master only'); +INSERT INTO t5 VALUES (6, 'master only'); +INSERT INTO t6 VALUES (6, 'master only'); +COMMIT; + +BEGIN; +INSERT INTO t4 VALUES (7, 'master only'); +INSERT INTO t5 VALUES (7, 'master only'); +INSERT INTO t6 VALUES (7, 'master only'); +COMMIT; + +SELECT * FROM t4 ORDER BY a; +SELECT * FROM t5 ORDER BY a; +SELECT * FROM t6 ORDER BY a; + +save_master_pos; + +--echo *** On Slave *** +connection slave; +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=10; +START SLAVE; +source include/wait_for_slave_to_start.inc; +sync_with_master; + +SELECT * FROM t4 ORDER BY a; +SELECT * FROM t5 ORDER BY a; +SELECT * FROM t6 ORDER BY a; + +# +# And the same, but with autocommit = 0 +# +connection slave; +STOP SLAVE; +source include/wait_for_slave_to_stop.inc; + +connection master; +SET AUTOCOMMIT=0; + +INSERT INTO t4 VALUES (4, 'master only'); +INSERT INTO t5 VALUES (4, 'master only'); +INSERT INTO t6 VALUES (4, 'master only'); +COMMIT; + +INSERT INTO t4 VALUES (5, 'master/slave'); +INSERT INTO t5 VALUES (5, 'master/slave'); +INSERT INTO t6 VALUES (5, 'master/slave'); +COMMIT; + +SELECT * FROM t4 ORDER BY a; +SELECT * FROM t5 ORDER BY a; +SELECT * FROM t6 ORDER BY a; + +save_master_pos; + +--echo *** On Slave *** +connection slave; +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +source include/wait_for_slave_to_start.inc; +sync_with_master; + +SELECT * FROM t4 ORDER BY a; +SELECT * FROM t5 ORDER BY a; +SELECT * FROM t6 ORDER BY a; + +connection master; +DROP TABLE t4, t5, t6; +sync_slave_with_master; + +--echo **** Case 3: Statement logging format and LOAD DATA with non-transactional table **** + +# LOAD DATA creates two events in binary log for statement binlog format. +# Try to skip the first. + +--echo *** On Master *** +connection master; +CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam; + +--echo *** On Slave *** +sync_slave_with_master; +STOP SLAVE; +source include/wait_for_slave_to_stop.inc; + +--echo *** On Master *** +connection master; +SET SESSION BINLOG_FORMAT=STATEMENT; +exec cp ./suite/rpl/data/rpl_bug28618.dat $MYSQLTEST_VARDIR/tmp/; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|'; +remove_file $MYSQLTEST_VARDIR/tmp/rpl_bug28618.dat; + +SELECT * FROM t10 ORDER BY a; + +save_master_pos; + +--echo *** On Slave *** +connection slave; +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +source include/wait_for_slave_to_start.inc; +sync_with_master; + +SELECT * FROM t10 ORDER BY a; + +connection master; +DROP TABLE t10; +sync_slave_with_master; + diff --git a/mysql-test/suite/rpl/t/rpl_sp_effects.test b/mysql-test/suite/rpl/t/rpl_sp_effects.test index 027bfd69f36..c1092e3260f 100644 --- a/mysql-test/suite/rpl/t/rpl_sp_effects.test +++ b/mysql-test/suite/rpl/t/rpl_sp_effects.test @@ -201,6 +201,10 @@ sync_slave_with_master; connection slave; SELECT 'slave', a FROM t1 ORDER BY a; +# +# cleanup +# + connection master; drop table t1; drop function f1; @@ -208,4 +212,50 @@ drop function f2; drop procedure p1; sync_slave_with_master; +# +# bug#26199 Replication Failure on Slave when using stored procs +# with bit-type parameters + +connection master; + +create table t2 (b BIT(7)); +delimiter //; +create procedure sp_bug26199(bitvalue BIT(7)) +begin + insert into t2 set b = bitvalue; +end // + +create function sf_bug26199(b BIT(7)) returns int +begin + insert into t2 values(b); + return 0; +end// + +DELIMITER ;// + + + +call sp_bug26199(b'1110'); +call sp_bug26199('\0'); +select sf_bug26199(b'1111111'); +select sf_bug26199(b'101111111'); +select sf_bug26199('\''); +select hex(b) from t2; + +sync_slave_with_master; +#connection slave; +select hex(b) from t2; + +# +# cleanup bug#26199 +# +connection master; +drop table t2; +drop procedure sp_bug26199; +drop function sf_bug26199; + +sync_slave_with_master; + SET GLOBAL log_bin_trust_function_creators = 0; + +--echo end of the tests diff --git a/mysql-test/suite/rpl/t/rpl_stm_mystery22.test b/mysql-test/suite/rpl/t/rpl_stm_mystery22.test index 017593fdfba..b43a734fffc 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_mystery22.test +++ b/mysql-test/suite/rpl/t/rpl_stm_mystery22.test @@ -28,7 +28,7 @@ insert into t1 values(NULL,'new'); save_master_pos; connection slave; # wait until the slave tries to run the query, fails and aborts slave thread -wait_for_slave_to_stop; +source include/wait_for_slave_sql_error.inc; select * from t1 order by n; delete from t1 where n = 2; --disable_warnings diff --git a/mysql-test/suite/rpl/t/rpl_stm_until.test b/mysql-test/suite/rpl/t/rpl_stm_until.test index 98e7e0e5eac..c8d3cb1823d 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_until.test +++ b/mysql-test/suite/rpl/t/rpl_stm_until.test @@ -12,6 +12,8 @@ save_master_pos; connection slave; sync_with_master; stop slave; +# Make sure the slave sql and io thread has stopped +--source include/wait_for_slave_to_stop.inc connection master; # create some events on master @@ -51,6 +53,8 @@ save_master_pos; connection slave; sync_with_master; stop slave; +# Make sure the slave sql and io thread has stopped +--source include/wait_for_slave_to_stop.inc # this should stop immediately as we are already there start slave until master_log_file='master-bin.000001', master_log_pos=776; diff --git a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test index b0012827db8..05dcb91ca28 100644 --- a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test +++ b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test @@ -518,6 +518,42 @@ CREATE TABLE t12 (data LONG); LOCK TABLES t12 WRITE; INSERT INTO t12 VALUES(UUID()); UNLOCK TABLES; +sync_slave_with_master; + +# +# BUG#28086: SBR of USER() becomes corrupted on slave +# + +connection master; + +# Just to get something that is non-trivial, albeit still simple, we +# stuff the result of USER() and CURRENT_USER() into a variable. +--delimiter $$ +CREATE FUNCTION my_user() + RETURNS CHAR(64) +BEGIN + DECLARE user CHAR(64); + SELECT USER() INTO user; + RETURN user; +END $$ +--delimiter ; + +--delimiter $$ +CREATE FUNCTION my_current_user() + RETURNS CHAR(64) +BEGIN + DECLARE user CHAR(64); + SELECT CURRENT_USER() INTO user; + RETURN user; +END $$ +--delimiter ; + +DROP TABLE IF EXISTS t13; +CREATE TABLE t13 (data CHAR(64)); +INSERT INTO t13 VALUES (USER()); +INSERT INTO t13 VALUES (my_user()); +INSERT INTO t13 VALUES (CURRENT_USER()); +INSERT INTO t13 VALUES (my_current_user()); source include/show_binlog_events.inc; sync_slave_with_master; diff --git a/mysql-test/suite/rpl/t/rpl_temporary.test b/mysql-test/suite/rpl/t/rpl_temporary.test index 09b8b83f25f..852dfdbc25c 100644 --- a/mysql-test/suite/rpl/t/rpl_temporary.test +++ b/mysql-test/suite/rpl/t/rpl_temporary.test @@ -208,8 +208,9 @@ select * from t1; connection master; drop table t1; +--remove_file $MYSQLTEST_VARDIR/tmp/bug14157.sql # Delete the anonymous users source include/delete_anonymous_users.inc; -# End of 5.1 tests +# End of tests diff --git a/mysql-test/suite/rpl/t/rpl_temporary_errors-slave.opt b/mysql-test/suite/rpl/t/rpl_temporary_errors-slave.opt new file mode 100644 index 00000000000..80c171170f6 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_temporary_errors-slave.opt @@ -0,0 +1,3 @@ +--loose-debug="+d,all_errors_are_temporary_errors" --slave-transaction-retries=2 + + diff --git a/mysql-test/suite/rpl/t/rpl_temporary_errors.test b/mysql-test/suite/rpl/t/rpl_temporary_errors.test new file mode 100644 index 00000000000..6a57f3cc167 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_temporary_errors.test @@ -0,0 +1,27 @@ +source include/master-slave.inc; + +--echo **** On Master **** +connection master; +SET SESSION BINLOG_FORMAT=ROW; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); +--echo **** On Slave **** +sync_slave_with_master; +SHOW STATUS LIKE 'Slave_retried_transactions'; +UPDATE t1 SET a = 5, b = 47 WHERE a = 1; +SELECT * FROM t1; +--echo **** On Master **** +connection master; +UPDATE t1 SET a = 5, b = 5 WHERE a = 1; +SELECT * FROM t1; +#SHOW BINLOG EVENTS; +--echo **** On Slave **** +sync_slave_with_master; +SHOW STATUS LIKE 'Slave_retried_transactions'; +SELECT * FROM t1; +source include/show_slave_status.inc; +DROP TABLE t1; + +--echo **** On Master **** +connection master; +DROP TABLE t1; diff --git a/mysql-test/suite/rpl/t/rpl_trigger.test b/mysql-test/suite/rpl/t/rpl_trigger.test index 9f5f6fc9b4c..4a496ea4923 100644 --- a/mysql-test/suite/rpl/t/rpl_trigger.test +++ b/mysql-test/suite/rpl/t/rpl_trigger.test @@ -316,8 +316,13 @@ SELECT * FROM t2; # 2. Check that the trigger is non-SUID on the slave; # 3. Check that the trigger can be activated on the slave. +# +# We disable warnings here since it affects the result file in +# different ways depending on the mode being used. +disable_warnings; INSERT INTO t1 VALUES(2); +enable_warnings; SELECT * FROM t1; SELECT * FROM t2; diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result index 41e888827b1..d6d805daf70 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result @@ -615,6 +615,66 @@ c1 c2 c3 c4 c5 c6 c7 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle NULL CURRENT_TIMESTAMP 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE NULL CURRENT_TIMESTAMP 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA NULL CURRENT_TIMESTAMP +*** Create t14a on slave *** +STOP SLAVE; +RESET SLAVE; +CREATE TABLE t14a (c1 INT KEY, c4 BLOB, c5 CHAR(5), +c6 INT DEFAULT '1', +c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP +)ENGINE='NDB'; +*** Create t14a on Master *** +CREATE TABLE t14a (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) +) ENGINE='NDB'; +RESET MASTER; +*** Start Slave *** +START SLAVE; +*** Master Data Insert *** +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t14a () VALUES(1,@b1,'Kyle'), +(2,@b1,'JOE'), +(3,@b1,'QA'); +SELECT * FROM t14a ORDER BY c1; +c1 c4 c5 +1 b1b1b1b1b1b1b1b1 Kyle +2 b1b1b1b1b1b1b1b1 JOE +3 b1b1b1b1b1b1b1b1 QA +*** Select on Slave **** +SELECT * FROM t14a ORDER BY c1; +c1 c4 c5 c6 c7 +1 b1b1b1b1b1b1b1b1 Kyle NULL CURRENT_TIMESTAMP +2 b1b1b1b1b1b1b1b1 JOE NULL CURRENT_TIMESTAMP +3 b1b1b1b1b1b1b1b1 QA NULL CURRENT_TIMESTAMP +STOP SLAVE; +RESET SLAVE; +*** Master Drop c5 *** +ALTER TABLE t14a DROP COLUMN c5; +RESET MASTER; +*** Start Slave *** +START SLAVE; +*** Master Data Insert *** +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t14a () VALUES(4,@b1), +(5,@b1), +(6,@b1); +SELECT * FROM t14a ORDER BY c1; +c1 c4 +1 b1b1b1b1b1b1b1b1 +2 b1b1b1b1b1b1b1b1 +3 b1b1b1b1b1b1b1b1 +4 b1b1b1b1b1b1b1b1 +5 b1b1b1b1b1b1b1b1 +6 b1b1b1b1b1b1b1b1 +*** Select on Slave **** +SELECT * FROM t14a ORDER BY c1; +c1 c4 c5 c6 c7 +1 b1b1b1b1b1b1b1b1 Kyle NULL CURRENT_TIMESTAMP +2 b1b1b1b1b1b1b1b1 JOE NULL CURRENT_TIMESTAMP +3 b1b1b1b1b1b1b1b1 QA NULL CURRENT_TIMESTAMP +4 b1b1b1b1b1b1b1b1 NULL NULL CURRENT_TIMESTAMP +5 b1b1b1b1b1b1b1b1 NULL NULL CURRENT_TIMESTAMP +6 b1b1b1b1b1b1b1b1 NULL NULL CURRENT_TIMESTAMP *** connect to master and drop columns *** ALTER TABLE t14 DROP COLUMN c2; ALTER TABLE t14 DROP COLUMN c4; @@ -707,7 +767,7 @@ Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1060 Last_SQL_Error Error 'Duplicate column name 'c6'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5' -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; *** Try to insert in master **** INSERT INTO t15 () VALUES(5,2.00,'Replication Testing',@b1,'Buda',2); diff --git a/mysql-test/suite/rpl_ndb/r/rpl_row_basic_7ndb.result b/mysql-test/suite/rpl_ndb/r/rpl_row_basic_7ndb.result index 5519e0dcd0c..abd5bad8e49 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_row_basic_7ndb.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_row_basic_7ndb.result @@ -415,4 +415,23 @@ a b c 2 4 8 3 6 9 99 99 99 +**** Test for BUG#31552 **** +**** On Master **** +DELETE FROM t1; +**** Resetting master and slave **** +STOP SLAVE; +RESET SLAVE; +RESET MASTER; +START SLAVE; +**** On Master **** +INSERT INTO t1 VALUES ('K','K'), ('L','L'), ('M','M'); +**** On Master **** +DELETE FROM t1 WHERE C1 = 'L'; +DELETE FROM t1; +SELECT COUNT(*) FROM t1 ORDER BY c1,c2; +COUNT(*) 0 +Last_SQL_Error +0 +SELECT COUNT(*) FROM t1 ORDER BY c1,c2; +COUNT(*) 0 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8; diff --git a/mysql-test/suite/rpl_ndb/t/disabled.def b/mysql-test/suite/rpl_ndb/t/disabled.def index 05e8297bf0e..3832fe49cb9 100644 --- a/mysql-test/suite/rpl_ndb/t/disabled.def +++ b/mysql-test/suite/rpl_ndb/t/disabled.def @@ -15,8 +15,6 @@ rpl_ndb_2innodb : Bug#29549 rpl_ndb_myisam2ndb,rpl_ndb_innodb2ndb faile rpl_ndb_2myisam : Bug#29549 rpl_ndb_myisam2ndb,rpl_ndb_innodb2ndb failed on Solaris for pack_length issue rpl_ndb_2other : BUG#21842 2007-08-30 tsmith test has never worked on bigendian (sol10-sparc-a, powermacg5 rpl_ndb_dd_partitions : BUG#19259 2006-04-21 rpl_ndb_dd_partitions fails on s/AMD -rpl_ndb_innodb2ndb : Bug#29549 rpl_ndb_myisam2ndb,rpl_ndb_innodb2ndb failed on Solaris for pack_length issue -rpl_ndb_myisam2ndb : Bug#29549 rpl_ndb_myisam2ndb,rpl_ndb_innodb2ndb failed on Solaris for pack_length issue rpl_ndb_ddl : BUG#28798 2007-05-31 lars Valgrind failure in NDB rpl_ndb_mix_innodb : BUG#28123 rpl_ndb_mix_innodb.test casue slave to core on sol10-sparc-a rpl_ndb_ctype_ucs2_def : BUG#27404 util thd mysql_parse sig11 when mysqld default multibyte charset |