|
1
by brian
clean slate |
1 |
stop slave; |
2 |
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; |
|
3 |
reset master; |
|
4 |
reset slave; |
|
5 |
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; |
|
6 |
start slave; |
|
7 |
==== Initialization ==== |
|
8 |
[on master] |
|
9 |
SET @m_default_week_format= @@global.default_week_format; |
|
10 |
SET @m_init_slave= @@global.init_slave; |
|
11 |
SET @m_lc_time_names= @@global.lc_time_names; |
|
12 |
SET @m_low_priority_updates= @@global.low_priority_updates; |
|
13 |
SET @m_relay_log_purge= @@global.relay_log_purge; |
|
14 |
SET @m_slave_exec_mode= @@global.slave_exec_mode; |
|
15 |
SET @m_sql_mode= @@global.sql_mode; |
|
16 |
SET @m_sync_binlog= @@global.sync_binlog; |
|
17 |
[on slave] |
|
18 |
SET @s_default_week_format= @@global.default_week_format; |
|
19 |
SET @s_init_slave= @@global.init_slave; |
|
20 |
SET @s_lc_time_names= @@global.lc_time_names; |
|
21 |
SET @s_low_priority_updates= @@global.low_priority_updates; |
|
22 |
SET @s_relay_log_purge= @@global.relay_log_purge; |
|
23 |
SET @s_slave_exec_mode= @@global.slave_exec_mode; |
|
24 |
SET @s_sql_mode= @@global.sql_mode; |
|
25 |
SET @s_sync_binlog= @@global.sync_binlog; |
|
26 |
SET @@global.relay_log_purge = OFF; |
|
27 |
SET @@global.sync_binlog = 1000000; |
|
28 |
SET @@global.slave_exec_mode = 'STRICT'; |
|
29 |
SET @@sql_big_selects = OFF; |
|
30 |
SET @@last_insert_id = 10; |
|
31 |
SET @@global.low_priority_updates = OFF; |
|
32 |
SET @@local.low_priority_updates = OFF; |
|
33 |
SET @@global.default_week_format = 1; |
|
34 |
SET @@local.default_week_format = 2; |
|
35 |
SET @@global.lc_time_names = 'zh_HK'; |
|
36 |
SET @@local.lc_time_names = 'zh_TW'; |
|
37 |
SET @@global.sql_mode = 'ALLOW_INVALID_DATES'; |
|
38 |
SET @@local.sql_mode = 'ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE'; |
|
39 |
SET @user_num = 10; |
|
40 |
SET @user_text = 'Alunda'; |
|
41 |
[on master] |
|
42 |
**** Resetting master and slave **** |
|
43 |
STOP SLAVE; |
|
44 |
RESET SLAVE; |
|
45 |
RESET MASTER; |
|
46 |
START SLAVE; |
|
47 |
[on slave] |
|
48 |
SET @@global.init_slave = 'ant'; |
|
49 |
[on master] |
|
50 |
CREATE TABLE tstmt (id INT AUTO_INCREMENT PRIMARY KEY, |
|
51 |
truth BOOLEAN, |
|
52 |
num INT, |
|
53 |
text VARCHAR(100)); |
|
54 |
CREATE TABLE tproc LIKE tstmt; |
|
55 |
CREATE TABLE tfunc LIKE tstmt; |
|
56 |
CREATE TABLE ttrig LIKE tstmt; |
|
57 |
CREATE TABLE tprep LIKE tstmt; |
|
58 |
CREATE TABLE trigger_table (text CHAR(4)); |
|
59 |
==== Insert variables directly ==== |
|
60 |
---- global variables ---- |
|
61 |
SET @@global.relay_log_purge = ON; |
|
62 |
INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge); |
|
63 |
SET @@global.relay_log_purge = OFF; |
|
64 |
INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge); |
|
65 |
SET @@global.sync_binlog = 2000000; |
|
66 |
INSERT INTO tstmt(num) VALUES (@@global.sync_binlog); |
|
67 |
SET @@global.sync_binlog = 3000000; |
|
68 |
INSERT INTO tstmt(num) VALUES (@@global.sync_binlog); |
|
69 |
SET @@global.init_slave = 'bison'; |
|
70 |
INSERT INTO tstmt(text) VALUES (@@global.init_slave); |
|
71 |
SET @@global.init_slave = 'cat'; |
|
72 |
INSERT INTO tstmt(text) VALUES (@@global.init_slave); |
|
73 |
SET @@global.slave_exec_mode = 'IDEMPOTENT'; |
|
74 |
INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode); |
|
75 |
SET @@global.slave_exec_mode = 'STRICT'; |
|
76 |
INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode); |
|
77 |
---- session variables ---- |
|
78 |
SET @@sql_big_selects = ON; |
|
79 |
INSERT INTO tstmt(truth) VALUES (@@sql_big_selects); |
|
80 |
SET @@sql_big_selects = OFF; |
|
81 |
INSERT INTO tstmt(truth) VALUES (@@sql_big_selects); |
|
82 |
SET @@last_insert_id = 20; |
|
83 |
INSERT INTO tstmt(num) VALUES (@@last_insert_id); |
|
84 |
SET @@last_insert_id = 30; |
|
85 |
INSERT INTO tstmt(num) VALUES (@@last_insert_id); |
|
86 |
---- global and session variables ---- |
|
87 |
SET @@global.low_priority_updates = ON; |
|
88 |
SET @@local.low_priority_updates = OFF; |
|
89 |
INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates); |
|
90 |
INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates); |
|
91 |
SET @@global.low_priority_updates = OFF; |
|
92 |
SET @@local.low_priority_updates = ON; |
|
93 |
INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates); |
|
94 |
INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates); |
|
95 |
SET @@global.default_week_format = 3; |
|
96 |
SET @@local.default_week_format = 4; |
|
97 |
INSERT INTO tstmt(num) VALUES (@@global.default_week_format); |
|
98 |
INSERT INTO tstmt(num) VALUES (@@local.default_week_format); |
|
99 |
SET @@global.default_week_format = 5; |
|
100 |
SET @@local.default_week_format = 6; |
|
101 |
INSERT INTO tstmt(num) VALUES (@@global.default_week_format); |
|
102 |
INSERT INTO tstmt(num) VALUES (@@local.default_week_format); |
|
103 |
SET @@global.lc_time_names = 'sv_SE'; |
|
104 |
SET @@local.lc_time_names = 'sv_FI'; |
|
105 |
INSERT INTO tstmt(text) VALUES (@@global.lc_time_names); |
|
106 |
INSERT INTO tstmt(text) VALUES (@@local.lc_time_names); |
|
107 |
SET @@global.lc_time_names = 'ar_TN'; |
|
108 |
SET @@local.lc_time_names = 'ar_IQ'; |
|
109 |
INSERT INTO tstmt(text) VALUES (@@global.lc_time_names); |
|
110 |
INSERT INTO tstmt(text) VALUES (@@local.lc_time_names); |
|
111 |
SET @@global.sql_mode = ''; |
|
112 |
SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; |
|
113 |
INSERT INTO tstmt(text) VALUES (@@global.sql_mode); |
|
114 |
INSERT INTO tstmt(text) VALUES (@@local.sql_mode); |
|
115 |
SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; |
|
116 |
SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; |
|
117 |
INSERT INTO tstmt(text) VALUES (@@global.sql_mode); |
|
118 |
INSERT INTO tstmt(text) VALUES (@@local.sql_mode); |
|
119 |
---- user variables ---- |
|
120 |
SET @user_num = 20; |
|
121 |
INSERT INTO tstmt(num) VALUES (@user_num); |
|
122 |
SET @user_num = 30; |
|
123 |
INSERT INTO tstmt(num) VALUES (@user_num); |
|
124 |
SET @user_text = 'Bergsbrunna'; |
|
125 |
INSERT INTO tstmt(text) VALUES (@user_text); |
|
126 |
SET @user_text = 'Centrum'; |
|
127 |
INSERT INTO tstmt(text) VALUES (@user_text); |
|
128 |
==== Insert variables from a stored procedure ==== |
|
129 |
CREATE PROCEDURE proc() |
|
130 |
BEGIN
|
|
131 |
# GLOBAL
|
|
132 |
# boolean
|
|
133 |
SET @@global.relay_log_purge = ON; |
|
134 |
INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge); |
|
135 |
SET @@global.relay_log_purge = OFF; |
|
136 |
INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge); |
|
137 |
# numeric
|
|
138 |
SET @@global.sync_binlog = 2000000; |
|
139 |
INSERT INTO tproc(num) VALUES (@@global.sync_binlog); |
|
140 |
SET @@global.sync_binlog = 3000000; |
|
141 |
INSERT INTO tproc(num) VALUES (@@global.sync_binlog); |
|
142 |
# string
|
|
143 |
SET @@global.init_slave = 'bison'; |
|
144 |
INSERT INTO tproc(text) VALUES (@@global.init_slave); |
|
145 |
SET @@global.init_slave = 'cat'; |
|
146 |
INSERT INTO tproc(text) VALUES (@@global.init_slave); |
|
147 |
# enumeration
|
|
148 |
SET @@global.slave_exec_mode = 'IDEMPOTENT'; |
|
149 |
INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode); |
|
150 |
SET @@global.slave_exec_mode = 'STRICT'; |
|
151 |
INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode); |
|
152 |
# SESSION
|
|
153 |
# boolean
|
|
154 |
SET @@sql_big_selects = ON; |
|
155 |
INSERT INTO tproc(truth) VALUES (@@sql_big_selects); |
|
156 |
SET @@sql_big_selects = OFF; |
|
157 |
INSERT INTO tproc(truth) VALUES (@@sql_big_selects); |
|
158 |
# numeric
|
|
159 |
SET @@last_insert_id = 20; |
|
160 |
INSERT INTO tproc(num) VALUES (@@last_insert_id); |
|
161 |
SET @@last_insert_id = 30; |
|
162 |
INSERT INTO tproc(num) VALUES (@@last_insert_id); |
|
163 |
# BOTH
|
|
164 |
# boolean
|
|
165 |
SET @@global.low_priority_updates = ON; |
|
166 |
SET @@local.low_priority_updates = OFF; |
|
167 |
INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates); |
|
168 |
INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates); |
|
169 |
SET @@global.low_priority_updates = OFF; |
|
170 |
SET @@local.low_priority_updates = ON; |
|
171 |
INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates); |
|
172 |
INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates); |
|
173 |
# numeric
|
|
174 |
SET @@global.default_week_format = 3; |
|
175 |
SET @@local.default_week_format = 4; |
|
176 |
INSERT INTO tproc(num) VALUES (@@global.default_week_format); |
|
177 |
INSERT INTO tproc(num) VALUES (@@local.default_week_format); |
|
178 |
SET @@global.default_week_format = 5; |
|
179 |
SET @@local.default_week_format = 6; |
|
180 |
INSERT INTO tproc(num) VALUES (@@global.default_week_format); |
|
181 |
INSERT INTO tproc(num) VALUES (@@local.default_week_format); |
|
182 |
# text
|
|
183 |
SET @@global.lc_time_names = 'sv_SE'; |
|
184 |
SET @@local.lc_time_names = 'sv_FI'; |
|
185 |
INSERT INTO tproc(text) VALUES (@@global.lc_time_names); |
|
186 |
INSERT INTO tproc(text) VALUES (@@local.lc_time_names); |
|
187 |
SET @@global.lc_time_names = 'ar_TN'; |
|
188 |
SET @@local.lc_time_names = 'ar_IQ'; |
|
189 |
INSERT INTO tproc(text) VALUES (@@global.lc_time_names); |
|
190 |
INSERT INTO tproc(text) VALUES (@@local.lc_time_names); |
|
191 |
# enum
|
|
192 |
SET @@global.sql_mode = ''; |
|
193 |
SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; |
|
194 |
INSERT INTO tproc(text) VALUES (@@global.sql_mode); |
|
195 |
INSERT INTO tproc(text) VALUES (@@local.sql_mode); |
|
196 |
SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; |
|
197 |
SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; |
|
198 |
INSERT INTO tproc(text) VALUES (@@global.sql_mode); |
|
199 |
INSERT INTO tproc(text) VALUES (@@local.sql_mode); |
|
200 |
# USER
|
|
201 |
# numeric
|
|
202 |
SET @user_num = 20; |
|
203 |
INSERT INTO tproc(num) VALUES (@user_num); |
|
204 |
SET @user_num = 30; |
|
205 |
INSERT INTO tproc(num) VALUES (@user_num); |
|
206 |
# string
|
|
207 |
SET @user_text = 'Bergsbrunna'; |
|
208 |
INSERT INTO tproc(text) VALUES (@user_text); |
|
209 |
SET @user_text = 'Centrum'; |
|
210 |
INSERT INTO tproc(text) VALUES (@user_text); |
|
211 |
END| |
|
212 |
CALL proc(); |
|
213 |
==== Insert variables from a stored function ==== |
|
214 |
CREATE FUNCTION func() |
|
215 |
RETURNS INT |
|
216 |
BEGIN
|
|
217 |
# GLOBAL
|
|
218 |
# boolean
|
|
219 |
SET @@global.relay_log_purge = ON; |
|
220 |
INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge); |
|
221 |
SET @@global.relay_log_purge = OFF; |
|
222 |
INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge); |
|
223 |
# numeric
|
|
224 |
SET @@global.sync_binlog = 2000000; |
|
225 |
INSERT INTO tfunc(num) VALUES (@@global.sync_binlog); |
|
226 |
SET @@global.sync_binlog = 3000000; |
|
227 |
INSERT INTO tfunc(num) VALUES (@@global.sync_binlog); |
|
228 |
# string
|
|
229 |
SET @@global.init_slave = 'bison'; |
|
230 |
INSERT INTO tfunc(text) VALUES (@@global.init_slave); |
|
231 |
SET @@global.init_slave = 'cat'; |
|
232 |
INSERT INTO tfunc(text) VALUES (@@global.init_slave); |
|
233 |
# enumeration
|
|
234 |
SET @@global.slave_exec_mode = 'IDEMPOTENT'; |
|
235 |
INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode); |
|
236 |
SET @@global.slave_exec_mode = 'STRICT'; |
|
237 |
INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode); |
|
238 |
# SESSION
|
|
239 |
# boolean
|
|
240 |
SET @@sql_big_selects = ON; |
|
241 |
INSERT INTO tfunc(truth) VALUES (@@sql_big_selects); |
|
242 |
SET @@sql_big_selects = OFF; |
|
243 |
INSERT INTO tfunc(truth) VALUES (@@sql_big_selects); |
|
244 |
# numeric
|
|
245 |
SET @@last_insert_id = 20; |
|
246 |
INSERT INTO tfunc(num) VALUES (@@last_insert_id); |
|
247 |
SET @@last_insert_id = 30; |
|
248 |
INSERT INTO tfunc(num) VALUES (@@last_insert_id); |
|
249 |
# BOTH
|
|
250 |
# boolean
|
|
251 |
SET @@global.low_priority_updates = ON; |
|
252 |
SET @@local.low_priority_updates = OFF; |
|
253 |
INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates); |
|
254 |
INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates); |
|
255 |
SET @@global.low_priority_updates = OFF; |
|
256 |
SET @@local.low_priority_updates = ON; |
|
257 |
INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates); |
|
258 |
INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates); |
|
259 |
# numeric
|
|
260 |
SET @@global.default_week_format = 3; |
|
261 |
SET @@local.default_week_format = 4; |
|
262 |
INSERT INTO tfunc(num) VALUES (@@global.default_week_format); |
|
263 |
INSERT INTO tfunc(num) VALUES (@@local.default_week_format); |
|
264 |
SET @@global.default_week_format = 5; |
|
265 |
SET @@local.default_week_format = 6; |
|
266 |
INSERT INTO tfunc(num) VALUES (@@global.default_week_format); |
|
267 |
INSERT INTO tfunc(num) VALUES (@@local.default_week_format); |
|
268 |
# text
|
|
269 |
SET @@global.lc_time_names = 'sv_SE'; |
|
270 |
SET @@local.lc_time_names = 'sv_FI'; |
|
271 |
INSERT INTO tfunc(text) VALUES (@@global.lc_time_names); |
|
272 |
INSERT INTO tfunc(text) VALUES (@@local.lc_time_names); |
|
273 |
SET @@global.lc_time_names = 'ar_TN'; |
|
274 |
SET @@local.lc_time_names = 'ar_IQ'; |
|
275 |
INSERT INTO tfunc(text) VALUES (@@global.lc_time_names); |
|
276 |
INSERT INTO tfunc(text) VALUES (@@local.lc_time_names); |
|
277 |
# enum
|
|
278 |
SET @@global.sql_mode = ''; |
|
279 |
SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; |
|
280 |
INSERT INTO tfunc(text) VALUES (@@global.sql_mode); |
|
281 |
INSERT INTO tfunc(text) VALUES (@@local.sql_mode); |
|
282 |
SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; |
|
283 |
SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; |
|
284 |
INSERT INTO tfunc(text) VALUES (@@global.sql_mode); |
|
285 |
INSERT INTO tfunc(text) VALUES (@@local.sql_mode); |
|
286 |
# USER
|
|
287 |
# numeric
|
|
288 |
SET @user_num = 20; |
|
289 |
INSERT INTO tfunc(num) VALUES (@user_num); |
|
290 |
SET @user_num = 30; |
|
291 |
INSERT INTO tfunc(num) VALUES (@user_num); |
|
292 |
# string
|
|
293 |
SET @user_text = 'Bergsbrunna'; |
|
294 |
INSERT INTO tfunc(text) VALUES (@user_text); |
|
295 |
SET @user_text = 'Centrum'; |
|
296 |
INSERT INTO tfunc(text) VALUES (@user_text); |
|
297 |
RETURN 0; |
|
298 |
END| |
|
299 |
SELECT func(); |
|
300 |
func() |
|
301 |
0
|
|
302 |
==== Insert variables from a trigger ==== |
|
303 |
CREATE TRIGGER trig |
|
304 |
BEFORE INSERT ON trigger_table |
|
305 |
FOR EACH ROW |
|
306 |
BEGIN
|
|
307 |
# GLOBAL
|
|
308 |
# boolean
|
|
309 |
SET @@global.relay_log_purge = ON; |
|
310 |
INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge); |
|
311 |
SET @@global.relay_log_purge = OFF; |
|
312 |
INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge); |
|
313 |
# numeric
|
|
314 |
SET @@global.sync_binlog = 2000000; |
|
315 |
INSERT INTO ttrig(num) VALUES (@@global.sync_binlog); |
|
316 |
SET @@global.sync_binlog = 3000000; |
|
317 |
INSERT INTO ttrig(num) VALUES (@@global.sync_binlog); |
|
318 |
# string
|
|
319 |
SET @@global.init_slave = 'bison'; |
|
320 |
INSERT INTO ttrig(text) VALUES (@@global.init_slave); |
|
321 |
SET @@global.init_slave = 'cat'; |
|
322 |
INSERT INTO ttrig(text) VALUES (@@global.init_slave); |
|
323 |
# enumeration
|
|
324 |
SET @@global.slave_exec_mode = 'IDEMPOTENT'; |
|
325 |
INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode); |
|
326 |
SET @@global.slave_exec_mode = 'STRICT'; |
|
327 |
INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode); |
|
328 |
# SESSION
|
|
329 |
# boolean
|
|
330 |
SET @@sql_big_selects = ON; |
|
331 |
INSERT INTO ttrig(truth) VALUES (@@sql_big_selects); |
|
332 |
SET @@sql_big_selects = OFF; |
|
333 |
INSERT INTO ttrig(truth) VALUES (@@sql_big_selects); |
|
334 |
# numeric
|
|
335 |
SET @@last_insert_id = 20; |
|
336 |
INSERT INTO ttrig(num) VALUES (@@last_insert_id); |
|
337 |
SET @@last_insert_id = 30; |
|
338 |
INSERT INTO ttrig(num) VALUES (@@last_insert_id); |
|
339 |
# BOTH
|
|
340 |
# boolean
|
|
341 |
SET @@global.low_priority_updates = ON; |
|
342 |
SET @@local.low_priority_updates = OFF; |
|
343 |
INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates); |
|
344 |
INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates); |
|
345 |
SET @@global.low_priority_updates = OFF; |
|
346 |
SET @@local.low_priority_updates = ON; |
|
347 |
INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates); |
|
348 |
INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates); |
|
349 |
# numeric
|
|
350 |
SET @@global.default_week_format = 3; |
|
351 |
SET @@local.default_week_format = 4; |
|
352 |
INSERT INTO ttrig(num) VALUES (@@global.default_week_format); |
|
353 |
INSERT INTO ttrig(num) VALUES (@@local.default_week_format); |
|
354 |
SET @@global.default_week_format = 5; |
|
355 |
SET @@local.default_week_format = 6; |
|
356 |
INSERT INTO ttrig(num) VALUES (@@global.default_week_format); |
|
357 |
INSERT INTO ttrig(num) VALUES (@@local.default_week_format); |
|
358 |
# text
|
|
359 |
SET @@global.lc_time_names = 'sv_SE'; |
|
360 |
SET @@local.lc_time_names = 'sv_FI'; |
|
361 |
INSERT INTO ttrig(text) VALUES (@@global.lc_time_names); |
|
362 |
INSERT INTO ttrig(text) VALUES (@@local.lc_time_names); |
|
363 |
SET @@global.lc_time_names = 'ar_TN'; |
|
364 |
SET @@local.lc_time_names = 'ar_IQ'; |
|
365 |
INSERT INTO ttrig(text) VALUES (@@global.lc_time_names); |
|
366 |
INSERT INTO ttrig(text) VALUES (@@local.lc_time_names); |
|
367 |
# enum
|
|
368 |
SET @@global.sql_mode = ''; |
|
369 |
SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; |
|
370 |
INSERT INTO ttrig(text) VALUES (@@global.sql_mode); |
|
371 |
INSERT INTO ttrig(text) VALUES (@@local.sql_mode); |
|
372 |
SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; |
|
373 |
SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; |
|
374 |
INSERT INTO ttrig(text) VALUES (@@global.sql_mode); |
|
375 |
INSERT INTO ttrig(text) VALUES (@@local.sql_mode); |
|
376 |
# USER
|
|
377 |
# numeric
|
|
378 |
SET @user_num = 20; |
|
379 |
INSERT INTO ttrig(num) VALUES (@user_num); |
|
380 |
SET @user_num = 30; |
|
381 |
INSERT INTO ttrig(num) VALUES (@user_num); |
|
382 |
# string
|
|
383 |
SET @user_text = 'Bergsbrunna'; |
|
384 |
INSERT INTO ttrig(text) VALUES (@user_text); |
|
385 |
SET @user_text = 'Centrum'; |
|
386 |
INSERT INTO ttrig(text) VALUES (@user_text); |
|
387 |
END| |
|
388 |
INSERT INTO trigger_table VALUES ('bye.'); |
|
389 |
==== Insert variables from a prepared statement ==== |
|
390 |
PREPARE p1 FROM 'SET @@global.relay_log_purge = ON'; |
|
391 |
PREPARE p2 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)'; |
|
392 |
PREPARE p3 FROM 'SET @@global.relay_log_purge = OFF'; |
|
393 |
PREPARE p4 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)'; |
|
394 |
PREPARE p5 FROM 'SET @@global.sync_binlog = 2000000'; |
|
395 |
PREPARE p6 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)'; |
|
396 |
PREPARE p7 FROM 'SET @@global.sync_binlog = 3000000'; |
|
397 |
PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)'; |
|
398 |
PREPARE p9 FROM 'SET @@global.init_slave = \'bison\''; |
|
399 |
PREPARE p10 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)'; |
|
400 |
PREPARE p11 FROM 'SET @@global.init_slave = \'cat\''; |
|
401 |
PREPARE p12 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)'; |
|
402 |
PREPARE p13 FROM 'SET @@global.slave_exec_mode = \'IDEMPOTENT\''; |
|
403 |
PREPARE p14 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)'; |
|
404 |
PREPARE p15 FROM 'SET @@global.slave_exec_mode = \'STRICT\''; |
|
405 |
PREPARE p16 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)'; |
|
406 |
PREPARE p17 FROM 'SET @@sql_big_selects = ON'; |
|
407 |
PREPARE p18 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)'; |
|
408 |
PREPARE p19 FROM 'SET @@sql_big_selects = OFF'; |
|
409 |
PREPARE p20 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)'; |
|
410 |
PREPARE p21 FROM 'SET @@last_insert_id = 20'; |
|
411 |
PREPARE p22 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)'; |
|
412 |
PREPARE p23 FROM 'SET @@last_insert_id = 30'; |
|
413 |
PREPARE p24 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)'; |
|
414 |
PREPARE p25 FROM 'SET @@global.low_priority_updates = ON'; |
|
415 |
PREPARE p26 FROM 'SET @@local.low_priority_updates = OFF'; |
|
416 |
PREPARE p27 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)'; |
|
417 |
PREPARE p28 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)'; |
|
418 |
PREPARE p29 FROM 'SET @@global.low_priority_updates = OFF'; |
|
419 |
PREPARE p30 FROM 'SET @@local.low_priority_updates = ON'; |
|
420 |
PREPARE p31 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)'; |
|
421 |
PREPARE p32 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)'; |
|
422 |
PREPARE p33 FROM 'SET @@global.default_week_format = 3'; |
|
423 |
PREPARE p34 FROM 'SET @@local.default_week_format = 4'; |
|
424 |
PREPARE p35 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)'; |
|
425 |
PREPARE p36 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)'; |
|
426 |
PREPARE p37 FROM 'SET @@global.default_week_format = 5'; |
|
427 |
PREPARE p38 FROM 'SET @@local.default_week_format = 6'; |
|
428 |
PREPARE p39 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)'; |
|
429 |
PREPARE p40 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)'; |
|
430 |
PREPARE p41 FROM 'SET @@global.lc_time_names = \'sv_SE\''; |
|
431 |
PREPARE p42 FROM 'SET @@local.lc_time_names = \'sv_FI\''; |
|
432 |
PREPARE p43 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)'; |
|
433 |
PREPARE p44 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)'; |
|
434 |
PREPARE p45 FROM 'SET @@global.lc_time_names = \'ar_TN\''; |
|
435 |
PREPARE p46 FROM 'SET @@local.lc_time_names = \'ar_IQ\''; |
|
436 |
PREPARE p47 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)'; |
|
437 |
PREPARE p48 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)'; |
|
438 |
PREPARE p49 FROM 'SET @@global.sql_mode = \'\''; |
|
439 |
PREPARE p50 FROM 'SET @@local.sql_mode = \'IGNORE_SPACE,NO_AUTO_CREATE_USER\''; |
|
440 |
PREPARE p51 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)'; |
|
441 |
PREPARE p52 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)'; |
|
442 |
PREPARE p53 FROM 'SET @@global.sql_mode = \'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION\''; |
|
443 |
PREPARE p54 FROM 'SET @@local.sql_mode = \'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS\''; |
|
444 |
PREPARE p55 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)'; |
|
445 |
PREPARE p56 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)'; |
|
446 |
PREPARE p57 FROM 'SET @user_num = 20'; |
|
447 |
PREPARE p58 FROM 'INSERT INTO tprep(num) VALUES (@user_num)'; |
|
448 |
PREPARE p59 FROM 'SET @user_num = 30'; |
|
449 |
PREPARE p60 FROM 'INSERT INTO tprep(num) VALUES (@user_num)'; |
|
450 |
PREPARE p61 FROM 'SET @user_text = \'Bergsbrunna\''; |
|
451 |
PREPARE p62 FROM 'INSERT INTO tprep(text) VALUES (@user_text)'; |
|
452 |
PREPARE p63 FROM 'SET @user_text = \'Centrum\''; |
|
453 |
PREPARE p64 FROM 'INSERT INTO tprep(text) VALUES (@user_text)'; |
|
454 |
EXECUTE p1; |
|
455 |
EXECUTE p2; |
|
456 |
EXECUTE p3; |
|
457 |
EXECUTE p4; |
|
458 |
EXECUTE p5; |
|
459 |
EXECUTE p6; |
|
460 |
EXECUTE p7; |
|
461 |
EXECUTE p8; |
|
462 |
EXECUTE p9; |
|
463 |
EXECUTE p10; |
|
464 |
EXECUTE p11; |
|
465 |
EXECUTE p12; |
|
466 |
EXECUTE p13; |
|
467 |
EXECUTE p14; |
|
468 |
EXECUTE p15; |
|
469 |
EXECUTE p16; |
|
470 |
EXECUTE p17; |
|
471 |
EXECUTE p18; |
|
472 |
EXECUTE p19; |
|
473 |
EXECUTE p20; |
|
474 |
EXECUTE p21; |
|
475 |
EXECUTE p22; |
|
476 |
EXECUTE p23; |
|
477 |
EXECUTE p24; |
|
478 |
EXECUTE p25; |
|
479 |
EXECUTE p26; |
|
480 |
EXECUTE p27; |
|
481 |
EXECUTE p28; |
|
482 |
EXECUTE p29; |
|
483 |
EXECUTE p30; |
|
484 |
EXECUTE p31; |
|
485 |
EXECUTE p32; |
|
486 |
EXECUTE p33; |
|
487 |
EXECUTE p34; |
|
488 |
EXECUTE p35; |
|
489 |
EXECUTE p36; |
|
490 |
EXECUTE p37; |
|
491 |
EXECUTE p38; |
|
492 |
EXECUTE p39; |
|
493 |
EXECUTE p40; |
|
494 |
EXECUTE p41; |
|
495 |
EXECUTE p42; |
|
496 |
EXECUTE p43; |
|
497 |
EXECUTE p44; |
|
498 |
EXECUTE p45; |
|
499 |
EXECUTE p46; |
|
500 |
EXECUTE p47; |
|
501 |
EXECUTE p48; |
|
502 |
EXECUTE p49; |
|
503 |
EXECUTE p50; |
|
504 |
EXECUTE p51; |
|
505 |
EXECUTE p52; |
|
506 |
EXECUTE p53; |
|
507 |
EXECUTE p54; |
|
508 |
EXECUTE p55; |
|
509 |
EXECUTE p56; |
|
510 |
EXECUTE p57; |
|
511 |
EXECUTE p58; |
|
512 |
EXECUTE p59; |
|
513 |
EXECUTE p60; |
|
514 |
EXECUTE p61; |
|
515 |
EXECUTE p62; |
|
516 |
EXECUTE p63; |
|
517 |
EXECUTE p64; |
|
518 |
==== Results ==== |
|
519 |
SELECT * FROM tstmt ORDER BY id; |
|
520 |
id truth num text |
|
521 |
1 1 NULL NULL |
|
522 |
2 0 NULL NULL |
|
523 |
3 NULL 2000000 NULL |
|
524 |
4 NULL 3000000 NULL |
|
525 |
5 NULL NULL bison |
|
526 |
6 NULL NULL cat |
|
527 |
7 NULL NULL IDEMPOTENT |
|
528 |
8 NULL NULL STRICT |
|
529 |
9 1 NULL NULL |
|
530 |
10 0 NULL NULL |
|
531 |
11 NULL 20 NULL |
|
532 |
12 NULL 30 NULL |
|
533 |
13 1 NULL NULL |
|
534 |
14 0 NULL NULL |
|
535 |
15 0 NULL NULL |
|
536 |
16 1 NULL NULL |
|
537 |
17 NULL 3 NULL |
|
538 |
18 NULL 4 NULL |
|
539 |
19 NULL 5 NULL |
|
540 |
20 NULL 6 NULL |
|
541 |
21 NULL NULL sv_SE |
|
542 |
22 NULL NULL sv_FI |
|
543 |
23 NULL NULL ar_TN |
|
544 |
24 NULL NULL ar_IQ |
|
545 |
25 NULL NULL |
|
546 |
26 NULL NULL IGNORE_SPACE,NO_AUTO_CREATE_USER |
|
547 |
27 NULL NULL NO_DIR_IN_CREATE,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_ENGINE_SUBSTITUTION |
|
548 |
28 NULL NULL NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS |
|
549 |
29 NULL 20 NULL |
|
550 |
30 NULL 30 NULL |
|
551 |
31 NULL NULL Bergsbrunna |
|
552 |
32 NULL NULL Centrum |
|
553 |
Comparing tables master:test.tstmt and master:test.tproc |
|
554 |
Comparing tables master:test.tstmt and master:test.tfunc |
|
555 |
Comparing tables master:test.tstmt and master:test.ttrig |
|
556 |
Comparing tables master:test.tstmt and master:test.tprep |
|
557 |
Comparing tables master:test.tstmt and slave:test.tstmt |
|
558 |
Comparing tables master:test.tstmt and slave:test.tproc |
|
559 |
Comparing tables master:test.tstmt and slave:test.tfunc |
|
560 |
Comparing tables master:test.tstmt and slave:test.ttrig |
|
561 |
Comparing tables master:test.tstmt and slave:test.tprep |
|
562 |
==== Clean up ==== |
|
563 |
[on master] |
|
564 |
DROP PROCEDURE proc; |
|
565 |
DROP FUNCTION func; |
|
566 |
DROP TRIGGER trig; |
|
567 |
DROP TABLE tstmt, tproc, tfunc, ttrig, tprep, trigger_table; |
|
568 |
SET @@global.default_week_format= @m_default_week_format; |
|
569 |
SET @@global.init_slave= @m_init_slave; |
|
570 |
SET @@global.lc_time_names= @m_lc_time_names; |
|
571 |
SET @@global.low_priority_updates= @m_low_priority_updates; |
|
572 |
SET @@global.relay_log_purge= @m_relay_log_purge; |
|
573 |
SET @@global.slave_exec_mode= @m_slave_exec_mode; |
|
574 |
SET @@global.sql_mode= @m_sql_mode; |
|
575 |
SET @@global.sync_binlog= @m_sync_binlog; |
|
576 |
[on slave] |
|
577 |
SET @@global.default_week_format= @s_default_week_format; |
|
578 |
SET @@global.init_slave= @s_init_slave; |
|
579 |
SET @@global.lc_time_names= @s_lc_time_names; |
|
580 |
SET @@global.low_priority_updates= @s_low_priority_updates; |
|
581 |
SET @@global.relay_log_purge= @s_relay_log_purge; |
|
582 |
SET @@global.slave_exec_mode= @s_slave_exec_mode; |
|
583 |
SET @@global.sql_mode= @s_sql_mode; |
|
584 |
SET @@global.sync_binlog= @s_sync_binlog; |