Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # Test that queries referencing variables are replicated correctly in
4 # mixed and row-based logging mode.
5 #
6 #
7 # ==== Method ====
8 #
9 # The test simply does a lot of "INSERT INTO t1 VALUES (@@variable)"
10 # and checks the result on the slave.
11 #
12 # Statements referencing a variable only replicate correctly in mixed
13 # and row mode: in row mode, the values inserted are replicated. In
14 # mixed mode, statements referencing a variable are marked as unsafe,
15 # meaning they will be replicated by row. In statement mode, the
16 # slave's value will be used and replication will break. (Except in a
17 # small number of special cases: random seeds, insert_id, and
18 # auto_increment are replicated).
19 #
20 # We test the following variable scopes:
21 # - server system variables
22 # - server session variables
23 # - server "both" variables
24 # - user variables
25 #
26 # For each scope, we use variables of the following types if they
27 # exist:
28 # - boolean
29 # - numeric
30 # - string
31 # - enumeration variables
32 #
33 # We use these types of variables in the following contexts:
34 # - directly
35 # - from a stored procedure
36 # - from a stored function
37 # - from a trigger
38 # - from a prepared statement
39 #
40 # For all variables where it is possible, we set the variable to one
41 # value on slave, and insert it on the master with two distinct
42 # values.
43 #
44 # The same insertions are made in four different tables using direct
45 # insert, stored procedure, stored function, or trigger. Then all
46 # eight resulting tables on master and slave are compared.
47 #
48 #
49 # ==== Related bugs ====
50 #
51 # BUG#31168: @@hostname does not replicate
52 #
53 #
54 # ==== Related test cases ====
55 #
56 # binlog.binlog_unsafe tests that a warning is issued if system
57 # variables are replicated in statement mode.
58 #
59 # rpl.rpl_variables_stm tests the small subset of variables that
60 # actually can be replicated safely in statement mode.
61
62
63 source include/have_binlog_format_mixed_or_row.inc;
64 source include/master-slave.inc;
65
66
67 --echo ==== Initialization ====
68
69 # Backup the values of global variables so that they can be restored
70 # later.
71 connection master;
72 SET @m_default_week_format= @@global.default_week_format;
73 SET @m_init_slave= @@global.init_slave;
74 SET @m_lc_time_names= @@global.lc_time_names;
75 SET @m_low_priority_updates= @@global.low_priority_updates;
76 SET @m_relay_log_purge= @@global.relay_log_purge;
77 SET @m_slave_exec_mode= @@global.slave_exec_mode;
78 SET @m_sql_mode= @@global.sql_mode;
79 SET @m_sync_binlog= @@global.sync_binlog;
80
81 connection slave;
82 SET @s_default_week_format= @@global.default_week_format;
83 SET @s_init_slave= @@global.init_slave;
84 SET @s_lc_time_names= @@global.lc_time_names;
85 SET @s_low_priority_updates= @@global.low_priority_updates;
86 SET @s_relay_log_purge= @@global.relay_log_purge;
87 SET @s_slave_exec_mode= @@global.slave_exec_mode;
88 SET @s_sql_mode= @@global.sql_mode;
89 SET @s_sync_binlog= @@global.sync_binlog;
90
91 # Set global variables on slave to something different than on master.
92 SET @@global.relay_log_purge = OFF;
93 SET @@global.sync_binlog = 1000000;
94 SET @@global.slave_exec_mode = 'STRICT';
95 SET @@sql_big_selects = OFF;
96 SET @@last_insert_id = 10;
97 SET @@global.low_priority_updates = OFF;
98 SET @@local.low_priority_updates = OFF;
99 SET @@global.default_week_format = 1;
100 SET @@local.default_week_format = 2;
101 SET @@global.lc_time_names = 'zh_HK';
102 SET @@local.lc_time_names = 'zh_TW';
103 SET @@global.sql_mode = 'ALLOW_INVALID_DATES';
104 SET @@local.sql_mode = 'ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE';
105 SET @user_num = 10;
106 SET @user_text = 'Alunda';
107
108 # Stop slave so that we get a fresh sql thread, reading the slave's
109 # global values of variables into its local copies.
110 --source include/stop_slave.inc
111 --source include/start_slave.inc
112
113 # We would have wanted to set this together with the other variables
114 # above, but can't because it affects how the slave works.
115 SET @@global.init_slave = 'SELECT 1';
116
117
118 connection master;
119 # checking values of read-only variables
120 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
121 query_vertical SELECT @@pid_file, @@datadir;
122 --echo **** Relay log variables
123 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
124 query_vertical SELECT @@relay_log, @@relay_log_index, @@relay_log_basename;
125 --echo **** Binary log variables
126 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
127 query_vertical SELECT @@log_bin, @@log_bin_index, @@log_bin_basename;
128
129 connection slave;
130 # checking values of read-only variables
131 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
132 query_vertical SELECT @@pid_file, @@datadir;
133 --echo **** Relay log variables
134 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
135 query_vertical SELECT @@relay_log, @@relay_log_index, @@relay_log_basename;
136 --echo **** Binary log variables
137 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
138 query_vertical SELECT @@log_bin, @@log_bin_index, @@log_bin_basename;
139
140 connection master;
141
142 # Tables where everything happens.
143 CREATE TABLE tstmt (id INT AUTO_INCREMENT PRIMARY KEY,
144 truth BOOLEAN,
145 num INT,
146 text VARCHAR(100));
147 CREATE TABLE tproc LIKE tstmt;
148 CREATE TABLE tfunc LIKE tstmt;
149 CREATE TABLE ttrig LIKE tstmt;
150 CREATE TABLE tprep LIKE tstmt;
151
152 # Table on which we put a trigger.
153 CREATE TABLE trigger_table (text CHAR(4));
154
155
156 --echo ==== Insert variables directly ====
157
158 --echo ---- global variables ----
159
160 # boolean
161 SET @@global.relay_log_purge = ON;
162 INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge);
163 SET @@global.relay_log_purge = OFF;
164 INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge);
165
166 # numeric
167 SET @@global.sync_binlog = 2000000;
168 INSERT INTO tstmt(num) VALUES (@@global.sync_binlog);
169 SET @@global.sync_binlog = 3000000;
170 INSERT INTO tstmt(num) VALUES (@@global.sync_binlog);
171
172 # string
173 SET @@global.init_slave = 'SELECT 2';
174 INSERT INTO tstmt(text) VALUES (@@global.init_slave);
175 SET @@global.init_slave = 'SELECT 3';
176 INSERT INTO tstmt(text) VALUES (@@global.init_slave);
177
178 # enumeration
179 SET @@global.slave_exec_mode = 'IDEMPOTENT';
180 INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode);
181 SET @@global.slave_exec_mode = 'STRICT';
182 INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode);
183
184
185 --echo ---- session variables ----
186
187 # boolean
188 SET @@sql_big_selects = ON;
189 INSERT INTO tstmt(truth) VALUES (@@sql_big_selects);
190 SET @@sql_big_selects = OFF;
191 INSERT INTO tstmt(truth) VALUES (@@sql_big_selects);
192
193 # numeric
194 SET @@last_insert_id = 20;
195 INSERT INTO tstmt(num) VALUES (@@last_insert_id);
196 SET @@last_insert_id = 30;
197 INSERT INTO tstmt(num) VALUES (@@last_insert_id);
198
199 --echo ---- global and session variables ----
200
201 # boolean
202 SET @@global.low_priority_updates = ON;
203 SET @@local.low_priority_updates = OFF;
204 INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates);
205 INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates);
206 SET @@global.low_priority_updates = OFF;
207 SET @@local.low_priority_updates = ON;
208 INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates);
209 INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates);
210
211 # numeric
212 SET @@global.default_week_format = 3;
213 SET @@local.default_week_format = 4;
214 INSERT INTO tstmt(num) VALUES (@@global.default_week_format);
215 INSERT INTO tstmt(num) VALUES (@@local.default_week_format);
216 SET @@global.default_week_format = 5;
217 SET @@local.default_week_format = 6;
218 INSERT INTO tstmt(num) VALUES (@@global.default_week_format);
219 INSERT INTO tstmt(num) VALUES (@@local.default_week_format);
220
221 # string
222 SET @@global.lc_time_names = 'sv_SE';
223 SET @@local.lc_time_names = 'sv_FI';
224 INSERT INTO tstmt(text) VALUES (@@global.lc_time_names);
225 INSERT INTO tstmt(text) VALUES (@@local.lc_time_names);
226 SET @@global.lc_time_names = 'ar_TN';
227 SET @@local.lc_time_names = 'ar_IQ';
228 INSERT INTO tstmt(text) VALUES (@@global.lc_time_names);
229 INSERT INTO tstmt(text) VALUES (@@local.lc_time_names);
230
231 # enum
232 SET @@global.sql_mode = '';
233 SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
234 INSERT INTO tstmt(text) VALUES (@@global.sql_mode);
235 INSERT INTO tstmt(text) VALUES (@@local.sql_mode);
236 SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION';
237 SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
238 INSERT INTO tstmt(text) VALUES (@@global.sql_mode);
239 INSERT INTO tstmt(text) VALUES (@@local.sql_mode);
240
241 --echo ---- user variables ----
242
243 # numeric
244 SET @user_num = 20;
245 INSERT INTO tstmt(num) VALUES (@user_num);
246 SET @user_num = 30;
247 INSERT INTO tstmt(num) VALUES (@user_num);
248
249 # string
250 SET @user_text = 'Bergsbrunna';
251 INSERT INTO tstmt(text) VALUES (@user_text);
252 SET @user_text = 'Centrum';
253 INSERT INTO tstmt(text) VALUES (@user_text);
254
255
256 --echo ==== Insert variables from a stored procedure ====
257
258 DELIMITER |;
259 CREATE PROCEDURE proc()
260 BEGIN
261
262 # GLOBAL
263
264 # boolean
265 SET @@global.relay_log_purge = ON;
266 INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge);
267 SET @@global.relay_log_purge = OFF;
268 INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge);
269
270 # numeric
271 SET @@global.sync_binlog = 2000000;
272 INSERT INTO tproc(num) VALUES (@@global.sync_binlog);
273 SET @@global.sync_binlog = 3000000;
274 INSERT INTO tproc(num) VALUES (@@global.sync_binlog);
275
276 # string
277 SET @@global.init_slave = 'SELECT 2';
278 INSERT INTO tproc(text) VALUES (@@global.init_slave);
279 SET @@global.init_slave = 'SELECT 3';
280 INSERT INTO tproc(text) VALUES (@@global.init_slave);
281
282 # enumeration
283 SET @@global.slave_exec_mode = 'IDEMPOTENT';
284 INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode);
285 SET @@global.slave_exec_mode = 'STRICT';
286 INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode);
287
288 # SESSION
289
290 # boolean
291 SET @@sql_big_selects = ON;
292 INSERT INTO tproc(truth) VALUES (@@sql_big_selects);
293 SET @@sql_big_selects = OFF;
294 INSERT INTO tproc(truth) VALUES (@@sql_big_selects);
295
296 # numeric
297 SET @@last_insert_id = 20;
298 INSERT INTO tproc(num) VALUES (@@last_insert_id);
299 SET @@last_insert_id = 30;
300 INSERT INTO tproc(num) VALUES (@@last_insert_id);
301
302 # BOTH
303
304 # boolean
305 SET @@global.low_priority_updates = ON;
306 SET @@local.low_priority_updates = OFF;
307 INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates);
308 INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates);
309 SET @@global.low_priority_updates = OFF;
310 SET @@local.low_priority_updates = ON;
311 INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates);
312 INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates);
313
314 # numeric
315 SET @@global.default_week_format = 3;
316 SET @@local.default_week_format = 4;
317 INSERT INTO tproc(num) VALUES (@@global.default_week_format);
318 INSERT INTO tproc(num) VALUES (@@local.default_week_format);
319 SET @@global.default_week_format = 5;
320 SET @@local.default_week_format = 6;
321 INSERT INTO tproc(num) VALUES (@@global.default_week_format);
322 INSERT INTO tproc(num) VALUES (@@local.default_week_format);
323
324 # text
325 SET @@global.lc_time_names = 'sv_SE';
326 SET @@local.lc_time_names = 'sv_FI';
327 INSERT INTO tproc(text) VALUES (@@global.lc_time_names);
328 INSERT INTO tproc(text) VALUES (@@local.lc_time_names);
329 SET @@global.lc_time_names = 'ar_TN';
330 SET @@local.lc_time_names = 'ar_IQ';
331 INSERT INTO tproc(text) VALUES (@@global.lc_time_names);
332 INSERT INTO tproc(text) VALUES (@@local.lc_time_names);
333
334 # enum
335 SET @@global.sql_mode = '';
336 SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
337 INSERT INTO tproc(text) VALUES (@@global.sql_mode);
338 INSERT INTO tproc(text) VALUES (@@local.sql_mode);
339 SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION';
340 SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
341 INSERT INTO tproc(text) VALUES (@@global.sql_mode);
342 INSERT INTO tproc(text) VALUES (@@local.sql_mode);
343
344 # USER
345
346 # numeric
347 SET @user_num = 20;
348 INSERT INTO tproc(num) VALUES (@user_num);
349 SET @user_num = 30;
350 INSERT INTO tproc(num) VALUES (@user_num);
351
352 # string
353 SET @user_text = 'Bergsbrunna';
354 INSERT INTO tproc(text) VALUES (@user_text);
355 SET @user_text = 'Centrum';
356 INSERT INTO tproc(text) VALUES (@user_text);
357
358 END|
359 DELIMITER ;|
360
361 CALL proc();
362
363
364 --echo ==== Insert variables from a stored function ====
365
366 DELIMITER |;
367 CREATE FUNCTION func()
368 RETURNS INT
369 BEGIN
370
371 # GLOBAL
372
373 # boolean
374 SET @@global.relay_log_purge = ON;
375 INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge);
376 SET @@global.relay_log_purge = OFF;
377 INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge);
378
379 # numeric
380 SET @@global.sync_binlog = 2000000;
381 INSERT INTO tfunc(num) VALUES (@@global.sync_binlog);
382 SET @@global.sync_binlog = 3000000;
383 INSERT INTO tfunc(num) VALUES (@@global.sync_binlog);
384
385 # string
386 SET @@global.init_slave = 'SELECT 2';
387 INSERT INTO tfunc(text) VALUES (@@global.init_slave);
388 SET @@global.init_slave = 'SELECT 3';
389 INSERT INTO tfunc(text) VALUES (@@global.init_slave);
390
391 # enumeration
392 SET @@global.slave_exec_mode = 'IDEMPOTENT';
393 INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode);
394 SET @@global.slave_exec_mode = 'STRICT';
395 INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode);
396
397 # SESSION
398
399 # boolean
400 SET @@sql_big_selects = ON;
401 INSERT INTO tfunc(truth) VALUES (@@sql_big_selects);
402 SET @@sql_big_selects = OFF;
403 INSERT INTO tfunc(truth) VALUES (@@sql_big_selects);
404
405 # numeric
406 SET @@last_insert_id = 20;
407 INSERT INTO tfunc(num) VALUES (@@last_insert_id);
408 SET @@last_insert_id = 30;
409 INSERT INTO tfunc(num) VALUES (@@last_insert_id);
410
411 # BOTH
412
413 # boolean
414 SET @@global.low_priority_updates = ON;
415 SET @@local.low_priority_updates = OFF;
416 INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates);
417 INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates);
418 SET @@global.low_priority_updates = OFF;
419 SET @@local.low_priority_updates = ON;
420 INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates);
421 INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates);
422
423 # numeric
424 SET @@global.default_week_format = 3;
425 SET @@local.default_week_format = 4;
426 INSERT INTO tfunc(num) VALUES (@@global.default_week_format);
427 INSERT INTO tfunc(num) VALUES (@@local.default_week_format);
428 SET @@global.default_week_format = 5;
429 SET @@local.default_week_format = 6;
430 INSERT INTO tfunc(num) VALUES (@@global.default_week_format);
431 INSERT INTO tfunc(num) VALUES (@@local.default_week_format);
432
433 # text
434 SET @@global.lc_time_names = 'sv_SE';
435 SET @@local.lc_time_names = 'sv_FI';
436 INSERT INTO tfunc(text) VALUES (@@global.lc_time_names);
437 INSERT INTO tfunc(text) VALUES (@@local.lc_time_names);
438 SET @@global.lc_time_names = 'ar_TN';
439 SET @@local.lc_time_names = 'ar_IQ';
440 INSERT INTO tfunc(text) VALUES (@@global.lc_time_names);
441 INSERT INTO tfunc(text) VALUES (@@local.lc_time_names);
442
443 # enum
444 SET @@global.sql_mode = '';
445 SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
446 INSERT INTO tfunc(text) VALUES (@@global.sql_mode);
447 INSERT INTO tfunc(text) VALUES (@@local.sql_mode);
448 SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION';
449 SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
450 INSERT INTO tfunc(text) VALUES (@@global.sql_mode);
451 INSERT INTO tfunc(text) VALUES (@@local.sql_mode);
452
453 # USER
454
455 # numeric
456 SET @user_num = 20;
457 INSERT INTO tfunc(num) VALUES (@user_num);
458 SET @user_num = 30;
459 INSERT INTO tfunc(num) VALUES (@user_num);
460
461 # string
462 SET @user_text = 'Bergsbrunna';
463 INSERT INTO tfunc(text) VALUES (@user_text);
464 SET @user_text = 'Centrum';
465 INSERT INTO tfunc(text) VALUES (@user_text);
466
467 RETURN 0;
468 END|
469 DELIMITER ;|
470
471 --disable_ps2_protocol
472 SELECT func();
473 --enable_ps2_protocol
474
475 --echo ==== Insert variables from a trigger ====
476
477 DELIMITER |;
478 CREATE TRIGGER trig
479 BEFORE INSERT ON trigger_table
480 FOR EACH ROW
481 BEGIN
482
483 # GLOBAL
484
485 # boolean
486 SET @@global.relay_log_purge = ON;
487 INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge);
488 SET @@global.relay_log_purge = OFF;
489 INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge);
490
491 # numeric
492 SET @@global.sync_binlog = 2000000;
493 INSERT INTO ttrig(num) VALUES (@@global.sync_binlog);
494 SET @@global.sync_binlog = 3000000;
495 INSERT INTO ttrig(num) VALUES (@@global.sync_binlog);
496
497 # string
498 SET @@global.init_slave = 'SELECT 2';
499 INSERT INTO ttrig(text) VALUES (@@global.init_slave);
500 SET @@global.init_slave = 'SELECT 3';
501 INSERT INTO ttrig(text) VALUES (@@global.init_slave);
502
503 # enumeration
504 SET @@global.slave_exec_mode = 'IDEMPOTENT';
505 INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode);
506 SET @@global.slave_exec_mode = 'STRICT';
507 INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode);
508
509 # SESSION
510
511 # boolean
512 SET @@sql_big_selects = ON;
513 INSERT INTO ttrig(truth) VALUES (@@sql_big_selects);
514 SET @@sql_big_selects = OFF;
515 INSERT INTO ttrig(truth) VALUES (@@sql_big_selects);
516
517 # numeric
518 SET @@last_insert_id = 20;
519 INSERT INTO ttrig(num) VALUES (@@last_insert_id);
520 SET @@last_insert_id = 30;
521 INSERT INTO ttrig(num) VALUES (@@last_insert_id);
522
523 # BOTH
524
525 # boolean
526 SET @@global.low_priority_updates = ON;
527 SET @@local.low_priority_updates = OFF;
528 INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates);
529 INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates);
530 SET @@global.low_priority_updates = OFF;
531 SET @@local.low_priority_updates = ON;
532 INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates);
533 INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates);
534
535 # numeric
536 SET @@global.default_week_format = 3;
537 SET @@local.default_week_format = 4;
538 INSERT INTO ttrig(num) VALUES (@@global.default_week_format);
539 INSERT INTO ttrig(num) VALUES (@@local.default_week_format);
540 SET @@global.default_week_format = 5;
541 SET @@local.default_week_format = 6;
542 INSERT INTO ttrig(num) VALUES (@@global.default_week_format);
543 INSERT INTO ttrig(num) VALUES (@@local.default_week_format);
544
545 # text
546 SET @@global.lc_time_names = 'sv_SE';
547 SET @@local.lc_time_names = 'sv_FI';
548 INSERT INTO ttrig(text) VALUES (@@global.lc_time_names);
549 INSERT INTO ttrig(text) VALUES (@@local.lc_time_names);
550 SET @@global.lc_time_names = 'ar_TN';
551 SET @@local.lc_time_names = 'ar_IQ';
552 INSERT INTO ttrig(text) VALUES (@@global.lc_time_names);
553 INSERT INTO ttrig(text) VALUES (@@local.lc_time_names);
554
555 # enum
556 SET @@global.sql_mode = '';
557 SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
558 INSERT INTO ttrig(text) VALUES (@@global.sql_mode);
559 INSERT INTO ttrig(text) VALUES (@@local.sql_mode);
560 SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION';
561 SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
562 INSERT INTO ttrig(text) VALUES (@@global.sql_mode);
563 INSERT INTO ttrig(text) VALUES (@@local.sql_mode);
564
565 # USER
566
567 # numeric
568 SET @user_num = 20;
569 INSERT INTO ttrig(num) VALUES (@user_num);
570 SET @user_num = 30;
571 INSERT INTO ttrig(num) VALUES (@user_num);
572
573 # string
574 SET @user_text = 'Bergsbrunna';
575 INSERT INTO ttrig(text) VALUES (@user_text);
576 SET @user_text = 'Centrum';
577 INSERT INTO ttrig(text) VALUES (@user_text);
578 END|
579 DELIMITER ;|
580
581 INSERT INTO trigger_table VALUES ('bye.');
582
583
584 --echo ==== Insert variables from a prepared statement ====
585
586 # GLOBAL
587
588 # boolean
589 PREPARE p1 FROM 'SET @@global.relay_log_purge = ON';
590 PREPARE p2 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)';
591 PREPARE p3 FROM 'SET @@global.relay_log_purge = OFF';
592 PREPARE p4 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)';
593
594 # numeric
595 PREPARE p5 FROM 'SET @@global.sync_binlog = 2000000';
596 PREPARE p6 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)';
597 PREPARE p7 FROM 'SET @@global.sync_binlog = 3000000';
598 PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)';
599
600 # string
601 PREPARE p9 FROM 'SET @@global.init_slave = \'SELECT 2\'';
602 PREPARE p10 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';
603 PREPARE p11 FROM 'SET @@global.init_slave = \'SELECT 3\'';
604 PREPARE p12 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';
605
606 # enumeration
607 PREPARE p13 FROM 'SET @@global.slave_exec_mode = \'IDEMPOTENT\'';
608 PREPARE p14 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)';
609 PREPARE p15 FROM 'SET @@global.slave_exec_mode = \'STRICT\'';
610 PREPARE p16 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)';
611
612 # SESSION
613
614 # boolean
615 PREPARE p17 FROM 'SET @@sql_big_selects = ON';
616 PREPARE p18 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)';
617 PREPARE p19 FROM 'SET @@sql_big_selects = OFF';
618 PREPARE p20 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)';
619
620 # numeric
621 PREPARE p21 FROM 'SET @@last_insert_id = 20';
622 PREPARE p22 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)';
623 PREPARE p23 FROM 'SET @@last_insert_id = 30';
624 PREPARE p24 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)';
625
626 # BOTH
627
628 # boolean
629 PREPARE p25 FROM 'SET @@global.low_priority_updates = ON';
630 PREPARE p26 FROM 'SET @@local.low_priority_updates = OFF';
631 PREPARE p27 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)';
632 PREPARE p28 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)';
633 PREPARE p29 FROM 'SET @@global.low_priority_updates = OFF';
634 PREPARE p30 FROM 'SET @@local.low_priority_updates = ON';
635 PREPARE p31 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)';
636 PREPARE p32 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)';
637
638 # numeric
639 PREPARE p33 FROM 'SET @@global.default_week_format = 3';
640 PREPARE p34 FROM 'SET @@local.default_week_format = 4';
641 PREPARE p35 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)';
642 PREPARE p36 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)';
643 PREPARE p37 FROM 'SET @@global.default_week_format = 5';
644 PREPARE p38 FROM 'SET @@local.default_week_format = 6';
645 PREPARE p39 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)';
646 PREPARE p40 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)';
647
648 # text
649 PREPARE p41 FROM 'SET @@global.lc_time_names = \'sv_SE\'';
650 PREPARE p42 FROM 'SET @@local.lc_time_names = \'sv_FI\'';
651 PREPARE p43 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)';
652 PREPARE p44 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)';
653 PREPARE p45 FROM 'SET @@global.lc_time_names = \'ar_TN\'';
654 PREPARE p46 FROM 'SET @@local.lc_time_names = \'ar_IQ\'';
655 PREPARE p47 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)';
656 PREPARE p48 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)';
657
658 # enum
659 PREPARE p49 FROM 'SET @@global.sql_mode = \'\'';
660 PREPARE p50 FROM 'SET @@local.sql_mode = \'IGNORE_SPACE,NO_AUTO_CREATE_USER\'';
661 PREPARE p51 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)';
662 PREPARE p52 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)';
663 PREPARE p53 FROM 'SET @@global.sql_mode = \'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION\'';
664 PREPARE p54 FROM 'SET @@local.sql_mode = \'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS\'';
665 PREPARE p55 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)';
666 PREPARE p56 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)';
667
668 # USER
669
670 # numeric
671 PREPARE p57 FROM 'SET @user_num = 20';
672 PREPARE p58 FROM 'INSERT INTO tprep(num) VALUES (@user_num)';
673 PREPARE p59 FROM 'SET @user_num = 30';
674 PREPARE p60 FROM 'INSERT INTO tprep(num) VALUES (@user_num)';
675
676 # string
677 PREPARE p61 FROM 'SET @user_text = \'Bergsbrunna\'';
678 PREPARE p62 FROM 'INSERT INTO tprep(text) VALUES (@user_text)';
679 PREPARE p63 FROM 'SET @user_text = \'Centrum\'';
680 PREPARE p64 FROM 'INSERT INTO tprep(text) VALUES (@user_text)';
681
682 EXECUTE p1; EXECUTE p2; EXECUTE p3; EXECUTE p4; EXECUTE p5; EXECUTE p6;
683 EXECUTE p7; EXECUTE p8; EXECUTE p9; EXECUTE p10; EXECUTE p11; EXECUTE p12;
684 EXECUTE p13; EXECUTE p14; EXECUTE p15; EXECUTE p16; EXECUTE p17; EXECUTE p18;
685 EXECUTE p19; EXECUTE p20; EXECUTE p21; EXECUTE p22; EXECUTE p23; EXECUTE p24;
686 EXECUTE p25; EXECUTE p26; EXECUTE p27; EXECUTE p28; EXECUTE p29; EXECUTE p30;
687 EXECUTE p31; EXECUTE p32; EXECUTE p33; EXECUTE p34; EXECUTE p35; EXECUTE p36;
688 EXECUTE p37; EXECUTE p38; EXECUTE p39; EXECUTE p40; EXECUTE p41; EXECUTE p42;
689 EXECUTE p43; EXECUTE p44; EXECUTE p45; EXECUTE p46; EXECUTE p47; EXECUTE p48;
690 EXECUTE p49; EXECUTE p50; EXECUTE p51; EXECUTE p52; EXECUTE p53; EXECUTE p54;
691 EXECUTE p55; EXECUTE p56; EXECUTE p57; EXECUTE p58; EXECUTE p59; EXECUTE p60;
692 EXECUTE p61; EXECUTE p62; EXECUTE p63; EXECUTE p64;
693
694 --sync_slave_with_master
695 --connection master
696
697
698 --echo ==== Results ====
699
700 # Show the result in table test.tstmt on master...
701 SELECT * FROM tstmt ORDER BY id;
702
703 --sync_slave_with_master
704
705 # ... then compare test.tstmt on master to the other tables on master and slave.
706 let $diff_tables= master:tstmt, master:tproc, master:tfunc, master:ttrig, master:tprep, slave:tstmt, slave:tproc, slave:tfunc, slave:ttrig, slave:tprep;
707 source include/diff_tables.inc;
708
709
710 --echo ==== Clean up ====
711
712 connection master;
713 DROP PROCEDURE proc;
714 DROP FUNCTION func;
715 DROP TRIGGER trig;
716 DROP TABLE tstmt, tproc, tfunc, ttrig, tprep, trigger_table;
717
718 SET @@global.default_week_format= @m_default_week_format;
719 SET @@global.init_slave= @m_init_slave;
720 SET @@global.lc_time_names= @m_lc_time_names;
721 SET @@global.low_priority_updates= @m_low_priority_updates;
722 SET @@global.relay_log_purge= @m_relay_log_purge;
723 SET @@global.slave_exec_mode= @m_slave_exec_mode;
724 SET @@global.sql_mode= @m_sql_mode;
725 SET @@global.sync_binlog= @m_sync_binlog;
726
727 connection slave;
728 SET @@global.default_week_format= @s_default_week_format;
729 SET @@global.init_slave= @s_init_slave;
730 SET @@global.lc_time_names= @s_lc_time_names;
731 SET @@global.low_priority_updates= @s_low_priority_updates;
732 SET @@global.relay_log_purge= @s_relay_log_purge;
733 SET @@global.slave_exec_mode= @s_slave_exec_mode;
734 SET @@global.sql_mode= @s_sql_mode;
735 SET @@global.sync_binlog= @s_sync_binlog;
736
737 connection master;
738 sync_slave_with_master;
739 --source include/rpl_end.inc