Debian Codegraph @

mysql-test/suite/rpl/t/rpl_colSize.test

raw
1 ##################################################################
2 # rpl_colSize #
3 # #
4 # This test is designed to test the changes included in WL#3228. #
5 # The changes include the ability to replicate with the master #
6 # having columns that are smaller (shorter) than the slave. #
7 ##################################################################
8
9 -- source include/not_group_replication_plugin.inc
10 -- source include/set_privilege_checks_user_as_system_user.inc
11 -- source include/master-slave.inc
12
13 --disable_warnings
14 DROP TABLE IF EXISTS t1;
15 --enable_warnings
16
17 --echo **** Testing WL#3228 changes. ****
18 --echo *** Create "wider" table on slave ***
19 --source include/sync_slave_sql_with_master.inc
20 STOP SLAVE;
21 --source include/wait_for_slave_to_stop.inc
22 RESET SLAVE;
23 RESET MASTER;
24
25 SET @saved_replica_type_conversions = @@replica_type_conversions;
26 SET GLOBAL REPLICA_TYPE_CONVERSIONS = 'ALL_NON_LOSSY';
27
28 #Get default engine value WL#6921
29 --let $DEFAULT_ENGINE = `select @@global.default_storage_engine`
30
31 eval CREATE TABLE t1 (
32 a float (47),
33 b double (143,9),
34 c decimal (65,30),
35 d numeric (4,0),
36 e bit (32),
37 f char (21),
38 g varchar (1300),
39 h binary (33),
40 j varbinary (200),
41 k enum ('5','6','7', '8','9','0'),
42 l set ('1','2','3','4','5','6','7','8','9','0','11','12','13','14','15','16','17','18','19','21','22','23','24','25','26','27','28','29'),
43 m TINYBLOB,
44 n BLOB,
45 o MEDIUMBLOB,
46 p LONGBLOB,
47 q TINYTEXT,
48 r TEXT,
49 s MEDIUMTEXT,
50 t LONGTEXT
51 );
52
53 --echo *** Create same table on master but with narrow columns ***
54 connection master;
55 eval CREATE TABLE t1 (
56 a float (44),
57 b double (10,3),
58 c decimal (10,2),
59 d numeric (3,0),
60 e bit (16),
61 f char (10),
62 g varchar (100),
63 h binary (20),
64 j varbinary (20),
65 k enum ('5','6','7'),
66 l set ('1','2','3','4','5','6','7','8','9','0'),
67 m TINYBLOB,
68 n BLOB,
69 o MEDIUMBLOB,
70 p LONGBLOB,
71 q TINYTEXT,
72 r TEXT,
73 s MEDIUMTEXT,
74 t LONGTEXT
75 );
76
77 RESET MASTER;
78
79 --echo *** Start replication ***
80 connection slave;
81 START SLAVE;
82 --source include/wait_for_slave_to_start.inc
83
84 --echo *** Insert data on master and display it. ***
85 connection master;
86
87 INSERT INTO t1 () VALUES (
88 17.567,
89 2.123,
90 10.20,
91 125,
92 hex(64),
93 'TEST',
94 'This is a test',
95 'binary data',
96 'more binary data',
97 '6',
98 '7',
99 "blob 1",
100 "blob 2",
101 "blob 3",
102 "blob 4",
103 "text 1",
104 "text 2",
105 "text 3",
106 "text 4");
107
108 # Replace values in columns that display differently between SBR & RBR
109 --replace_column 5 # 8 #
110 SELECT * FROM t1 ORDER BY a;
111
112 --echo *** Select data from slave to compare ***
113 --source include/sync_slave_sql_with_master.inc
114
115 # Replace values in columns that display differently between SBR & RBR
116 --replace_column 5 # 8 #
117 SELECT * FROM t1 ORDER BY a;
118
119 # Test boundary limits of varchar and char fields
120 # Master/Slave
121 # <256/<256 with m < s, m > s, and m == s <-- col a
122 # >255/<256 with m < s, m > s, and m == s <-- error will be caught in BUG#22086
123 # <256/>255 with m < s, m > s, and m == s <-- col b
124 # >255/>255 with m < s, m > s, and m == s <-- col c
125 #
126 # Test boundary limits of CHAR fields
127 # Master/Slave
128 # <256/<256 with m < s, m > s, and m == s <-- col d
129 # >255/<256 with m < s, m > s, and m == s <-- error char limited to 255 chars
130 # <256/>255 with m < s, m > s, and m == s <-- error char limited to 255 chars
131 # >255/>255 with m < s, m > s, and m == s <-- error char limited to 255 chars
132
133 connection master;
134 DROP TABLE t1;
135
136 --echo Create varchar table on master
137 CREATE TABLE t1 (
138 a VARCHAR(50),
139 b VARCHAR(100),
140 c VARCHAR(300),
141 d CHAR(5)
142 );
143
144 --let $sync_slave_connection= slave
145 --source include/sync_slave_sql_with_master.inc
146
147 --echo Alter varchar table on slave
148 ALTER TABLE t1 CHANGE COLUMN a a VARCHAR(100);
149 ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(400);
150 ALTER TABLE t1 CHANGE COLUMN c c VARCHAR(500);
151 ALTER TABLE t1 CHANGE COLUMN d d CHAR(100);
152
153 connection master;
154
155 --echo Insert some values and select them on master
156 INSERT INTO t1 VALUES ("This is a test of col a.",
157 "This is another test of col b.",
158 "This is a test of the large col c.",
159 "Col d");
160 SELECT * FROM t1;
161 #Replace default engine value with static engine string WL#6921
162 --replace_result $DEFAULT_ENGINE ENGINE default DEFAULT
163 SHOW CREATE TABLE t1;
164
165 --let $sync_slave_connection= slave
166 --source include/sync_slave_sql_with_master.inc
167
168 --echo Insert some values and select them on slave
169 SELECT * FROM t1;
170 #Replace default engine value with static engine string WL#6921
171 --replace_result $DEFAULT_ENGINE ENGINE default DEFAULT
172 SHOW CREATE TABLE t1;
173
174
175 # Test boundary limits of bit fields
176 # m < s, m % 8 != 0, and s % 8 == 0 col a
177 # m < s, m % 8 == 0, and s % 8 != 0 col b
178 # m < s, m % 8 != 0, and s % 8 != 0 col c
179 # m > s, m % 8 != 0, and s % 8 == 0 <-- error will be caught in BUG#22086
180 # m > s, m % 8 == 0, and s % 8 != 0 <-- error will be caught in BUG#22086
181 # m > s, m % 8 != 0, and s % 8 != 0 <-- error will be caught in BUG#22086
182
183 connection master;
184 DROP TABLE t1;
185
186 --echo Create bit table on master
187 CREATE TABLE t1 (
188 a BIT(7),
189 b BIT(8),
190 c BIT(21),
191 d BIT(11),
192 e BIT(11)
193 );
194
195 --echo Create bit table on slave
196 --source include/sync_slave_sql_with_master.inc
197
198 DROP TABLE t1;
199 CREATE TABLE t1 (
200 a BIT(16),
201 b BIT(22),
202 c BIT(54),
203 d BIT(25),
204 e BIT(13)
205 );
206
207 connection master;
208
209 --echo Insert some values and select them on master
210 INSERT INTO t1 VALUES (
211 b'1010101',
212 b'10101011',
213 b'101010110101010101111',
214 b'10101010101',
215 b'10101011111'
216 );
217
218 SELECT BIN(a), BIN(b), BIN(c), BIN(d), BIN(e) FROM t1;
219 #Replace default engine value with static engine string WL#6921
220 --replace_result $DEFAULT_ENGINE ENGINE default DEFAULT
221 SHOW CREATE TABLE t1;
222
223 --let $sync_slave_connection= slave
224 --source include/sync_slave_sql_with_master.inc
225
226 --echo Insert some values and select them on master
227 SELECT BIN(a), BIN(b), BIN(c), BIN(d), BIN(e) FROM t1;
228 #Replace default engine value with static engine string WL#6921
229 --replace_result $DEFAULT_ENGINE ENGINE default DEFAULT
230 SHOW CREATE TABLE t1;
231
232 ###############################################################################
233 # Bug#19704825 TEMPORARY SLAVE TYPE CONVERSION TABLES RELEASED TO EARLY
234 # Problem: Memory used in preparing slave type conversion
235 # temporary table is getting released early and causing unexpected results
236 # in case of nested events.
237 # Eg: If a insert statement is calling a trigger which contains another
238 # insert statement.
239 # Steps to reproduce:
240 # 1) Create a table t1
241 # 2) Change it's definition on slave, so that insert will create conversion
242 # temporary table on slave (set REPLICA_TYPE_CONVERSIONS appropriately to
243 # allow the scenario)
244 # 3) Create a table t2
245 # 4) Create a trigger on top of table t1. Inside the trigger, insert a tuple
246 # into table t2
247 # 5) Insert a tuple into table t1 on Master
248 # 6) Make sure this insert is replicated without any issues.
249 ###############################################################################
250
251 # Step 1, 2 are already done by above test code.
252
253 # Step 3: Create a table t2
254 connection master;
255 CREATE TABLE t2(i INTEGER);
256
257 # Step 4: Create a trigger on top of table t1. Inside the trigger, insert a
258 # tuple into table t2
259 --delimiter $$
260 CREATE TRIGGER t1_tr BEFORE INSERT ON t1 FOR EACH ROW
261 BEGIN
262 INSERT INTO t2 VALUES(1);
263 END $$
264 --delimiter ;
265
266 # Step 5: Insert a tuple into table t1 on Master
267 INSERT INTO t1 VALUES (
268 b'1010101',
269 b'10101011',
270 b'101010110101010101111',
271 b'10101010101',
272 b'10101011111'
273 );
274
275 # Step 6) Make sure this insert is replicated without any issues.
276 --source include/sync_slave_sql_with_master.inc
277
278 # End of test for Bug#19704825
279
280 --echo *** Cleanup ***
281 connection master;
282 DROP TABLE t1;
283 DROP TABLE t2;
284 --source include/sync_slave_sql_with_master.inc
285
286 SET GLOBAL REPLICA_TYPE_CONVERSIONS = @saved_replica_type_conversions;
287
288 # END 5.1 Test Case
289 --source include/rpl_end.inc