stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; **** Diff Table Def Start **** *** On Slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t1 (a INT, b INT PRIMARY KEY, c CHAR(20), d FLOAT DEFAULT '2.00', e CHAR(4) DEFAULT 'TEST') ENGINE='MyISAM'; *** Create t1 on Master *** CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(10) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** INSERT INTO t1 () VALUES(1,2,'TEXAS'),(2,1,'AUSTIN'),(3,4,'QA'); SELECT * FROM t1 ORDER BY a; a b c 1 2 TEXAS 2 1 AUSTIN 3 4 QA *** Select from slave *** SELECT * FROM t1 ORDER BY a; a b c d e 1 2 TEXAS 2 TEST 2 1 AUSTIN 2 TEST 3 4 QA 2 TEST *** Drop t1 *** DROP TABLE t1; *** Create t2 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t2 (a INT, b INT PRIMARY KEY, c CHAR(5), d FLOAT DEFAULT '2.00', e CHAR(5) DEFAULT 'TEST2') ENGINE='MyISAM'; *** Create t2 on Master *** CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(10) ) ENGINE='MyISAM'; RESET MASTER; *** Master Data Insert *** INSERT INTO t2 () VALUES(1,2,'Kyle, TEX'),(2,1,'JOE AUSTIN'),(3,4,'QA TESTING'); SELECT * FROM t2 ORDER BY a; a b c 1 2 Kyle, TEX 2 1 JOE AUSTIN 3 4 QA TESTING *** Start Slave *** START SLAVE; SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port # Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos # Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1534 Last_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1534 Last_SQL_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. STOP SLAVE; RESET SLAVE; SELECT * FROM t2 ORDER BY a; a b c d e RESET MASTER; START SLAVE; *** Drop t2 *** DROP TABLE t2; *** Create t3 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t3 (a INT, b INT PRIMARY KEY, c CHAR(20), d FLOAT DEFAULT '2.00', e CHAR(5) DEFAULT 'TEST2') ENGINE='MyISAM'; *** Create t3 on Master *** CREATE TABLE t3 (a BLOB, b INT PRIMARY KEY, c CHAR(20) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** set @b1 = 'b1'; set @b1 = concat(@b1,@b1); INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TESTING'); ******************************************** *** Expect slave to fail with Error 1522 *** ******************************************** SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port # Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos # Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1534 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1534 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; *** Drop t3 *** DROP TABLE t3; *** Create t4 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t4 (a INT, b INT PRIMARY KEY, c CHAR(20), d FLOAT DEFAULT '2.00', e CHAR(5) DEFAULT 'TEST2') ENGINE='MyISAM'; *** Create t4 on Master *** CREATE TABLE t4 (a DECIMAL(8,2), b INT PRIMARY KEY, c CHAR(20) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** INSERT INTO t4 () VALUES(100.22,2,'Kyle, TEX'),(200.26,1,'JOE AUSTIN'), (30000.22,4,'QA TESTING'); ******************************************** *** Expect slave to fail with Error 1522 *** ******************************************** SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port # Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos # Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1534 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1534 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; *** Drop t4 *** DROP TABLE t4; *** Create t5 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t5 (a INT PRIMARY KEY, b CHAR(5), c FLOAT, d INT, e DOUBLE, f DECIMAL(8,2))ENGINE='MyISAM'; *** Create t5 on Master *** CREATE TABLE t5 (a INT PRIMARY KEY, b VARCHAR(6), c DECIMAL(8,2), d BIT, e BLOB, f FLOAT) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), (2,'JOE',300.01,0,'b2b2',1.0000009); ******************************************** *** Expect slave to fail with Error 1522 *** ******************************************** SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port # Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos # Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1534 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1534 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; *** Drop t5 *** DROP TABLE t5; *** Create t6 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t6 (a INT PRIMARY KEY, b CHAR(5), c FLOAT, d INT)ENGINE='MyISAM'; *** Create t6 on Master *** CREATE TABLE t6 (a INT PRIMARY KEY, b VARCHAR(6), c DECIMAL(8,2), d BIT ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), (2,'JOE',300.01,0); ******************************************** *** Expect slave to fail with Error 1522 *** ******************************************** SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port # Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos # Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1534 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1534 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** DROP TABLE t6; DROP TABLE t6; START SLAVE; **** Diff Table Def End **** **** Extra Colums Start **** *** Create t7 on slave *** STOP SLAVE; RESET SLAVE; 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='MyISAM'; *** Create t7 on Master *** CREATE TABLE t7 (a INT PRIMARY KEY, b BLOB, c CHAR(5) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** 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; a b c 1 b1b1 Kyle 2 b1b1 JOE 3 b1b1 QA *** Select from slave *** SELECT * FROM t7 ORDER BY a; a b c d e 1 b1b1 Kyle 0000-00-00 00:00:00 Extra Column Testing 2 b1b1 JOE 0000-00-00 00:00:00 Extra Column Testing 3 b1b1 QA 0000-00-00 00:00:00 Extra Column Testing *** Drop t7 *** DROP TABLE t7; *** Create t8 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t8 (a INT KEY, b BLOB, c CHAR(5), d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00', e INT)ENGINE='MyISAM'; *** Create t8 on Master *** CREATE TABLE t8 (a INT PRIMARY KEY, b BLOB, c CHAR(5) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** set @b1 = 'b1b1b1b1'; set @b1 = concat(@b1,@b1); INSERT INTO t8 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); *** Drop t8 *** DROP TABLE t8; *** Create t10 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233', c CHAR(5), e INT DEFAULT '1')ENGINE='MyISAM'; *** Create t10 on Master *** CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** set @b1 = 'b1b1b1b1'; set @b1 = concat(@b1,@b1); INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** *** Expect slave to fail with Error 1522 *** ******************************************** SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port # Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos # Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1534 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1534 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; *** Drop t10 *** DROP TABLE t10; *** Create t11 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT, c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM'; *** Create t11 on Master *** CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** set @b1 = 'b1b1b1b1'; set @b1 = concat(@b1,@b1); INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** *** Expect slave to fail with Error 1522 *** ******************************************** SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port # Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos # Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1534 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1534 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; *** Drop t11 *** DROP TABLE t11; *** Create t12 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT, c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM'; *** Create t12 on Master *** CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** 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; a b c 1 b1b1b1b1b1b1b1b1 Kyle 2 b1b1b1b1b1b1b1b1 JOE 3 b1b1b1b1b1b1b1b1 QA *** Select on Slave *** SELECT * FROM t12 ORDER BY a; a b f c e 1 b1b1b1b1b1b1b1b1 Kyle test 1 2 b1b1b1b1b1b1b1b1 JOE test 1 3 b1b1b1b1b1b1b1b1 QA test 1 *** Drop t12 *** DROP TABLE t12; **** Extra Colums End **** *** BUG 22177 Start *** *** Create t13 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t13 (a INT KEY, b BLOB, c CHAR(5), d INT DEFAULT '1', e TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP )ENGINE='MyISAM'; *** Create t13 on Master *** CREATE TABLE t13 (a INT PRIMARY KEY, b BLOB, c CHAR(5) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** 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; a b c 1 b1b1b1b1b1b1b1b1 Kyle 2 b1b1b1b1b1b1b1b1 JOE 3 b1b1b1b1b1b1b1b1 QA *** Select on Slave **** SELECT * FROM t13 ORDER BY a; a b c d e 1 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 2 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 3 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP *** Drop t13 *** DROP TABLE t13; *** 22117 END *** *** Alter Master Table Testing Start *** *** Create t14 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5), c6 INT DEFAULT '1', c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP )ENGINE='MyISAM'; *** Create t14 on Master *** CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** 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; c1 c2 c3 c4 c5 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA *** Select on Slave **** SELECT * FROM t14 ORDER BY c1; c1 c2 c3 c4 c5 c6 c7 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP *** connect to master and drop columns *** ALTER TABLE t14 DROP COLUMN c2; ALTER TABLE t14 DROP COLUMN c4; *** Select from Master *** SELECT * FROM t14 ORDER BY c1; c1 c3 c5 1 Replication Testing Extra Col Kyle 2 This Test Should work JOE 3 If is does not, I will open a bug QA *** Select from Slave *** SELECT * FROM t14 ORDER BY c1; c1 c3 c5 c6 c7 1 Replication Testing Extra Col Kyle 1 CURRENT_TIMESTAMP 2 This Test Should work JOE 1 CURRENT_TIMESTAMP 3 If is does not, I will open a bug QA 1 CURRENT_TIMESTAMP *** Drop t14 *** DROP TABLE t14; *** Create t15 on slave *** STOP SLAVE; RESET SLAVE; 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='MyISAM'; *** Create t15 on Master *** CREATE TABLE t15 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT, c4 BLOB, c5 CHAR(5)) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** set @b1 = 'b1b1b1b1'; set @b1 = concat(@b1,@b1); INSERT INTO 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; c1 c2 c3 c4 c5 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA *** Select on Slave **** SELECT * FROM t15 ORDER BY c1; c1 c2 c3 c4 c5 c6 c7 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP *** Add column on master that is a Extra on Slave *** ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5; ******************************************** *** Expect slave to fail with Error 1060 *** ******************************************** SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port # Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos # Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1060 Last_Error Error 'Duplicate column name 'c6'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5' Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1060 Last_SQL_Error Error 'Duplicate column name 'c6'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5' SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; *** Try to insert in master **** INSERT INTO t15 () VALUES(5,2.00,'Replication Testing',@b1,'Buda',2); SELECT * FROM t15 ORDER BY c1; c1 c2 c3 c4 c5 c6 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle NULL 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE NULL 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA NULL 5 2.00 Replication Testing b1b1b1b1b1b1b1b1 Buda 2 *** Try to select from slave **** SELECT * FROM t15 ORDER BY c1; c1 c2 c3 c4 c5 c6 c7 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP 5 2.00 Replication Testing b1b1b1b1b1b1b1b1 Buda 2 CURRENT_TIMESTAMP *** DROP TABLE t15 *** DROP TABLE t15; *** Create t16 on slave *** STOP SLAVE; RESET SLAVE; 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='MyISAM'; *** Create t16 on Master *** CREATE TABLE t16 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT, c4 BLOB, c5 CHAR(5))ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** set @b1 = 'b1b1b1b1'; set @b1 = concat(@b1,@b1); INSERT INTO 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; c1 c2 c3 c4 c5 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA *** Select on Slave **** SELECT * FROM t16 ORDER BY c1; c1 c2 c3 c4 c5 c6 c7 1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP *** Add Partition on 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; Table Create Table t16 CREATE TABLE `t16` ( `c1` int(11) NOT NULL, `c2` decimal(8,2) DEFAULT NULL, `c3` text, `c4` blob, `c5` char(5) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (c1) PARTITIONS 4 */ *** Show table on Slave **** SHOW CREATE TABLE t16; Table Create Table t16 CREATE TABLE `t16` ( `c1` int(11) NOT NULL, `c2` decimal(8,2) DEFAULT NULL, `c3` text, `c4` blob, `c5` char(5) DEFAULT NULL, `c6` int(11) DEFAULT '1', `c7` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`c1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (c1) PARTITIONS 4 */ *** DROP TABLE t16 *** DROP TABLE t16; *** Alter Master End *** *** Create t17 on slave *** STOP SLAVE; RESET SLAVE; CREATE TABLE t17 (a SMALLINT, b INT PRIMARY KEY, c CHAR(5), d FLOAT DEFAULT '2.00', e CHAR(5) DEFAULT 'TEST2') ENGINE='MyISAM'; *** Create t17 on Master *** CREATE TABLE t17 (a BIGINT PRIMARY KEY, b INT, c CHAR(10) ) ENGINE='MyISAM'; RESET MASTER; *** Start Slave *** START SLAVE; *** Master Data Insert *** INSERT INTO t17 () VALUES(9223372036854775807,2,'Kyle, TEX'); ******************************************** *** Expect slave to fail with Error 1522 *** ******************************************** SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 Master_User root Master_Port # Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos # Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1534 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 1534 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; ** DROP table t17 *** DROP TABLE t17;