1
# Test how replication of tables work when the definition on the
2
# master and slave differs.
4
# Consider making these part of the basic RBR tests.
9
DROP TABLE IF EXISTS t1_int,t1_bit,t1_char,t1_nodef;
10
DROP TABLE IF EXISTS t2,t3,t4,t5,t6,t9;
13
sync_slave_with_master;
15
SET @my_sql_mode= @@global.sql_mode;
16
SET GLOBAL SQL_MODE='STRICT_ALL_TABLES';
20
eval CREATE TABLE t1_int (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
21
eval CREATE TABLE t1_bit (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
22
eval CREATE TABLE t1_char (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
23
eval CREATE TABLE t1_nodef (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
24
eval CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
25
eval CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
26
eval CREATE TABLE t4 (a INT) ENGINE=$engine_type;
27
eval CREATE TABLE t5 (a INT, b INT, c INT) ENGINE=$engine_type;
28
eval CREATE TABLE t6 (a INT, b INT, c INT) ENGINE=$engine_type;
29
eval CREATE TABLE t7 (a INT NOT NULL) ENGINE=$engine_type;
30
eval CREATE TABLE t8 (a INT NOT NULL) ENGINE=$engine_type;
32
# Table used to detect that slave is running
33
eval CREATE TABLE t9 (a INT) ENGINE=$engine_type;
35
sync_slave_with_master;
37
# On the slave, we add one INT column last in table 't1_int',
38
ALTER TABLE t1_int ADD x INT DEFAULT 42;
39
# ... and add BIT columns last in table 't1_bit' to ensure that we
40
# have at least one extra null byte on the slave,
42
ADD x BIT(3) DEFAULT b'011',
43
ADD y BIT(5) DEFAULT b'10101',
44
ADD z BIT(2) DEFAULT b'10';
45
# ... and add one CHAR column last in table 't1_char',
46
ALTER TABLE t1_char ADD x CHAR(20) DEFAULT 'Just a test';
47
# ... and add one non-nullable INT column last in table 't1_text'
49
ALTER TABLE t1_nodef ADD x INT NOT NULL, ADD y INT NOT NULL, ADD z INT NOT NULL;
50
# ... and remove the last column in t2
51
ALTER TABLE t2 DROP b;
52
# ... change the type of the single column in table 't4'
53
ALTER TABLE t4 MODIFY a FLOAT;
54
# ... change the type of the middle column of table 't5'
55
ALTER TABLE t5 MODIFY b FLOAT;
56
# ... change the type of the last column of table 't6'
57
ALTER TABLE t6 MODIFY c FLOAT;
59
# ... add one byte worth of null bytes to the table on the slave
60
ALTER TABLE t7 ADD e1 INT, ADD e2 INT, ADD e3 INT, ADD e4 INT,
61
ADD e5 INT, ADD e6 INT, ADD e7 INT, ADD e8 INT;
63
# ... add 8 columns that are nullable: t8 will not be entirely
64
# nullable and have no null bits (just an X bit)
65
ALTER TABLE t8 ADD e1 INT NOT NULL DEFAULT 0, ADD e2 INT NOT NULL DEFAULT 0,
66
ADD e3 INT NOT NULL DEFAULT 0, ADD e4 INT NOT NULL DEFAULT 0,
67
ADD e5 INT NOT NULL DEFAULT 0, ADD e6 INT NOT NULL DEFAULT 0,
68
ADD e7 INT NOT NULL DEFAULT 0, ADD e8 INT NOT NULL DEFAULT 0;
70
# Insert some values for tables on slave side. These should not be
71
# modified when the row from the master is applied.
72
# since bug#31552/31609 idempotency is not default any longer. In order
73
# the following INSERTs to pass the mode is switched temprorarily
74
set @@global.slave_exec_mode= 'IDEMPOTENT';
76
# so the inserts are going to be overriden
77
INSERT INTO t1_int VALUES (2, 4, 4711);
78
INSERT INTO t1_char VALUES (2, 4, 'Foo is a bar');
79
INSERT INTO t1_bit VALUES (2, 4, b'101', b'11100', b'01');
81
--echo **** On Master ****
83
INSERT INTO t1_int VALUES (1,2);
84
INSERT INTO t1_int VALUES (2,5);
85
INSERT INTO t1_bit VALUES (1,2);
86
INSERT INTO t1_bit VALUES (2,5);
87
INSERT INTO t1_char VALUES (1,2);
88
INSERT INTO t1_char VALUES (2,5);
89
SELECT * FROM t1_int ORDER BY a;
90
SELECT * FROM t1_bit ORDER BY a;
91
SELECT * FROM t1_char ORDER BY a;
92
--echo **** On Slave ****
93
sync_slave_with_master;
94
set @@global.slave_exec_mode= default;
96
SELECT a,b,x FROM t1_int ORDER BY a;
97
SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit ORDER BY a;
98
SELECT a,b,x FROM t1_char ORDER BY a;
100
--echo **** On Master ****
102
UPDATE t1_int SET b=2*b WHERE a=2;
103
UPDATE t1_char SET b=2*b WHERE a=2;
104
UPDATE t1_bit SET b=2*b WHERE a=2;
105
SELECT * FROM t1_int ORDER BY a;
106
SELECT * FROM t1_bit ORDER BY a;
107
SELECT * FROM t1_char ORDER BY a;
108
--echo **** On Slave ****
109
sync_slave_with_master;
110
SELECT a,b,x FROM t1_int ORDER BY a;
111
SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit ORDER BY a;
112
SELECT a,b,x FROM t1_char ORDER BY a;
115
INSERT INTO t9 VALUES (2);
116
sync_slave_with_master;
117
# Now slave is guaranteed to be running
119
INSERT INTO t1_nodef VALUES (1,2);
121
# Last insert on wider slave table succeeds while slave sql sql_mode permits.
122
# The previous version of the above test expected slave sql to stop.
123
# bug#38173 relaxed conditions to stop only with the strict mode.
124
sync_slave_with_master;
125
select count(*) from t1_nodef;
128
# Replicating to tables with fewer columns at the end works as of WL#3228
131
INSERT INTO t9 VALUES (2);
132
sync_slave_with_master;
133
# Now slave is guaranteed to be running
135
--echo **** On Master ****
136
INSERT INTO t2 VALUES (2,4);
138
sync_slave_with_master;
139
--echo **** On Slave ****
141
--replace_result $MASTER_MYPORT MASTER_PORT
142
--replace_column 1 # 4 # 7 # 8 # 9 # 20 <Last_Error> 22 # 23 # 33 # 35 <Last_IO_Errno> 36 <Last_IO_Error> 38 <Last_SQL_Error>
143
--query_vertical SHOW SLAVE STATUS
146
INSERT INTO t9 VALUES (4);
147
sync_slave_with_master;
148
# Now slave is guaranteed to be running
150
INSERT INTO t4 VALUES (4);
152
--source include/wait_for_slave_sql_to_stop.inc
153
--replace_result $MASTER_MYPORT MASTER_PORT
154
--replace_column 1 # 4 # 7 # 8 # 9 # 20 <Last_Error> 22 # 23 # 33 # 35 <Last_IO_Errno> 36 <Last_IO_Error> 38 <Last_SQL_Error>
155
--query_vertical SHOW SLAVE STATUS
156
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
160
INSERT INTO t9 VALUES (5);
161
sync_slave_with_master;
162
# Now slave is guaranteed to be running
164
INSERT INTO t5 VALUES (5,10,25);
166
--source include/wait_for_slave_sql_to_stop.inc
167
--replace_result $MASTER_MYPORT MASTER_PORT
168
--replace_column 1 # 4 # 7 # 8 # 9 # 20 <Last_Error> 22 # 23 # 33 # 35 <Last_IO_Errno> 36 <Last_IO_Error> 38 <Last_SQL_Error>
169
--query_vertical SHOW SLAVE STATUS
170
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
174
INSERT INTO t9 VALUES (6);
175
sync_slave_with_master;
176
# Now slave is guaranteed to be running
178
INSERT INTO t6 VALUES (6,12,36);
180
--source include/wait_for_slave_sql_to_stop.inc
181
--replace_result $MASTER_MYPORT MASTER_PORT
182
--replace_column 1 # 4 # 7 # 8 # 9 # 20 <Last_Error> 22 # 23 # 33 # 35 <Last_IO_Errno> 36 <Last_IO_Error> 38 <Last_SQL_Error>
183
--query_vertical SHOW SLAVE STATUS
184
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
188
INSERT INTO t9 VALUES (6);
189
sync_slave_with_master;
190
--replace_result $SLAVE_MYPORT SLAVE_PORT
191
--replace_column 1 # 4 # 7 # 8 # 9 # 20 <Last_Error> 22 # 23 # 33 # 35 <Last_IO_Errno> 36 <Last_IO_Error> 38 <Last_SQL_Error>
192
--query_vertical SHOW SLAVE STATUS
194
# Testing some tables extra field that can be null and cannot be null
195
# (but have default values)
198
INSERT INTO t7 VALUES (1),(2),(3);
199
INSERT INTO t8 VALUES (1),(2),(3);
200
SELECT * FROM t7 ORDER BY a;
201
SELECT * FROM t8 ORDER BY a;
202
sync_slave_with_master;
203
SELECT * FROM t7 ORDER BY a;
204
SELECT * FROM t8 ORDER BY a;
206
# We will now try to update and then delete a row on the master where
207
# the extra field on the slave does not have a default value. This
208
# update should not generate an error even though there is no default
209
# for the extra column.
211
--echo **** On Master ****
215
INSERT INTO t1_nodef VALUES (1,2);
216
INSERT INTO t1_nodef VALUES (2,4);
218
sync_slave_with_master;
220
--echo **** On Slave ****
222
INSERT INTO t1_nodef VALUES (1,2,3,4,5);
223
INSERT INTO t1_nodef VALUES (2,4,6,8,10);
225
--echo **** On Master ****
227
UPDATE t1_nodef SET b=2*b WHERE a=1;
228
SELECT * FROM t1_nodef ORDER BY a;
230
--echo **** On Slave ****
231
sync_slave_with_master;
232
SELECT * FROM t1_nodef ORDER BY a;
234
--echo **** On Master ****
236
DELETE FROM t1_nodef WHERE a=2;
237
SELECT * FROM t1_nodef ORDER BY a;
239
--echo **** On Slave ****
240
sync_slave_with_master;
241
SELECT * FROM t1_nodef ORDER BY a;
243
--echo **** Cleanup ****
246
DROP TABLE IF EXISTS t1_int,t1_bit,t1_char,t1_nodef;
247
DROP TABLE IF EXISTS t2,t3,t4,t5,t6,t7,t8,t9;
249
sync_slave_with_master;
252
SET @@global.sql_mode= @my_sql_mode;