diff options
Diffstat (limited to 'mysql-test/extra/rpl_tests/rpl_extra_col_slave.test')
| -rw-r--r-- | mysql-test/extra/rpl_tests/rpl_extra_col_slave.test | 918 |
1 files changed, 918 insertions, 0 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_extra_col_slave.test b/mysql-test/extra/rpl_tests/rpl_extra_col_slave.test new file mode 100644 index 00000000000..119e081878c --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_extra_col_slave.test @@ -0,0 +1,918 @@ +################################################# +# Purpose: To test having extra columns on the slave. +################################################## + +# Some tests in here requre partitioning +-- source include/have_partition.inc + +################################################# +############ Different Table Def Test ########### +################################################# +# Purpose: To have different table def on the # +# master and slave. Most of these tests# +# should stop the slave. # +################################################# + +call mtr.add_suppression("Slave: Unknown table 't6' Error_code: 1051"); +call mtr.add_suppression("Slave SQL.*Column [0-9] of table .test.t[0-9]*. cannot be converted from type.* Error_code: 1677"); + +--echo **** Diff Table Def Start **** + +############################################## +### Try to replicate w/ PK on diff columns ### +### Should Stop Slave ### +############################################## + +--echo *** On Slave *** +sync_slave_with_master; +STOP SLAVE; +RESET SLAVE; + +SET @saved_slave_type_conversions = @@slave_type_conversions; +SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_NON_LOSSY'; + +eval CREATE TABLE t1 (a INT, b INT PRIMARY KEY, c CHAR(20), + d FLOAT DEFAULT '2.00', + e CHAR(4) DEFAULT 'TEST') + ENGINE=$engine_type; + +--echo *** Create t1 on Master *** +connection master; +eval CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(10) + ) ENGINE=$engine_type; + +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; + +INSERT INTO t1 () VALUES(1,2,'TEXAS'),(2,1,'AUSTIN'),(3,4,'QA'); +SELECT * FROM t1 ORDER BY a; + +--echo *** Select from slave *** +sync_slave_with_master; +SELECT * FROM t1 ORDER BY a; + +SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions; + +--echo *** Drop t1 *** +connection master; +DROP TABLE t1; +sync_slave_with_master; + +############################################ +### Try to replicate CHAR(10) to CHAR(5) ### +### Should Stop Slave or truncate value ### +############################################ + +## BUG22086 +--echo *** Create t2 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t2 (a INT, b INT PRIMARY KEY, c CHAR(5), + d FLOAT DEFAULT '2.00', + e CHAR(5) DEFAULT 'TEST2') + ENGINE=$engine_type; + +--echo *** Create t2 on Master *** +connection master; +eval CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(10) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Master Data Insert *** +connection master; + +INSERT INTO t2 () VALUES(1,2,'Kyle, TEX'),(2,1,'JOE AUSTIN'),(3,4,'QA TESTING'); +SELECT * FROM t2 ORDER BY a; + +--echo *** Start Slave *** +connection slave; +START SLAVE; +--let $slave_sql_errno= 1677 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error.inc +STOP SLAVE; +RESET SLAVE; +SELECT * FROM t2 ORDER BY a; + +connection master; +RESET MASTER; + +connection slave; +START SLAVE; + +--echo *** Drop t2 *** +connection master; +DROP TABLE t2; +sync_slave_with_master; + +#################################### +### Try to replicate BLOB to INT ### +### Should Stop Slave ### +#################################### +--echo *** Create t3 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t3 (a INT, b INT PRIMARY KEY, c CHAR(20), + d FLOAT DEFAULT '2.00', + e CHAR(5) DEFAULT 'TEST2') + ENGINE=$engine_type; + +--echo *** Create t3 on Master *** +connection master; +eval CREATE TABLE t3 (a BLOB, b INT PRIMARY KEY, c CHAR(20) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; + +set @b1 = 'b1'; +set @b1 = concat(@b1,@b1); + +INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TESTING'); + +--echo ******************************************** +--echo *** Expect slave to fail with Error 1677 *** +--echo ******************************************** +connection slave; +--let $slave_sql_errno= 1677 +--let $slave_skip_counter= 2 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error_and_skip.inc + +--echo *** Drop t3 *** +connection master; +DROP TABLE t3; +sync_slave_with_master; + +##################################################### +# Columns with different types, more columns at end # +# Expect: proper error message (wrong types) # +##################################################### + +--echo *** Create t4 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t4 (a INT, b INT PRIMARY KEY, c CHAR(20), + d FLOAT DEFAULT '2.00', + e CHAR(5) DEFAULT 'TEST2') + ENGINE=$engine_type; + +--echo *** Create t4 on Master *** +connection master; +eval CREATE TABLE t4 (a DECIMAL(8,2), b INT PRIMARY KEY, c CHAR(20) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; + +INSERT INTO t4 () VALUES(100.22,2,'Kyle, TEX'),(200.26,1,'JOE AUSTIN'), + (30000.22,4,'QA TESTING'); + +--echo ******************************************** +--echo *** Expect slave to fail with Error 1677 *** +--echo ******************************************** +connection slave; +--let $slave_sql_errno= 1677 +--let $slave_skip_counter= 2 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error_and_skip.inc + +--echo *** Drop t4 *** +connection master; +DROP TABLE t4; +sync_slave_with_master; + +####################################################### +# Columns with different types, same number of colums # +# Expect: Proper error message # +####################################################### + +--echo *** Create t5 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t5 (a INT PRIMARY KEY, b CHAR(5), + c FLOAT, d INT, e DOUBLE, + f DECIMAL(8,2))ENGINE=$engine_type; + +--echo *** Create t5 on Master *** +connection master; +eval CREATE TABLE t5 (a INT PRIMARY KEY, b VARCHAR(6), + c DECIMAL(8,2), d BIT, e BLOB, + f FLOAT) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; + +INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), + (2,'JOE',300.01,0,'b2b2',1.0000009); + +--echo ******************************************** +--echo *** Expect slave to fail with Error 1677 *** +--echo ******************************************** +connection slave; +--let $slave_sql_errno= 1677 +--let $slave_skip_counter= 2 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error_and_skip.inc + +--echo *** Drop t5 *** +connection master; +DROP TABLE t5; +sync_slave_with_master; + +####################################################### +################## Continued ########################## +####################################################### +# Columns with different types, same number of colums # +# Expect: Proper error message # +####################################################### + +--echo *** Create t6 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t6 (a INT PRIMARY KEY, b CHAR(5), + c FLOAT, d INT)ENGINE=$engine_type; + +--echo *** Create t6 on Master *** +connection master; +eval CREATE TABLE t6 (a INT PRIMARY KEY, b VARCHAR(6), + c DECIMAL(8,2), d BIT + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; + +INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), + (2,'JOE',300.01,0); + +--echo ******************************************** +--echo *** Expect slave to fail with Error 1677 *** +--echo ******************************************** +connection slave; +--let $slave_sql_errno= 1677 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error.inc + +--echo *** Drop t6 *** +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +connection master; +DROP TABLE t6; +--sync_slave_with_master + + +--echo **** Diff Table Def End **** + +####################################### +#### Extra Column on Slave Testing #### +####################################### +# Purpose: To test extra colums on the# +# Slave # +####################################### + +--echo **** Extra Colums Start **** + +########################################## +# More columns in slave at end of table, # +# added columns have default values # +# Expect: it should work, default values # +# should be used # +########################################## + +--echo *** Create t7 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t7 (a INT KEY, b BLOB, c CHAR(5), + d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00', + e CHAR(20) DEFAULT 'Extra Column Testing') + ENGINE=$engine_type; + +--echo *** Create t7 on Master *** +connection master; +eval CREATE TABLE t7 (a INT PRIMARY KEY, b BLOB, c CHAR(5) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t7 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); +SELECT * FROM t7 ORDER BY a; + +--echo *** Select from slave *** +sync_slave_with_master; +SELECT * FROM t7 ORDER BY a; + +--echo *** Drop t7 *** +connection master; +DROP TABLE t7; +sync_slave_with_master; + +########################################### +# More columns in slave at end of table, # +# added columns do not have default values# +# Expect: Proper error message # +########################################### +# NOTE: This should fail but currently # +# works. BUG#22101 # +########################################### +--echo *** Create t8 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t8 (a INT KEY, b BLOB, c CHAR(5), + d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00', + e INT)ENGINE=$engine_type; + +--echo *** Create t8 on Master *** +connection master; +eval CREATE TABLE t8 (a INT PRIMARY KEY, b BLOB, c CHAR(5) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t8 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); + +### Uncomment once bug is fixed + +#connection slave; +#--let $slave_sql_errno= SOMETHING +#--let $slave_skip_counter= 2 +#--let $show_slave_sql_error= 1 +#--source include/wait_for_slave_sql_error_and_skip.inc + +--echo *** Drop t8 *** +connection master; +DROP TABLE t8; +sync_slave_with_master; + +########################################### +############# Continued ################### +# More columns in slave at end of table, # +# added columns do not have default values# +# Expect: Proper error message # +########################################### +# Bug#22234, Bug#23907 Extra Slave Col is not +# erroring on extra col with no default values. +############################################################### +# Error reaction is up to sql_mode of the slave sql (bug#38173) +#--echo *** Create t9 on slave *** +# Please, check BUG#47741 to see why you are not testing NDB. +if (`SELECT UPPER(LEFT($engine_type, 3)) != 'NDB'`) +{ + STOP SLAVE; + RESET SLAVE; + eval CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), + d TIMESTAMP, + e INT NOT NULL, + f text not null, + g text, + h blob not null, + i blob) ENGINE=$engine_type; + + --echo *** Create t9 on Master *** + connection master; + eval CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5) + ) ENGINE=$engine_type; + RESET MASTER; + + --echo *** Start Slave *** + connection slave; + START SLAVE; + + --echo *** Master Data Insert *** + connection master; + set @b1 = 'b1b1b1b1'; + + set @b1 = concat(@b1,@b1); + INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); + + # the test would stop slave if @@sql_mode for the sql thread + # was set to strict. Otherwise, as with this tests setup, + # the implicit defaults will be inserted into fields even though + # they are declared without DEFAULT clause. + + sync_slave_with_master; + select * from t9; + + # todo: fix Bug #43992 slave sql thread can't tune own sql_mode ... + # and add/restore waiting for stop test + + #--let $slave_sql_errno= SOMETHING + #--let $slave_skip_counter= 2 + #--let $show_slave_sql_error= 1 + #--source include/wait_for_slave_sql_error_and_skip.inc + + #--echo *** Drop t9 *** + connection master; + DROP TABLE t9; + sync_slave_with_master; + +} + +############################################ +# More columns in slave at middle of table # +# Expect: Proper error message # +############################################ +--echo *** Create t10 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233', + c CHAR(5), e INT DEFAULT '1')ENGINE=$engine_type; + +--echo *** Create t10 on Master *** +connection master; +eval CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); + +--echo ******************************************** +--echo *** Expect slave to fail with Error 1677 *** +--echo ******************************************** +connection slave; +--let $slave_sql_errno= 1677 +--let $slave_skip_counter= 2 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error_and_skip.inc + +--echo *** Drop t10 *** +connection master; +DROP TABLE t10; +sync_slave_with_master; + +############################################ +############## Continued ################### +############################################ +# More columns in slave at middle of table # +# Expect: Proper error message # +############################################ +--echo *** Create t11 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t11 (a INT KEY, b BLOB, f INT, + c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type; + +--echo *** Create t11 on Master *** +connection master; +eval CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); + +--echo ******************************************** +--echo *** Expect slave to fail with Error 1677 *** +--echo ******************************************** +connection slave; +--let $slave_sql_errno= 1677 +--let $slave_skip_counter= 2 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error_and_skip.inc + +--echo *** Drop t11 *** +connection master; +DROP TABLE t11; +sync_slave_with_master; + +############################################ +############## Continued ################### +############################################ +# More columns in slave at middle of table # +# Expect: This one should pass blob-text # +############################################ +--echo *** Create t12 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT, + c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type; + +--echo *** Create t12 on Master *** +connection master; +eval CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t12 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); +SELECT * FROM t12 ORDER BY a; + +--echo *** Select on Slave *** +sync_slave_with_master; +SELECT * FROM t12 ORDER BY a; + +--echo *** Drop t12 *** +connection master; +DROP TABLE t12; +sync_slave_with_master; + +--echo **** Extra Colums End **** + +############################### +# BUG#22177 CURRENT_TIMESTAMP # +# Sould work with ^ # +############################### +--echo *** BUG 22177 Start *** +--echo *** Create t13 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t13 (a INT KEY, b BLOB, c CHAR(5), + d INT DEFAULT '1', + e TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP + )ENGINE=$engine_type; + +--echo *** Create t13 on Master *** +connection master; +eval CREATE TABLE t13 (a INT PRIMARY KEY, b BLOB, c CHAR(5) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t13 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); +SELECT * FROM t13 ORDER BY a; + +--echo *** Select on Slave **** +sync_slave_with_master; +--replace_column 5 CURRENT_TIMESTAMP +SELECT * FROM t13 ORDER BY a; + +--echo *** Drop t13 *** +connection master; +DROP TABLE t13; +sync_slave_with_master; + +--echo *** 22117 END *** + +############################## +# ALTER MASTER TABLE TESTING # +############################## + +--echo *** Alter Master Table Testing Start *** + +#################################################### +# - Alter Master adding columns at middle of table # +# Expect: columns added # +#################################################### + +--echo *** Create t14 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5), + c6 INT DEFAULT '1', + c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP + )ENGINE=$engine_type; + +--echo *** Create t14 on Master *** +connection master; +eval CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1; +ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'), + (2,2.00,'This Test Should work',@b1,'JOE'), + (3,3.00,'If is does not, I will open a bug',@b1,'QA'); +SELECT * FROM t14 ORDER BY c1; + + +--echo *** Select on Slave **** +sync_slave_with_master; +--replace_column 7 CURRENT_TIMESTAMP +SELECT * FROM t14 ORDER BY c1; + +#################################################### +# - Alter Master drop column at end of table # +# Expect: column dropped # +#################################################### + +--echo *** Create t14a on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t14a (c1 INT KEY, c4 BLOB, c5 CHAR(5), + c6 INT DEFAULT '1', + c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP + )ENGINE=$engine_type; + +--echo *** Create t14a on Master *** +connection master; +eval CREATE TABLE t14a (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t14a () VALUES(1,@b1,'Kyle'), + (2,@b1,'JOE'), + (3,@b1,'QA'); + +SELECT * FROM t14a ORDER BY c1; +--echo *** Select on Slave **** +sync_slave_with_master; +--replace_column 5 CURRENT_TIMESTAMP +SELECT * FROM t14a ORDER BY c1; +STOP SLAVE; +RESET SLAVE; + +--echo *** Master Drop c5 *** +connection master; +ALTER TABLE t14a DROP COLUMN c5; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); + +INSERT INTO t14a () VALUES(4,@b1), + (5,@b1), + (6,@b1); +SELECT * FROM t14a ORDER BY c1; + +--echo *** Select on Slave **** +sync_slave_with_master; +--replace_column 5 CURRENT_TIMESTAMP +SELECT * FROM t14a ORDER BY c1; + +--connection master +DROP TABLE t14a; +--sync_slave_with_master + +#################################################### +# - Alter Master Dropping columns from the middle. # +# Expect: columns dropped # +#################################################### + +--echo *** connect to master and drop columns *** +connection master; +ALTER TABLE t14 DROP COLUMN c2; +ALTER TABLE t14 DROP COLUMN c4; +--echo *** Select from Master *** +SELECT * FROM t14 ORDER BY c1; + +--echo *** Select from Slave *** +sync_slave_with_master; +--replace_column 5 CURRENT_TIMESTAMP +SELECT * FROM t14 ORDER BY c1; + +--echo *** Drop t14 *** +connection master; +DROP TABLE t14; +sync_slave_with_master; + +############################################################## +# - Alter Master adding columns that already exist on slave. # +# Expect: proper error message # +############################################################## + +--echo *** Create t15 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t15 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT, + c4 BLOB, c5 CHAR(5), + c6 INT DEFAULT '1', + c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP + )ENGINE=$engine_type; + +--echo *** Create t15 on Master *** +connection master; +eval CREATE TABLE t15 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT, + c4 BLOB, c5 CHAR(5)) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +call mtr.add_suppression("Error .Unknown table .t6.. on query.* Error_code: 1051"); +call mtr.add_suppression("Error .Duplicate column name .c6.. on query.* Error_code: 1060"); +call mtr.add_suppression("Table definition on master and slave does not match: Column . ...e mismatch.* Error_code: 1535"); + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t15 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'), + (2,2.00,'This Test Should work',@b1,'JOE'), + (3,3.00,'If is does not, I will open a bug',@b1,'QA'); +SELECT * FROM t15 ORDER BY c1; + + +--echo *** Select on Slave **** +sync_slave_with_master; +--replace_column 7 CURRENT_TIMESTAMP +SELECT * FROM t15 ORDER BY c1; + +--echo *** Add column on master that is a Extra on Slave *** +connection master; +ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5; + +--echo ******************************************** +--echo *** Expect slave to fail with Error 1060 *** +--echo ******************************************** +connection slave; +--let $slave_sql_errno= 1060 +--let $slave_skip_counter= 1 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error_and_skip.inc + +--echo *** Try to insert in master **** +connection master; +INSERT INTO t15 () VALUES(5,2.00,'Replication Testing',@b1,'Buda',2); +SELECT * FROM t15 ORDER BY c1; + +#SHOW BINLOG EVENTS; + +--echo *** Try to select from slave **** +sync_slave_with_master; +--replace_column 7 CURRENT_TIMESTAMP +SELECT * FROM t15 ORDER BY c1; + +--echo *** DROP TABLE t15 *** +connection master; +DROP TABLE t15; +sync_slave_with_master; + +#################################### +# - Alter Master and ADD PARTITION # +# Expect:? # +#################################### + +--echo *** Create t16 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t16 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT, + c4 BLOB, c5 CHAR(5), + c6 INT DEFAULT '1', + c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP + )ENGINE=$engine_type; + +--echo *** Create t16 on Master *** +connection master; +eval CREATE TABLE t16 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT, + c4 BLOB, c5 CHAR(5))ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; +set @b1 = 'b1b1b1b1'; +set @b1 = concat(@b1,@b1); +INSERT INTO t16 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'), + (2,2.00,'This Test Should work',@b1,'JOE'), + (3,3.00,'If is does not, I will open a bug',@b1,'QA'); +SELECT * FROM t16 ORDER BY c1; + +--echo *** Select on Slave **** +sync_slave_with_master; +--replace_column 7 CURRENT_TIMESTAMP +SELECT * FROM t16 ORDER BY c1; + +--echo *** Add Partition on master *** +connection master; +ALTER TABLE t16 PARTITION BY KEY(c1) PARTITIONS 4; +INSERT INTO t16 () VALUES(4,1.00,'Replication Rocks',@b1,'Omer'); +SHOW CREATE TABLE t16; + +--echo *** Show table on Slave **** +sync_slave_with_master; +SHOW CREATE TABLE t16; + +--echo *** DROP TABLE t16 *** +connection master; +DROP TABLE t16; +sync_slave_with_master; + +--echo *** Alter Master End *** + +############################################ +### Try to replicate BIGINT to SMALLINT ### +### Should Stop Slave ### +############################################ + +--echo *** Create t17 on slave *** +STOP SLAVE; +RESET SLAVE; +eval CREATE TABLE t17 (a SMALLINT, b INT PRIMARY KEY, c CHAR(5), + d FLOAT DEFAULT '2.00', + e CHAR(5) DEFAULT 'TEST2') + ENGINE=$engine_type; + +--echo *** Create t17 on Master *** +connection master; +eval CREATE TABLE t17 (a BIGINT PRIMARY KEY, b INT, c CHAR(10) + ) ENGINE=$engine_type; +RESET MASTER; + +--echo *** Start Slave *** +connection slave; +START SLAVE; + +--echo *** Master Data Insert *** +connection master; + +INSERT INTO t17 () VALUES(9223372036854775807,2,'Kyle, TEX'); + +--echo ******************************************** +--echo *** Expect slave to fail with Error 1677 *** +--echo ******************************************** +connection slave; +--let $slave_sql_errno= 1677 +--let $slave_skip_counter= 2 +--let $show_slave_sql_error= 1 +--source include/wait_for_slave_sql_error_and_skip.inc + +--echo ** DROP table t17 *** +connection master; +DROP TABLE t17; +sync_slave_with_master; |