Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # In general, queries referencing @@system_variables are not
4 # considered safe to write to the binlog in statement-based logging
5 # mode. However, a few special cases are supported.
6 #
7 # This test verifies that queries referencing these specially
8 # supported variables are replicated correctly in statement mode.
9 #
10 #
11 # ==== Method ====
12 #
13 # The test simply does a lot of "INSERT INTO t1 VALUES (@@variable)"
14 # and checks the result on the slave.
15 #
16 # Statements referencing a variable only replicate correctly in mixed
17 # and row mode: in row mode, the values inserted are replicated. In
18 # mixed mode, statements referencing a variable are marked as unsafe,
19 # meaning they will be replicated by row. In statement mode, the
20 # slave's value will be used and replication will break. (Except in a
21 # small number of special cases: random seeds, insert_id, and
22 # auto_increment are replicated).
23 #
24 # We test all replicated variables, from each of the following
25 # contexts:
26 # - directly
27 # - from a stored procedure
28 # - from a stored function
29 # - from a trigger
30 # - from a prepared statement
31 #
32 # For all variables where it is possible, we set the variable to one
33 # value on slave, and insert it on the master with two distinct
34 # values.
35 #
36 # The same insertions are made in four different tables using direct
37 # insert, stored procedure, stored function, or trigger. Then all
38 # eight resulting tables on master and slave are compared.
39 #
40 #
41 # ==== Related bugs ====
42 #
43 # BUG#31168: @@hostname does not replicate
44 #
45 #
46 # ==== Related test cases ====
47 #
48 # binlog.binlog_unsafe tests that a warning is issued if system
49 # variables are replicated in statement mode.
50 #
51 # rpl.rpl_variables verifies that variables which cannot be replicated
52 # safely in statement mode are replicated correctly in mixed or row
53 # mode.
54
55 source include/have_binlog_format_mixed_or_statement.inc;
56 source include/master-slave.inc;
57
58 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
59
60 --echo ==== Initialization ====
61
62 # Backup the values of global variables so that they can be restored
63 # later.
64
65 connection master;
66 SET @m_auto_increment_increment= @@global.auto_increment_increment;
67 SET @m_auto_increment_offset= @@global.auto_increment_offset;
68 SET @m_character_set_client= @@global.character_set_client;
69 SET @m_collation_connection= @@global.collation_connection;
70 SET @m_collation_server= @@global.collation_server;
71 SET @m_time_zone= @@global.time_zone;
72 SET @m_lc_time_names= @@global.lc_time_names;
73 SET @m_collation_database= @@global.collation_database;
74
75 connection slave;
76 SET @s_auto_increment_increment= @@global.auto_increment_increment;
77 SET @s_auto_increment_offset= @@global.auto_increment_offset;
78 SET @s_character_set_client= @@global.character_set_client;
79 SET @s_collation_connection= @@global.collation_connection;
80 SET @s_collation_server= @@global.collation_server;
81 SET @s_time_zone= @@global.time_zone;
82 SET @s_lc_time_names= @@global.lc_time_names;
83 SET @s_collation_database= @@global.collation_database;
84
85 SET @@global.auto_increment_increment=19;
86 SET @@global.auto_increment_offset=4;
87 SET @@global.character_set_client='latin2';
88 SET @@global.collation_connection='latin2_bin';
89 SET @@global.collation_server='geostd8_general_ci';
90 SET @@global.time_zone='Japan';
91 SET @@global.lc_time_names='sv_SE';
92 SET @@global.collation_database='geostd8_bin';
93
94 connection master;
95
96 # Tables where everything happens.
97 CREATE TABLE tstmt (id INT AUTO_INCREMENT PRIMARY KEY,
98 num INT,
99 text VARCHAR(100));
100 CREATE TABLE tproc LIKE tstmt;
101 CREATE TABLE tfunc LIKE tstmt;
102 CREATE TABLE ttrig LIKE tstmt;
103 CREATE TABLE tprep LIKE tstmt;
104
105 # Table on which we put a trigger.
106 CREATE TABLE trigger_table (text CHAR(4));
107
108
109 --echo ==== Insert variables directly ====
110
111 SET @@pseudo_thread_id= 4712;
112 INSERT INTO tstmt(num) VALUES (@@session.pseudo_thread_id);
113 SET @@pseudo_thread_id= 4713;
114 INSERT INTO tstmt(num) VALUES (@@session.pseudo_thread_id);
115
116 SET @@foreign_key_checks= 0;
117 INSERT INTO tstmt(num) VALUES (@@session.foreign_key_checks);
118 SET @@foreign_key_checks= 1;
119 INSERT INTO tstmt(num) VALUES (@@session.foreign_key_checks);
120
121 SET @@sql_auto_is_null= 0;
122 INSERT INTO tstmt(num) VALUES (@@session.sql_auto_is_null);
123 SET @@sql_auto_is_null= 1;
124 INSERT INTO tstmt(num) VALUES (@@session.sql_auto_is_null);
125
126 SET @@unique_checks= 0;
127 INSERT INTO tstmt(num) VALUES (@@session.unique_checks);
128 SET @@unique_checks= 1;
129 INSERT INTO tstmt(num) VALUES (@@session.unique_checks);
130
131 SET @@auto_increment_increment= 11;
132 INSERT INTO tstmt(num) VALUES (@@session.auto_increment_increment);
133 SET @@auto_increment_increment= 19;
134 INSERT INTO tstmt(num) VALUES (@@session.auto_increment_increment);
135
136 SET @@auto_increment_offset= 13;
137 INSERT INTO tstmt(num) VALUES (@@session.auto_increment_offset);
138 SET @@auto_increment_offset= 17;
139 INSERT INTO tstmt(num) VALUES (@@session.auto_increment_offset);
140
141 # reset these as they affect the index column
142 SET @@auto_increment_increment= 1;
143 SET @@auto_increment_offset= 1;
144
145 SET @@character_set_client= 'cp1257';
146 INSERT INTO tstmt(text) VALUES (@@session.character_set_client);
147 SET @@character_set_client= 'cp1256';
148 INSERT INTO tstmt(text) VALUES (@@session.character_set_client);
149
150 SET @@collation_connection= 'cp1251_ukrainian_ci';
151 INSERT INTO tstmt(text) VALUES (@@session.collation_connection);
152 INSERT INTO tstmt(text) VALUES (@@session.character_set_connection);
153 SET @@collation_connection= 'cp1251_bulgarian_ci';
154 INSERT INTO tstmt(text) VALUES (@@session.collation_connection);
155 INSERT INTO tstmt(text) VALUES (@@session.character_set_connection);
156
157 SET @@collation_server= 'latin7_bin';
158 INSERT INTO tstmt(text) VALUES (@@session.collation_server);
159 INSERT INTO tstmt(text) VALUES (@@session.character_set_server);
160 SET @@collation_server= 'latin7_general_cs';
161 INSERT INTO tstmt(text) VALUES (@@session.collation_server);
162 INSERT INTO tstmt(text) VALUES (@@session.character_set_server);
163
164 SET @@time_zone= 'Europe/Moscow';
165 INSERT INTO tstmt(text) VALUES (@@session.time_zone);
166 SET @@time_zone= 'Universal';
167 INSERT INTO tstmt(text) VALUES (@@session.time_zone);
168
169 SET @@lc_time_names= 'sv_FI';
170 INSERT INTO tstmt(text) VALUES (@@session.lc_time_names);
171 SET @@lc_time_names= 'no_NO';
172 INSERT INTO tstmt(text) VALUES (@@session.lc_time_names);
173
174 SET @@collation_database= 'latin7_general_ci';
175 INSERT INTO tstmt(text) VALUES (@@session.collation_database);
176 INSERT INTO tstmt(text) VALUES (@@session.character_set_database);
177 SET @@collation_database= 'latin7_estonian_cs';
178 INSERT INTO tstmt(text) VALUES (@@session.collation_database);
179 INSERT INTO tstmt(text) VALUES (@@session.character_set_database);
180
181 SET @@timestamp= 47114711;
182 INSERT INTO tstmt(text) VALUES (@@session.timestamp);
183 SET @@timestamp= 47124712;
184 INSERT INTO tstmt(text) VALUES (@@session.timestamp);
185
186 SET @@last_insert_id= 1616;
187 INSERT INTO tstmt(text) VALUES (@@session.last_insert_id);
188 SET @@last_insert_id= 1717;
189 INSERT INTO tstmt(text) VALUES (@@session.last_insert_id);
190
191
192 --echo ==== Insert variables from a stored procedure ====
193
194 DELIMITER |;
195 CREATE PROCEDURE proc()
196 BEGIN
197
198 SET @@pseudo_thread_id= 4712;
199 INSERT INTO tproc(num) VALUES (@@session.pseudo_thread_id);
200 SET @@pseudo_thread_id= 4713;
201 INSERT INTO tproc(num) VALUES (@@session.pseudo_thread_id);
202
203 SET @@foreign_key_checks= 0;
204 INSERT INTO tproc(num) VALUES (@@session.foreign_key_checks);
205 SET @@foreign_key_checks= 1;
206 INSERT INTO tproc(num) VALUES (@@session.foreign_key_checks);
207
208 SET @@sql_auto_is_null= 0;
209 INSERT INTO tproc(num) VALUES (@@session.sql_auto_is_null);
210 SET @@sql_auto_is_null= 1;
211 INSERT INTO tproc(num) VALUES (@@session.sql_auto_is_null);
212
213 SET @@unique_checks= 0;
214 INSERT INTO tproc(num) VALUES (@@session.unique_checks);
215 SET @@unique_checks= 1;
216 INSERT INTO tproc(num) VALUES (@@session.unique_checks);
217
218 SET @@auto_increment_increment= 11;
219 INSERT INTO tproc(num) VALUES (@@session.auto_increment_increment);
220 SET @@auto_increment_increment= 19;
221 INSERT INTO tproc(num) VALUES (@@session.auto_increment_increment);
222
223 SET @@auto_increment_offset= 13;
224 INSERT INTO tproc(num) VALUES (@@session.auto_increment_offset);
225 SET @@auto_increment_offset= 17;
226 INSERT INTO tproc(num) VALUES (@@session.auto_increment_offset);
227
228 # reset these as they affect the index column
229 SET @@auto_increment_increment= 1;
230 SET @@auto_increment_offset= 1;
231
232 SET @@character_set_client= 'cp1257';
233 INSERT INTO tproc(text) VALUES (@@session.character_set_client);
234 SET @@character_set_client= 'cp1256';
235 INSERT INTO tproc(text) VALUES (@@session.character_set_client);
236
237 SET @@collation_connection= 'cp1251_ukrainian_ci';
238 INSERT INTO tproc(text) VALUES (@@session.collation_connection);
239 INSERT INTO tproc(text) VALUES (@@session.character_set_connection);
240 SET @@collation_connection= 'cp1251_bulgarian_ci';
241 INSERT INTO tproc(text) VALUES (@@session.collation_connection);
242 INSERT INTO tproc(text) VALUES (@@session.character_set_connection);
243
244 SET @@collation_server= 'latin7_bin';
245 INSERT INTO tproc(text) VALUES (@@session.collation_server);
246 INSERT INTO tproc(text) VALUES (@@session.character_set_server);
247 SET @@collation_server= 'latin7_general_cs';
248 INSERT INTO tproc(text) VALUES (@@session.collation_server);
249 INSERT INTO tproc(text) VALUES (@@session.character_set_server);
250
251 SET @@time_zone= 'Europe/Moscow';
252 INSERT INTO tproc(text) VALUES (@@session.time_zone);
253 SET @@time_zone= 'Universal';
254 INSERT INTO tproc(text) VALUES (@@session.time_zone);
255
256 SET @@lc_time_names= 'sv_FI';
257 INSERT INTO tproc(text) VALUES (@@session.lc_time_names);
258 SET @@lc_time_names= 'no_NO';
259 INSERT INTO tproc(text) VALUES (@@session.lc_time_names);
260
261 SET @@collation_database= 'latin7_general_ci';
262 INSERT INTO tproc(text) VALUES (@@session.collation_database);
263 INSERT INTO tproc(text) VALUES (@@session.character_set_database);
264 SET @@collation_database= 'latin7_estonian_cs';
265 INSERT INTO tproc(text) VALUES (@@session.collation_database);
266 INSERT INTO tproc(text) VALUES (@@session.character_set_database);
267
268 SET @@timestamp= 47114711;
269 INSERT INTO tproc(text) VALUES (@@session.timestamp);
270 SET @@timestamp= 47124712;
271 INSERT INTO tproc(text) VALUES (@@session.timestamp);
272
273 SET @@last_insert_id= 1616;
274 INSERT INTO tproc(text) VALUES (@@session.last_insert_id);
275 SET @@last_insert_id= 1717;
276 INSERT INTO tproc(text) VALUES (@@session.last_insert_id);
277
278 END|
279 DELIMITER ;|
280
281 CALL proc();
282
283
284 --echo ==== Insert variables from a stored function ====
285
286 DELIMITER |;
287 CREATE FUNCTION func()
288 RETURNS INT
289 BEGIN
290
291 SET @@pseudo_thread_id= 4712;
292 INSERT INTO tfunc(num) VALUES (@@session.pseudo_thread_id);
293 SET @@pseudo_thread_id= 4713;
294 INSERT INTO tfunc(num) VALUES (@@session.pseudo_thread_id);
295
296 SET @@foreign_key_checks= 0;
297 INSERT INTO tfunc(num) VALUES (@@session.foreign_key_checks);
298 SET @@foreign_key_checks= 1;
299 INSERT INTO tfunc(num) VALUES (@@session.foreign_key_checks);
300
301 SET @@sql_auto_is_null= 0;
302 INSERT INTO tfunc(num) VALUES (@@session.sql_auto_is_null);
303 SET @@sql_auto_is_null= 1;
304 INSERT INTO tfunc(num) VALUES (@@session.sql_auto_is_null);
305
306 SET @@unique_checks= 0;
307 INSERT INTO tfunc(num) VALUES (@@session.unique_checks);
308 SET @@unique_checks= 1;
309 INSERT INTO tfunc(num) VALUES (@@session.unique_checks);
310
311 SET @@auto_increment_increment= 11;
312 INSERT INTO tfunc(num) VALUES (@@session.auto_increment_increment);
313 SET @@auto_increment_increment= 19;
314 INSERT INTO tfunc(num) VALUES (@@session.auto_increment_increment);
315
316 SET @@auto_increment_offset= 13;
317 INSERT INTO tfunc(num) VALUES (@@session.auto_increment_offset);
318 SET @@auto_increment_offset= 17;
319 INSERT INTO tfunc(num) VALUES (@@session.auto_increment_offset);
320
321 # reset these as they affect the index column
322 SET @@auto_increment_increment= 1;
323 SET @@auto_increment_offset= 1;
324
325 SET @@character_set_client= 'cp1257';
326 INSERT INTO tfunc(text) VALUES (@@session.character_set_client);
327 SET @@character_set_client= 'cp1256';
328 INSERT INTO tfunc(text) VALUES (@@session.character_set_client);
329
330 SET @@collation_connection= 'cp1251_ukrainian_ci';
331 INSERT INTO tfunc(text) VALUES (@@session.collation_connection);
332 INSERT INTO tfunc(text) VALUES (@@session.character_set_connection);
333 SET @@collation_connection= 'cp1251_bulgarian_ci';
334 INSERT INTO tfunc(text) VALUES (@@session.collation_connection);
335 INSERT INTO tfunc(text) VALUES (@@session.character_set_connection);
336
337 SET @@collation_server= 'latin7_bin';
338 INSERT INTO tfunc(text) VALUES (@@session.collation_server);
339 INSERT INTO tfunc(text) VALUES (@@session.character_set_server);
340 SET @@collation_server= 'latin7_general_cs';
341 INSERT INTO tfunc(text) VALUES (@@session.collation_server);
342 INSERT INTO tfunc(text) VALUES (@@session.character_set_server);
343
344 SET @@time_zone= 'Europe/Moscow';
345 INSERT INTO tfunc(text) VALUES (@@session.time_zone);
346 SET @@time_zone= 'Universal';
347 INSERT INTO tfunc(text) VALUES (@@session.time_zone);
348
349 SET @@lc_time_names= 'sv_FI';
350 INSERT INTO tfunc(text) VALUES (@@session.lc_time_names);
351 SET @@lc_time_names= 'no_NO';
352 INSERT INTO tfunc(text) VALUES (@@session.lc_time_names);
353
354 SET @@collation_database= 'latin7_general_ci';
355 INSERT INTO tfunc(text) VALUES (@@session.collation_database);
356 INSERT INTO tfunc(text) VALUES (@@session.character_set_database);
357 SET @@collation_database= 'latin7_estonian_cs';
358 INSERT INTO tfunc(text) VALUES (@@session.collation_database);
359 INSERT INTO tfunc(text) VALUES (@@session.character_set_database);
360
361 SET @@timestamp= 47114711;
362 INSERT INTO tfunc(text) VALUES (@@session.timestamp);
363 SET @@timestamp= 47124712;
364 INSERT INTO tfunc(text) VALUES (@@session.timestamp);
365
366 SET @@last_insert_id= 1616;
367 INSERT INTO tfunc(text) VALUES (@@session.last_insert_id);
368 SET @@last_insert_id= 1717;
369 INSERT INTO tfunc(text) VALUES (@@session.last_insert_id);
370
371 RETURN 0;
372 END|
373 DELIMITER ;|
374
375 --disable_ps2_protocol
376 --disable_warnings
377 SELECT func();
378 --enable_warnings
379 --enable_ps2_protocol
380
381 --echo ==== Insert variables from a trigger ====
382
383 DELIMITER |;
384 CREATE TRIGGER trig
385 BEFORE INSERT ON trigger_table
386 FOR EACH ROW
387 BEGIN
388
389 SET @@pseudo_thread_id= 4712;
390 INSERT INTO ttrig(num) VALUES (@@session.pseudo_thread_id);
391 SET @@pseudo_thread_id= 4713;
392 INSERT INTO ttrig(num) VALUES (@@session.pseudo_thread_id);
393
394 SET @@foreign_key_checks= 0;
395 INSERT INTO ttrig(num) VALUES (@@session.foreign_key_checks);
396 SET @@foreign_key_checks= 1;
397 INSERT INTO ttrig(num) VALUES (@@session.foreign_key_checks);
398
399 SET @@sql_auto_is_null= 0;
400 INSERT INTO ttrig(num) VALUES (@@session.sql_auto_is_null);
401 SET @@sql_auto_is_null= 1;
402 INSERT INTO ttrig(num) VALUES (@@session.sql_auto_is_null);
403
404 SET @@unique_checks= 0;
405 INSERT INTO ttrig(num) VALUES (@@session.unique_checks);
406 SET @@unique_checks= 1;
407 INSERT INTO ttrig(num) VALUES (@@session.unique_checks);
408
409 SET @@auto_increment_increment= 11;
410 INSERT INTO ttrig(num) VALUES (@@session.auto_increment_increment);
411 SET @@auto_increment_increment= 19;
412 INSERT INTO ttrig(num) VALUES (@@session.auto_increment_increment);
413
414 SET @@auto_increment_offset= 13;
415 INSERT INTO ttrig(num) VALUES (@@session.auto_increment_offset);
416 SET @@auto_increment_offset= 17;
417 INSERT INTO ttrig(num) VALUES (@@session.auto_increment_offset);
418
419 # reset these as they affect the index column
420 SET @@auto_increment_increment= 1;
421 SET @@auto_increment_offset= 1;
422
423 SET @@character_set_client= 'cp1257';
424 INSERT INTO ttrig(text) VALUES (@@session.character_set_client);
425 SET @@character_set_client= 'cp1256';
426 INSERT INTO ttrig(text) VALUES (@@session.character_set_client);
427
428 SET @@collation_connection= 'cp1251_ukrainian_ci';
429 INSERT INTO ttrig(text) VALUES (@@session.collation_connection);
430 INSERT INTO ttrig(text) VALUES (@@session.character_set_connection);
431 SET @@collation_connection= 'cp1251_bulgarian_ci';
432 INSERT INTO ttrig(text) VALUES (@@session.collation_connection);
433 INSERT INTO ttrig(text) VALUES (@@session.character_set_connection);
434
435 SET @@collation_server= 'latin7_bin';
436 INSERT INTO ttrig(text) VALUES (@@session.collation_server);
437 INSERT INTO ttrig(text) VALUES (@@session.character_set_server);
438 SET @@collation_server= 'latin7_general_cs';
439 INSERT INTO ttrig(text) VALUES (@@session.collation_server);
440 INSERT INTO ttrig(text) VALUES (@@session.character_set_server);
441
442 SET @@time_zone= 'Europe/Moscow';
443 INSERT INTO ttrig(text) VALUES (@@session.time_zone);
444 SET @@time_zone= 'Universal';
445 INSERT INTO ttrig(text) VALUES (@@session.time_zone);
446
447 SET @@lc_time_names= 'sv_FI';
448 INSERT INTO ttrig(text) VALUES (@@session.lc_time_names);
449 SET @@lc_time_names= 'no_NO';
450 INSERT INTO ttrig(text) VALUES (@@session.lc_time_names);
451
452 SET @@collation_database= 'latin7_general_ci';
453 INSERT INTO ttrig(text) VALUES (@@session.collation_database);
454 INSERT INTO ttrig(text) VALUES (@@session.character_set_database);
455 SET @@collation_database= 'latin7_estonian_cs';
456 INSERT INTO ttrig(text) VALUES (@@session.collation_database);
457 INSERT INTO ttrig(text) VALUES (@@session.character_set_database);
458
459 SET @@timestamp= 47114711;
460 INSERT INTO ttrig(text) VALUES (@@session.timestamp);
461 SET @@timestamp= 47124712;
462 INSERT INTO ttrig(text) VALUES (@@session.timestamp);
463
464 SET @@last_insert_id= 1616;
465 INSERT INTO ttrig(text) VALUES (@@session.last_insert_id);
466 SET @@last_insert_id= 1717;
467 INSERT INTO ttrig(text) VALUES (@@session.last_insert_id);
468
469 END|
470 DELIMITER ;|
471
472 --disable_warnings
473 INSERT INTO trigger_table VALUES ('bye.');
474 --enable_warnings
475
476
477 --echo ==== Insert variables from a prepared statement ====
478
479 # GLOBAL
480
481 PREPARE p1 FROM 'SET @@pseudo_thread_id= 4712';
482 PREPARE p2 FROM 'INSERT INTO tprep(num) VALUES (@@session.pseudo_thread_id)';
483 PREPARE p3 FROM 'SET @@pseudo_thread_id= 4713';
484 PREPARE p4 FROM 'INSERT INTO tprep(num) VALUES (@@session.pseudo_thread_id)';
485
486 PREPARE p5 FROM 'SET @@foreign_key_checks= 0';
487 PREPARE p6 FROM 'INSERT INTO tprep(num) VALUES (@@session.foreign_key_checks)';
488 PREPARE p7 FROM 'SET @@foreign_key_checks= 1';
489 PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@session.foreign_key_checks)';
490
491 PREPARE p9 FROM 'SET @@sql_auto_is_null= 0';
492 PREPARE p10 FROM 'INSERT INTO tprep(num) VALUES (@@session.sql_auto_is_null)';
493 PREPARE p11 FROM 'SET @@sql_auto_is_null= 1';
494 PREPARE p12 FROM 'INSERT INTO tprep(num) VALUES (@@session.sql_auto_is_null)';
495
496 PREPARE p13 FROM 'SET @@unique_checks= 0';
497 PREPARE p14 FROM 'INSERT INTO tprep(num) VALUES (@@session.unique_checks)';
498 PREPARE p15 FROM 'SET @@unique_checks= 1';
499 PREPARE p16 FROM 'INSERT INTO tprep(num) VALUES (@@session.unique_checks)';
500
501 PREPARE p17 FROM 'SET @@auto_increment_increment= 11';
502 PREPARE p18 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_increment)';
503 PREPARE p19 FROM 'SET @@auto_increment_increment= 19';
504 PREPARE p20 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_increment)';
505
506 PREPARE p21 FROM 'SET @@auto_increment_offset= 13';
507 PREPARE p22 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_offset)';
508 PREPARE p23 FROM 'SET @@auto_increment_offset= 17';
509 PREPARE p24 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_offset)';
510
511 # reset these as they affect the index column
512 PREPARE p25 FROM 'SET @@auto_increment_increment= 1';
513 PREPARE p26 FROM 'SET @@auto_increment_offset= 1';
514
515 PREPARE p27 FROM 'SET @@character_set_client= \'cp1257\'';
516 PREPARE p28 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_client)';
517 PREPARE p29 FROM 'SET @@character_set_client= \'cp1256\'';
518 PREPARE p30 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_client)';
519
520 PREPARE p31 FROM 'SET @@collation_connection= \'cp1251_ukrainian_ci\'';
521 PREPARE p32 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_connection)';
522 PREPARE p33 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_connection)';
523 PREPARE p34 FROM 'SET @@collation_connection= \'cp1251_bulgarian_ci\'';
524 PREPARE p35 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_connection)';
525 PREPARE p36 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_connection)';
526
527 PREPARE p37 FROM 'SET @@collation_server= \'latin7_bin\'';
528 PREPARE p38 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_server)';
529 PREPARE p39 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_server)';
530 PREPARE p40 FROM 'SET @@collation_server= \'latin7_general_cs\'';
531 PREPARE p41 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_server)';
532 PREPARE p42 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_server)';
533
534 PREPARE p43 FROM 'SET @@time_zone= \'Europe/Moscow\'';
535 PREPARE p44 FROM 'INSERT INTO tprep(text) VALUES (@@session.time_zone)';
536 PREPARE p45 FROM 'SET @@time_zone= \'Universal\'';
537 PREPARE p46 FROM 'INSERT INTO tprep(text) VALUES (@@session.time_zone)';
538
539 PREPARE p47 FROM 'SET @@lc_time_names= \'sv_FI\'';
540 PREPARE p48 FROM 'INSERT INTO tprep(text) VALUES (@@session.lc_time_names)';
541 PREPARE p49 FROM 'SET @@lc_time_names= \'no_NO\'';
542 PREPARE p50 FROM 'INSERT INTO tprep(text) VALUES (@@session.lc_time_names)';
543
544 PREPARE p51 FROM 'SET @@collation_database= \'latin7_general_ci\'';
545 PREPARE p52 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_database)';
546 PREPARE p53 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_database)';
547 PREPARE p54 FROM 'SET @@collation_database= \'latin7_estonian_cs\'';
548 PREPARE p55 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_database)';
549 PREPARE p56 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_database)';
550
551 PREPARE p57 FROM 'SET @@timestamp= 47114711';
552 PREPARE p58 FROM 'INSERT INTO tprep(text) VALUES (@@session.timestamp)';
553 PREPARE p59 FROM 'SET @@timestamp= 47124712';
554 PREPARE p60 FROM 'INSERT INTO tprep(text) VALUES (@@session.timestamp)';
555
556 PREPARE p61 FROM 'SET @@last_insert_id= 1616';
557 PREPARE p62 FROM 'INSERT INTO tprep(text) VALUES (@@session.last_insert_id)';
558 PREPARE p63 FROM 'SET @@last_insert_id= 1717';
559 PREPARE p64 FROM 'INSERT INTO tprep(text) VALUES (@@session.last_insert_id)';
560
561 EXECUTE p1; EXECUTE p2; EXECUTE p3; EXECUTE p4; EXECUTE p5; EXECUTE p6;
562 EXECUTE p7; EXECUTE p8; EXECUTE p9; EXECUTE p10; EXECUTE p11; EXECUTE p12;
563 EXECUTE p13; EXECUTE p14; EXECUTE p15; EXECUTE p16; EXECUTE p17; EXECUTE p18;
564 EXECUTE p19; EXECUTE p20; EXECUTE p21; EXECUTE p22; EXECUTE p23; EXECUTE p24;
565 EXECUTE p25; EXECUTE p26; EXECUTE p27; EXECUTE p28; EXECUTE p29; EXECUTE p30;
566 EXECUTE p31; EXECUTE p32; EXECUTE p33; EXECUTE p34; EXECUTE p35; EXECUTE p36;
567 EXECUTE p37; EXECUTE p38; EXECUTE p39; EXECUTE p40; EXECUTE p41; EXECUTE p42;
568 EXECUTE p43; EXECUTE p44; EXECUTE p45; EXECUTE p46; EXECUTE p47; EXECUTE p48;
569 EXECUTE p49; EXECUTE p50; EXECUTE p51; EXECUTE p52; EXECUTE p53; EXECUTE p54;
570 EXECUTE p55; EXECUTE p56; EXECUTE p57; EXECUTE p58; EXECUTE p59; EXECUTE p60;
571 EXECUTE p61; EXECUTE p62; EXECUTE p63; EXECUTE p64;
572
573 --sync_slave_with_master
574 --connection master
575
576
577 --echo ==== Results ====
578
579 # Show the result in table test.tstmt on master...
580 SELECT * FROM tstmt ORDER BY id;
581
582 --sync_slave_with_master
583
584 # ... then compare test.tstmt on master to the other tables on master and slave.
585 let $diff_tables= master:tstmt, master:tproc, master:tfunc, master:ttrig, master:tprep, slave:tstmt, slave:tproc, slave:tfunc, slave:ttrig, slave:tprep;
586 source include/diff_tables.inc;
587
588
589 --echo ==== Clean up ====
590
591 connection master;
592 DROP PROCEDURE proc;
593 DROP FUNCTION func;
594 DROP TRIGGER trig;
595 DROP TABLE tstmt, tproc, tfunc, ttrig, tprep, trigger_table;
596
597 SET @@global.auto_increment_increment= @m_auto_increment_increment;
598 SET @@global.auto_increment_offset= @m_auto_increment_offset;
599 SET @@global.character_set_client= @m_character_set_client;
600 SET @@global.collation_connection= @m_collation_connection;
601 SET @@global.collation_server= @m_collation_server;
602 SET @@global.time_zone= @m_time_zone;
603 SET @@global.lc_time_names= @m_lc_time_names;
604 SET @@global.collation_database= @m_collation_database;
605
606 connection slave;
607 SET @@global.auto_increment_increment= @s_auto_increment_increment;
608 SET @@global.auto_increment_offset= @s_auto_increment_offset;
609 SET @@global.character_set_client= @s_character_set_client;
610 SET @@global.collation_connection= @s_collation_connection;
611 SET @@global.collation_server= @s_collation_server;
612 SET @@global.time_zone= @s_time_zone;
613 SET @@global.lc_time_names= @s_lc_time_names;
614 SET @@global.collation_database= @s_collation_database;
615
616 connection master;
617 sync_slave_with_master;
618 --source include/rpl_end.inc