Debian Codegraph @

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

raw
1 #############################################################
2 # Author: Serge Kozlov <[email protected]>
3 # Date: 07/01/2008
4 # Purpose: Testing possible affects of some system dynamic
5 # variables to the replication.
6 # Scenario for each variable:
7 # 1) Set different values for master and slave
8 # 2) Create and replicate a data from master to slave
9 # 3) Check results on master and slave: changes on slave
10 # shouldn't be affected to replicated data.
11 #############################################################
12 --source include/have_innodb.inc
13 --source include/master-slave.inc
14 --echo
15
16 #
17 # AUTO_INCREMENT
18 #
19 --echo * auto_increment_increment, auto_increment_offset *
20
21 --connection master
22 SET @@global.auto_increment_increment=2;
23 SET @@session.auto_increment_increment=2;
24 SET @@global.auto_increment_offset=10;
25 SET @@session.auto_increment_offset=10;
26
27 --connection slave
28 SET @@global.auto_increment_increment=3;
29 SET @@session.auto_increment_increment=3;
30 SET @@global.auto_increment_offset=20;
31 SET @@session.auto_increment_offset=20;
32
33 --connection master
34 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM CHARSET=latin1;
35 INSERT INTO t1 (b) VALUES ('master');
36 INSERT INTO t1 (b) VALUES ('master');
37 SELECT * FROM t1 ORDER BY a;
38
39 --sync_slave_with_master
40 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM CHARSET=latin1;
41 INSERT INTO t1 (b) VALUES ('slave');
42 INSERT INTO t1 (b) VALUES ('slave');
43 INSERT INTO t2 (b) VALUES ('slave');
44 INSERT INTO t2 (b) VALUES ('slave');
45 SELECT * FROM t1 ORDER BY a;
46 SELECT * FROM t2 ORDER BY a;
47
48 --connection master
49 --disable_warnings
50 DROP TABLE IF EXISTS t1,t2;
51 --enable_warnings
52 SET @@global.auto_increment_increment=1;
53 SET @@session.auto_increment_increment=1;
54 SET @@global.auto_increment_offset=1;
55 SET @@session.auto_increment_offset=1;
56
57 --connection slave
58 SET @@global.auto_increment_increment=1;
59 SET @@session.auto_increment_increment=1;
60 SET @@global.auto_increment_offset=1;
61 SET @@session.auto_increment_offset=1;
62
63 --connection slave
64 SET auto_increment_increment=1;
65 SET auto_increment_offset=1;
66 --echo
67
68 #
69 # CHARACTER_SET_DATABASE, COLLATION_SERVER
70 #
71 --echo * character_set_database, collation_server *
72
73 --connection master
74 SET @restore_master_collation_database=@@global.collation_database;
75 SET @restore_master_collation_server=@@global.collation_server;
76 SET @@global.collation_database=latin1_swedish_ci;
77 SET @@session.collation_database=latin1_swedish_ci;
78 SET @@global.collation_server=latin1_german1_ci;
79 SET @@session.collation_server=latin1_german1_ci;
80
81 --connection slave
82 SET @restore_slave_collation_database=@@global.collation_database;
83 SET @restore_slave_collation_server=@@global.collation_server;
84 SET @@global.collation_database=utf8_general_ci;
85 SET @@session.collation_database=utf8_general_ci;
86 SET @@global.collation_server=utf8_bin;
87 SET @@session.collation_server=utf8_bin;
88
89 --connection master
90 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM CHARSET=latin1;
91 SHOW CREATE TABLE t1;
92
93 --sync_slave_with_master
94 CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=MyISAM CHARSET=latin1;
95 SHOW CREATE TABLE t1;
96 SHOW CREATE TABLE t2;
97
98 SET @@global.collation_server=latin1_swedish_ci;
99 SET @@session.collation_server=latin1_swedish_ci;
100
101 --connection master
102 SET @@global.collation_server=latin1_swedish_ci;
103 SET @@session.collation_server=latin1_swedish_ci;
104
105 --disable_warnings
106 DROP TABLE IF EXISTS t1,t2;
107 --enable_warnings
108 --echo
109
110 #
111 # DEFAULT_WEEK_FORMAT
112 #
113 --echo * default_week_format *
114
115 --connection master
116 SET @@global.default_week_format=0;
117 SET @@session.default_week_format=0;
118
119 --connection slave
120 SET @@global.default_week_format=1;
121 SET @@session.default_week_format=1;
122
123 --connection master
124 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10), c INT) ENGINE=MyISAM CHARSET=latin1;
125 INSERT INTO t1 VALUES (1, 'master ', WEEK('2008-01-07'));
126 SELECT * FROM t1 ORDER BY a;
127
128 --sync_slave_with_master
129 INSERT INTO t1 VALUES (2, 'slave ', WEEK('2008-01-07'));
130 SELECT * FROM t1 ORDER BY a;
131
132 --connection master
133 DROP TABLE t1;
134
135 --connection slave
136 SET @@global.default_week_format=0;
137 SET @@session.default_week_format=0;
138 --echo
139
140 #
141 # LOCAL_INFILE
142 #
143 --echo * local_infile *
144
145 --connection slave
146 SET @@global.local_infile=0;
147
148 --connection master
149 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(20), c CHAR(254)) ENGINE=MyISAM CHARSET=latin1;
150 --copy_file ./std_data/words.dat $MYSQLTEST_VARDIR/tmp/words.dat
151 --copy_file ./std_data/words2.dat $MYSQLTEST_VARDIR/tmp/words2.dat
152 --replace_regex /\'.+\'/'FILE'/
153 --eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/tmp/words.dat' INTO TABLE t1 (b)
154 SELECT COUNT(*) FROM t1;
155 --sync_slave_with_master
156 --replace_regex /\'.+\'/'FILE2'/
157 --error ER_LOAD_INFILE_CAPABILITY_DISABLED
158 --eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/tmp/words2.dat' INTO TABLE t1 (b)
159 SELECT COUNT(*) FROM t1;
160
161 SET @@global.local_infile=1;
162
163 --connection master
164 DROP TABLE t1;
165 --echo
166
167 #
168 # MAX_HEAP_TABLE_SIZE
169 #
170 --echo * max_heap_table_size *
171
172 --connection slave
173 SET @restore_slave_max_heap_table_size=@@global.max_heap_table_size;
174 SET @@global.max_heap_table_size=16384;
175 SET @@session.max_heap_table_size=16384;
176
177 --connection master
178 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), c CHAR(254)) ENGINE=MEMORY CHARSET=latin1;
179 let $counter=2000;
180 --disable_query_log
181 while ($counter) {
182 INSERT INTO t1 (b,c) VALUES ('master', REPEAT('A', 254));
183 dec $counter;
184 }
185 --enable_query_log
186 SELECT COUNT(*)=2000 FROM t1;
187
188 --sync_slave_with_master
189 let $counter=2000;
190 --disable_query_log
191 while ($counter) {
192 --error 0,1114
193 INSERT INTO t1 (b,c) VALUES ('slave', REPEAT('A', 254));
194 dec $counter;
195 }
196 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), c CHAR(254)) ENGINE=MEMORY CHARSET=latin1;
197 let $counter=2000;
198 --disable_query_log
199 while ($counter) {
200 --error 0,1114
201 INSERT INTO t2 (b,c) VALUES ('slave', REPEAT('A', 254));
202 dec $counter;
203 }
204 --enable_query_log
205 # We don't know how many memory used and can't check exact values so need to check following
206 # conditions
207 SELECT COUNT(*)=2000 FROM t1 WHERE b='master' GROUP BY b ORDER BY b;
208 SELECT COUNT(*)<2000 AND COUNT(*)>0 FROM t1 WHERE b='slave' GROUP BY b ORDER BY b;
209 SELECT COUNT(*)<2000 AND COUNT(*)>0 FROM t2 WHERE b='slave' GROUP BY b ORDER BY b;
210
211 --connection master
212 --disable_warnings
213 DROP TABLE IF EXISTS t1,t2;
214 --enable_warnings
215 --echo
216
217 #
218 # STORAGE_ENGINE
219 #
220 --echo * storage_engine *
221
222 --connection master
223 SET @restore_master_storage_engine=@@global.default_storage_engine;
224 SET @@global.default_storage_engine=InnoDB;
225 SET @@session.default_storage_engine=InnoDB;
226
227 --connection slave
228 SET @restore_slave_storage_engine=@@global.default_storage_engine;
229 SET @@global.default_storage_engine=Memory;
230 SET @@session.default_storage_engine=Memory;
231
232 --connection master
233 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) CHARSET=latin1;
234 CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) ENGINE=InnoDB CHARSET=latin1;
235
236 --sync_slave_with_master
237 CREATE TABLE t3 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10)) CHARSET=latin1;
238
239 --connection master
240 SHOW CREATE TABLE t1;
241 SHOW CREATE TABLE t2;
242
243 --connection slave
244 SHOW CREATE TABLE t1;
245 SHOW CREATE TABLE t2;
246 SHOW CREATE TABLE t3;
247
248 SET @@global.default_storage_engine=InnoDB;
249 SET @@session.default_storage_engine=InnoDB;
250
251 --connection master
252 --disable_warnings
253 DROP TABLE IF EXISTS t1,t2,t3;
254 --enable_warnings
255 --echo
256
257 #
258 # SQL_MODE
259 #
260 --echo * sql_mode *
261
262 --connection master
263 SET @old_sql_mode_master= @@global.sql_mode;
264 SET @@global.sql_mode=ANSI;
265 SET @@session.sql_mode=ANSI;
266
267 --connection slave
268 SET @old_sql_mode_slave= @@global.sql_mode;
269 SET @@global.sql_mode=TRADITIONAL;
270 SET @@session.sql_mode=TRADITIONAL;
271
272 --connection master
273 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b VARCHAR(10), c DATE) CHARSET=latin1;
274 INSERT INTO t1 VALUES (1, 'master', '0000-00-00');
275 SELECT * FROM t1 ORDER BY a;
276
277 --sync_slave_with_master
278 --error 1292
279 INSERT INTO t1 VALUES (1, 'slave', '0000-00-00');
280 SELECT * FROM t1 ORDER BY a;
281 SET @@global.sql_mode='';
282 SET @@session.sql_mode='';
283
284 --connection master
285 SET @@global.sql_mode='';
286 SET @@session.sql_mode='';
287 DROP TABLE t1;
288 --echo
289
290
291 # Clean up
292 --echo *** clean up ***
293 --connection master
294 SET @@global.collation_database=@restore_master_collation_database;
295 SET @@global.collation_server=@restore_master_collation_server;
296 SET @@global.default_storage_engine=@restore_master_storage_engine;
297 SET @@global.sql_mode=@old_sql_mode_master;
298 --sync_slave_with_master
299 SET @@global.collation_database=@restore_slave_collation_database;
300 SET @@global.collation_server=@restore_slave_collation_server;
301 SET @@global.max_heap_table_size=@restore_slave_max_heap_table_size;
302 SET @@global.default_storage_engine=@restore_slave_storage_engine;
303 SET @@global.sql_mode=@old_sql_mode_slave;
304
305 # Put at the end since the test otherwise emptied the table.
306 remove_file $MYSQLTEST_VARDIR/tmp/words.dat;
307 remove_file $MYSQLTEST_VARDIR/tmp/words2.dat;
308 --echo
309 call mtr.add_suppression("The table 't[12]' is full");
310
311 # End of 5.1 test
312 --source include/rpl_end.inc