Debian Codegraph @

mysql-test/suite/rpl/include/rpl_row_tabledefs.test

raw
1 # Test how replication of tables work when the definition on the
2 # master and slave differs.
3
4 # Consider making these part of the basic RBR tests.
5
6 connection master;
7 --disable_warnings
8 --disable_query_log
9 DROP TABLE IF EXISTS t1_int,t1_bit,t1_char,t1_nodef;
10 DROP TABLE IF EXISTS t2,t3,t4,t5,t6,t9;
11 --enable_query_log
12 --enable_warnings
13 sync_slave_with_master;
14 STOP SLAVE;
15 SET @my_sql_mode= @@global.sql_mode;
16 SET GLOBAL SQL_MODE='STRICT_ALL_TABLES';
17 START SLAVE;
18
19 connection master;
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;
31
32 # Table used to detect that slave is running
33 eval CREATE TABLE t9 (a INT) ENGINE=$engine_type;
34
35 sync_slave_with_master;
36
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,
41 ALTER TABLE t1_bit
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'
48 # with no default,
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;
58
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;
62
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;
69
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';
75
76 # so the inserts are going to be overridden
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');
80
81 connection master;
82 INSERT INTO t1_int VALUES (1,2);
83 INSERT INTO t1_int VALUES (2,5);
84 INSERT INTO t1_bit VALUES (1,2);
85 INSERT INTO t1_bit VALUES (2,5);
86 INSERT INTO t1_char VALUES (1,2);
87 INSERT INTO t1_char VALUES (2,5);
88 SELECT * FROM t1_int ORDER BY a;
89 SELECT * FROM t1_bit ORDER BY a;
90 SELECT * FROM t1_char ORDER BY a;
91 sync_slave_with_master;
92 set @@global.slave_exec_mode= default;
93
94 SELECT a,b,x FROM t1_int ORDER BY a;
95 SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit ORDER BY a;
96 SELECT a,b,x FROM t1_char ORDER BY a;
97
98 connection master;
99 UPDATE t1_int SET b=2*b WHERE a=2;
100 UPDATE t1_char SET b=2*b WHERE a=2;
101 UPDATE t1_bit SET b=2*b WHERE a=2;
102 SELECT * FROM t1_int ORDER BY a;
103 SELECT * FROM t1_bit ORDER BY a;
104 SELECT * FROM t1_char ORDER BY a;
105 sync_slave_with_master;
106 SELECT a,b,x FROM t1_int ORDER BY a;
107 SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit ORDER BY a;
108 SELECT a,b,x FROM t1_char ORDER BY a;
109
110 connection master;
111 INSERT INTO t9 VALUES (2);
112 sync_slave_with_master;
113 # Now slave is guaranteed to be running
114 connection master;
115 INSERT INTO t1_nodef VALUES (1,2);
116
117 # Last insert on wider slave table succeeds while slave sql sql_mode permits.
118 # The previous version of the above test expected slave sql to stop.
119 # bug#38173 relaxed conditions to stop only with the strict mode.
120 sync_slave_with_master;
121 select count(*) from t1_nodef;
122
123 #
124 # Replicating to tables with fewer columns at the end works as of WL#3228
125 #
126 connection master;
127 INSERT INTO t9 VALUES (2);
128 sync_slave_with_master;
129 # Now slave is guaranteed to be running
130 connection master;
131 INSERT INTO t2 VALUES (2,4);
132 SELECT * FROM t2;
133 sync_slave_with_master;
134 SELECT * FROM t2;
135 --source include/check_slave_is_running.inc
136
137 connection master;
138 INSERT INTO t9 VALUES (4);
139 sync_slave_with_master;
140 # Now slave is guaranteed to be running
141 connection master;
142 INSERT INTO t4 VALUES (4);
143 connection slave;
144 call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column [012] type mismatch.* error.* 1535");
145 call mtr.add_suppression("Slave SQL.*Column [0-9] of table .test.t[0-9]. cannot be converted from type.* error.* 1677");
146 --let $slave_skip_counter= 2
147 --let $slave_sql_errno= 1677
148 --let $show_slave_sql_error= 1
149 --source include/wait_for_slave_sql_error_and_skip.inc
150
151 connection master;
152 INSERT INTO t9 VALUES (5);
153 sync_slave_with_master;
154 # Now slave is guaranteed to be running
155 connection master;
156 INSERT INTO t5 VALUES (5,10,25);
157 connection slave;
158 --let $slave_skip_counter= 2
159 --let $slave_sql_errno= 1677
160 --let $show_slave_sql_error= 1
161 --source include/wait_for_slave_sql_error_and_skip.inc
162
163 connection master;
164 INSERT INTO t9 VALUES (6);
165 sync_slave_with_master;
166 # Now slave is guaranteed to be running
167 connection master;
168 INSERT INTO t6 VALUES (6,12,36);
169 connection slave;
170 --let $slave_skip_counter= 2
171 --let $slave_sql_errno= 1677
172 --let $show_slave_sql_error= 1
173 --source include/wait_for_slave_sql_error_and_skip.inc
174
175 connection master;
176 INSERT INTO t9 VALUES (6);
177 sync_slave_with_master;
178 --source include/check_slave_is_running.inc
179
180 # Testing some tables extra field that can be null and cannot be null
181 # (but have default values)
182
183 connection master;
184 INSERT INTO t7 VALUES (1),(2),(3);
185 INSERT INTO t8 VALUES (1),(2),(3);
186 SELECT * FROM t7 ORDER BY a;
187 SELECT * FROM t8 ORDER BY a;
188 sync_slave_with_master;
189 SELECT * FROM t7 ORDER BY a;
190 SELECT * FROM t8 ORDER BY a;
191
192 # We will now try to update and then delete a row on the master where
193 # the extra field on the slave does not have a default value. This
194 # update should not generate an error even though there is no default
195 # for the extra column.
196
197 connection master;
198 TRUNCATE t1_nodef;
199 SET SQL_LOG_BIN=0;
200 INSERT INTO t1_nodef VALUES (1,2);
201 INSERT INTO t1_nodef VALUES (2,4);
202 SET SQL_LOG_BIN=1;
203 sync_slave_with_master;
204
205 connection slave;
206 INSERT INTO t1_nodef VALUES (1,2,3,4,5);
207 INSERT INTO t1_nodef VALUES (2,4,6,8,10);
208
209 connection master;
210 UPDATE t1_nodef SET b=2*b WHERE a=1;
211 SELECT * FROM t1_nodef ORDER BY a;
212
213 sync_slave_with_master;
214 SELECT * FROM t1_nodef ORDER BY a;
215
216 connection master;
217 DELETE FROM t1_nodef WHERE a=2;
218 SELECT * FROM t1_nodef ORDER BY a;
219
220 sync_slave_with_master;
221 SELECT * FROM t1_nodef ORDER BY a;
222
223 --echo **** Cleanup ****
224 connection master;
225 --disable_warnings
226 DROP TABLE IF EXISTS t1_int,t1_bit,t1_char,t1_nodef;
227 DROP TABLE IF EXISTS t2,t3,t4,t5,t6,t7,t8,t9;
228 --enable_warnings
229 sync_slave_with_master;
230
231 # Restore sql_mode
232 SET @@global.sql_mode= @my_sql_mode;