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