diff options
Diffstat (limited to 'mysql-test/suite/rpl')
51 files changed, 6409 insertions, 5 deletions
diff --git a/mysql-test/suite/rpl/include/start_alter_basic.inc b/mysql-test/suite/rpl/include/start_alter_basic.inc new file mode 100644 index 00000000000..f6e4b6b3253 --- /dev/null +++ b/mysql-test/suite/rpl/include/start_alter_basic.inc @@ -0,0 +1,60 @@ +# +# Run start alter basic tests (CA/RA with given engine) +# Params:- +# $engine +# $sync_slave +# master_node and slave_node connection should be defined + +--echo # $engine +--connection master_node +--eval create table t1(a int, b int) engine=$engine; +insert into t1 values(1,1); +insert into t1 values(2,2); +--echo # Normal Alter +alter table t1 add column c int; +show create table t1; +--echo # Failed Alter +insert into t1 values(1,1, NULL); +--error ER_DUP_ENTRY +alter table t1 change a a int unique; +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; + +# The following restriction should be removed post MDEV-26005 fix. +# TODO MDEV-26130 , should be removed after fixing +#if ($engine != 'aria') +#{ +#--eval create temporary table tmp_tbl(a int, b int) engine=$engine; +#insert into tmp_tbl values(1,1); +#insert into tmp_tbl values(2,2); +#--echo # Normal Alter +#alter table tmp_tbl add column c int; +#--echo # Failed Alter +#insert into tmp_tbl values(1,1, NULL); +#--error ER_DUP_ENTRY +#alter table tmp_tbl change a a int unique ; +#show create table tmp_tbl; +#} +if ($sync_slave) +{ + --source include/save_master_gtid.inc + --connection slave_node + --source include/sync_with_master_gtid.inc + show create table t1; +} + +--connection master_node +drop table t1; +#if ($engine != 'aria') +#{ +#drop temporary table tmp_tbl; +#} +if ($sync_slave) +{ + --source include/save_master_gtid.inc + --connection slave_node + --source include/sync_with_master_gtid.inc +} diff --git a/mysql-test/suite/rpl/include/start_alter_concurrent.inc b/mysql-test/suite/rpl/include/start_alter_concurrent.inc new file mode 100644 index 00000000000..e5472152807 --- /dev/null +++ b/mysql-test/suite/rpl/include/start_alter_concurrent.inc @@ -0,0 +1,230 @@ +# +# ==== Purpose ==== +# +# Run concurrent split alter on given storage engine +# With concurrent 10 connections +# +# ==== Usage ==== +# +# --let $alter_engine= Engine Name (myisam , innodb ...) +# --let $alter_algorithm= ... +# --let $alter_online = [][ONLINE] +# --let $domain_1= +# --let $domain_2= +# --let $M_port= //Master port +# --let $S_port= //Slave port +# --let $sync_slave= // 0/1 whether to sync slave with master or not +# + + +--connection master_node +set global debug_dbug="+d,start_alter_delay_master"; + +--let $i= 1 +while($i < 11) +{ + if($i == 1 && $domain_1) + { + --eval set gtid_domain_id= $domain_1; + } + if($i == 6 && $domain_2) + { + --eval set gtid_domain_id= $domain_2; + } + --eval create table t$i( a int primary key, b int) engine=$alter_engine + --eval insert into t$i values(1,1),(2,2) + --inc $i +} + + +if ($sync_slave) +{ + --echo # Sync slave + --source include/save_master_gtid.inc + --connection slave_node + --source include/sync_with_master_gtid.inc + --connection master_node +} + +--let $i= 1 +while($i < 21) +{ + if($i == 1 || $i == 11) + { + if($domain_1) + { + --eval set global gtid_domain_id= $domain_1; + } + } + if($i == 6 || $i == 16) + { + if($domain_2) + { + --eval set global gtid_domain_id= $domain_2; + } + } + connect(con$i,127.0.0.1,root,,$db_name, $M_port); + --inc $i +} + +--let $i= 1 +while($i < 11) +{ + --connection con$i + --send_eval alter $alter_online table t$i add column c int, force, algorithm=$alter_algorithm + --inc $i +} + +--connection master_node +set DEBUG_SYNC= "now signal alter_cont"; + +--let $i= 1 +while($i < 11) +{ + --connection con$i + --reap + --inc $i +} +--connection master_node +set DEBUG_SYNC= 'RESET'; + +if ($sync_slave) +{ + --echo # Sync slave + --source include/save_master_gtid.inc + --connection slave_node + --source include/sync_with_master_gtid.inc + --connection master_node +} + + +--echo # Concurrent DML +--let $i= 1 +while($i < 11) +{ + --connection con$i + --send_eval alter table t$i add column d int, force, algorithm=$alter_algorithm + --inc $i +} + +if ($alter_algorithm == "inplace") +{ + --sleep 1 + --let $i= 11 + --let $j= 1 + while($i < 21) + { + --connection con$i + --send_eval insert into t$j values(5,5,5); + --inc $i + --inc $j + } + + --let $i= 11 + while($i < 21) + { + --connection con$i + --reap + --inc $i + } + + if ($sync_slave) + { + --echo # Sync slave + --source include/save_master_gtid.inc + --connection slave_node + --source include/sync_with_master_gtid.inc + --connection master_node + } +} +--connection master_node +set DEBUG_SYNC= "now signal alter_cont"; +--let $i= 1 +while($i < 11) +{ + --connection con$i + --reap + --inc $i +} +--connection master_node +set DEBUG_SYNC= 'RESET'; + +if ($sync_slave) +{ + --echo # Sync slave + --source include/save_master_gtid.inc + --connection slave_node + --source include/sync_with_master_gtid.inc + --connection master_node +} + + +--echo # Rollback tests +--let $i= 1 +while($i < 11) +{ + --connection con$i + --eval insert into t$i values(3,2,1,1) + --send_eval alter table t$i change b b int unique, force, algorithm=$alter_algorithm + --inc $i +} +--connection master_node +set DEBUG_SYNC= "now signal alter_cont"; + + +--let $i= 1 +while ($i < 11) +{ + --connection con$i + --error ER_DUP_ENTRY + --reap + --inc $i +} +--connection master_node +set DEBUG_SYNC= 'RESET'; + + +if ($sync_slave) +{ + --echo # Sync slave + --source include/save_master_gtid.inc + --connection slave_node + --source include/sync_with_master_gtid.inc + --connection master_node +} + +if ($sync_slave) +{ + --enable_query_log + --echo # diff_table of master and slave , we will do only in the case when + --echo # sync_slave is on + --let $i= 1 + while($i < 11) + { + --let $diff_tables= master_node:t$i, slave_node:t$i + source include/diff_tables.inc; + --inc $i + } + --disable_query_log +} + +--connection master_node +drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; + +if ($sync_slave) +{ + --echo # Sync slave + --source include/save_master_gtid.inc + --connection slave_node + --source include/sync_with_master_gtid.inc + --connection master_node +} + +--let $i= 1 +while ($i < 21) +{ + --disconnect con$i + --inc $i +} +--connection master_node +SET GLOBAL debug_dbug= ""; diff --git a/mysql-test/suite/rpl/include/start_alter_include.inc b/mysql-test/suite/rpl/include/start_alter_include.inc new file mode 100644 index 00000000000..c66075c0164 --- /dev/null +++ b/mysql-test/suite/rpl/include/start_alter_include.inc @@ -0,0 +1,64 @@ +# +# ==== Purpose ==== +# +# Run concurrent split alter on different storage engine and with sync to given +# master +# +# ==== Usage ==== +# --let $domain_1= +# --let $domain_2= +# --let $M_port= //Master port +# --let $S_port= //Slave port +# --let $sync_slave= +# --let $db_name= +# + +connect(master_node,127.0.0.1,root,,$db_name, $M_port); +if (!$sync_slave) +{ + --eval set gtid_domain_id= $domain_1; +} +connect(slave_node,127.0.0.1,root,,test, $S_port); +if (!$sync_slave) +{ + --eval set gtid_domain_id= $domain_2; +} + +--let $engine=myisam +--source include/start_alter_basic.inc + +--let $engine=innodb +--source include/start_alter_basic.inc + +--let $engine=aria +--source include/start_alter_basic.inc + +--disable_query_log +--disable_warnings +--connection master_node +--echo # concurrent alter Myisam +--let $alter_engine=myisam +--let $alter_algorithm=copy +--source include/start_alter_concurrent.inc + +--connection master_node +--echo # concurrent alter Aria +--let $alter_engine=aria +--let $alter_algorithm=copy +--source include/start_alter_concurrent.inc + +--connection master_node +--echo # concurrent alter Innodb copy +--let $alter_engine=innodb +--let $alter_algorithm=copy +--source include/start_alter_concurrent.inc + +--connection master_node +--echo # concurrent alter Innodb Inplace +--let $alter_engine=innodb +--let $alter_algorithm=inplace +--source include/start_alter_concurrent.inc +--disconnect master_node +--disconnect slave_node +--enable_warnings +--enable_query_log diff --git a/mysql-test/suite/rpl/include/start_alter_options.inc b/mysql-test/suite/rpl/include/start_alter_options.inc new file mode 100644 index 00000000000..72c7104b114 --- /dev/null +++ b/mysql-test/suite/rpl/include/start_alter_options.inc @@ -0,0 +1,399 @@ +# This test will test all the option related to the Alter Table command +# NOTE not all alter statements will follow alter_algorithm since for some statements +# copy is only option +# parameters +# $alter_algorithm {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT} +# $show_binlog +# +--let $force_needed= force , +# Error that is caused by a particular ALTER's option combination +--let $alter_error = 0 + +--connection slave +stop slave; +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +change master to master_use_gtid= current_pos; +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +start slave; +--connection master +set binlog_alter_two_phase=true; +create table t1(a int , b int) engine=innodb; +create table a1(a int , b int) engine=myisam; +create temporary table tmp_tbl(a int, b int) engine=innodb; + +# These are grammer rules for ALTER TABLE we will got through all alter table +# rules in this test. +# | ADD [COLUMN] [IF NOT EXISTS] col_name column_definition +# [FIRST | AFTER col_name ] +# | ADD [COLUMN] [IF NOT EXISTS] (col_name column_definition,...) +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED +} +--error 0,$alter_error +--eval alter table t1 add column if not exists c int , $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED +} +--error 0,$alter_error +--eval alter table t1 add column d int first, $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_BAD_FIELD_ERROR +} +--error 0,$alter_error +--eval alter table t1 add column e int after c, $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_BAD_FIELD_ERROR +} +--error 0,$alter_error +--eval alter table t1 add column f int after c, $force_needed add column g int first ,add column h char, algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, $force_needed algorithm=$alter_algorithm +if ($alter_algorithm == 'copy') +{ +--eval alter table tmp_tbl add column if not exists c int , $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl add column d int first, $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl add column e int after c, $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl add column f int after c, $force_needed add column g int first ,add column h char, algorithm=$alter_algorithm +--eval alter table tmp_tbl drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, $force_needed algorithm=$alter_algorithm +} +--echo # show binlog and clear status +--sync_slave_with_master +if ($show_binlog) +{ + --source include/show_binlog_events.inc +} +reset master; +--connection master + + +# +# | ADD {INDEX|KEY} [IF NOT EXISTS] [index_name] +# [index_type] (index_col_name,...) [index_option] ... +# | ADD [CONSTRAINT [symbol]] +# UNIQUE [INDEX|KEY] [index_name] +# [index_type] (index_col_name,...) [index_option] ... +# +# | ADD FULLTEXT [INDEX|KEY] [index_name] +# (index_col_name,...) [index_option] ... +# | DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE] +# | DROP PRIMARY KEY +# | DROP {INDEX|KEY} [IF EXISTS] index_name +# | DROP FOREIGN KEY [IF EXISTS] fk_symbol +# | DROP CONSTRAINT [IF EXISTS] constraint_name +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED +} +--error 0,$alter_error +--eval alter table t1 add column f int after b, $force_needed add column g int first ,add column h varchar(100), algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST +} +--error 0,$alter_error +--eval alter table t1 add index if not exists index_1(f), $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop index index_1, $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST +} +--error 0,$alter_error +--eval alter table t1 add unique key unique_1(g), $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop index unique_1, $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST +} +--error 0,$alter_error +--eval alter table t1 add fulltext key f_1(h), $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop column f, drop column g , $force_needed algorithm=$alter_algorithm +if ($alter_algorithm == 'copy') +{ +--eval alter table tmp_tbl add column f int after b, $force_needed add column g int first ,add column h varchar(100), algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST +} +--error 0,$alter_error +--eval alter table tmp_tbl add index if not exists index_1(f), $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl drop index index_1, $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl add unique key unique_1(g), $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl drop index unique_1, $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl drop column f, drop column g , $force_needed algorithm=$alter_algorithm +} +# | ADD [CONSTRAINT [symbol]] PRIMARY KEY +# [index_type] (index_col_name,...) [index_option] ... +# primary key changes cant use inplace algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST +} +--error 0,$alter_error +--eval alter table t1 add primary key(h), $force_needed algorithm=copy +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop primary key, $force_needed algorithm=copy +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop column h, $force_needed algorithm=copy +if ($alter_algorithm == 'copy') +{ +--eval alter table tmp_tbl add primary key(h), $force_needed algorithm=copy +--eval alter table tmp_tbl drop primary key, $force_needed algorithm=copy +--eval alter table tmp_tbl drop column h, $force_needed algorithm=copy +} +--echo # show binlog and clear status +--sync_slave_with_master +if ($show_binlog) +{ + --source include/show_binlog_events.inc +} +reset master; +--connection master + +## NOTE force_needed and algorithm will not be used for system versioning +# | ADD PERIOD FOR SYSTEM_TIME (start_column_name, end_column_name) +# | ADD SYSTEM VERSIONING +# | DROP SYSTEM VERSIONING +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED +} +--error 0,$alter_error +--eval alter table t1 add column f varchar(100) after b, add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm +--eval alter table t1 add period for system_time(f,h) +--eval alter table t1 add system versioning +--eval alter table t1 drop system versioning +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop column f, drop column g , drop column h, $force_needed algorithm=$alter_algorithm +--echo # show binlog and clear status +if ($alter_algorithm == 'copy') +{ +--eval alter table tmp_tbl add column f varchar(100) after b, add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl drop column f, drop column g , drop column h, $force_needed algorithm=$alter_algorithm +} +--sync_slave_with_master +if ($show_binlog) +{ + --source include/show_binlog_events.inc +} +reset master; +--connection master + + +# | ALTER [COLUMN] col_name SET DEFAULT literal | (expression) +# | ALTER [COLUMN] col_name DROP DEFAULT +# | CHANGE [COLUMN] [IF EXISTS] old_col_name new_col_name column_definition +# [FIRST|AFTER col_name] +# | MODIFY [COLUMN] [IF EXISTS] col_name column_definition +# [FIRST | AFTER col_name] +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED +} +--error 0,$alter_error +--eval alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm ; +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_BAD_FIELD_ERROR +} +--error 0,$alter_error +--eval alter table t1 alter column f set default "****", $force_needed algorithm=$alter_algorithm ; +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_BAD_FIELD_ERROR +} +--error 0,$alter_error +--eval alter table t1 alter column f drop default, $force_needed algorithm=$alter_algorithm ; +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_BAD_FIELD_ERROR +} +--error 0,$alter_error +--eval alter table t1 change column g new_g char, $force_needed algorithm=copy; +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_BAD_FIELD_ERROR +} +--error 0,$alter_error +--eval alter table t1 modify column h varchar(100), $force_needed algorithm=copy; +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop column new_g ,drop column f, drop column h, $force_needed algorithm=$alter_algorithm ; +if ($alter_algorithm == 'copy') +{ +--eval alter table tmp_tbl add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm ; +--eval alter table tmp_tbl alter column f set default "****", $force_needed algorithm=$alter_algorithm ; +--eval alter table tmp_tbl alter column f drop default, $force_needed algorithm=$alter_algorithm ; +--eval alter table tmp_tbl change column g new_g char, $force_needed algorithm=copy; +--eval alter table tmp_tbl modify column h varchar(100), $force_needed algorithm=copy; +--eval alter table tmp_tbl drop column new_g ,drop column f, drop column h, $force_needed algorithm=$alter_algorithm ; +} +--echo # show binlog and clear status +--sync_slave_with_master +if ($show_binlog) +{ + --source include/show_binlog_events.inc +} +reset master; +--connection master + +# | DISABLE KEYS +# | ENABLE KEYS +# | RENAME [TO] new_tbl_name +# | ORDER BY col_name [, col_name] ... +# | RENAME COLUMN old_col_name TO new_col_name +# | RENAME {INDEX|KEY} old_index_name TO new_index_name +# | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] +# | [DEFAULT] CHARACTER SET [=] charset_name +# | [DEFAULT] COLLATE [=] collation_name +# | DISCARD TABLESPACE +# | IMPORT TABLESPACE +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED +} +--error 0,$alter_error +--eval alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST +} +--error 0,$alter_error +--eval alter table t1 add index if not exists index_1(f), $force_needed algorithm=$alter_algorithm +--eval alter table t1 disable keys, $force_needed algorithm=copy +--eval alter table t1 enable keys, $force_needed algorithm=copy +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED +} +--error 0,$alter_error +--eval alter table t1 rename t2, $force_needed algorithm=$alter_algorithm +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_NO_SUCH_TABLE +} +--error 0,$alter_error +--eval alter table t2 rename t1, $force_needed algorithm=$alter_algorithm +--eval alter table a1 order by a +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_BAD_FIELD_ERROR +} +--error 0,$alter_error +--eval alter table t1 rename column f to new_f, $force_needed algorithm=copy +--eval alter table t1 convert to character set 'utf8', $force_needed algorithm=copy +--eval alter table t1 default character set 'utf8', $force_needed algorithm=copy +--eval alter table t1 default collate 'utf8_icelandic_ci', $force_needed algorithm=copy +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop column new_f ,drop column g, drop column h, $force_needed algorithm=$alter_algorithm +if ($alter_algorithm == 'copy') +{ +--eval alter table tmp_tbl add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl add index if not exists index_1(f), $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl disable keys, $force_needed algorithm=copy +--eval alter table tmp_tbl enable keys, $force_needed algorithm=copy +--eval alter table a1 order by a +--eval alter table tmp_tbl rename column f to new_f, $force_needed algorithm=copy +--eval alter table tmp_tbl convert to character set 'utf8', $force_needed algorithm=copy +--eval alter table tmp_tbl default character set 'utf8', $force_needed algorithm=copy +--eval alter table tmp_tbl default collate 'utf8_icelandic_ci', $force_needed algorithm=copy +--eval alter table tmp_tbl drop column new_f ,drop column g, drop column h, $force_needed algorithm=$alter_algorithm +} +##--eval alter table t1 discard tablespace; +######--eval alter table t1 import tablespace; + +--echo # show binlog and clear status +--sync_slave_with_master +if ($show_binlog) +{ + --source include/show_binlog_events.inc +} +reset master; +--connection master + +# Only add partition and remove partition is tested +# | ADD PARTITION (partition_definition) +# | REMOVE PARTITIONING +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED +} +--error 0,$alter_error +--eval alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm +--eval alter table t1 partition by hash(b) partitions 8 +--eval alter table t1 remove partitioning +if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`) +{ + --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY +} +--error 0,$alter_error +--eval alter table t1 drop column f ,drop column g, drop column h, $force_needed algorithm=$alter_algorithm +if ($alter_algorithm == 'copy') +{ +--eval alter table tmp_tbl add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm +--eval alter table tmp_tbl drop column f ,drop column g, drop column h, $force_needed algorithm=$alter_algorithm +} +--echo # show binlog and clear status +--sync_slave_with_master +if ($show_binlog) +{ + --source include/show_binlog_events.inc +} +reset master; +--connection master + +# clean master/slave +--connection master +drop table t1, a1; +drop temporary table tmp_tbl; +--sync_slave_with_master +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +--source include/start_slave.inc diff --git a/mysql-test/suite/rpl/r/rpl_alter_rollback.result b/mysql-test/suite/rpl/r/rpl_alter_rollback.result new file mode 100644 index 00000000000..3bd91a516c4 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_alter_rollback.result @@ -0,0 +1,54 @@ +# +# Test verifies that "ROLLBACK ALTER" is written to binary log upon +#ALTER command execution failure. +# +include/master-slave.inc +[connection master] +connection master; +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +create table t1 (f1 int primary key) engine=InnoDB; +create table t2 (f1 int primary key, +constraint c1 foreign key (f1) references t1(f1), +constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; +ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +create table t2 (f1 int primary key, +constraint c1 foreign key (f1) references t1(f1)) engine=innodb; +alter table t2 add constraint c1 foreign key (f1) references t1(f1); +ERROR HY000: Can't create table `test`.`t2` (errno: 121 "Duplicate key on write or update") +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (f1 int primary key) engine=InnoDB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t2 (f1 int primary key, +constraint c1 foreign key (f1) references t1(f1)) engine=innodb +master-bin.000001 # Gtid # # GTID #-#-# START ALTER +master-bin.000001 # Query # # use `test`; alter table t2 add constraint c1 foreign key (f1) references t1(f1) +master-bin.000001 # Gtid # # GTID #-#-# ROLLBACK ALTER id=# +master-bin.000001 # Query # # use `test`; alter table t2 add constraint c1 foreign key (f1) references t1(f1) +set foreign_key_checks = 0; +alter table t2 add constraint c1 foreign key (f1) references t1(f1); +ERROR HY000: Duplicate FOREIGN KEY constraint name 'test/c1' +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (f1 int primary key) engine=InnoDB +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t2 (f1 int primary key, +constraint c1 foreign key (f1) references t1(f1)) engine=innodb +master-bin.000001 # Gtid # # GTID #-#-# START ALTER +master-bin.000001 # Query # # use `test`; alter table t2 add constraint c1 foreign key (f1) references t1(f1) +master-bin.000001 # Gtid # # GTID #-#-# ROLLBACK ALTER id=# +master-bin.000001 # Query # # use `test`; alter table t2 add constraint c1 foreign key (f1) references t1(f1) +master-bin.000001 # Gtid # # GTID #-#-# START ALTER +master-bin.000001 # Query # # use `test`; set foreign_key_checks=1; alter table t2 add constraint c1 foreign key (f1) references t1(f1) +master-bin.000001 # Gtid # # GTID #-#-# ROLLBACK ALTER id=# +master-bin.000001 # Query # # use `test`; set foreign_key_checks=1; alter table t2 add constraint c1 foreign key (f1) references t1(f1) +connection slave; +connection master; +drop table t2, t1; +connection slave; +connection master; +set global binlog_alter_two_phase=0;; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result b/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result index 54156685806..4c35d42d90a 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result @@ -3,6 +3,7 @@ include/master-slave.inc *** Test that we check against incorrect table definition for mysql.gtid_slave_pos *** connection master; CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB; +call mtr.add_suppression("Incorrect definition of table mysql.gtid_slave_pos:.*"); connection slave; connection slave; include/stop_slave.inc @@ -83,16 +84,16 @@ ERROR 25000: You are not allowed to execute this command in a transaction ROLLBACK; SET GLOBAL gtid_strict_mode= 1; SET GLOBAL gtid_slave_pos = "0-1-1"; -ERROR HY000: Specified GTID 0-1-1 conflicts with the binary log which contains a more recent GTID 0-2-11. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos +ERROR HY000: Specified GTID 0-1-1 conflicts with the binary log which contains a more recent GTID 0-2-12. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos SET GLOBAL gtid_slave_pos = ""; -ERROR HY000: Specified value for @@gtid_slave_pos contains no value for replication domain 0. This conflicts with the binary log which contains GTID 0-2-11. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos +ERROR HY000: Specified value for @@gtid_slave_pos contains no value for replication domain 0. This conflicts with the binary log which contains GTID 0-2-12. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos SET GLOBAL gtid_strict_mode= 0; SET GLOBAL gtid_slave_pos = "0-1-1"; Warnings: -Warning 1947 Specified GTID 0-1-1 conflicts with the binary log which contains a more recent GTID 0-2-11. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos +Warning 1947 Specified GTID 0-1-1 conflicts with the binary log which contains a more recent GTID 0-2-12. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos SET GLOBAL gtid_slave_pos = ""; Warnings: -Warning 1948 Specified value for @@gtid_slave_pos contains no value for replication domain 0. This conflicts with the binary log which contains GTID 0-2-11. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos +Warning 1948 Specified value for @@gtid_slave_pos contains no value for replication domain 0. This conflicts with the binary log which contains GTID 0-2-12. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos RESET MASTER; SET GLOBAL gtid_slave_pos = "0-1-1"; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_1.result b/mysql-test/suite/rpl/r/rpl_start_alter_1.result new file mode 100644 index 00000000000..9edb23216fe --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_1.result @@ -0,0 +1,313 @@ +include/master-slave.inc +[connection master] +connection master; +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +connection slave; +set global gtid_strict_mode=1; +# Legacy Master Slave +connect master_node,127.0.0.1,root,,$db_name, $M_port; +connect slave_node,127.0.0.1,root,,test, $S_port; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# concurrent alter Myisam +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Aria +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb copy +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb Inplace +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +connection master; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +set global gtid_strict_mode = 0;; +connection master; +set global binlog_alter_two_phase=0;; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_2.result b/mysql-test/suite/rpl/r/rpl_start_alter_2.result new file mode 100644 index 00000000000..a862fc5556a --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_2.result @@ -0,0 +1,326 @@ +include/master-slave.inc +[connection master] +connection master; +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +connection slave; +set global gtid_strict_mode=1; +connection slave; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=10; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +include/start_slave.inc +# Parallel Slave +connection master; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +connect slave_node,127.0.0.1,root,,test, $S_port; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# concurrent alter Myisam +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Aria +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb copy +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb Inplace +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +connection master; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +# cleanup +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +set global gtid_domain_id= 0; +include/start_slave.inc +connection master; +set global binlog_alter_two_phase=0;; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_3.result b/mysql-test/suite/rpl/r/rpl_start_alter_3.result new file mode 100644 index 00000000000..97754401471 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_3.result @@ -0,0 +1,326 @@ +include/master-slave.inc +[connection master] +connection master; +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +connection slave; +set global gtid_strict_mode=1; +connection slave; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +include/start_slave.inc +# Parallel Slave +connection master; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +connect slave_node,127.0.0.1,root,,test, $S_port; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# concurrent alter Myisam +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Aria +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb copy +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb Inplace +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +connection master; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +# cleanup +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +set global gtid_domain_id= 0; +include/start_slave.inc +connection master; +set global binlog_alter_two_phase=0;; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_4.result b/mysql-test/suite/rpl/r/rpl_start_alter_4.result new file mode 100644 index 00000000000..9d7d6376bba --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_4.result @@ -0,0 +1,327 @@ +include/master-slave.inc +[connection master] +connection master; +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +connection slave; +set global gtid_strict_mode=1; +connection slave; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=10; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +include/start_slave.inc +# Parallel Slave +connection master; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +connect slave_node,127.0.0.1,root,,test, $S_port; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# concurrent alter Myisam +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Aria +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb copy +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb Inplace +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +connection master; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +# cleanup +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +set global gtid_domain_id= 0; +include/start_slave.inc +connection master; +set global binlog_alter_two_phase=0;; +set global gtid_domain_id= 0; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_5.result b/mysql-test/suite/rpl/r/rpl_start_alter_5.result new file mode 100644 index 00000000000..4e592c1931f --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_5.result @@ -0,0 +1,327 @@ +include/master-slave.inc +[connection master] +connection master; +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +connection slave; +set global gtid_strict_mode=1; +connection slave; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +include/start_slave.inc +# Parallel Slave +connection master; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +connect slave_node,127.0.0.1,root,,test, $S_port; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# concurrent alter Myisam +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Aria +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb copy +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb Inplace +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +connection master; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +# cleanup +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +set global gtid_domain_id= 0; +include/start_slave.inc +connection master; +set global binlog_alter_two_phase=0;; +set global gtid_domain_id= 0; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_6.result b/mysql-test/suite/rpl/r/rpl_start_alter_6.result new file mode 100644 index 00000000000..6c26d511ee2 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_6.result @@ -0,0 +1,329 @@ +include/master-slave.inc +[connection master] +connection master; +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +connection slave; +set global gtid_strict_mode=1; +connection slave; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=10; +set global slave_parallel_mode=optimistic; +set global slave_domain_parallel_threads=3; +change master to master_use_gtid=slave_pos; +include/start_slave.inc +# Parallel Slave +connection master; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +connect slave_node,127.0.0.1,root,,test, $S_port; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +# concurrent alter Myisam +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Aria +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb copy +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# concurrent alter Innodb Inplace +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Concurrent DML +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +# diff_table of master and slave , we will do only in the case when +# sync_slave is on +include/diff_tables.inc [master_node:t1, slave_node:t1] +include/diff_tables.inc [master_node:t2, slave_node:t2] +include/diff_tables.inc [master_node:t3, slave_node:t3] +include/diff_tables.inc [master_node:t4, slave_node:t4] +include/diff_tables.inc [master_node:t5, slave_node:t5] +include/diff_tables.inc [master_node:t6, slave_node:t6] +include/diff_tables.inc [master_node:t7, slave_node:t7] +include/diff_tables.inc [master_node:t8, slave_node:t8] +include/diff_tables.inc [master_node:t9, slave_node:t9] +include/diff_tables.inc [master_node:t10, slave_node:t10] +# Sync slave +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +connection master; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +# cleanup +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +set global slave_domain_parallel_threads = 0;; +set global gtid_domain_id= 0; +include/start_slave.inc +connection master; +set global binlog_alter_two_phase=0;; +set global gtid_domain_id= 0; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_7.result b/mysql-test/suite/rpl/r/rpl_start_alter_7.result new file mode 100644 index 00000000000..cfe31497179 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_7.result @@ -0,0 +1,368 @@ +connect server_1,127.0.0.1,root,,,$SERVER_MYPORT_1; +connect server_2,127.0.0.1,root,,,$SERVER_MYPORT_2; +connect server_3,127.0.0.1,root,,,$SERVER_MYPORT_3; +connection server_1; +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +connection server_2; +stop slave; +Warnings: +Note 1255 Slave already has been stopped +set global binlog_alter_two_phase=true; +connection server_3; +SET GLOBAL slave_parallel_threads=8; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +connection server_1; +set gtid_domain_id= 11; +create database s1; +use s1; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +set gtid_domain_id= 11;; +connect slave_node,127.0.0.1,root,,test, $S_port; +set gtid_domain_id= 11;; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +# concurrent alter Myisam +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Aria +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb copy +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb Inplace +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +connection server_1; +drop database s1; +select @@gtid_binlog_pos; +@@gtid_binlog_pos +11-1-412 +connection server_2; +set gtid_domain_id= 12; +create database s2; +use s2; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +set gtid_domain_id= 12;; +connect slave_node,127.0.0.1,root,,test, $S_port; +set gtid_domain_id= 12;; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +# concurrent alter Myisam +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Aria +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb copy +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb Inplace +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +connection server_2; +drop database s2; +select @@gtid_binlog_pos; +@@gtid_binlog_pos +12-2-412 +connection server_3; +start all slaves; +Warnings: +Note 1937 SLAVE 'm2' started +Note 1937 SLAVE 'm1' started +set default_master_connection = 'm1'; +include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +include/wait_for_slave_to_start.inc +set default_master_connection = 'm1'; +include/sync_with_master_gtid.inc +set default_master_connection = 'm2'; +include/sync_with_master_gtid.inc +# cleanup +connection server_3; +set default_master_connection = 'm1'; +include/stop_slave.inc +set default_master_connection = 'm2'; +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +set global gtid_domain_id= 0; +reset master; +RESET SLAVE ALL; +SET GLOBAL gtid_slave_pos= ''; +connection server_1; +set global binlog_alter_two_phase=0;; +set global gtid_domain_id= 0; +reset master; +connection server_2; +set global gtid_domain_id= 0; +set global binlog_alter_two_phase=0; +reset master; +disconnect server_1; +disconnect server_2; +disconnect server_3; diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_8.result b/mysql-test/suite/rpl/r/rpl_start_alter_8.result new file mode 100644 index 00000000000..8002f295f5c --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_8.result @@ -0,0 +1,362 @@ +connect server_1,127.0.0.1,root,,,$SERVER_MYPORT_1; +connect server_2,127.0.0.1,root,,,$SERVER_MYPORT_2; +connect server_3,127.0.0.1,root,,,$SERVER_MYPORT_3; +connection server_1; +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +connection server_2; +stop slave; +Warnings: +Note 1255 Slave already has been stopped +set global binlog_alter_two_phase=true; +connection server_3; +SET GLOBAL slave_parallel_threads=20; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +connection server_1; +set gtid_domain_id= 11; +create database s1; +use s1; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +set gtid_domain_id= 11;; +connect slave_node,127.0.0.1,root,,test, $S_port; +set gtid_domain_id= 11;; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +# concurrent alter Myisam +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Aria +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb copy +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb Inplace +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +connection server_1; +drop database s1; +connection server_2; +set gtid_domain_id= 12; +create database s2; +use s2; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +set gtid_domain_id= 12;; +connect slave_node,127.0.0.1,root,,test, $S_port; +set gtid_domain_id= 12;; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +# concurrent alter Myisam +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Aria +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb copy +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb Inplace +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +connection server_2; +drop database s2; +connection server_3; +start all slaves; +Warnings: +Note 1937 SLAVE 'm2' started +Note 1937 SLAVE 'm1' started +set default_master_connection = 'm1'; +include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +include/wait_for_slave_to_start.inc +set default_master_connection = 'm1'; +include/sync_with_master_gtid.inc +set default_master_connection = 'm2'; +include/sync_with_master_gtid.inc +# cleanup +connection server_3; +set default_master_connection = 'm1'; +include/stop_slave.inc +set default_master_connection = 'm2'; +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +set global gtid_domain_id= 0; +reset master; +RESET SLAVE ALL; +SET GLOBAL gtid_slave_pos= ''; +connection server_1; +set global binlog_alter_two_phase=0;; +set global gtid_domain_id= 0; +reset master; +connection server_2; +set global gtid_domain_id= 0; +set global binlog_alter_two_phase=0;; +reset master; +disconnect server_1; +disconnect server_2; +disconnect server_3; diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_bugs.result b/mysql-test/suite/rpl/r/rpl_start_alter_bugs.result new file mode 100644 index 00000000000..3fb3df27afd --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_bugs.result @@ -0,0 +1,33 @@ +include/master-slave.inc +[connection master] +connection master; +set global binlog_alter_two_phase=true; +connection slave; +stop slave; +SET global slave_parallel_threads=2; +set global slave_parallel_mode=optimistic; +start slave; +connection master; +CREATE TABLE t1 (i int primary key) ENGINE = InnoDB; +connection master1; +ALTER TABLE t1 DROP PRIMARY KEY; +ALTER TABLE t1 ADD UNIQUE KEY ui (i); +ALTER TABLE t1 ADD PRIMARY KEY (i); +connection slave; +connection master; +drop table t1; +CREATE TABLE t1 (a int)engine=innodb; +ALTER TABLE t1 add column b int, LOCK=EXCLUSIVE; +drop table t1; +CREATE TABLE t1 (pk int)engine=innodb; +ALTER TABLE t1 DROP FOREIGN KEY y, LOCK=EXCLUSIVE; +ERROR 42000: Can't DROP FOREIGN KEY `y`; check that it exists +drop table t1; +connection slave; +connection master; +set global binlog_alter_two_phase=false; +connection slave; +include/stop_slave.inc +SET global slave_parallel_threads=0; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_chain_basic.result b/mysql-test/suite/rpl/r/rpl_start_alter_chain_basic.result new file mode 100644 index 00000000000..b6c28458561 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_chain_basic.result @@ -0,0 +1,83 @@ +include/rpl_init.inc [topology=1->2->3->4] +connection server_3; +set global gtid_strict_mode=1; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=10; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +include/start_slave.inc +connection server_1; +set global binlog_alter_two_phase=ON; +set binlog_alter_two_phase=ON; +connect master_node,127.0.0.1,root,,$db_name, $SERVER_MYPORT_1; +connect slave_node,127.0.0.1,root,,test, $SERVER_MYPORT_2; +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +include/save_master_gtid.inc +connection slave_node; +include/sync_with_master_gtid.inc +disconnect master_node; +disconnect slave_node; +connection server_1; +set global binlog_alter_two_phase=0; +include/rpl_sync.inc +connection server_2; +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; +domain_id seq_no +0 12 +connection server_3; +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; +domain_id seq_no +0 12 +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +include/start_slave.inc +select @@slave_parallel_threads; +@@slave_parallel_threads +0 +connection server_4; +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; +domain_id seq_no +0 12 +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_ftwrl.result b/mysql-test/suite/rpl/r/rpl_start_alter_ftwrl.result new file mode 100644 index 00000000000..4f22c2a1f5f --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_ftwrl.result @@ -0,0 +1,60 @@ +include/master-slave.inc +[connection master] +connection slave; +include/stop_slave.inc +SET @@global.slave_parallel_threads=4; +SET @@global.slave_parallel_mode=optimistic; +CHANGE MASTER TO master_use_gtid=slave_pos; +SET @@global.debug_dbug="+d,at_write_start_alter"; +include/start_slave.inc +connection master; +SET @@session.binlog_alter_two_phase=true; +CREATE TABLE t1 (a INT) ENGINE=innodb; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +connection master; +SET @@session.alter_algorithm='INSTANT'; +SET @@session.gtid_domain_id=11; +ALTER TABLE t1 ADD COLUMN b int; +# START Alter having exclusive lock is waiting for the signal +connection slave; +# FTWRL is sent first to wait for SA +connection slave1; +FLUSH TABLES WITH READ LOCK; +# SA completes +connection slave; +set DEBUG_SYNC= "now signal alter_cont"; +connection slave1; +connection slave; +# Release CA +connection slave1; +UNLOCK TABLES; +connection master; +connection slave; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT) ENGINE=innodb +slave-bin.000001 # Gtid # # GTID #-#-# START ALTER +slave-bin.000001 # Query # # use `test`; ALTER TABLE t1 ADD COLUMN b int +slave-bin.000001 # Gtid # # GTID #-#-# COMMIT ALTER id=# +slave-bin.000001 # Query # # use `test`; ALTER TABLE t1 ADD COLUMN b int +connection master; +DROP TABLE t1; +connection slave; +# cleanup +connection slave; +set DEBUG_SYNC = RESET; +include/stop_slave.inc +set global slave_parallel_threads = 0; +set global slave_parallel_mode = optimistic; +set @@global.debug_dbug = ""; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_instant.result b/mysql-test/suite/rpl/r/rpl_start_alter_instant.result new file mode 100644 index 00000000000..17aaad81aa4 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_instant.result @@ -0,0 +1,66 @@ +include/master-slave.inc +[connection master] +connection master; +set binlog_alter_two_phase=true; +CREATE OR REPLACE TABLE tab ( +a int PRIMARY KEY, +b varchar(50), +c varchar(50) +) CHARACTER SET=latin1 engine=innodb; +SET SESSION alter_algorithm='INSTANT'; +ALTER TABLE tab MODIFY COLUMN b varchar(100); +SET SESSION alter_algorithm='NOCOPY'; +ALTER TABLE tab MODIFY COLUMN c varchar(100); +SHOW CREATE TABLE tab; +Table Create Table +tab CREATE TABLE `tab` ( + `a` int(11) NOT NULL, + `b` varchar(100) DEFAULT NULL, + `c` varchar(100) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE tab ( +a int PRIMARY KEY, +b varchar(50), +c varchar(50) +) CHARACTER SET=latin1 engine=innodb +master-bin.000001 # Gtid # # GTID #-#-# START ALTER +master-bin.000001 # Query # # use `test`; ALTER TABLE tab MODIFY COLUMN b varchar(100) +master-bin.000001 # Gtid # # GTID #-#-# COMMIT ALTER id=# +master-bin.000001 # Query # # use `test`; ALTER TABLE tab MODIFY COLUMN b varchar(100) +master-bin.000001 # Gtid # # GTID #-#-# START ALTER +master-bin.000001 # Query # # use `test`; ALTER TABLE tab MODIFY COLUMN c varchar(100) +master-bin.000001 # Gtid # # GTID #-#-# COMMIT ALTER id=# +master-bin.000001 # Query # # use `test`; ALTER TABLE tab MODIFY COLUMN c varchar(100) +connection slave; +SHOW CREATE TABLE tab; +Table Create Table +tab CREATE TABLE `tab` ( + `a` int(11) NOT NULL, + `b` varchar(100) DEFAULT NULL, + `c` varchar(100) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE tab ( +a int PRIMARY KEY, +b varchar(50), +c varchar(50) +) CHARACTER SET=latin1 engine=innodb +slave-bin.000001 # Gtid # # GTID #-#-# START ALTER +slave-bin.000001 # Query # # use `test`; ALTER TABLE tab MODIFY COLUMN b varchar(100) +slave-bin.000001 # Gtid # # GTID #-#-# COMMIT ALTER id=# +slave-bin.000001 # Query # # use `test`; ALTER TABLE tab MODIFY COLUMN b varchar(100) +slave-bin.000001 # Gtid # # GTID #-#-# START ALTER +slave-bin.000001 # Query # # use `test`; ALTER TABLE tab MODIFY COLUMN c varchar(100) +slave-bin.000001 # Gtid # # GTID #-#-# COMMIT ALTER id=# +slave-bin.000001 # Query # # use `test`; ALTER TABLE tab MODIFY COLUMN c varchar(100) +connection master; +DROP TABLE tab; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_1.result b/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_1.result new file mode 100644 index 00000000000..e008bbc81a7 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_1.result @@ -0,0 +1,172 @@ +include/master-slave.inc +[connection master] +connection master; +set global binlog_alter_two_phase=true; +connection slave; +include/stop_slave.inc +change master to master_use_gtid= current_pos; +set global gtid_strict_mode=1; +# Legacy Master Slave +connect master_node,127.0.0.1,root,,$db_name, $M_port; +set gtid_domain_id= 0;; +connect slave_node,127.0.0.1,root,,test, $S_port; +set gtid_domain_id= 0;; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +# concurrent alter Myisam +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Aria +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb copy +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb Inplace +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +connection master; +select @@gtid_binlog_state; +@@gtid_binlog_state +0-1-410 +RESET master; +connection slave; +select @@gtid_binlog_state; +@@gtid_binlog_state +0-1-410 +set global gtid_strict_mode=0; +include/start_slave.inc +connection master; +set global binlog_alter_two_phase=false; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_2.result b/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_2.result new file mode 100644 index 00000000000..2c1ae667fd6 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_2.result @@ -0,0 +1,419 @@ +connect server_1,127.0.0.1,root,,,$SERVER_MYPORT_1; +connect server_2,127.0.0.1,root,,,$SERVER_MYPORT_2; +connect server_3,127.0.0.1,root,,,$SERVER_MYPORT_3; +connection server_1; +SET @save_binlog_alter_two_phase= @@GLOBAL.binlog_alter_two_phase; +SET GLOBAL binlog_alter_two_phase = ON; +SET binlog_alter_two_phase = ON; +# Create table and perform CA and RA +CREATE TABLE t1( a INT, b INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1,1); +INSERT INTO t1 VALUES(2,2); +# Normal Alter +ALTER TABLE t1 ADD COLUMN c INT; +# Failed Alter +INSERT INTO t1 VALUES(1,1, NULL); +ALTER TABLE t1 CHANGE a a INT UNIQUE; +ERROR 23000: Duplicate entry '1' for key 'a' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT @@gtid_binlog_state; +@@gtid_binlog_state +0-1-8 +# apply the binlog +DROP TABLE t1; +# reset the binlog +RESET MASTER; +# execute the binlog +SELECT @@gtid_binlog_state; +@@gtid_binlog_state +0-1-8 +# Same as before +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# reset the binlog +RESET MASTER; +RESET SLAVE; +connection server_2; +SET @save_binlog_alter_two_phase= @@GLOBAL.binlog_alter_two_phase; +SET GLOBAL binlog_alter_two_phase = ON; +connection server_3; +SET @save_gtid_strict_mode= @@GLOBAL.gtid_strict_mode; +SET @slave_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @slave_parallel_mode= @@GLOBAL.slave_parallel_mode; +SET GLOBAL slave_parallel_threads=20; +SET GLOBAL slave_parallel_mode=optimistic; +SET GLOBAL gtid_strict_mode=1; +connection server_1; +SET gtid_domain_id= 11; +CREATE DATABASE s1; +USE s1; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +set gtid_domain_id= 11;; +connect slave_node,127.0.0.1,root,,test, $S_port; +set gtid_domain_id= 11;; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +# concurrent alter Myisam +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Aria +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb copy +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb Inplace +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +connection server_1; +DROP DATABASE s1; +connection server_2; +SET gtid_domain_id= 12; +CREATE DATABASE s2; +USE s2; +connect master_node,127.0.0.1,root,,$db_name, $M_port; +set gtid_domain_id= 12;; +connect slave_node,127.0.0.1,root,,test, $S_port; +set gtid_domain_id= 12;; +# myisam +connection master_node; +create table t1(a int, b int) engine=myisam;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# innodb +connection master_node; +create table t1(a int, b int) engine=innodb;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master_node; +drop table t1; +# aria +connection master_node; +create table t1(a int, b int) engine=aria;; +insert into t1 values(1,1); +insert into t1 values(2,2); +# Normal Alter +alter table t1 add column c int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +# Failed Alter +insert into t1 values(1,1, NULL); +alter table t1 change a a int unique; +ERROR 23000: Duplicate entry '1' for key 'a' +set @@session.binlog_alter_two_phase = 0; +alter table t1 change a a int; +set @@session.binlog_alter_two_phase = 1; +alter table t1 change a a int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 +connection master_node; +drop table t1; +# concurrent alter Myisam +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Aria +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb copy +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +# concurrent alter Innodb Inplace +# Concurrent DML +# Rollback tests +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +ERROR 23000: Duplicate entry '2' for key 'b' +connection server_2; +DROP DATABASE s2; +connection server_3; +START ALL SLAVES; +Warnings: +Note 1937 SLAVE 'm2' started +Note 1937 SLAVE 'm1' started +SET default_master_connection = 'm1'; +include/wait_for_slave_to_start.inc +SET default_master_connection = 'm2'; +include/wait_for_slave_to_start.inc +SET default_master_connection = 'm1'; +include/sync_with_master_gtid.inc +SET default_master_connection = 'm2'; +include/sync_with_master_gtid.inc +# Stop slaves and apply binlog +connection server_3; +SET default_master_connection = 'm1'; +include/stop_slave.inc +SET default_master_connection = 'm2'; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads = @slave_parallel_threads; +SET GLOBAL slave_parallel_mode = @slave_parallel_mode; +SET GLOBAL gtid_strict_mode = @save_gtid_strict_mode; +SET GLOBAL gtid_domain_id= 0; +SELECT @@gtid_binlog_state; +@@gtid_binlog_state +11-1-412,12-2-412 +# reset the binlog +RESET MASTER; +# execute the binlog +SELECT @@gtid_binlog_state; +@@gtid_binlog_state +11-1-412,12-2-412 +# One more time to simulate S->S case +RESET MASTER; +# execute the binlog +SELECT @@gtid_binlog_state; +@@gtid_binlog_state +11-1-412,12-2-412 +RESET MASTER; +RESET SLAVE ALL; +SET GLOBAL gtid_slave_pos= ''; +connection server_1; +SET GLOBAL binlog_alter_two_phase=@save_binlog_alter_two_phase; +SET GLOBAL gtid_domain_id= 0; +RESET MASTER; +connection server_2; +SET GLOBAL gtid_domain_id= 0; +SET GLOBAL binlog_alter_two_phase=@save_binlog_alter_two_phase; +RESET MASTER; +disconnect server_1; +disconnect server_2; +disconnect server_3; diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_optimize.result b/mysql-test/suite/rpl/r/rpl_start_alter_optimize.result new file mode 100644 index 00000000000..24f016e93a0 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_optimize.result @@ -0,0 +1,18 @@ +include/master-slave.inc +[connection master] +connection master; +set binlog_alter_two_phase = ON; +connection master; +CREATE TABLE t1 (i int) engine=innodb; +CREATE TABLE t2 (i int) engine=innodb; +ALTER TABLE t1 DROP CONSTRAINT IF EXISTS y; +Warnings: +Note 1091 Can't DROP CONSTRAINT `y`; check that it exists +OPTIMIZE TABLE t2; +Table Op Msg_type Msg_text +test.t2 optimize note Table does not support optimize, doing recreate + analyze instead +test.t2 optimize status OK +connection slave; +connection master; +drop table t1,t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_options.result b/mysql-test/suite/rpl/r/rpl_start_alter_options.result new file mode 100644 index 00000000000..30854b12be1 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_options.result @@ -0,0 +1,497 @@ +include/master-slave.inc +[connection master] +connection slave; +stop slave; +change master to master_use_gtid= current_pos; +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +start slave; +connection master; +set binlog_alter_two_phase=true; +create table t1(a int , b int) engine=innodb; +create table a1(a int , b int) engine=myisam; +create temporary table tmp_tbl(a int, b int) engine=innodb; +alter table t1 add column if not exists c int , force , algorithm=default; +alter table t1 add column d int first, force , algorithm=default; +alter table t1 add column e int after c, force , algorithm=default; +alter table t1 add column f int after c, force , add column g int first ,add column h char, algorithm=default; +alter table t1 drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, force , algorithm=default; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f int after b, force , add column g int first ,add column h varchar(100), algorithm=default; +alter table t1 add index if not exists index_1(f), force , algorithm=default; +alter table t1 drop index index_1, force , algorithm=default; +alter table t1 add unique key unique_1(g), force , algorithm=default; +alter table t1 drop index unique_1, force , algorithm=default; +alter table t1 add fulltext key f_1(h), force , algorithm=default; +alter table t1 drop column f, drop column g , force , algorithm=default; +alter table t1 add primary key(h), force , algorithm=copy; +alter table t1 drop primary key, force , algorithm=copy; +alter table t1 drop column h, force , algorithm=copy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b, add column g varchar(100) first ,add column h char, force , algorithm=default; +alter table t1 add period for system_time(f,h); +alter table t1 add system versioning; +alter table t1 drop system versioning; +alter table t1 drop column f, drop column g , drop column h, force , algorithm=default; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=default ;; +alter table t1 alter column f set default "****", force , algorithm=default ;; +alter table t1 alter column f drop default, force , algorithm=default ;; +alter table t1 change column g new_g char, force , algorithm=copy;; +alter table t1 modify column h varchar(100), force , algorithm=copy;; +alter table t1 drop column new_g ,drop column f, drop column h, force , algorithm=default ;; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=default; +alter table t1 add index if not exists index_1(f), force , algorithm=default; +alter table t1 disable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 enable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 rename t2, force , algorithm=default; +alter table t2 rename t1, force , algorithm=default; +alter table a1 order by a; +alter table t1 rename column f to new_f, force , algorithm=copy; +alter table t1 convert to character set 'utf8', force , algorithm=copy; +alter table t1 default character set 'utf8', force , algorithm=copy; +alter table t1 default collate 'utf8_icelandic_ci', force , algorithm=copy; +alter table t1 drop column new_f ,drop column g, drop column h, force , algorithm=default; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=default; +alter table t1 partition by hash(b) partitions 8; +alter table t1 remove partitioning; +alter table t1 drop column f ,drop column g, drop column h, force , algorithm=default; +# show binlog and clear status +connection slave; +reset master; +connection master; +connection master; +drop table t1, a1; +drop temporary table tmp_tbl; +connection slave; +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +include/start_slave.inc +connection slave; +stop slave; +change master to master_use_gtid= current_pos; +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +start slave; +connection master; +set binlog_alter_two_phase=true; +create table t1(a int , b int) engine=innodb; +create table a1(a int , b int) engine=myisam; +create temporary table tmp_tbl(a int, b int) engine=innodb; +alter table t1 add column if not exists c int , force , algorithm=inplace; +alter table t1 add column d int first, force , algorithm=inplace; +alter table t1 add column e int after c, force , algorithm=inplace; +alter table t1 add column f int after c, force , add column g int first ,add column h char, algorithm=inplace; +alter table t1 drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, force , algorithm=inplace; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f int after b, force , add column g int first ,add column h varchar(100), algorithm=inplace; +alter table t1 add index if not exists index_1(f), force , algorithm=inplace; +alter table t1 drop index index_1, force , algorithm=inplace; +alter table t1 add unique key unique_1(g), force , algorithm=inplace; +alter table t1 drop index unique_1, force , algorithm=inplace; +alter table t1 add fulltext key f_1(h), force , algorithm=inplace; +alter table t1 drop column f, drop column g , force , algorithm=inplace; +alter table t1 add primary key(h), force , algorithm=copy; +alter table t1 drop primary key, force , algorithm=copy; +alter table t1 drop column h, force , algorithm=copy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b, add column g varchar(100) first ,add column h char, force , algorithm=inplace; +alter table t1 add period for system_time(f,h); +alter table t1 add system versioning; +alter table t1 drop system versioning; +alter table t1 drop column f, drop column g , drop column h, force , algorithm=inplace; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=inplace ;; +alter table t1 alter column f set default "****", force , algorithm=inplace ;; +alter table t1 alter column f drop default, force , algorithm=inplace ;; +alter table t1 change column g new_g char, force , algorithm=copy;; +alter table t1 modify column h varchar(100), force , algorithm=copy;; +alter table t1 drop column new_g ,drop column f, drop column h, force , algorithm=inplace ;; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=inplace; +alter table t1 add index if not exists index_1(f), force , algorithm=inplace; +alter table t1 disable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 enable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 rename t2, force , algorithm=inplace; +alter table t2 rename t1, force , algorithm=inplace; +alter table a1 order by a; +alter table t1 rename column f to new_f, force , algorithm=copy; +alter table t1 convert to character set 'utf8', force , algorithm=copy; +alter table t1 default character set 'utf8', force , algorithm=copy; +alter table t1 default collate 'utf8_icelandic_ci', force , algorithm=copy; +alter table t1 drop column new_f ,drop column g, drop column h, force , algorithm=inplace; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=inplace; +alter table t1 partition by hash(b) partitions 8; +alter table t1 remove partitioning; +alter table t1 drop column f ,drop column g, drop column h, force , algorithm=inplace; +# show binlog and clear status +connection slave; +reset master; +connection master; +connection master; +drop table t1, a1; +drop temporary table tmp_tbl; +connection slave; +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +include/start_slave.inc +connection slave; +stop slave; +change master to master_use_gtid= current_pos; +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +start slave; +connection master; +set binlog_alter_two_phase=true; +create table t1(a int , b int) engine=innodb; +create table a1(a int , b int) engine=myisam; +create temporary table tmp_tbl(a int, b int) engine=innodb; +alter table t1 add column if not exists c int , force , algorithm=copy; +alter table t1 add column d int first, force , algorithm=copy; +alter table t1 add column e int after c, force , algorithm=copy; +alter table t1 add column f int after c, force , add column g int first ,add column h char, algorithm=copy; +alter table t1 drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, force , algorithm=copy; +alter table tmp_tbl add column if not exists c int , force , algorithm=copy; +alter table tmp_tbl add column d int first, force , algorithm=copy; +alter table tmp_tbl add column e int after c, force , algorithm=copy; +alter table tmp_tbl add column f int after c, force , add column g int first ,add column h char, algorithm=copy; +alter table tmp_tbl drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, force , algorithm=copy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f int after b, force , add column g int first ,add column h varchar(100), algorithm=copy; +alter table t1 add index if not exists index_1(f), force , algorithm=copy; +alter table t1 drop index index_1, force , algorithm=copy; +alter table t1 add unique key unique_1(g), force , algorithm=copy; +alter table t1 drop index unique_1, force , algorithm=copy; +alter table t1 add fulltext key f_1(h), force , algorithm=copy; +alter table t1 drop column f, drop column g , force , algorithm=copy; +alter table tmp_tbl add column f int after b, force , add column g int first ,add column h varchar(100), algorithm=copy; +alter table tmp_tbl add index if not exists index_1(f), force , algorithm=copy; +alter table tmp_tbl drop index index_1, force , algorithm=copy; +alter table tmp_tbl add unique key unique_1(g), force , algorithm=copy; +alter table tmp_tbl drop index unique_1, force , algorithm=copy; +alter table tmp_tbl drop column f, drop column g , force , algorithm=copy; +alter table t1 add primary key(h), force , algorithm=copy; +alter table t1 drop primary key, force , algorithm=copy; +alter table t1 drop column h, force , algorithm=copy; +alter table tmp_tbl add primary key(h), force , algorithm=copy; +alter table tmp_tbl drop primary key, force , algorithm=copy; +alter table tmp_tbl drop column h, force , algorithm=copy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b, add column g varchar(100) first ,add column h char, force , algorithm=copy; +alter table t1 add period for system_time(f,h); +alter table t1 add system versioning; +alter table t1 drop system versioning; +alter table t1 drop column f, drop column g , drop column h, force , algorithm=copy; +# show binlog and clear status +alter table tmp_tbl add column f varchar(100) after b, add column g varchar(100) first ,add column h char, force , algorithm=copy; +alter table tmp_tbl drop column f, drop column g , drop column h, force , algorithm=copy; +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=copy ;; +alter table t1 alter column f set default "****", force , algorithm=copy ;; +alter table t1 alter column f drop default, force , algorithm=copy ;; +alter table t1 change column g new_g char, force , algorithm=copy;; +alter table t1 modify column h varchar(100), force , algorithm=copy;; +alter table t1 drop column new_g ,drop column f, drop column h, force , algorithm=copy ;; +alter table tmp_tbl add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=copy ;; +alter table tmp_tbl alter column f set default "****", force , algorithm=copy ;; +alter table tmp_tbl alter column f drop default, force , algorithm=copy ;; +alter table tmp_tbl change column g new_g char, force , algorithm=copy;; +alter table tmp_tbl modify column h varchar(100), force , algorithm=copy;; +alter table tmp_tbl drop column new_g ,drop column f, drop column h, force , algorithm=copy ;; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=copy; +alter table t1 add index if not exists index_1(f), force , algorithm=copy; +alter table t1 disable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 enable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 rename t2, force , algorithm=copy; +alter table t2 rename t1, force , algorithm=copy; +alter table a1 order by a; +alter table t1 rename column f to new_f, force , algorithm=copy; +alter table t1 convert to character set 'utf8', force , algorithm=copy; +alter table t1 default character set 'utf8', force , algorithm=copy; +alter table t1 default collate 'utf8_icelandic_ci', force , algorithm=copy; +alter table t1 drop column new_f ,drop column g, drop column h, force , algorithm=copy; +alter table tmp_tbl add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=copy; +alter table tmp_tbl add index if not exists index_1(f), force , algorithm=copy; +alter table tmp_tbl disable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`tmp_tbl` doesn't have this option +alter table tmp_tbl enable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`tmp_tbl` doesn't have this option +alter table a1 order by a; +alter table tmp_tbl rename column f to new_f, force , algorithm=copy; +alter table tmp_tbl convert to character set 'utf8', force , algorithm=copy; +alter table tmp_tbl default character set 'utf8', force , algorithm=copy; +alter table tmp_tbl default collate 'utf8_icelandic_ci', force , algorithm=copy; +alter table tmp_tbl drop column new_f ,drop column g, drop column h, force , algorithm=copy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=copy; +alter table t1 partition by hash(b) partitions 8; +alter table t1 remove partitioning; +alter table t1 drop column f ,drop column g, drop column h, force , algorithm=copy; +alter table tmp_tbl add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=copy; +alter table tmp_tbl drop column f ,drop column g, drop column h, force , algorithm=copy; +# show binlog and clear status +connection slave; +reset master; +connection master; +connection master; +drop table t1, a1; +drop temporary table tmp_tbl; +connection slave; +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +include/start_slave.inc +# Prove formal support for nocopy and instant +connection slave; +stop slave; +change master to master_use_gtid= current_pos; +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +start slave; +connection master; +set binlog_alter_two_phase=true; +create table t1(a int , b int) engine=innodb; +create table a1(a int , b int) engine=myisam; +create temporary table tmp_tbl(a int, b int) engine=innodb; +alter table t1 add column if not exists c int , force , algorithm=instant; +alter table t1 add column d int first, force , algorithm=instant; +alter table t1 add column e int after c, force , algorithm=instant; +alter table t1 add column f int after c, force , add column g int first ,add column h char, algorithm=instant; +alter table t1 drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, force , algorithm=instant; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f int after b, force , add column g int first ,add column h varchar(100), algorithm=instant; +alter table t1 add index if not exists index_1(f), force , algorithm=instant; +alter table t1 drop index index_1, force , algorithm=instant; +alter table t1 add unique key unique_1(g), force , algorithm=instant; +alter table t1 drop index unique_1, force , algorithm=instant; +alter table t1 add fulltext key f_1(h), force , algorithm=instant; +alter table t1 drop column f, drop column g , force , algorithm=instant; +alter table t1 add primary key(h), force , algorithm=copy; +alter table t1 drop primary key, force , algorithm=copy; +alter table t1 drop column h, force , algorithm=copy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b, add column g varchar(100) first ,add column h char, force , algorithm=instant; +alter table t1 add period for system_time(f,h); +alter table t1 add system versioning; +alter table t1 drop system versioning; +alter table t1 drop column f, drop column g , drop column h, force , algorithm=instant; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=instant ;; +alter table t1 alter column f set default "****", force , algorithm=instant ;; +alter table t1 alter column f drop default, force , algorithm=instant ;; +alter table t1 change column g new_g char, force , algorithm=copy;; +alter table t1 modify column h varchar(100), force , algorithm=copy;; +alter table t1 drop column new_g ,drop column f, drop column h, force , algorithm=instant ;; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=instant; +alter table t1 add index if not exists index_1(f), force , algorithm=instant; +alter table t1 disable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 enable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 rename t2, force , algorithm=instant; +alter table t2 rename t1, force , algorithm=instant; +alter table a1 order by a; +alter table t1 rename column f to new_f, force , algorithm=copy; +alter table t1 convert to character set 'utf8', force , algorithm=copy; +alter table t1 default character set 'utf8', force , algorithm=copy; +alter table t1 default collate 'utf8_icelandic_ci', force , algorithm=copy; +alter table t1 drop column new_f ,drop column g, drop column h, force , algorithm=instant; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=instant; +alter table t1 partition by hash(b) partitions 8; +alter table t1 remove partitioning; +alter table t1 drop column f ,drop column g, drop column h, force , algorithm=instant; +# show binlog and clear status +connection slave; +reset master; +connection master; +connection master; +drop table t1, a1; +drop temporary table tmp_tbl; +connection slave; +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +include/start_slave.inc +connection slave; +stop slave; +change master to master_use_gtid= current_pos; +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +start slave; +connection master; +set binlog_alter_two_phase=true; +create table t1(a int , b int) engine=innodb; +create table a1(a int , b int) engine=myisam; +create temporary table tmp_tbl(a int, b int) engine=innodb; +alter table t1 add column if not exists c int , force , algorithm=nocopy; +alter table t1 add column d int first, force , algorithm=nocopy; +alter table t1 add column e int after c, force , algorithm=nocopy; +alter table t1 add column f int after c, force , add column g int first ,add column h char, algorithm=nocopy; +alter table t1 drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, force , algorithm=nocopy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f int after b, force , add column g int first ,add column h varchar(100), algorithm=nocopy; +alter table t1 add index if not exists index_1(f), force , algorithm=nocopy; +alter table t1 drop index index_1, force , algorithm=nocopy; +alter table t1 add unique key unique_1(g), force , algorithm=nocopy; +alter table t1 drop index unique_1, force , algorithm=nocopy; +alter table t1 add fulltext key f_1(h), force , algorithm=nocopy; +alter table t1 drop column f, drop column g , force , algorithm=nocopy; +alter table t1 add primary key(h), force , algorithm=copy; +alter table t1 drop primary key, force , algorithm=copy; +alter table t1 drop column h, force , algorithm=copy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b, add column g varchar(100) first ,add column h char, force , algorithm=nocopy; +alter table t1 add period for system_time(f,h); +alter table t1 add system versioning; +alter table t1 drop system versioning; +alter table t1 drop column f, drop column g , drop column h, force , algorithm=nocopy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=nocopy ;; +alter table t1 alter column f set default "****", force , algorithm=nocopy ;; +alter table t1 alter column f drop default, force , algorithm=nocopy ;; +alter table t1 change column g new_g char, force , algorithm=copy;; +alter table t1 modify column h varchar(100), force , algorithm=copy;; +alter table t1 drop column new_g ,drop column f, drop column h, force , algorithm=nocopy ;; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=nocopy; +alter table t1 add index if not exists index_1(f), force , algorithm=nocopy; +alter table t1 disable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 enable keys, force , algorithm=copy; +Warnings: +Note 1031 Storage engine InnoDB of the table `test`.`t1` doesn't have this option +alter table t1 rename t2, force , algorithm=nocopy; +alter table t2 rename t1, force , algorithm=nocopy; +alter table a1 order by a; +alter table t1 rename column f to new_f, force , algorithm=copy; +alter table t1 convert to character set 'utf8', force , algorithm=copy; +alter table t1 default character set 'utf8', force , algorithm=copy; +alter table t1 default collate 'utf8_icelandic_ci', force , algorithm=copy; +alter table t1 drop column new_f ,drop column g, drop column h, force , algorithm=nocopy; +# show binlog and clear status +connection slave; +reset master; +connection master; +alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, force , algorithm=nocopy; +alter table t1 partition by hash(b) partitions 8; +alter table t1 remove partitioning; +alter table t1 drop column f ,drop column g, drop column h, force , algorithm=nocopy; +# show binlog and clear status +connection slave; +reset master; +connection master; +connection master; +drop table t1, a1; +drop temporary table tmp_tbl; +connection slave; +include/stop_slave.inc +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_para_to_seq.result b/mysql-test/suite/rpl/r/rpl_start_alter_para_to_seq.result new file mode 100644 index 00000000000..d93ec724372 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_para_to_seq.result @@ -0,0 +1,14 @@ +include/master-slave.inc +[connection master] +connection master; +CREATE TABLE t1 (a1 int, d1 int DEFAULT 0); +INSERT INTO t1 VALUES (1,1) ; +SET binlog_alter_two_phase = ON; +ALTER TABLE t1 WAIT 9 RENAME COLUMN a1 TO a2; +SET binlog_alter_two_phase = OFF; +ALTER TABLE t1 ALTER COLUMN d1 DROP DEFAULT; +connection slave; +connection master; +drop table t1; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_restart_master.result b/mysql-test/suite/rpl/r/rpl_start_alter_restart_master.result new file mode 100644 index 00000000000..a2aba33aee0 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_restart_master.result @@ -0,0 +1,85 @@ +include/master-slave.inc +[connection master] +connection slave; +SET @old_debug_slave= @@global.debug; +stop slave; +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +start slave; +connection master; +call mtr.add_suppression("ALTER query started at .+ could not be completed"); +SET @old_debug_master= @@global.debug; +set binlog_alter_two_phase=true; +create table t3( a int primary key, b int) engine=innodb; +connection master; +connection slave; +include/stop_slave.inc +connection master; +SET SESSION debug_dbug="d,start_alter_kill_after_binlog"; +alter table t3 add column d int; +ERROR HY000: Lost connection to server during query +include/rpl_reconnect.inc +set binlog_alter_two_phase= true; +alter table t3 add column d int; +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'ALTER query started at .+ could not be completed' COLLATE 'latin1_swedish_ci')) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t3( a int primary key, b int) engine=innodb +master-bin.000001 # Gtid # # GTID #-#-# START ALTER +master-bin.000001 # Query # # use `test`; alter table t3 add column d int +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000002 # Gtid # # GTID #-#-# START ALTER +master-bin.000002 # Query # # use `test`; alter table t3 add column d int +master-bin.000002 # Gtid # # GTID #-#-# COMMIT ALTER id=# +master-bin.000002 # Query # # use `test`; alter table t3 add column d int +connection slave; +include/start_slave.inc +connection master; +connection slave; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'ALTER query started at .+ could not be completed' COLLATE 'latin1_swedish_ci')) +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t3( a int primary key, b int) engine=innodb +slave-bin.000001 # Gtid # # GTID #-#-# START ALTER +slave-bin.000001 # Query # # use `test`; alter table t3 add column d int +slave-bin.000001 # Gtid # # GTID #-#-# START ALTER +slave-bin.000001 # Query # # use `test`; alter table t3 add column d int +slave-bin.000001 # Gtid # # GTID #-#-# COMMIT ALTER id=# +slave-bin.000001 # Query # # use `test`; alter table t3 add column d int +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection master; +SET GLOBAL debug_dbug= @old_debug_master; +drop table t3; +set global binlog_alter_two_phase = 0; +connection slave; +SET GLOBAL debug_dbug= @old_debug_slave; +stop slave; +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +start slave; +connection master; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_restart_slave.result b/mysql-test/suite/rpl/r/rpl_start_alter_restart_slave.result new file mode 100644 index 00000000000..0a1c1f7971e --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_alter_restart_slave.result @@ -0,0 +1,119 @@ +include/master-slave.inc +[connection master] +connection slave; +SET @old_debug_slave= @@global.debug; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +set global debug_dbug="+d,rpl_slave_stop_CA_before_binlog"; +include/start_slave.inc +connection master; +SET @old_debug_master= @@global.debug; +set global debug_dbug="+d,start_alter_delay_master"; +set global binlog_alter_two_phase=true; +create table t1( a int primary key, b int) engine=myisam; +create table t2( a int primary key, b int) engine=myisam; +connect con1,localhost,root,,; +alter table t1 add column c int;; +connection master; +# Get into binlog first and wait +# master gtid state is 0-1-3 +connect con2,localhost,root,,; +alter table t2 add column c int;; +connection master; +# Get into binlog next and wait as well +# master gtid state is 0-1-4 +set DEBUG_SYNC= "now signal alter_cont"; +connection con1; +connection con2; +create table t3( a int primary key, b int) engine=innodb; +# master gtid state is 0-1-7 +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid_list 1 # [] +master-bin.000001 # Binlog_checkpoint 1 # master-bin.000001 +master-bin.000001 # Gtid 1 # GTID #-#-# +master-bin.000001 # Query 1 # use `test`; create table t1( a int primary key, b int) engine=myisam +master-bin.000001 # Gtid 1 # GTID #-#-# +master-bin.000001 # Query 1 # use `test`; create table t2( a int primary key, b int) engine=myisam +master-bin.000001 # Gtid 1 # GTID #-#-# START ALTER +master-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +master-bin.000001 # Gtid 1 # GTID #-#-# START ALTER +master-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +master-bin.000001 # Gtid 1 # GTID #-#-# COMMIT ALTER id=<seq_no> +master-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +master-bin.000001 # Gtid 1 # GTID #-#-# COMMIT ALTER id=<seq_no> +master-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +master-bin.000001 # Gtid 1 # GTID #-#-# +master-bin.000001 # Query 1 # use `test`; create table t3( a int primary key, b int) engine=innodb +# Stop Slave +# As master binlog is SA SA CA CA +# let's stop at first CA processing (in process_commit_alter) +connection slave; +include/sync_with_master_gtid.inc +connect extra_slave,127.0.0.1,root,,test,$SLAVE_MYPORT; +stop slave;; +connection slave; +connection extra_slave; +SET GLOBAL debug_dbug= @old_debug_slave; +connection slave; +include/wait_for_slave_sql_to_stop.inc +# The list of events after the slave has stopped must have just one CA: +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid_list 2 # [] +slave-bin.000001 # Binlog_checkpoint 2 # slave-bin.000001 +slave-bin.000001 # Gtid 1 # GTID #-#-# +slave-bin.000001 # Query 1 # use `test`; create table t1( a int primary key, b int) engine=myisam +slave-bin.000001 # Gtid 1 # GTID #-#-# +slave-bin.000001 # Query 1 # use `test`; create table t2( a int primary key, b int) engine=myisam +slave-bin.000001 # Gtid 1 # GTID #-#-# START ALTER +slave-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +slave-bin.000001 # Gtid 1 # GTID #-#-# START ALTER +slave-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +slave-bin.000001 # Gtid 1 # GTID #-#-# COMMIT ALTER id=<seq_no> +slave-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; +domain_id seq_no +0 5 +include/start_slave.inc +connection master; +connection slave; +# Everything from the master binlog must have been applied now: +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; +domain_id seq_no +0 7 +# slave gtid state is 0-1-7 +# The list of events after the slave has synchronized must have both CA: +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid_list 2 # [] +slave-bin.000001 # Binlog_checkpoint 2 # slave-bin.000001 +slave-bin.000001 # Gtid 1 # GTID #-#-# +slave-bin.000001 # Query 1 # use `test`; create table t1( a int primary key, b int) engine=myisam +slave-bin.000001 # Gtid 1 # GTID #-#-# +slave-bin.000001 # Query 1 # use `test`; create table t2( a int primary key, b int) engine=myisam +slave-bin.000001 # Gtid 1 # GTID #-#-# START ALTER +slave-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +slave-bin.000001 # Gtid 1 # GTID #-#-# START ALTER +slave-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +slave-bin.000001 # Gtid 1 # GTID #-#-# COMMIT ALTER id=<seq_no> +slave-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +slave-bin.000001 # Gtid 1 # GTID #-#-# COMMIT ALTER id=<seq_no> +slave-bin.000001 # Query 1 # use `test`; alter table <t> add column c int +slave-bin.000001 # Gtid 1 # GTID #-#-# +slave-bin.000001 # Query 1 # use `test`; create table t3( a int primary key, b int) engine=innodb +connection master; +drop table t1,t2,t3; +set global binlog_alter_two_phase = 0; +SET GLOBAL debug_dbug= @old_debug_master; +set DEBUG_SYNC= 'RESET'; +connection slave; +stop slave; +set global slave_parallel_threads = 0;; +set global slave_parallel_mode = optimistic;; +set global gtid_strict_mode = 0;; +set DEBUG_SYNC= 'RESET'; +start slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_alter_rollback.test b/mysql-test/suite/rpl/t/rpl_alter_rollback.test new file mode 100644 index 00000000000..c24f01ff007 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_alter_rollback.test @@ -0,0 +1,42 @@ +# +# MENT-662: Lag Free Alter On Slave +# + +--echo # +--echo # Test verifies that "ROLLBACK ALTER" is written to binary log upon +--echo #ALTER command execution failure. +--echo # +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection master +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; + +create table t1 (f1 int primary key) engine=InnoDB; +--error ER_CANT_CREATE_TABLE +create table t2 (f1 int primary key, +constraint c1 foreign key (f1) references t1(f1), +constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; +create table t2 (f1 int primary key, + constraint c1 foreign key (f1) references t1(f1)) engine=innodb; + +--error ER_CANT_CREATE_TABLE +alter table t2 add constraint c1 foreign key (f1) references t1(f1); +--source include/show_binlog_events.inc + +set foreign_key_checks = 0; +--error ER_DUP_CONSTRAINT_NAME +alter table t2 add constraint c1 foreign key (f1) references t1(f1); +--source include/show_binlog_events.inc +--sync_slave_with_master + +--connection master +drop table t2, t1; +--sync_slave_with_master + +--connection master +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate.test b/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate.test index 07fc1e558cb..f7aefd625a1 100644 --- a/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate.test +++ b/mysql-test/suite/rpl/t/rpl_blackhole_row_annotate.test @@ -44,6 +44,6 @@ FLUSH LOGS; let $MYSQLD_DATADIR= `select @@datadir`; --replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /xid=\d*/xid=<xid>/ ---exec $MYSQL_BINLOG --base64-output=decode-rows $MYSQLD_DATADIR/slave-bin.000001 +--exec $MYSQL_BINLOG --base64-output=decode-rows --skip-gtid-strict-mode $MYSQLD_DATADIR/slave-bin.000001 source include/rpl_end.inc; diff --git a/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test b/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test index c02e2670c92..412489b3ee3 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test @@ -5,6 +5,7 @@ --echo *** Test that we check against incorrect table definition for mysql.gtid_slave_pos *** --connection master CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB; +call mtr.add_suppression("Incorrect definition of table mysql.gtid_slave_pos:.*"); --sync_slave_with_master --connection slave diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_1.test b/mysql-test/suite/rpl/t/rpl_start_alter_1.test new file mode 100644 index 00000000000..9ce061f1031 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_1.test @@ -0,0 +1,33 @@ +# +# Start Alter with Legacy Replication +# +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/master-slave.inc + +--connection master +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +--connection slave +--let $gtid_strict_mode= `select @@gtid_strict_mode` +set global gtid_strict_mode=1; + +--echo # Legacy Master Slave +--let $domain_1=0 +--let $domain_2=0 +--let $M_port= $MASTER_MYPORT +--let $S_port= $SLAVE_MYPORT +--let $sync_slave=1 + +--source include/start_alter_include.inc +--connection master +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc +--eval set global gtid_strict_mode = $gtid_strict_mode; + +--connection master +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_2.test b/mysql-test/suite/rpl/t/rpl_start_alter_2.test new file mode 100644 index 00000000000..457409c51a6 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_2.test @@ -0,0 +1,53 @@ +# +# Start Alter with Parallel Replication +# 1 domain id +# |Concurrent alters| < |Parallel workers on slave| +# |x| denotes number of entities it encloses +# +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/master-slave.inc +--connection master +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +--connection slave +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +set global gtid_strict_mode=1; + + +--connection slave +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads=10; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +--source include/start_slave.inc + +--echo # Parallel Slave +--connection master +--let $master_server= "master" +--let $domain_1=0 +--let $domain_2=0 +--let $M_port= $MASTER_MYPORT +--let $S_port= $SLAVE_MYPORT +--let $sync_slave=1 +--source include/start_alter_include.inc +--connection master +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--echo # cleanup +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +set global gtid_domain_id= 0; +--source include/start_slave.inc + +--connection master +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_3.test b/mysql-test/suite/rpl/t/rpl_start_alter_3.test new file mode 100644 index 00000000000..b280aeb9e5e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_3.test @@ -0,0 +1,54 @@ +# +# Start Alter with Parallel Replication +# 1 domain id +# |Concurrent alters| >= |Parallel workers on slave| +# |x| denotes number of entities it encloses +# +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/master-slave.inc +--source include/have_debug.inc +--connection master +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +--connection slave +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +set global gtid_strict_mode=1; + + +--connection slave +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +--source include/start_slave.inc + +--echo # Parallel Slave +--connection master +--let $master_server= "master" +--let $domain_1=0 +--let $domain_2=0 +--let $M_port= $MASTER_MYPORT +--let $S_port= $SLAVE_MYPORT +--let $sync_slave=1 +--source include/start_alter_include.inc +--connection master +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--echo # cleanup +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +set global gtid_domain_id= 0; +--source include/start_slave.inc + +--connection master +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_4.test b/mysql-test/suite/rpl/t/rpl_start_alter_4.test new file mode 100644 index 00000000000..8c67b50a7bf --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_4.test @@ -0,0 +1,54 @@ +# +# Start Alter with Parallel Replication +# 2 domain id +# |Concurrent alters| < |Parallel workers on slave| +# |x| denotes number of entities it encloses +# +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/master-slave.inc +--source include/have_debug.inc +--connection master +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +--connection slave +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +set global gtid_strict_mode=1; + +--connection slave +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads=10; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +--source include/start_slave.inc + +--echo # Parallel Slave +--connection master +--let $master_server= "master" +--let $domain_1=11 +--let $domain_2=12 +--let $M_port= $MASTER_MYPORT +--let $S_port= $SLAVE_MYPORT +--let $sync_slave=1 +--source include/start_alter_include.inc +--connection master +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--echo # cleanup +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +set global gtid_domain_id= 0; +--source include/start_slave.inc + +--connection master +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; +set global gtid_domain_id= 0; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_5.test b/mysql-test/suite/rpl/t/rpl_start_alter_5.test new file mode 100644 index 00000000000..10d0d523a68 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_5.test @@ -0,0 +1,54 @@ +# +# Start Alter with Parallel Replication +# 2 domain id +# |Concurrent alters| >= |Parallel workers on slave| +# |x| denotes number of entities it encloses +# +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/master-slave.inc +--source include/have_debug.inc +--connection master +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +--connection slave +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +set global gtid_strict_mode=1; + +--connection slave +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +--source include/start_slave.inc + +--echo # Parallel Slave +--connection master +--let $master_server= "master" +--let $domain_1=11 +--let $domain_2=12 +--let $M_port= $MASTER_MYPORT +--let $S_port= $SLAVE_MYPORT +--let $sync_slave=1 +--source include/start_alter_include.inc +--connection master +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--echo # cleanup +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +set global gtid_domain_id= 0; +--source include/start_slave.inc + +--connection master +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; +set global gtid_domain_id= 0; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_6.test b/mysql-test/suite/rpl/t/rpl_start_alter_6.test new file mode 100644 index 00000000000..fc49ea4a406 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_6.test @@ -0,0 +1,58 @@ +# +# Start Alter with Parallel Replication +# 2 domain id +# |Concurrent alters| < |Parallel workers on slave| +# |x| denotes number of entities it encloses +# slave_domain_parallel_threads < |Concurrent Alters| +# +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/master-slave.inc +--source include/have_debug.inc +--connection master +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; +--connection slave +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +--let $slave_domain_parallel_threads= `select @@slave_domain_parallel_threads` +set global gtid_strict_mode=1; + +--connection slave +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads=10; +set global slave_parallel_mode=optimistic; +set global slave_domain_parallel_threads=3; +change master to master_use_gtid=slave_pos; +--source include/start_slave.inc + +--echo # Parallel Slave +--connection master +--let $master_server= "master" +--let $domain_1=11 +--let $domain_2=12 +--let $M_port= $MASTER_MYPORT +--let $S_port= $SLAVE_MYPORT +--let $sync_slave=1 +--source include/start_alter_include.inc +--connection master +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--echo # cleanup +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +--eval set global slave_domain_parallel_threads = $slave_domain_parallel_threads; +set global gtid_domain_id= 0; +--source include/start_slave.inc + +--connection master +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; +set global gtid_domain_id= 0; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_7.cnf b/mysql-test/suite/rpl/t/rpl_start_alter_7.cnf new file mode 100644 index 00000000000..a0f6dc5710c --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_7.cnf @@ -0,0 +1,19 @@ +!include ../my.cnf + +[mysqld.1] +log-bin +log-slave-updates + +[mysqld.2] +log-bin +log-slave-updates + +[mysqld.3] +log-bin +log-slave-updates + + +[ENV] +SERVER_MYPORT_1= @mysqld.1.port +SERVER_MYPORT_2= @mysqld.2.port +SERVER_MYPORT_3= @mysqld.3.port diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_7.test b/mysql-test/suite/rpl/t/rpl_start_alter_7.test new file mode 100644 index 00000000000..7225c075ea7 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_7.test @@ -0,0 +1,112 @@ +# +# Start Alter with Parallel Replication, With 2 sources +# 2 domain id (From 2 sources) +# |Concurrent alters| >= |Parallel workers on slave| +# |x| denotes number of entities it encloses +# +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--connect (server_1,127.0.0.1,root,,,$SERVER_MYPORT_1) +--connect (server_2,127.0.0.1,root,,,$SERVER_MYPORT_2) +--connect (server_3,127.0.0.1,root,,,$SERVER_MYPORT_3) + +--connection server_1 +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; + +--connection server_2 +stop slave; +set global binlog_alter_two_phase=true; + +--connection server_3 +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +SET GLOBAL slave_parallel_threads=8; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; + + +--disable_warnings +--disable_query_log +--replace_result $SERVER_MYPORT_1 MYPORT_1 +eval change master 'm1' to master_port=$SERVER_MYPORT_1 , master_host='127.0.0.1', master_user='root', master_use_gtid=slave_pos; +--replace_result $SERVER_MYPORT_2 MYPORT_2 +eval change master 'm2' to master_port=$SERVER_MYPORT_2 , master_host='127.0.0.1', master_user='root', master_use_gtid=slave_pos; +--enable_query_log +--enable_warnings + +--connection server_1 +set gtid_domain_id= 11; +create database s1; +use s1; +--let $domain_1=11 +--let $domain_2=11 +--let $M_port= $SERVER_MYPORT_1 +--let $S_port= $SERVER_MYPORT_3 +--let $sync_slave=0 +--let $db_name=s1 +--source include/start_alter_include.inc +--connection server_1 +drop database s1; +select @@gtid_binlog_pos; +--let $master_pos_1= `SELECT @@gtid_binlog_pos` + +--connection server_2 +set gtid_domain_id= 12; +create database s2; +use s2; +--let $domain_1=12 +--let $domain_2=12 +--let $M_port= $SERVER_MYPORT_2 +--let $S_port= $SERVER_MYPORT_3 +--let $sync_slave=0 +--let $db_name=s2 +--source include/start_alter_include.inc +--connection server_2 +drop database s2; +select @@gtid_binlog_pos; +--let $master_pos_2= `SELECT @@gtid_binlog_pos` + +--connection server_3 +start all slaves; +set default_master_connection = 'm1'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +--source include/wait_for_slave_to_start.inc + +set default_master_connection = 'm1'; +--let $master_pos= $master_pos_1 +--source include/sync_with_master_gtid.inc +set default_master_connection = 'm2'; +--let $master_pos= $master_pos_2 +--source include/sync_with_master_gtid.inc + +--echo # cleanup +--connection server_3 +set default_master_connection = 'm1'; +--source include/stop_slave.inc +set default_master_connection = 'm2'; +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +set global gtid_domain_id= 0; +reset master; +RESET SLAVE ALL; +SET GLOBAL gtid_slave_pos= ''; + +--connection server_1 +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; +set global gtid_domain_id= 0; +reset master; +--connection server_2 +set global gtid_domain_id= 0; +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase +reset master; + +--disconnect server_1 +--disconnect server_2 +--disconnect server_3 diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_8.cnf b/mysql-test/suite/rpl/t/rpl_start_alter_8.cnf new file mode 100644 index 00000000000..a0f6dc5710c --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_8.cnf @@ -0,0 +1,19 @@ +!include ../my.cnf + +[mysqld.1] +log-bin +log-slave-updates + +[mysqld.2] +log-bin +log-slave-updates + +[mysqld.3] +log-bin +log-slave-updates + + +[ENV] +SERVER_MYPORT_1= @mysqld.1.port +SERVER_MYPORT_2= @mysqld.2.port +SERVER_MYPORT_3= @mysqld.3.port diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_8.test b/mysql-test/suite/rpl/t/rpl_start_alter_8.test new file mode 100644 index 00000000000..4ab8e2b01e5 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_8.test @@ -0,0 +1,109 @@ +# +# Start Alter with Parallel Replication, With 2 sources +# 2 domain id (From 2 sources) +# |Concurrent alters| < |Parallel workers on slave| +# |x| denotes number of entities it encloses +# +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--connect (server_1,127.0.0.1,root,,,$SERVER_MYPORT_1) +--connect (server_2,127.0.0.1,root,,,$SERVER_MYPORT_2) +--connect (server_3,127.0.0.1,root,,,$SERVER_MYPORT_3) + +--connection server_1 +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase = ON; +set binlog_alter_two_phase = ON; + +--connection server_2 +stop slave; +set global binlog_alter_two_phase=true; + +--connection server_3 +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +SET GLOBAL slave_parallel_threads=20; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; + +--disable_warnings +--disable_query_log +--replace_result $SERVER_MYPORT_1 MYPORT_1 +eval change master 'm1' to master_port=$SERVER_MYPORT_1 , master_host='127.0.0.1', master_user='root', master_use_gtid=slave_pos; +--replace_result $SERVER_MYPORT_2 MYPORT_2 +eval change master 'm2' to master_port=$SERVER_MYPORT_2 , master_host='127.0.0.1', master_user='root', master_use_gtid=slave_pos; +--enable_query_log +--enable_warnings + +--connection server_1 +set gtid_domain_id= 11; +create database s1; +use s1; +--let $domain_1=11 +--let $domain_2=11 +--let $M_port= $SERVER_MYPORT_1 +--let $S_port= $SERVER_MYPORT_3 +--let $sync_slave=0 +--let $db_name=s1 +--source include/start_alter_include.inc +--connection server_1 +drop database s1; +--let $master_pos_1= `SELECT @@gtid_binlog_pos` + +--connection server_2 +set gtid_domain_id= 12; +create database s2; +use s2; +--let $domain_1=12 +--let $domain_2=12 +--let $M_port= $SERVER_MYPORT_2 +--let $S_port= $SERVER_MYPORT_3 +--let $sync_slave=0 +--let $db_name=s2 +--source include/start_alter_include.inc +--connection server_2 +drop database s2; +--let $master_pos_2= `SELECT @@gtid_binlog_pos` + +--connection server_3 +start all slaves; +set default_master_connection = 'm1'; +--source include/wait_for_slave_to_start.inc +set default_master_connection = 'm2'; +--source include/wait_for_slave_to_start.inc + +set default_master_connection = 'm1'; +--let $master_pos= $master_pos_1 +--source include/sync_with_master_gtid.inc +set default_master_connection = 'm2'; +--let $master_pos= $master_pos_2 +--source include/sync_with_master_gtid.inc + +--echo # cleanup +--connection server_3 +set default_master_connection = 'm1'; +--source include/stop_slave.inc +set default_master_connection = 'm2'; +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +set global gtid_domain_id= 0; +reset master; +RESET SLAVE ALL; +SET GLOBAL gtid_slave_pos= ''; + +--connection server_1 +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; +set global gtid_domain_id= 0; +reset master; +--connection server_2 +set global gtid_domain_id= 0; +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase; +reset master; + +--disconnect server_1 +--disconnect server_2 +--disconnect server_3 diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_bugs.test b/mysql-test/suite/rpl/t/rpl_start_alter_bugs.test new file mode 100644 index 00000000000..52eef9fbb16 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_bugs.test @@ -0,0 +1,47 @@ +# +# MDEV-22985 Assertion `!(thd->rgi_slave && thd->rgi_slave->did_mark_start_commit)' failed in ha_rollback_trans# +# +# + +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection master +set global binlog_alter_two_phase=true; + +--connection slave +stop slave; +SET global slave_parallel_threads=2; +set global slave_parallel_mode=optimistic; +start slave; +--connection master + +CREATE TABLE t1 (i int primary key) ENGINE = InnoDB; +--connection master1 +ALTER TABLE t1 DROP PRIMARY KEY; +ALTER TABLE t1 ADD UNIQUE KEY ui (i); +ALTER TABLE t1 ADD PRIMARY KEY (i); + +--sync_slave_with_master + + #MENT 1274 +--connection master +drop table t1; +CREATE TABLE t1 (a int)engine=innodb; +ALTER TABLE t1 add column b int, LOCK=EXCLUSIVE; +drop table t1; +CREATE TABLE t1 (pk int)engine=innodb; +--error ER_CANT_DROP_FIELD_OR_KEY +ALTER TABLE t1 DROP FOREIGN KEY y, LOCK=EXCLUSIVE; +drop table t1; +--sync_slave_with_master +--connection master +set global binlog_alter_two_phase=false; + +--connection slave +--source include/stop_slave.inc +SET global slave_parallel_threads=0; +--source include/start_slave.inc + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_chain_basic.cnf b/mysql-test/suite/rpl/t/rpl_start_alter_chain_basic.cnf new file mode 100644 index 00000000000..498d8ed1096 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_chain_basic.cnf @@ -0,0 +1,24 @@ +!include ../my.cnf + +[mysqld.1] +log-slave-updates +loose-innodb + +[mysqld.2] +log-slave-updates +loose-innodb + +[mysqld.3] +log-slave-updates +binlog_alter_two_phase=1 +loose-innodb + +[mysqld.4] +loose-innodb + +[ENV] +SERVER_MYPORT_3= @mysqld.3.port +SERVER_MYSOCK_3= @mysqld.3.socket + +SERVER_MYPORT_4= @mysqld.4.port +SERVER_MYSOCK_4= @mysqld.4.socket diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_chain_basic.test b/mysql-test/suite/rpl/t/rpl_start_alter_chain_basic.test new file mode 100644 index 00000000000..2c6f9c0fd72 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_chain_basic.test @@ -0,0 +1,54 @@ +# +# MENT-662 Lag Free alter for slave +# In this we will see if chain replication works as +# M->S(Legacy)->S(Parallel)->S(Legacy, without log-slave-upadates) +# +--source include/have_innodb.inc +--let $rpl_topology=1->2->3->4 +--source include/rpl_init.inc + +--connection server_3 +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +set global gtid_strict_mode=1; + +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads=10; +set global slave_parallel_mode=optimistic; +change master to master_use_gtid=slave_pos; +--source include/start_slave.inc + + +--connection server_1 +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase=ON; +set binlog_alter_two_phase=ON; +--let $engine=innodb +--let $sync_slave= 1 +connect(master_node,127.0.0.1,root,,$db_name, $SERVER_MYPORT_1); +connect(slave_node,127.0.0.1,root,,test, $SERVER_MYPORT_2); +--source include/start_alter_basic.inc +--disconnect master_node +--disconnect slave_node +--connection server_1 +--eval set global binlog_alter_two_phase=$binlog_alter_two_phase +--source include/rpl_sync.inc + + +--connection server_2 +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; + +--connection server_3 +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +--source include/start_slave.inc +select @@slave_parallel_threads; + +--connection server_4 +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_ftwrl.test b/mysql-test/suite/rpl/t/rpl_start_alter_ftwrl.test new file mode 100644 index 00000000000..a8528cc6197 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_ftwrl.test @@ -0,0 +1,91 @@ +# +# MDEV-11675 Two phase ALTER binlogging +# +# Prove that FTWRL in the middle of START and "COMPLETE" parts of ALTER +# is safe. + +--source include/have_debug.inc +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection slave +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +--let $debug = `SELECT @@global.debug_dbug` + +--source include/stop_slave.inc + +SET @@global.slave_parallel_threads=4; +SET @@global.slave_parallel_mode=optimistic; +CHANGE MASTER TO master_use_gtid=slave_pos; + +SET @@global.debug_dbug="+d,at_write_start_alter"; +--source include/start_slave.inc + +--connection master +SET @@session.binlog_alter_two_phase=true; + +CREATE TABLE t1 (a INT) ENGINE=innodb; +--source include/save_master_gtid.inc + +# Make sure the table exists on slave now. +--connection slave +--source include/sync_with_master_gtid.inc + +--connection master +SET @@session.alter_algorithm='INSTANT'; +SET @@session.gtid_domain_id=11; +ALTER TABLE t1 ADD COLUMN b int; + + +--echo # START Alter having exclusive lock is waiting for the signal +--connection slave +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = 'debug sync point: now' +--source include/wait_condition.inc + +--echo # FTWRL is sent first to wait for SA +--connection slave1 +--send FLUSH TABLES WITH READ LOCK + +--echo # SA completes +# First wait for the FTWRL arrival. +--connection slave +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO = 'FLUSH TABLES WITH READ LOCK' and STATE = 'Waiting for worker threads to pause for global read lock' +--source include/wait_condition.inc + +set DEBUG_SYNC= "now signal alter_cont"; + +--connection slave1 +--reap + +# Commit ALTER is hanging now +--connection slave +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Slave_worker' AND STATE = 'Waiting for backup lock' +--source include/wait_condition.inc + +--echo # Release CA +--connection slave1 +UNLOCK TABLES; + +--connection master +--sync_slave_with_master + +SHOW CREATE TABLE t1; +--source include/show_binlog_events.inc + +--connection master +DROP TABLE t1; + +--sync_slave_with_master + +--echo # cleanup +--connection slave +set DEBUG_SYNC = RESET; +--source include/stop_slave.inc +--eval set global slave_parallel_threads = $slave_parallel_threads +--eval set global slave_parallel_mode = $slave_parallel_mode +--eval set @@global.debug_dbug = "$debug" +--source include/start_slave.inc + + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_instant.test b/mysql-test/suite/rpl/t/rpl_start_alter_instant.test new file mode 100644 index 00000000000..ecb62e04fad --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_instant.test @@ -0,0 +1,30 @@ +# MDEV-11675 +# Prove that "fast" ALTER options also combine with @@binlog_alter_two_phase. +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection master +set binlog_alter_two_phase=true; +CREATE OR REPLACE TABLE tab ( + a int PRIMARY KEY, + b varchar(50), + c varchar(50) +) CHARACTER SET=latin1 engine=innodb; + +SET SESSION alter_algorithm='INSTANT'; +ALTER TABLE tab MODIFY COLUMN b varchar(100); +SET SESSION alter_algorithm='NOCOPY'; +ALTER TABLE tab MODIFY COLUMN c varchar(100); +SHOW CREATE TABLE tab; +--source include/show_binlog_events.inc + +--sync_slave_with_master +SHOW CREATE TABLE tab; +--source include/show_binlog_events.inc + +--connection master +DROP TABLE tab; + +--sync_slave_with_master + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_1.test b/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_1.test new file mode 100644 index 00000000000..5c78eb290c8 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_1.test @@ -0,0 +1,39 @@ +# +# Start Alter with binlog applied using mysqlbinlog +# single maser with only one domain id +# +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/master-slave.inc + +--connection master +set global binlog_alter_two_phase=true; +--connection slave +--source include/stop_slave.inc +change master to master_use_gtid= current_pos; +set global gtid_strict_mode=1; + +--echo # Legacy Master Slave +--let $domain_1=0 +--let $domain_2=0 +--let $M_port= $MASTER_MYPORT +--let $S_port= $SLAVE_MYPORT +--let $sync_slave=0 + +--source include/start_alter_include.inc +--connection master +--let $MYSQLD_DATADIR= `select @@datadir;` +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/master.sql +select @@gtid_binlog_state; +RESET master; + +--connection slave +--exec $MYSQL --host=127.0.0.1 --port=$SLAVE_MYPORT -e "source $MYSQLTEST_VARDIR/tmp/master.sql" +select @@gtid_binlog_state; +set global gtid_strict_mode=0; +--source include/start_slave.inc + +--connection master +set global binlog_alter_two_phase=false; +remove_file $MYSQLTEST_VARDIR/tmp/master.sql; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_2.cnf b/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_2.cnf new file mode 100644 index 00000000000..a0f6dc5710c --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_2.cnf @@ -0,0 +1,19 @@ +!include ../my.cnf + +[mysqld.1] +log-bin +log-slave-updates + +[mysqld.2] +log-bin +log-slave-updates + +[mysqld.3] +log-bin +log-slave-updates + + +[ENV] +SERVER_MYPORT_1= @mysqld.1.port +SERVER_MYPORT_2= @mysqld.2.port +SERVER_MYPORT_3= @mysqld.3.port diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_2.test b/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_2.test new file mode 100644 index 00000000000..c7d5bd66e2b --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_2.test @@ -0,0 +1,164 @@ +# +# MENT-662 Lag Free Alter On Slave +# + +# Start Alter with Parallel Replication, With 2 sources +# 2 domain id (From 2 sources) +# |Concurrent alters| < |Parallel workers on slave| +# |x| denotes number of entities it encloses +# And then binary log from slave is replayed to slave again to check if +# binlog output is okay. +# + +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--connect (server_1,127.0.0.1,root,,,$SERVER_MYPORT_1) +--connect (server_2,127.0.0.1,root,,,$SERVER_MYPORT_2) +--connect (server_3,127.0.0.1,root,,,$SERVER_MYPORT_3) + +--connection server_1 +SET @save_binlog_alter_two_phase= @@GLOBAL.binlog_alter_two_phase; +SET GLOBAL binlog_alter_two_phase = ON; +SET binlog_alter_two_phase = ON; +--echo # Create table and perform CA and RA +CREATE TABLE t1( a INT, b INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1,1); +INSERT INTO t1 VALUES(2,2); +--echo # Normal Alter +ALTER TABLE t1 ADD COLUMN c INT; +--echo # Failed Alter +INSERT INTO t1 VALUES(1,1, NULL); +--error ER_DUP_ENTRY +ALTER TABLE t1 CHANGE a a INT UNIQUE; +SHOW CREATE TABLE t1; +SELECT @@gtid_binlog_state; + +--echo # apply the binlog +let MYSQLD_DATADIR= `select @@datadir;`; +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/master_1.sql +DROP TABLE t1; +--echo # reset the binlog +RESET MASTER; + +--echo # execute the binlog +--exec $MYSQL --port=$SERVER_MYPORT_1 --host=127.0.0.1 -e "source $MYSQLTEST_VARDIR/tmp/master_1.sql" +SELECT @@gtid_binlog_state; +--echo # Same as before +SHOW CREATE TABLE t1; +DROP TABLE t1; +--echo # reset the binlog +RESET MASTER; +RESET SLAVE; +remove_file $MYSQLTEST_VARDIR/tmp/master_1.sql; + +--connection server_2 +SET @save_binlog_alter_two_phase= @@GLOBAL.binlog_alter_two_phase; +SET GLOBAL binlog_alter_two_phase = ON; + +--connection server_3 +SET @save_gtid_strict_mode= @@GLOBAL.gtid_strict_mode; +SET @slave_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @slave_parallel_mode= @@GLOBAL.slave_parallel_mode; +SET GLOBAL slave_parallel_threads=20; +SET GLOBAL slave_parallel_mode=optimistic; +SET GLOBAL gtid_strict_mode=1; + +--disable_warnings +--disable_query_log +--replace_result $SERVER_MYPORT_1 MYPORT_1 +eval CHANGE MASTER 'm1' TO MASTER_PORT=$SERVER_MYPORT_1, MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_USE_GTID=slave_pos; +--replace_result $SERVER_MYPORT_2 MYPORT_2 +eval CHANGE MASTER 'm2' TO MASTER_PORT=$SERVER_MYPORT_2, MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_USE_GTID=slave_pos; +--enable_query_log +--enable_warnings + +--connection server_1 +SET gtid_domain_id= 11; +CREATE DATABASE s1; +USE s1; +--let $domain_1=11 +--let $domain_2=11 +--let $M_port= $SERVER_MYPORT_1 +--let $S_port= $SERVER_MYPORT_3 +--let $sync_slave=0 +--let $db_name=s1 +--source include/start_alter_include.inc + +--connection server_1 +DROP DATABASE s1; +--let $master_pos_1= `SELECT @@gtid_binlog_pos` + +--connection server_2 +SET gtid_domain_id= 12; +CREATE DATABASE s2; +USE s2; +--let $domain_1=12 +--let $domain_2=12 +--let $M_port= $SERVER_MYPORT_2 +--let $S_port= $SERVER_MYPORT_3 +--let $sync_slave=0 +--let $db_name=s2 +--source include/start_alter_include.inc +--connection server_2 +DROP DATABASE s2; +--let $master_pos_2= `SELECT @@gtid_binlog_pos` + +--connection server_3 +START ALL SLAVES; +SET default_master_connection = 'm1'; +--source include/wait_for_slave_to_start.inc +SET default_master_connection = 'm2'; +--source include/wait_for_slave_to_start.inc + +SET default_master_connection = 'm1'; +--let $master_pos= $master_pos_1 +--source include/sync_with_master_gtid.inc +SET default_master_connection = 'm2'; +--let $master_pos= $master_pos_2 +--source include/sync_with_master_gtid.inc + +--echo # Stop slaves and apply binlog +--connection server_3 +SET default_master_connection = 'm1'; +--source include/stop_slave.inc +SET default_master_connection = 'm2'; +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads = @slave_parallel_threads; +SET GLOBAL slave_parallel_mode = @slave_parallel_mode; +SET GLOBAL gtid_strict_mode = @save_gtid_strict_mode; +SET GLOBAL gtid_domain_id= 0; +SELECT @@gtid_binlog_state; + +let MYSQLD_DATADIR= `select @@datadir;`; +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/slave_1.sql +--echo # reset the binlog +RESET MASTER; +--echo # execute the binlog +--exec $MYSQL --port=$SERVER_MYPORT_3 --host=127.0.0.1 -e "source $MYSQLTEST_VARDIR/tmp/slave_1.sql" +SELECT @@gtid_binlog_state; + +--echo # One more time to simulate S->S case +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/slave_2.sql +RESET MASTER; +--echo # execute the binlog +--exec $MYSQL --port=$SERVER_MYPORT_3 --host=127.0.0.1 -e "source $MYSQLTEST_VARDIR/tmp/slave_2.sql" +SELECT @@gtid_binlog_state; +remove_file $MYSQLTEST_VARDIR/tmp/slave_1.sql; +remove_file $MYSQLTEST_VARDIR/tmp/slave_2.sql; +RESET MASTER; +RESET SLAVE ALL; +SET GLOBAL gtid_slave_pos= ''; + +--connection server_1 +SET GLOBAL binlog_alter_two_phase=@save_binlog_alter_two_phase; +SET GLOBAL gtid_domain_id= 0; +RESET MASTER; +--connection server_2 +SET GLOBAL gtid_domain_id= 0; +SET GLOBAL binlog_alter_two_phase=@save_binlog_alter_two_phase; +RESET MASTER; + +--disconnect server_1 +--disconnect server_2 +--disconnect server_3 diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_optimize.test b/mysql-test/suite/rpl/t/rpl_start_alter_optimize.test new file mode 100644 index 00000000000..528f2b52af7 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_optimize.test @@ -0,0 +1,25 @@ +# MDEV-11675 two phase logged ALTER +# +# The tests verifies execution of non-ALTER queries that are handled +# through mysql_alter_table function. + +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection master +set binlog_alter_two_phase = ON; + +--connection master +CREATE TABLE t1 (i int) engine=innodb; +CREATE TABLE t2 (i int) engine=innodb; + +ALTER TABLE t1 DROP CONSTRAINT IF EXISTS y; +OPTIMIZE TABLE t2; + +--sync_slave_with_master + +--connection master +drop table t1,t2; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_options.test b/mysql-test/suite/rpl/t/rpl_start_alter_options.test new file mode 100644 index 00000000000..12125b49122 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_options.test @@ -0,0 +1,31 @@ +# This test will test all the option related to the Alter Table command +# NOTE not all alter statements will follow alter_algorithm since for some statements +# copy is only option +# parameters +# $alter_algorithm {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT} +# $show_binlog +# + +--source include/have_partition.inc +--source include/have_innodb.inc +--source include/master-slave.inc + +--let $alter_algorithm= default +#--let $show_binlog= false +--source include/start_alter_options.inc + +--let $alter_algorithm= inplace +--source include/start_alter_options.inc + +--let $alter_algorithm= copy +--source include/start_alter_options.inc + +--echo # Prove formal support for nocopy and instant +--let $alter_algorithm= instant +--source include/start_alter_options.inc + +--let $alter_algorithm= nocopy +--source include/start_alter_options.inc + + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_para_to_seq.test b/mysql-test/suite/rpl/t/rpl_start_alter_para_to_seq.test new file mode 100644 index 00000000000..37e253139d2 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_para_to_seq.test @@ -0,0 +1,24 @@ +# MDEV-11675 binlog_alter_two_phase +# MDEV-27511 Assertion `rgi->gtid_ev_flags_extra & Gtid_log_event::FL_COMMIT_ALTER_E1' failed +# in write_bin_log_start_alter +# +--source include/have_log_bin.inc +--source include/master-slave.inc + +# The test proves the assert is not hit anymore. +--connection master +CREATE TABLE t1 (a1 int, d1 int DEFAULT 0); +INSERT INTO t1 VALUES (1,1) ; +SET binlog_alter_two_phase = ON; +ALTER TABLE t1 WAIT 9 RENAME COLUMN a1 TO a2; +SET binlog_alter_two_phase = OFF; +ALTER TABLE t1 ALTER COLUMN d1 DROP DEFAULT; + +--sync_slave_with_master + +# Cleanup +--connection master +drop table t1; +--sync_slave_with_master + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_restart_master.test b/mysql-test/suite/rpl/t/rpl_start_alter_restart_master.test new file mode 100644 index 00000000000..83e82bf9509 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_restart_master.test @@ -0,0 +1,75 @@ +# Test crashing of master after writing start alter into binary log. +# And the doing the same alter again, to test on slave if that is successful +# ====> SA Crash SA CA Case +# +--source include/have_log_bin.inc +--source include/have_binlog_format_mixed.inc +--source include/have_innodb.inc +--source include/master-slave.inc +--source include/have_debug.inc + +--connection slave +SET @old_debug_slave= @@global.debug; +stop slave; +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; +start slave; + +--connection master +call mtr.add_suppression("ALTER query started at .+ could not be completed"); + +SET @old_debug_master= @@global.debug; +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set binlog_alter_two_phase=true; +create table t3( a int primary key, b int) engine=innodb; + +--connection master +--sync_slave_with_master +--source include/stop_slave.inc + + +--connection master +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +SET SESSION debug_dbug="d,start_alter_kill_after_binlog"; +--error 2013 +alter table t3 add column d int; + +--let $rpl_server_number= 1 +--source include/rpl_reconnect.inc +set binlog_alter_two_phase= true; +alter table t3 add column d int; +show create table t3; +--source include/show_binlog_events.inc +--let $binlog_file=master-bin.000002 +--source include/show_binlog_events.inc +--let $binlog_file= + +--connection slave +--source include/start_slave.inc + +--connection master +--sync_slave_with_master +--source include/show_binlog_events.inc +show create table t3; + + +--connection master +SET GLOBAL debug_dbug= @old_debug_master; +drop table t3; +--eval set global binlog_alter_two_phase = $binlog_alter_two_phase + +--sync_slave_with_master +SET GLOBAL debug_dbug= @old_debug_slave; +stop slave; +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +start slave; +--connection master +let MYSQLD_DATADIR= `select @@datadir;`; +--remove_files_wildcard $MYSQLD_DATADIR/test #sql*.frm +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_restart_slave.test b/mysql-test/suite/rpl/t/rpl_start_alter_restart_slave.test new file mode 100644 index 00000000000..df028ff1820 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_alter_restart_slave.test @@ -0,0 +1,121 @@ +# This test will restart the slave in middle of start alter commit alter processing +# slave will be restarted after start alter and before binlogging of commit alter, +# Then we will recieved commit alter from the master. +# Commit alter will act like standalone alter +# =====> SA SA CA(Stop Slave before binlogging) CA +# +--source include/have_log_bin.inc +--source include/have_innodb.inc +--source include/master-slave.inc +--source include/have_debug.inc + +--connection slave +SET @old_debug_slave= @@global.debug; +--source include/stop_slave.inc +--let $gtid_strict_mode= `select @@gtid_strict_mode` +--let $slave_parallel_threads= `select @@slave_parallel_threads` +--let $slave_parallel_mode= `select @@slave_parallel_mode` +SET GLOBAL slave_parallel_threads=4; +set global slave_parallel_mode=optimistic; +set global gtid_strict_mode=1; + +set global debug_dbug="+d,rpl_slave_stop_CA_before_binlog"; + +--source include/start_slave.inc +# +# SLAVE Shutdown +--connection master +SET @old_debug_master= @@global.debug; +set global debug_dbug="+d,start_alter_delay_master"; +--let $binlog_alter_two_phase= `select @@binlog_alter_two_phase` +set global binlog_alter_two_phase=true; +create table t1( a int primary key, b int) engine=myisam; +create table t2( a int primary key, b int) engine=myisam; + +--connect (con1,localhost,root,,) +--send alter table t1 add column c int; + +--connection master +--echo # Get into binlog first and wait +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = 'debug sync point: now'; +--source include/wait_condition.inc +--let $master_gtid_state = `select @@gtid_binlog_state` +--echo # master gtid state is $master_gtid_state + +--connect (con2,localhost,root,,) +--send alter table t2 add column c int; + +--connection master +--echo # Get into binlog next and wait as well +--let $wait_condition = SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = 'debug sync point: now'; +--source include/wait_condition.inc +--let $master_gtid_state = `select @@gtid_binlog_state` +--echo # master gtid state is $master_gtid_state + +# Memorize for slave's next sync with master +--let $master_pos=$master_gtid_state + +set DEBUG_SYNC= "now signal alter_cont"; + +--connection con1 +--reap +--connection con2 +--reap +create table t3( a int primary key, b int) engine=innodb; +--let $master_gtid_state = `select @@gtid_binlog_state` +--echo # master gtid state is $master_gtid_state +--let $replace_regexp=/alter table t[12]/alter table <t>/ /id=[0-9]+/id=<seq_no>/ +--source include/show_binlog_events2.inc + +--echo # Stop Slave +--echo # As master binlog is SA SA CA CA +--echo # let's stop at first CA processing (in process_commit_alter) + +--connection slave +--source include/sync_with_master_gtid.inc +# set debug_sync="now wait_for CA_1_processing"; +connect(extra_slave,127.0.0.1,root,,test,$SLAVE_MYPORT); +--send stop slave; +--connection slave +# set debug_sync="now signal proceed_CA_1"; +--connection extra_slave +--reap +SET GLOBAL debug_dbug= @old_debug_slave; + +--connection slave +--source include/wait_for_slave_sql_to_stop.inc +--echo # The list of events after the slave has stopped must have just one CA: +--let $replace_regexp=/alter table t[12]/alter table <t>/ /id=[0-9]+/id=<seq_no>/ +--source include/show_binlog_events2.inc + +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; +--source include/start_slave.inc +--connection master +--sync_slave_with_master +--echo # Everything from the master binlog must have been applied now: +select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1; +--let $slave_gtid_state = `select @@gtid_binlog_state` +--echo # slave gtid state is $slave_gtid_state +if (`select $master_gtid_state <> $slave_gtid_state`) +{ + --echo Gtid state mismatch: $master_gtid_state <> $slave_gtid_state + --die +} +--echo # The list of events after the slave has synchronized must have both CA: +--let $replace_regexp=/alter table t[12]/alter table <t>/ /id=[0-9]+/id=<seq_no>/ +--source include/show_binlog_events2.inc + +--connection master +drop table t1,t2,t3; +--eval set global binlog_alter_two_phase = $binlog_alter_two_phase +SET GLOBAL debug_dbug= @old_debug_master; +set DEBUG_SYNC= 'RESET'; + +--sync_slave_with_master +stop slave; +--eval set global slave_parallel_threads = $slave_parallel_threads; +--eval set global slave_parallel_mode = $slave_parallel_mode; +--eval set global gtid_strict_mode = $gtid_strict_mode; +set DEBUG_SYNC= 'RESET'; +start slave; +--source include/rpl_end.inc |