Merge lp:~pomyks/maria/10.0-per-query-variables into lp:maria
- 10.0-per-query-variables
- Merge into 10.0
Proposed by
Patryk Pomykalski
Status: | Needs review |
---|---|
Proposed branch: | lp:~pomyks/maria/10.0-per-query-variables |
Merge into: | lp:maria |
Diff against target: |
2265 lines (+2080/-16) 13 files modified
mysql-test/suite/percona/percona_rpl_set_statement_variable_test.inc (+31/-0) mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.result (+140/-0) mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.test (+57/-0) mysql-test/suite/percona/percona_statement_set.result (+862/-0) mysql-test/suite/percona/percona_statement_set.test (+830/-0) sql/lex.h (+1/-0) sql/set_var.h (+3/-0) sql/sql_lex.cc (+1/-0) sql/sql_lex.h (+2/-0) sql/sql_parse.cc (+46/-0) sql/sql_plugin.cc (+42/-0) sql/sql_plugin.h (+8/-0) sql/sql_yacc.yy (+57/-16) |
To merge this branch: | bzr merge lp:~pomyks/maria/10.0-per-query-variables |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Maria-captains | Pending | ||
Review via email:
|
Commit message
Description of the change
To post a comment you must log in.
Unmerged revisions
- 3880. By Patryk Pomykalski
-
Port of per query variables feature from Percona Server.
Allows changing variables for duration of the query.
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | === added file 'mysql-test/suite/percona/percona_rpl_set_statement_variable_test.inc' |
2 | --- mysql-test/suite/percona/percona_rpl_set_statement_variable_test.inc 1970-01-01 00:00:00 +0000 |
3 | +++ mysql-test/suite/percona/percona_rpl_set_statement_variable_test.inc 2013-11-06 17:03:07 +0000 |
4 | @@ -0,0 +1,31 @@ |
5 | +#Check if the variable is replicated correctly with "SET STATEMENT" |
6 | +# Usage: |
7 | +# $rpl_ssvt_var_name - the name of tested variable; |
8 | +# $rpl_ssvt_var_value - the value to set; |
9 | +# $rpl_ssvt_table - the table name to insert values. |
10 | + |
11 | +--connection master |
12 | +--echo [connection master] |
13 | +eval SELECT @@$rpl_ssvt_var_name; |
14 | + |
15 | +--connection slave |
16 | +--echo [connection slave] |
17 | +eval SELECT @@$rpl_ssvt_var_name; |
18 | + |
19 | +--connection master |
20 | +--echo [connection master] |
21 | +--disable_result_log |
22 | +eval SET STATEMENT $rpl_ssvt_var_name=$rpl_ssvt_var_value FOR |
23 | + INSERT INTO $rpl_ssvt_table VALUES(@@$rpl_ssvt_var_name); |
24 | +--enable_result_log |
25 | +eval SELECT @@$rpl_ssvt_var_name; |
26 | + |
27 | +--sync_slave_with_master |
28 | +--echo [connection slave] |
29 | +eval SELECT * FROM $rpl_ssvt_table; |
30 | +eval SELECT @@$rpl_ssvt_var_name; |
31 | + |
32 | +--connection master |
33 | +--echo [connection master] |
34 | +eval DELETE FROM $rpl_ssvt_table; |
35 | + |
36 | |
37 | === added file 'mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.result' |
38 | --- mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.result 1970-01-01 00:00:00 +0000 |
39 | +++ mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.result 2013-11-06 17:03:07 +0000 |
40 | @@ -0,0 +1,140 @@ |
41 | +include/master-slave.inc |
42 | +[connection master] |
43 | +DROP TABLE IF EXISTS t1; |
44 | +DROP TABLE IF EXISTS t2; |
45 | +call mtr.add_suppression("Unsafe statement written to the binary log*"); |
46 | +CREATE TABLE t1 (a bigint unsigned not null); |
47 | +CREATE TABLE t2 (a char(255) not null); |
48 | + |
49 | +There are the following types of variables: |
50 | +1) variables that are NOT replicated correctly when using STATEMENT mode; |
51 | + |
52 | +[connection master] |
53 | +SELECT @@max_join_size; |
54 | +@@max_join_size |
55 | +18446744073709551615 |
56 | +[connection slave] |
57 | +SELECT @@max_join_size; |
58 | +@@max_join_size |
59 | +18446744073709551615 |
60 | +[connection master] |
61 | +SET STATEMENT max_join_size=2 FOR |
62 | +INSERT INTO t1 VALUES(@@max_join_size); |
63 | +SELECT @@max_join_size; |
64 | +@@max_join_size |
65 | +18446744073709551615 |
66 | +[connection slave] |
67 | +SELECT * FROM t1; |
68 | +a |
69 | +18446744073709551615 |
70 | +SELECT @@max_join_size; |
71 | +@@max_join_size |
72 | +18446744073709551615 |
73 | +[connection master] |
74 | +DELETE FROM t1; |
75 | + |
76 | +2) variables thar ARE replicated correctly |
77 | +They must be replicated correctly with "SET STATEMENT" too. |
78 | + |
79 | +[connection master] |
80 | +SELECT @@auto_increment_increment; |
81 | +@@auto_increment_increment |
82 | +1 |
83 | +[connection slave] |
84 | +SELECT @@auto_increment_increment; |
85 | +@@auto_increment_increment |
86 | +1 |
87 | +[connection master] |
88 | +SET STATEMENT auto_increment_increment=10 FOR |
89 | +INSERT INTO t1 VALUES(@@auto_increment_increment); |
90 | +SELECT @@auto_increment_increment; |
91 | +@@auto_increment_increment |
92 | +1 |
93 | +[connection slave] |
94 | +SELECT * FROM t1; |
95 | +a |
96 | +10 |
97 | +SELECT @@auto_increment_increment; |
98 | +@@auto_increment_increment |
99 | +1 |
100 | +[connection master] |
101 | +DELETE FROM t1; |
102 | + |
103 | +3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value; |
104 | + |
105 | +[connection master] |
106 | +SELECT @@sql_mode; |
107 | +@@sql_mode |
108 | + |
109 | +[connection slave] |
110 | +SELECT @@sql_mode; |
111 | +@@sql_mode |
112 | + |
113 | +[connection master] |
114 | +SET STATEMENT sql_mode='ERROR_FOR_DIVISION_BY_ZERO' FOR |
115 | +INSERT INTO t2 VALUES(@@sql_mode); |
116 | +SELECT @@sql_mode; |
117 | +@@sql_mode |
118 | + |
119 | +[connection slave] |
120 | +SELECT * FROM t2; |
121 | +a |
122 | +ERROR_FOR_DIVISION_BY_ZERO |
123 | +SELECT @@sql_mode; |
124 | +@@sql_mode |
125 | + |
126 | +[connection master] |
127 | +DELETE FROM t2; |
128 | +[connection master] |
129 | +SELECT @@sql_mode; |
130 | +@@sql_mode |
131 | + |
132 | +[connection slave] |
133 | +SELECT @@sql_mode; |
134 | +@@sql_mode |
135 | + |
136 | +[connection master] |
137 | +SET STATEMENT sql_mode='NO_DIR_IN_CREATE' FOR |
138 | +INSERT INTO t2 VALUES(@@sql_mode); |
139 | +SELECT @@sql_mode; |
140 | +@@sql_mode |
141 | + |
142 | +[connection slave] |
143 | +SELECT * FROM t2; |
144 | +a |
145 | + |
146 | +SELECT @@sql_mode; |
147 | +@@sql_mode |
148 | + |
149 | +[connection master] |
150 | +DELETE FROM t2; |
151 | + |
152 | +4) variables that are not replicated at all: |
153 | +default_storage_engine, storage_engine, max_heap_table_size |
154 | + |
155 | +[connection master] |
156 | +SELECT @@max_heap_table_size; |
157 | +@@max_heap_table_size |
158 | +1048576 |
159 | +[connection slave] |
160 | +SELECT @@max_heap_table_size; |
161 | +@@max_heap_table_size |
162 | +1048576 |
163 | +[connection master] |
164 | +SET STATEMENT max_heap_table_size=16384 FOR |
165 | +INSERT INTO t1 VALUES(@@max_heap_table_size); |
166 | +SELECT @@max_heap_table_size; |
167 | +@@max_heap_table_size |
168 | +1048576 |
169 | +[connection slave] |
170 | +SELECT * FROM t1; |
171 | +a |
172 | +1048576 |
173 | +SELECT @@max_heap_table_size; |
174 | +@@max_heap_table_size |
175 | +1048576 |
176 | +[connection master] |
177 | +DELETE FROM t1; |
178 | +DROP TABLE t1; |
179 | +DROP TABLE t2; |
180 | +include/stop_slave.inc |
181 | |
182 | === added file 'mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.test' |
183 | --- mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.test 1970-01-01 00:00:00 +0000 |
184 | +++ mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.test 2013-11-06 17:03:07 +0000 |
185 | @@ -0,0 +1,57 @@ |
186 | +--source include/master-slave.inc |
187 | +--source include/have_binlog_format_statement.inc |
188 | + |
189 | +--disable_warnings |
190 | +DROP TABLE IF EXISTS t1; |
191 | +DROP TABLE IF EXISTS t2; |
192 | +--enable_warnings |
193 | + |
194 | +call mtr.add_suppression("Unsafe statement written to the binary log*"); |
195 | +CREATE TABLE t1 (a bigint unsigned not null); |
196 | +CREATE TABLE t2 (a char(255) not null); |
197 | + |
198 | +--echo |
199 | +--echo There are the following types of variables: |
200 | +--echo 1) variables that are NOT replicated correctly when using STATEMENT mode; |
201 | +--echo |
202 | + |
203 | +--let $rpl_ssvt_var_name=max_join_size |
204 | +--let $rpl_ssvt_var_value=2 |
205 | +--let $rpl_ssvt_table=t1 |
206 | +--source suite/percona/percona_rpl_set_statement_variable_test.inc |
207 | + |
208 | +--echo |
209 | +--echo 2) variables thar ARE replicated correctly |
210 | +--echo They must be replicated correctly with "SET STATEMENT" too. |
211 | +--echo |
212 | +--let $rpl_ssvt_var_name=auto_increment_increment |
213 | +--let $rpl_ssvt_var_value=10 |
214 | +--let $rpl_ssvt_table=t1 |
215 | +--source suite/percona/percona_rpl_set_statement_variable_test.inc |
216 | + |
217 | +--echo |
218 | +--echo 3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value; |
219 | +--echo |
220 | +--let $rpl_ssvt_var_name=sql_mode |
221 | +--let $rpl_ssvt_var_value='ERROR_FOR_DIVISION_BY_ZERO' |
222 | +--let $rpl_ssvt_table=t2 |
223 | +--source suite/percona/percona_rpl_set_statement_variable_test.inc |
224 | +--let $rpl_ssvt_var_name=sql_mode |
225 | +--let $rpl_ssvt_var_value='NO_DIR_IN_CREATE' |
226 | +--let $rpl_ssvt_table=t2 |
227 | +--source suite/percona/percona_rpl_set_statement_variable_test.inc |
228 | + |
229 | +--echo |
230 | +--echo 4) variables that are not replicated at all: |
231 | +--echo default_storage_engine, storage_engine, max_heap_table_size |
232 | +--echo |
233 | +--let $rpl_ssvt_var_name=max_heap_table_size |
234 | +--let $rpl_ssvt_var_value=16384 |
235 | +--let $rpl_ssvt_table=t1 |
236 | +--source suite/percona/percona_rpl_set_statement_variable_test.inc |
237 | + |
238 | +connection master; |
239 | +DROP TABLE t1; |
240 | +DROP TABLE t2; |
241 | +sync_slave_with_master; |
242 | +source include/stop_slave.inc; |
243 | |
244 | === added file 'mysql-test/suite/percona/percona_statement_set.result' |
245 | --- mysql-test/suite/percona/percona_statement_set.result 1970-01-01 00:00:00 +0000 |
246 | +++ mysql-test/suite/percona/percona_statement_set.result 2013-11-06 17:03:07 +0000 |
247 | @@ -0,0 +1,862 @@ |
248 | +'# SET STATEMENT ..... FOR .... TEST' |
249 | +DROP TABLE IF EXISTS t1; |
250 | +DROP FUNCTION IF EXISTS myProc; |
251 | +DROP PROCEDURE IF EXISTS p1; |
252 | +DROP PROCEDURE IF EXISTS p2; |
253 | +DROP PROCEDURE IF EXISTS p3; |
254 | +DROP PROCEDURE IF EXISTS p4; |
255 | +DROP PROCEDURE IF EXISTS p5; |
256 | +DROP TABLE IF EXISTS STATEMENT; |
257 | +'# Setup database' |
258 | +CREATE TABLE t1 (v1 INT, v2 INT); |
259 | +INSERT INTO t1 VALUES (1,2); |
260 | +INSERT INTO t1 VALUES (3,4); |
261 | +'' |
262 | +'#------------------ STATEMENT Test 1 -----------------------#' |
263 | +'# Initialize variables to known setting' |
264 | +SET SESSION sort_buffer_size=100000; |
265 | +'' |
266 | +'# Pre-STATEMENT variable value' |
267 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
268 | +Variable_name Value |
269 | +sort_buffer_size 100000 |
270 | +SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1; |
271 | +v1 v2 |
272 | +1 2 |
273 | +3 4 |
274 | +'' |
275 | +'# Post-STATEMENT variable value' |
276 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
277 | +Variable_name Value |
278 | +sort_buffer_size 100000 |
279 | +'' |
280 | +'#------------------ STATEMENT Test 2 -----------------------#' |
281 | +'# Initialize variables to known setting' |
282 | +SET SESSION binlog_format=mixed; |
283 | +SET SESSION sort_buffer_size=100000; |
284 | +'# Pre-STATEMENT variable value' |
285 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
286 | +Variable_name Value |
287 | +sort_buffer_size 100000 |
288 | +SHOW SESSION VARIABLES LIKE 'binlog_format'; |
289 | +Variable_name Value |
290 | +binlog_format MIXED |
291 | +SET STATEMENT sort_buffer_size=150000, binlog_format=row |
292 | +FOR SELECT * FROM t1; |
293 | +v1 v2 |
294 | +1 2 |
295 | +3 4 |
296 | +'# Post-STATEMENT variable value' |
297 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
298 | +Variable_name Value |
299 | +sort_buffer_size 100000 |
300 | +SHOW SESSION VARIABLES LIKE 'binlog_format'; |
301 | +Variable_name Value |
302 | +binlog_format MIXED |
303 | +'' |
304 | +'#------------------ STATEMENT Test 3 -----------------------#' |
305 | +'# set initial variable value, make prepared statement |
306 | +SET SESSION binlog_format=row; |
307 | +PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1'; |
308 | +'' |
309 | +'# Change variable setting' |
310 | +SET SESSION binlog_format=mixed; |
311 | +'' |
312 | +'# Pre-STATEMENT variable value' |
313 | +'' |
314 | +SHOW SESSION VARIABLES LIKE 'binlog_format'; |
315 | +Variable_name Value |
316 | +binlog_format MIXED |
317 | +'' |
318 | +EXECUTE stmt1; |
319 | +v1 v2 |
320 | +1 2 |
321 | +3 4 |
322 | +'' |
323 | +'# Post-STATEMENT variable value' |
324 | +SHOW SESSION VARIABLES LIKE 'binlog_format'; |
325 | +Variable_name Value |
326 | +binlog_format MIXED |
327 | +'' |
328 | +DEALLOCATE PREPARE stmt1; |
329 | +'#------------------ STATEMENT Test 4 -----------------------#' |
330 | +'# set initial variable value, make prepared statement |
331 | +SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1; |
332 | +'' |
333 | +'# Pre-STATEMENT variable value' |
334 | +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; |
335 | +Variable_name Value |
336 | +myisam_sort_buffer_size 500000 |
337 | +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
338 | +Variable_name Value |
339 | +myisam_repair_threads 1 |
340 | +'' |
341 | +SET STATEMENT myisam_sort_buffer_size=800000, |
342 | +myisam_repair_threads=2 FOR OPTIMIZE TABLE t1; |
343 | +Table Op Msg_type Msg_text |
344 | +test.t1 optimize status OK |
345 | +'' |
346 | +'# Post-STATEMENT variable value' |
347 | +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; |
348 | +Variable_name Value |
349 | +myisam_sort_buffer_size 500000 |
350 | +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
351 | +Variable_name Value |
352 | +myisam_repair_threads 1 |
353 | +'' |
354 | +'#------------------ STATEMENT Test 5 -----------------------#' |
355 | +'# Initialize variables to known setting' |
356 | +SET SESSION sort_buffer_size=100000; |
357 | +'' |
358 | +'# Pre-STATEMENT variable value' |
359 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
360 | +Variable_name Value |
361 | +sort_buffer_size 100000 |
362 | +'' |
363 | +SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2; |
364 | +ERROR 42S02: Table 'test.t2' doesn't exist |
365 | +'' |
366 | +'# Post-STATEMENT variable value' |
367 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
368 | +Variable_name Value |
369 | +sort_buffer_size 100000 |
370 | +'' |
371 | +'#------------------ STATEMENT Test 6 -----------------------#' |
372 | +'# Initialize variables to known setting' |
373 | +SET SESSION keep_files_on_create=ON; |
374 | +'' |
375 | +'# Pre-STATEMENT variable value' |
376 | +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
377 | +Variable_name Value |
378 | +keep_files_on_create ON |
379 | +'' |
380 | +SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1; |
381 | +v1 v2 |
382 | +1 2 |
383 | +3 4 |
384 | +'' |
385 | +'# Post-STATEMENT variable value' |
386 | +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
387 | +Variable_name Value |
388 | +keep_files_on_create ON |
389 | +'' |
390 | +'#------------------ STATEMENT Test 7 -----------------------#' |
391 | +'# Initialize variables to known setting' |
392 | +SET SESSION max_join_size=2222220000000; |
393 | +'' |
394 | +'# Pre-STATEMENT variable value' |
395 | +SHOW SESSION VARIABLES LIKE 'max_join_size'; |
396 | +Variable_name Value |
397 | +max_join_size 2222220000000 |
398 | +'' |
399 | +SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1; |
400 | +v1 v2 |
401 | +1 2 |
402 | +3 4 |
403 | +'' |
404 | +'# Post-STATEMENT variable value' |
405 | +SHOW SESSION VARIABLES LIKE 'max_join_size'; |
406 | +Variable_name Value |
407 | +max_join_size 2222220000000 |
408 | +'' |
409 | +'#------------------Test 8-----------------------#' |
410 | +'# Initialize test variables' |
411 | +SET SESSION myisam_sort_buffer_size=500000, |
412 | +myisam_repair_threads=1, |
413 | +sort_buffer_size = 200000, |
414 | +max_join_size=2222220000000, |
415 | +keep_files_on_create=ON; |
416 | +'' |
417 | +'# LONG ' |
418 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
419 | +Variable_name Value |
420 | +sort_buffer_size 200000 |
421 | +SET STATEMENT sort_buffer_size = 100000 |
422 | +FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
423 | +Variable_name Value |
424 | +sort_buffer_size 100000 |
425 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
426 | +Variable_name Value |
427 | +sort_buffer_size 200000 |
428 | +'' |
429 | +'# MY_BOOL ' |
430 | +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
431 | +Variable_name Value |
432 | +keep_files_on_create ON |
433 | +SET STATEMENT keep_files_on_create=OFF |
434 | +FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
435 | +Variable_name Value |
436 | +keep_files_on_create OFF |
437 | +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
438 | +Variable_name Value |
439 | +keep_files_on_create ON |
440 | +'' |
441 | +'# INT/LONG ' |
442 | +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
443 | +Variable_name Value |
444 | +myisam_repair_threads 1 |
445 | +SET STATEMENT myisam_repair_threads=2 |
446 | +FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
447 | +Variable_name Value |
448 | +myisam_repair_threads 2 |
449 | +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
450 | +Variable_name Value |
451 | +myisam_repair_threads 1 |
452 | +'' |
453 | +'# ULONGLONG ' |
454 | +SHOW SESSION VARIABLES LIKE 'max_join_size'; |
455 | +Variable_name Value |
456 | +max_join_size 2222220000000 |
457 | +SET STATEMENT max_join_size=2000000000000 |
458 | +FOR SHOW SESSION VARIABLES LIKE 'max_join_size'; |
459 | +Variable_name Value |
460 | +max_join_size 2000000000000 |
461 | +SHOW SESSION VARIABLES LIKE 'max_join_size'; |
462 | +Variable_name Value |
463 | +max_join_size 2222220000000 |
464 | +'' |
465 | +'#------------------Test 9-----------------------#' |
466 | +'# set initial variable values |
467 | +SET SESSION myisam_sort_buffer_size=500000, |
468 | +myisam_repair_threads=1, |
469 | +sort_buffer_size=100000, |
470 | +binlog_format=mixed, |
471 | +keep_files_on_create=ON, |
472 | +max_join_size=2222220000000; |
473 | +'' |
474 | +'' |
475 | +'# Pre-STATEMENT variable value |
476 | +SELECT @@myisam_sort_buffer_size, |
477 | +@@myisam_repair_threads, |
478 | +@@sort_buffer_size, |
479 | +@@binlog_format, |
480 | +@@keep_files_on_create, |
481 | +@@max_join_size; |
482 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
483 | +500000 1 100000 MIXED 1 2222220000000 |
484 | +'' |
485 | +'' |
486 | +CREATE FUNCTION myProc (cost DECIMAL(10,2)) |
487 | +RETURNS DECIMAL(10,2) |
488 | +SQL SECURITY DEFINER |
489 | +tax: BEGIN |
490 | +DECLARE order_tax DECIMAL(10,2); |
491 | +SET order_tax = cost * .05; |
492 | +RETURN order_tax; |
493 | +END| |
494 | +'' |
495 | +'# During Execution values |
496 | +SET STATEMENT myisam_sort_buffer_size=400000, |
497 | +myisam_repair_threads=2, |
498 | +sort_buffer_size=200000, |
499 | +binlog_format=row, |
500 | +keep_files_on_create=OFF, |
501 | +max_join_size=4444440000000 FOR |
502 | +SELECT myProc(123.45); |
503 | +myProc(123.45) |
504 | +6.17 |
505 | +'' |
506 | +'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2' |
507 | +SELECT @@myisam_sort_buffer_size, |
508 | +@@myisam_repair_threads, |
509 | +@@sort_buffer_size, |
510 | +@@binlog_format, |
511 | +@@keep_files_on_create, |
512 | +@@max_join_size; |
513 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
514 | +500000 1 100000 MIXED 1 2222220000000 |
515 | +'' |
516 | +SET STATEMENT myisam_sort_buffer_size=400000, |
517 | +myisam_repair_threads=2, |
518 | +sort_buffer_size=200000, |
519 | +binlog_format=row, |
520 | +keep_files_on_create=OFF, |
521 | +max_join_size=4444440000000 FOR |
522 | +DROP FUNCTION myProc; |
523 | +'' |
524 | +'# Post-STATEMENT No 2 variable value |
525 | +SELECT @@myisam_sort_buffer_size, |
526 | +@@myisam_repair_threads, |
527 | +@@sort_buffer_size, |
528 | +@@binlog_format, |
529 | +@@keep_files_on_create, |
530 | +@@max_join_size; |
531 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
532 | +500000 1 100000 MIXED 1 2222220000000 |
533 | +'' |
534 | +'#------------------Test 10-----------------------#' |
535 | +'# set initial variable values |
536 | +SET SESSION myisam_sort_buffer_size=500000, |
537 | +myisam_repair_threads=1, |
538 | +sort_buffer_size=100000, |
539 | +binlog_format=mixed, |
540 | +keep_files_on_create=ON, |
541 | +max_join_size=2222220000000; |
542 | +'' |
543 | +'# Pre-STATEMENT variable value |
544 | +SELECT @@myisam_sort_buffer_size, |
545 | +@@myisam_repair_threads, |
546 | +@@sort_buffer_size, |
547 | +@@binlog_format, |
548 | +@@keep_files_on_create, |
549 | +@@max_join_size; |
550 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
551 | +500000 1 100000 MIXED 1 2222220000000 |
552 | +'' |
553 | +'' |
554 | +SET STATEMENT myisam_sort_buffer_size=400000, |
555 | +myisam_repair_threads=2, |
556 | +sort_buffer_size=200000, |
557 | +binlog_format=row, |
558 | +keep_files_on_create=OFF, |
559 | +max_join_size=4444440000000 FOR |
560 | +PREPARE stmt2 |
561 | +FROM 'SELECT * FROM t1'; |
562 | +'' |
563 | +'Test No 1 Post Value & Test 2 Pre values' |
564 | +SELECT @@myisam_sort_buffer_size, |
565 | +@@myisam_repair_threads, |
566 | +@@sort_buffer_size, |
567 | +@@binlog_format, |
568 | +@@keep_files_on_create, |
569 | +@@max_join_size; |
570 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
571 | +500000 1 100000 MIXED 1 2222220000000 |
572 | +'' |
573 | +'' |
574 | +SET STATEMENT myisam_sort_buffer_size=400000, |
575 | +myisam_repair_threads=2, |
576 | +sort_buffer_size=200000, |
577 | +binlog_format=row, |
578 | +keep_files_on_create=OFF, |
579 | +max_join_size=4444440000000 FOR |
580 | +EXECUTE stmt2; |
581 | +v1 v2 |
582 | +1 2 |
583 | +3 4 |
584 | +'' |
585 | +'# Post-STATEMENT No 2 |
586 | +SELECT @@myisam_sort_buffer_size, |
587 | +@@myisam_repair_threads, |
588 | +@@sort_buffer_size, |
589 | +@@binlog_format, |
590 | +@@keep_files_on_create, |
591 | +@@max_join_size; |
592 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
593 | +500000 1 100000 MIXED 1 2222220000000 |
594 | +'' |
595 | +DEALLOCATE PREPARE stmt2; |
596 | +'' |
597 | +'#------------------Test 11-----------------------#' |
598 | +'# set initial variable values |
599 | +SET SESSION myisam_sort_buffer_size=500000, |
600 | +myisam_repair_threads=1, |
601 | +sort_buffer_size=100000, |
602 | +binlog_format=mixed, |
603 | +keep_files_on_create=ON, |
604 | +max_join_size=2222220000000; |
605 | +'' |
606 | +'' |
607 | +'# Pre-STATEMENT variable value |
608 | +SELECT @@myisam_sort_buffer_size, |
609 | +@@myisam_repair_threads, |
610 | +@@sort_buffer_size, |
611 | +@@binlog_format, |
612 | +@@keep_files_on_create, |
613 | +@@max_join_size; |
614 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
615 | +500000 1 100000 MIXED 1 2222220000000 |
616 | +'' |
617 | +'' |
618 | +SET STATEMENT myisam_sort_buffer_size=400000, |
619 | +myisam_repair_threads=2, |
620 | +sort_buffer_size=200000, |
621 | +keep_files_on_create=OFF, |
622 | +max_join_size=4444440000000 FOR |
623 | +PREPARE stmt1 FROM |
624 | +'SET STATEMENT binlog_format=row FOR SELECT * FROM t1'; |
625 | +'' |
626 | +'Test No 1 Post Value & Test 2 Pre values' |
627 | +SELECT @@myisam_sort_buffer_size, |
628 | +@@myisam_repair_threads, |
629 | +@@sort_buffer_size, |
630 | +@@binlog_format, |
631 | +@@keep_files_on_create, |
632 | +@@max_join_size; |
633 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
634 | +500000 1 100000 MIXED 1 2222220000000 |
635 | +'' |
636 | +'' |
637 | +SET STATEMENT myisam_sort_buffer_size=400000, |
638 | +myisam_repair_threads=2, |
639 | +sort_buffer_size=200000, |
640 | +keep_files_on_create=OFF, |
641 | +max_join_size=4444440000000 FOR |
642 | +EXECUTE stmt1; |
643 | +v1 v2 |
644 | +1 2 |
645 | +3 4 |
646 | +'' |
647 | +'# Post-STATEMENT No 2 |
648 | +SELECT @@myisam_sort_buffer_size, |
649 | +@@myisam_repair_threads, |
650 | +@@sort_buffer_size, |
651 | +@@binlog_format, |
652 | +@@keep_files_on_create, |
653 | +@@max_join_size; |
654 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
655 | +500000 1 100000 MIXED 1 2222220000000 |
656 | +'' |
657 | +'' |
658 | +'#------------------Test 12-----------------------#' |
659 | +'# set initial variable values |
660 | +SET SESSION myisam_sort_buffer_size=500000, |
661 | +myisam_repair_threads=1, |
662 | +sort_buffer_size=100000, |
663 | +binlog_format=mixed, |
664 | +keep_files_on_create=ON, |
665 | +max_join_size=2222220000000; |
666 | +'' |
667 | +'' |
668 | +'# Pre-STATEMENT variable value |
669 | +SELECT @@myisam_sort_buffer_size, |
670 | +@@myisam_repair_threads, |
671 | +@@sort_buffer_size, |
672 | +@@binlog_format, |
673 | +@@keep_files_on_create, |
674 | +@@max_join_size; |
675 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
676 | +500000 1 100000 MIXED 1 2222220000000 |
677 | +'' |
678 | +'' |
679 | +SET STATEMENT myisam_sort_buffer_size=400000, |
680 | +myisam_repair_threads=2, |
681 | +sort_buffer_size=200000, |
682 | +binlog_format=row, |
683 | +keep_files_on_create=OFF, |
684 | +max_join_size=4444440000000 FOR |
685 | +CREATE PROCEDURE p1() BEGIN |
686 | +SELECT @@myisam_sort_buffer_size, |
687 | +@@myisam_repair_threads, |
688 | +@@sort_buffer_size, |
689 | +@@binlog_format, |
690 | +@@keep_files_on_create, |
691 | +@@max_join_size; |
692 | +END| |
693 | +'' |
694 | +'Test No 1 Post Value & Test 2 Pre values' |
695 | +SELECT @@myisam_sort_buffer_size, |
696 | +@@myisam_repair_threads, |
697 | +@@sort_buffer_size, |
698 | +@@binlog_format, |
699 | +@@keep_files_on_create, |
700 | +@@max_join_size; |
701 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
702 | +500000 1 100000 MIXED 1 2222220000000 |
703 | +'' |
704 | +'' |
705 | +SET STATEMENT myisam_sort_buffer_size=400000, |
706 | +myisam_repair_threads=2, |
707 | +sort_buffer_size=200000, |
708 | +binlog_format=row, |
709 | +keep_files_on_create=OFF, |
710 | +max_join_size=4444440000000 FOR |
711 | +CALL p1(); |
712 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
713 | +400000 2 200000 ROW 0 4444440000000 |
714 | +'' |
715 | +'# Post-STATEMENT No 2 |
716 | +SELECT @@myisam_sort_buffer_size, |
717 | +@@myisam_repair_threads, |
718 | +@@sort_buffer_size, |
719 | +@@binlog_format, |
720 | +@@keep_files_on_create, |
721 | +@@max_join_size; |
722 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
723 | +500000 1 100000 MIXED 1 2222220000000 |
724 | +'' |
725 | +'' |
726 | +'#------------------Test 13-----------------------#' |
727 | +'# set initial variable values |
728 | +SET SESSION myisam_sort_buffer_size=500000, |
729 | +myisam_repair_threads=1, |
730 | +sort_buffer_size=100000, |
731 | +binlog_format=mixed, |
732 | +keep_files_on_create=ON, |
733 | +max_join_size=2222220000000; |
734 | +'' |
735 | +'' |
736 | +CREATE PROCEDURE p2() BEGIN |
737 | +SET STATEMENT myisam_sort_buffer_size=400000, |
738 | +myisam_repair_threads=3, |
739 | +sort_buffer_size=300000, |
740 | +binlog_format=mixed, |
741 | +keep_files_on_create=OFF, |
742 | +max_join_size=3333330000000 FOR |
743 | +CALL p1(); |
744 | +END| |
745 | +'' |
746 | +'# Pre-STATEMENT variable value |
747 | +SELECT @@myisam_sort_buffer_size, |
748 | +@@myisam_repair_threads, |
749 | +@@sort_buffer_size, |
750 | +@@binlog_format, |
751 | +@@keep_files_on_create, |
752 | +@@max_join_size; |
753 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
754 | +500000 1 100000 MIXED 1 2222220000000 |
755 | +'' |
756 | +'' |
757 | +SET STATEMENT myisam_sort_buffer_size=400000, |
758 | +myisam_repair_threads=2, |
759 | +sort_buffer_size=200000, |
760 | +binlog_format=row, |
761 | +keep_files_on_create=OFF, |
762 | +max_join_size=4444440000000 FOR |
763 | +CALL p2(); |
764 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
765 | +400000 3 300000 MIXED 0 3333330000000 |
766 | +'' |
767 | +'# Post-STATEMENT |
768 | +SELECT @@myisam_sort_buffer_size, |
769 | +@@myisam_repair_threads, |
770 | +@@sort_buffer_size, |
771 | +@@binlog_format, |
772 | +@@keep_files_on_create, |
773 | +@@max_join_size; |
774 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
775 | +500000 1 100000 MIXED 1 2222220000000 |
776 | +'' |
777 | +'' |
778 | +'#------------------Test 14-----------------------#' |
779 | +'# set initial variable values |
780 | +SET SESSION myisam_sort_buffer_size=500000, |
781 | +myisam_repair_threads=1, |
782 | +sort_buffer_size=100000, |
783 | +binlog_format=mixed, |
784 | +keep_files_on_create=ON, |
785 | +max_join_size=2222220000000; |
786 | +'' |
787 | +'' |
788 | +CREATE PROCEDURE p3() BEGIN |
789 | +SELECT @@myisam_sort_buffer_size, |
790 | +@@myisam_repair_threads, |
791 | +@@sort_buffer_size, |
792 | +@@binlog_format, |
793 | +@@keep_files_on_create, |
794 | +@@max_join_size; |
795 | +SET STATEMENT myisam_sort_buffer_size=320000, |
796 | +myisam_repair_threads=2, |
797 | +sort_buffer_size=220022, |
798 | +binlog_format=row, |
799 | +keep_files_on_create=ON, |
800 | +max_join_size=2222220000000 FOR |
801 | +CALL p2(); |
802 | +END| |
803 | +'' |
804 | +'# Pre-STATEMENT variable value |
805 | +SELECT @@myisam_sort_buffer_size, |
806 | +@@myisam_repair_threads, |
807 | +@@sort_buffer_size, |
808 | +@@binlog_format, |
809 | +@@keep_files_on_create, |
810 | +@@max_join_size; |
811 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
812 | +500000 1 100000 MIXED 1 2222220000000 |
813 | +'' |
814 | +'' |
815 | +SET STATEMENT myisam_sort_buffer_size=400000, |
816 | +myisam_repair_threads=2, |
817 | +sort_buffer_size=200000, |
818 | +binlog_format=row, |
819 | +keep_files_on_create=OFF, |
820 | +max_join_size=4444440000000 FOR |
821 | +CALL p3(); |
822 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
823 | +400000 2 200000 ROW 0 4444440000000 |
824 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
825 | +400000 3 300000 MIXED 0 3333330000000 |
826 | +'' |
827 | +'# Post-STATEMENT |
828 | +SELECT @@myisam_sort_buffer_size, |
829 | +@@myisam_repair_threads, |
830 | +@@sort_buffer_size, |
831 | +@@binlog_format, |
832 | +@@keep_files_on_create, |
833 | +@@max_join_size; |
834 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
835 | +500000 1 100000 MIXED 1 2222220000000 |
836 | +'' |
837 | +'' |
838 | +'' |
839 | +'' |
840 | +'#------------------Test 15-----------------------#' |
841 | +'# set initial variable values |
842 | +SET SESSION myisam_sort_buffer_size=500000, |
843 | +myisam_repair_threads=1, |
844 | +sort_buffer_size=100000, |
845 | +binlog_format=mixed, |
846 | +keep_files_on_create=ON, |
847 | +max_join_size=2222220000000; |
848 | +'' |
849 | +'' |
850 | +CREATE PROCEDURE p4() BEGIN |
851 | +SELECT @@myisam_sort_buffer_size, |
852 | +@@myisam_repair_threads, |
853 | +@@sort_buffer_size, |
854 | +@@binlog_format, |
855 | +@@keep_files_on_create, |
856 | +@@max_join_size; |
857 | +SET STATEMENT myisam_sort_buffer_size=320000, |
858 | +myisam_repair_threads=2, |
859 | +sort_buffer_size=220022, |
860 | +binlog_format=row, |
861 | +keep_files_on_create=ON, |
862 | +max_join_size=2222220000000 FOR |
863 | +SELECT @@myisam_sort_buffer_size, |
864 | +@@myisam_repair_threads, |
865 | +@@sort_buffer_size, |
866 | +@@binlog_format, |
867 | +@@keep_files_on_create, |
868 | +@@max_join_size; |
869 | +SET STATEMENT myisam_sort_buffer_size=320000, |
870 | +myisam_repair_threads=2, |
871 | +sort_buffer_size=220022, |
872 | +binlog_format=row, |
873 | +keep_files_on_create=ON, |
874 | +max_join_size=2222220000000 FOR |
875 | +SELECT @@myisam_sort_buffer_size, |
876 | +@@myisam_repair_threads, |
877 | +@@sort_buffer_size, |
878 | +@@binlog_format, |
879 | +@@keep_files_on_create, |
880 | +@@max_join_size; |
881 | +SET STATEMENT myisam_sort_buffer_size=320000, |
882 | +myisam_repair_threads=2, |
883 | +sort_buffer_size=220022, |
884 | +binlog_format=row, |
885 | +keep_files_on_create=ON, |
886 | +max_join_size=2222220000000 FOR |
887 | +SELECT @@myisam_sort_buffer_size, |
888 | +@@myisam_repair_threads, |
889 | +@@sort_buffer_size, |
890 | +@@binlog_format, |
891 | +@@keep_files_on_create, |
892 | +@@max_join_size; |
893 | +END| |
894 | +'' |
895 | +'# Pre-STATEMENT variable value |
896 | +SELECT @@myisam_sort_buffer_size, |
897 | +@@myisam_repair_threads, |
898 | +@@sort_buffer_size, |
899 | +@@binlog_format, |
900 | +@@keep_files_on_create, |
901 | +@@max_join_size; |
902 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
903 | +500000 1 100000 MIXED 1 2222220000000 |
904 | +'' |
905 | +'' |
906 | +SET STATEMENT myisam_sort_buffer_size=400000, |
907 | +myisam_repair_threads=2, |
908 | +sort_buffer_size=200000, |
909 | +binlog_format=row, |
910 | +keep_files_on_create=OFF, |
911 | +max_join_size=4444440000000 FOR |
912 | +CALL p4(); |
913 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
914 | +400000 2 200000 ROW 0 4444440000000 |
915 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
916 | +320000 2 220022 ROW 1 2222220000000 |
917 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
918 | +320000 2 220022 ROW 1 2222220000000 |
919 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
920 | +320000 2 220022 ROW 1 2222220000000 |
921 | +'' |
922 | +'# Post-STATEMENT |
923 | +SELECT @@myisam_sort_buffer_size, |
924 | +@@myisam_repair_threads, |
925 | +@@sort_buffer_size, |
926 | +@@binlog_format, |
927 | +@@keep_files_on_create, |
928 | +@@max_join_size; |
929 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
930 | +500000 1 100000 MIXED 1 2222220000000 |
931 | +'' |
932 | +'' |
933 | +'#------------------Test 16-----------------------#' |
934 | +'' |
935 | +'# Pre-STATEMENT variable value |
936 | +SELECT @@sql_mode; |
937 | +@@sql_mode |
938 | + |
939 | +'' |
940 | +'' |
941 | +SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1; |
942 | +v1 v2 |
943 | +1 2 |
944 | +3 4 |
945 | +'' |
946 | +'# Post-STATEMENT |
947 | +SELECT @@sql_mode; |
948 | +@@sql_mode |
949 | + |
950 | +'' |
951 | +'' |
952 | +'#------------------Test 17-----------------------#' |
953 | +'# set initial variable values |
954 | +SET SESSION myisam_sort_buffer_size=500000, |
955 | +myisam_repair_threads=1, |
956 | +sort_buffer_size=100000, |
957 | +binlog_format=mixed, |
958 | +keep_files_on_create=ON, |
959 | +max_join_size=2222220000000; |
960 | +'' |
961 | +'# Pre-STATEMENT variable value |
962 | +SELECT @@myisam_sort_buffer_size, |
963 | +@@myisam_repair_threads, |
964 | +@@sort_buffer_size, |
965 | +@@binlog_format, |
966 | +@@keep_files_on_create, |
967 | +@@max_join_size; |
968 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
969 | +500000 1 100000 MIXED 1 2222220000000 |
970 | +'' |
971 | +'' |
972 | +SET STATEMENT myisam_sort_buffer_size=320000, |
973 | +myisam_repair_threads=2, |
974 | +sort_buffer_size=220022, |
975 | +binlog_format=row, |
976 | +keep_files_on_create=ON, |
977 | +max_join_size=2222220000000 |
978 | +FOR SET SESSION |
979 | +myisam_sort_buffer_size=260000, |
980 | +myisam_repair_threads=3, |
981 | +sort_buffer_size=230013, |
982 | +binlog_format=row, |
983 | +keep_files_on_create=ON, |
984 | +max_join_size=2323230000000; |
985 | +'' |
986 | +'# Post-STATEMENT |
987 | +SELECT @@myisam_sort_buffer_size, |
988 | +@@myisam_repair_threads, |
989 | +@@sort_buffer_size, |
990 | +@@binlog_format, |
991 | +@@keep_files_on_create, |
992 | +@@max_join_size; |
993 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
994 | +500000 1 100000 MIXED 1 2222220000000 |
995 | +'' |
996 | +'' |
997 | +'#------------------Test 18-----------------------#' |
998 | +'# set initial variable values |
999 | +SET SESSION myisam_sort_buffer_size=500000, |
1000 | +myisam_repair_threads=1, |
1001 | +sort_buffer_size=100000, |
1002 | +binlog_format=mixed, |
1003 | +keep_files_on_create=ON, |
1004 | +max_join_size=2222220000000; |
1005 | +'' |
1006 | +'# Pre-STATEMENT variable value |
1007 | +SELECT @@myisam_sort_buffer_size, |
1008 | +@@myisam_repair_threads, |
1009 | +@@sort_buffer_size, |
1010 | +@@binlog_format, |
1011 | +@@keep_files_on_create, |
1012 | +@@max_join_size; |
1013 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
1014 | +500000 1 100000 MIXED 1 2222220000000 |
1015 | +'' |
1016 | +'' |
1017 | +CREATE PROCEDURE p5() BEGIN |
1018 | +SELECT @@myisam_sort_buffer_size, |
1019 | +@@myisam_repair_threads, |
1020 | +@@sort_buffer_size, |
1021 | +@@binlog_format, |
1022 | +@@keep_files_on_create, |
1023 | +@@max_join_size; |
1024 | +SET SESSION |
1025 | +myisam_sort_buffer_size=260000, |
1026 | +myisam_repair_threads=3, |
1027 | +sort_buffer_size=230013, |
1028 | +binlog_format=row, |
1029 | +keep_files_on_create=ON, |
1030 | +max_join_size=2323230000000; |
1031 | +SELECT @@myisam_sort_buffer_size, |
1032 | +@@myisam_repair_threads, |
1033 | +@@sort_buffer_size, |
1034 | +@@binlog_format, |
1035 | +@@keep_files_on_create, |
1036 | +@@max_join_size; |
1037 | +END| |
1038 | +'' |
1039 | +'' |
1040 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1041 | +myisam_repair_threads=2, |
1042 | +sort_buffer_size=200000, |
1043 | +binlog_format=row, |
1044 | +keep_files_on_create=OFF, |
1045 | +max_join_size=4444440000000 FOR |
1046 | +CALL p5(); |
1047 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
1048 | +400000 2 200000 ROW 0 4444440000000 |
1049 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
1050 | +260000 3 230013 ROW 1 2323230000000 |
1051 | +'' |
1052 | +'# Post-STATEMENT |
1053 | +SELECT @@myisam_sort_buffer_size, |
1054 | +@@myisam_repair_threads, |
1055 | +@@sort_buffer_size, |
1056 | +@@binlog_format, |
1057 | +@@keep_files_on_create, |
1058 | +@@max_join_size; |
1059 | +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size |
1060 | +500000 1 100000 MIXED 1 2222220000000 |
1061 | +'' |
1062 | +'' |
1063 | +'#------------------Test 19-----------------------#' |
1064 | +SET STATEMENT max_error_count=100 FOR; |
1065 | +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 |
1066 | +SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2); |
1067 | +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT t1 VALUES (1,2)' at line 1 |
1068 | +SET STATEMENT FOR INSERT INTO t1 VALUES (1,2); |
1069 | +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 |
1070 | +SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); |
1071 | +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 |
1072 | +SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); |
1073 | +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 |
1074 | +SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); |
1075 | +ERROR HY000: Unknown system variable 'GLOBAL' |
1076 | +SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); |
1077 | +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1 |
1078 | +'' |
1079 | +'' |
1080 | +'#------------------Test 20-----------------------#' |
1081 | +SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2); |
1082 | +ERROR HY000: Variable 'connect_timeout' is a GLOBAL variable and should be set with SET GLOBAL |
1083 | +'' |
1084 | +'' |
1085 | +'#------------------Test 21-----------------------#' |
1086 | +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; |
1087 | +@@myisam_sort_buffer_size @@sort_buffer_size |
1088 | +500000 100000 |
1089 | +SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000 |
1090 | +FOR SET STATEMENT myisam_sort_buffer_size=200000 |
1091 | +FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; |
1092 | +@@myisam_sort_buffer_size @@sort_buffer_size |
1093 | +200000 3000000 |
1094 | +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; |
1095 | +@@myisam_sort_buffer_size @@sort_buffer_size |
1096 | +500000 100000 |
1097 | +'' |
1098 | +'' |
1099 | +'#------------------Test 22-----------------------#' |
1100 | +CREATE TABLE STATEMENT(a INT); |
1101 | +DROP TABLE STATEMENT; |
1102 | +'' |
1103 | +'# Cleanup' |
1104 | +DROP TABLE t1; |
1105 | +DROP PROCEDURE p1; |
1106 | +DROP PROCEDURE p2; |
1107 | +DROP PROCEDURE p3; |
1108 | +DROP PROCEDURE p4; |
1109 | +DROP PROCEDURE p5; |
1110 | |
1111 | === added file 'mysql-test/suite/percona/percona_statement_set.test' |
1112 | --- mysql-test/suite/percona/percona_statement_set.test 1970-01-01 00:00:00 +0000 |
1113 | +++ mysql-test/suite/percona/percona_statement_set.test 2013-11-06 17:03:07 +0000 |
1114 | @@ -0,0 +1,830 @@ |
1115 | +--echo '# SET STATEMENT ..... FOR .... TEST' |
1116 | +############################ STATEMENT_SET ############################# |
1117 | +# # |
1118 | +# Testing working functionality of SET STATEMENT # |
1119 | +# # |
1120 | +# # |
1121 | +# There is important documentation within # |
1122 | +# # |
1123 | +# # |
1124 | +# Author: Joe Lukas # |
1125 | +# Creation: # |
1126 | +# 2009-08-02 Implement this test as part of # |
1127 | +# WL#681 Per query variable settings # |
1128 | +# # |
1129 | +######################################################################## |
1130 | + |
1131 | +--disable_warnings |
1132 | +DROP TABLE IF EXISTS t1; |
1133 | +DROP FUNCTION IF EXISTS myProc; |
1134 | +DROP PROCEDURE IF EXISTS p1; |
1135 | +DROP PROCEDURE IF EXISTS p2; |
1136 | +DROP PROCEDURE IF EXISTS p3; |
1137 | +DROP PROCEDURE IF EXISTS p4; |
1138 | +DROP PROCEDURE IF EXISTS p5; |
1139 | +DROP TABLE IF EXISTS STATEMENT; |
1140 | +--enable_warnings |
1141 | +#################################################################### |
1142 | +#Set up current database |
1143 | +#################################################################### |
1144 | +--echo '# Setup database' |
1145 | +CREATE TABLE t1 (v1 INT, v2 INT); |
1146 | +INSERT INTO t1 VALUES (1,2); |
1147 | +INSERT INTO t1 VALUES (3,4); |
1148 | +--echo '' |
1149 | +--echo '#------------------ STATEMENT Test 1 -----------------------#' |
1150 | +#################################################################### |
1151 | +# Checks with variable value type ulong # |
1152 | +#################################################################### |
1153 | +--echo '# Initialize variables to known setting' |
1154 | +SET SESSION sort_buffer_size=100000; |
1155 | +--echo '' |
1156 | +--echo '# Pre-STATEMENT variable value' |
1157 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
1158 | +SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1; |
1159 | +--echo '' |
1160 | +--echo '# Post-STATEMENT variable value' |
1161 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
1162 | +--echo '' |
1163 | +--echo '#------------------ STATEMENT Test 2 -----------------------#' |
1164 | +#################################################################### |
1165 | +# Checks for multiple set values inside STATEMENT ... FOR # |
1166 | +#################################################################### |
1167 | +--echo '# Initialize variables to known setting' |
1168 | +SET SESSION binlog_format=mixed; |
1169 | +SET SESSION sort_buffer_size=100000; |
1170 | +--echo '# Pre-STATEMENT variable value' |
1171 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
1172 | +SHOW SESSION VARIABLES LIKE 'binlog_format'; |
1173 | +SET STATEMENT sort_buffer_size=150000, binlog_format=row |
1174 | + FOR SELECT * FROM t1; |
1175 | +--echo '# Post-STATEMENT variable value' |
1176 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
1177 | +SHOW SESSION VARIABLES LIKE 'binlog_format'; |
1178 | + |
1179 | +--echo '' |
1180 | +--echo '#------------------ STATEMENT Test 3 -----------------------#' |
1181 | +#################################################################### |
1182 | +# Check current variable value is stored in using stored # |
1183 | +# statements. # |
1184 | +#################################################################### |
1185 | +--echo '# set initial variable value, make prepared statement |
1186 | +SET SESSION binlog_format=row; |
1187 | +PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1'; |
1188 | +--echo '' |
1189 | +--echo '# Change variable setting' |
1190 | +SET SESSION binlog_format=mixed; |
1191 | +--echo '' |
1192 | +--echo '# Pre-STATEMENT variable value' |
1193 | +--echo '' |
1194 | +SHOW SESSION VARIABLES LIKE 'binlog_format'; |
1195 | +--echo '' |
1196 | +EXECUTE stmt1; |
1197 | +--echo '' |
1198 | +--echo '# Post-STATEMENT variable value' |
1199 | +SHOW SESSION VARIABLES LIKE 'binlog_format'; |
1200 | + |
1201 | +--echo '' |
1202 | +DEALLOCATE PREPARE stmt1; |
1203 | +--echo '#------------------ STATEMENT Test 4 -----------------------#' |
1204 | +#################################################################### |
1205 | +# Check works with OPTIMIZE TABLE command # |
1206 | +# Checks works with a variable value of type INT # |
1207 | +# Checks works with variable type ULONGLONG # |
1208 | +#################################################################### |
1209 | +--echo '# set initial variable value, make prepared statement |
1210 | +SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1; |
1211 | +--echo '' |
1212 | +--echo '# Pre-STATEMENT variable value' |
1213 | +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; |
1214 | +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
1215 | +--echo '' |
1216 | +SET STATEMENT myisam_sort_buffer_size=800000, |
1217 | + myisam_repair_threads=2 FOR OPTIMIZE TABLE t1; |
1218 | +--echo '' |
1219 | +--echo '# Post-STATEMENT variable value' |
1220 | +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; |
1221 | +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
1222 | + |
1223 | +--echo '' |
1224 | +--echo '#------------------ STATEMENT Test 5 -----------------------#' |
1225 | +#################################################################### |
1226 | +# Checks if variable reset after error in statement after FOR # |
1227 | +#################################################################### |
1228 | +--echo '# Initialize variables to known setting' |
1229 | +SET SESSION sort_buffer_size=100000; |
1230 | +--echo '' |
1231 | +--echo '# Pre-STATEMENT variable value' |
1232 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
1233 | +--echo '' |
1234 | +--error ER_NO_SUCH_TABLE |
1235 | +SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2; |
1236 | +--echo '' |
1237 | +--echo '# Post-STATEMENT variable value' |
1238 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
1239 | + |
1240 | +--echo '' |
1241 | +--echo '#------------------ STATEMENT Test 6 -----------------------#' |
1242 | +#################################################################### |
1243 | +# Checks works with variable type MY_BOOL # |
1244 | +#################################################################### |
1245 | +--echo '# Initialize variables to known setting' |
1246 | +SET SESSION keep_files_on_create=ON; |
1247 | +--echo '' |
1248 | +--echo '# Pre-STATEMENT variable value' |
1249 | +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
1250 | +--echo '' |
1251 | +SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1; |
1252 | +--echo '' |
1253 | +--echo '# Post-STATEMENT variable value' |
1254 | +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
1255 | + |
1256 | +--echo '' |
1257 | +--echo '#------------------ STATEMENT Test 7 -----------------------#' |
1258 | +#################################################################### |
1259 | +# Checks works with variable type HA_ROWS # |
1260 | +#################################################################### |
1261 | +--echo '# Initialize variables to known setting' |
1262 | +SET SESSION max_join_size=2222220000000; |
1263 | +--echo '' |
1264 | +--echo '# Pre-STATEMENT variable value' |
1265 | +SHOW SESSION VARIABLES LIKE 'max_join_size'; |
1266 | +--echo '' |
1267 | +SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1; |
1268 | +--echo '' |
1269 | +--echo '# Post-STATEMENT variable value' |
1270 | +SHOW SESSION VARIABLES LIKE 'max_join_size'; |
1271 | + |
1272 | +--echo '' |
1273 | +--echo '#------------------Test 8-----------------------#' |
1274 | +#################################################################### |
1275 | +# Ensure variable of each type is set to proper value during # |
1276 | +# statement after FOR execution # |
1277 | +#################################################################### |
1278 | +--echo '# Initialize test variables' |
1279 | +SET SESSION myisam_sort_buffer_size=500000, |
1280 | + myisam_repair_threads=1, |
1281 | + sort_buffer_size = 200000, |
1282 | + max_join_size=2222220000000, |
1283 | + keep_files_on_create=ON; |
1284 | + |
1285 | +--echo '' |
1286 | +--echo '# LONG ' |
1287 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
1288 | +SET STATEMENT sort_buffer_size = 100000 |
1289 | + FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
1290 | +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; |
1291 | +--echo '' |
1292 | +--echo '# MY_BOOL ' |
1293 | +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
1294 | +SET STATEMENT keep_files_on_create=OFF |
1295 | + FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
1296 | +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; |
1297 | + |
1298 | +--echo '' |
1299 | +--echo '# INT/LONG ' |
1300 | +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
1301 | +SET STATEMENT myisam_repair_threads=2 |
1302 | + FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
1303 | +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; |
1304 | +--echo '' |
1305 | +--echo '# ULONGLONG ' |
1306 | +SHOW SESSION VARIABLES LIKE 'max_join_size'; |
1307 | +SET STATEMENT max_join_size=2000000000000 |
1308 | + FOR SHOW SESSION VARIABLES LIKE 'max_join_size'; |
1309 | +SHOW SESSION VARIABLES LIKE 'max_join_size'; |
1310 | + |
1311 | +--echo '' |
1312 | +--echo '#------------------Test 9-----------------------#' |
1313 | +#################################################################### |
1314 | +# No 1 - Check works with CREATE ... BEGIN ... END command # |
1315 | +# Display variables during execution # |
1316 | +# No 2 - Test with DROP command # |
1317 | +#################################################################### |
1318 | +--echo '# set initial variable values |
1319 | +SET SESSION myisam_sort_buffer_size=500000, |
1320 | + myisam_repair_threads=1, |
1321 | + sort_buffer_size=100000, |
1322 | + binlog_format=mixed, |
1323 | + keep_files_on_create=ON, |
1324 | + max_join_size=2222220000000; |
1325 | +--echo '' |
1326 | +--echo '' |
1327 | +--echo '# Pre-STATEMENT variable value |
1328 | +SELECT @@myisam_sort_buffer_size, |
1329 | + @@myisam_repair_threads, |
1330 | + @@sort_buffer_size, |
1331 | + @@binlog_format, |
1332 | + @@keep_files_on_create, |
1333 | + @@max_join_size; |
1334 | +--echo '' |
1335 | +--echo '' |
1336 | +DELIMITER |; |
1337 | +CREATE FUNCTION myProc (cost DECIMAL(10,2)) |
1338 | + RETURNS DECIMAL(10,2) |
1339 | + |
1340 | + SQL SECURITY DEFINER |
1341 | + |
1342 | + tax: BEGIN |
1343 | + DECLARE order_tax DECIMAL(10,2); |
1344 | + SET order_tax = cost * .05; |
1345 | + RETURN order_tax; |
1346 | + END| |
1347 | +DELIMITER ;| |
1348 | +--echo '' |
1349 | +--echo '# During Execution values |
1350 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1351 | + myisam_repair_threads=2, |
1352 | + sort_buffer_size=200000, |
1353 | + binlog_format=row, |
1354 | + keep_files_on_create=OFF, |
1355 | + max_join_size=4444440000000 FOR |
1356 | + SELECT myProc(123.45); |
1357 | +--echo '' |
1358 | +--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2' |
1359 | +SELECT @@myisam_sort_buffer_size, |
1360 | + @@myisam_repair_threads, |
1361 | + @@sort_buffer_size, |
1362 | + @@binlog_format, |
1363 | + @@keep_files_on_create, |
1364 | + @@max_join_size; |
1365 | +--echo '' |
1366 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1367 | + myisam_repair_threads=2, |
1368 | + sort_buffer_size=200000, |
1369 | + binlog_format=row, |
1370 | + keep_files_on_create=OFF, |
1371 | + max_join_size=4444440000000 FOR |
1372 | + DROP FUNCTION myProc; |
1373 | +--echo '' |
1374 | +--echo '# Post-STATEMENT No 2 variable value |
1375 | +SELECT @@myisam_sort_buffer_size, |
1376 | + @@myisam_repair_threads, |
1377 | + @@sort_buffer_size, |
1378 | + @@binlog_format, |
1379 | + @@keep_files_on_create, |
1380 | + @@max_join_size; |
1381 | + |
1382 | +--echo '' |
1383 | +--echo '#------------------Test 10-----------------------#' |
1384 | +#################################################################### |
1385 | +# No 1 - Check with PREPARE statement # |
1386 | +# with STATEMENT inside with same variable as outside # |
1387 | +# No 2 - Check with EXECUTE statement # |
1388 | +#################################################################### |
1389 | +--echo '# set initial variable values |
1390 | +SET SESSION myisam_sort_buffer_size=500000, |
1391 | + myisam_repair_threads=1, |
1392 | + sort_buffer_size=100000, |
1393 | + binlog_format=mixed, |
1394 | + keep_files_on_create=ON, |
1395 | + max_join_size=2222220000000; |
1396 | +--echo '' |
1397 | +--echo '# Pre-STATEMENT variable value |
1398 | +SELECT @@myisam_sort_buffer_size, |
1399 | + @@myisam_repair_threads, |
1400 | + @@sort_buffer_size, |
1401 | + @@binlog_format, |
1402 | + @@keep_files_on_create, |
1403 | + @@max_join_size; |
1404 | +--echo '' |
1405 | +--echo '' |
1406 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1407 | + myisam_repair_threads=2, |
1408 | + sort_buffer_size=200000, |
1409 | + binlog_format=row, |
1410 | + keep_files_on_create=OFF, |
1411 | + max_join_size=4444440000000 FOR |
1412 | + PREPARE stmt2 |
1413 | + FROM 'SELECT * FROM t1'; |
1414 | +--echo '' |
1415 | +--echo 'Test No 1 Post Value & Test 2 Pre values' |
1416 | +SELECT @@myisam_sort_buffer_size, |
1417 | + @@myisam_repair_threads, |
1418 | + @@sort_buffer_size, |
1419 | + @@binlog_format, |
1420 | + @@keep_files_on_create, |
1421 | + @@max_join_size; |
1422 | +--echo '' |
1423 | +--echo '' |
1424 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1425 | + myisam_repair_threads=2, |
1426 | + sort_buffer_size=200000, |
1427 | + binlog_format=row, |
1428 | + keep_files_on_create=OFF, |
1429 | + max_join_size=4444440000000 FOR |
1430 | + EXECUTE stmt2; |
1431 | +--echo '' |
1432 | +--echo '# Post-STATEMENT No 2 |
1433 | +SELECT @@myisam_sort_buffer_size, |
1434 | + @@myisam_repair_threads, |
1435 | + @@sort_buffer_size, |
1436 | + @@binlog_format, |
1437 | + @@keep_files_on_create, |
1438 | + @@max_join_size; |
1439 | +--echo '' |
1440 | +DEALLOCATE PREPARE stmt2; |
1441 | +--echo '' |
1442 | +--echo '#------------------Test 11-----------------------#' |
1443 | +#################################################################### |
1444 | +# No 1 - Check with PREPARE statement # |
1445 | +# check with different variable on inside PREPARE # |
1446 | +# No 2 - Check with EXECUTE statement # |
1447 | +#################################################################### |
1448 | +--echo '# set initial variable values |
1449 | +SET SESSION myisam_sort_buffer_size=500000, |
1450 | + myisam_repair_threads=1, |
1451 | + sort_buffer_size=100000, |
1452 | + binlog_format=mixed, |
1453 | + keep_files_on_create=ON, |
1454 | + max_join_size=2222220000000; |
1455 | +--echo '' |
1456 | +--echo '' |
1457 | +--echo '# Pre-STATEMENT variable value |
1458 | +SELECT @@myisam_sort_buffer_size, |
1459 | + @@myisam_repair_threads, |
1460 | + @@sort_buffer_size, |
1461 | + @@binlog_format, |
1462 | + @@keep_files_on_create, |
1463 | + @@max_join_size; |
1464 | +--echo '' |
1465 | +--echo '' |
1466 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1467 | + myisam_repair_threads=2, |
1468 | + sort_buffer_size=200000, |
1469 | + keep_files_on_create=OFF, |
1470 | + max_join_size=4444440000000 FOR |
1471 | + PREPARE stmt1 FROM |
1472 | + 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1'; |
1473 | +--echo '' |
1474 | +--echo 'Test No 1 Post Value & Test 2 Pre values' |
1475 | +SELECT @@myisam_sort_buffer_size, |
1476 | + @@myisam_repair_threads, |
1477 | + @@sort_buffer_size, |
1478 | + @@binlog_format, |
1479 | + @@keep_files_on_create, |
1480 | + @@max_join_size; |
1481 | +--echo '' |
1482 | +--echo '' |
1483 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1484 | + myisam_repair_threads=2, |
1485 | + sort_buffer_size=200000, |
1486 | + keep_files_on_create=OFF, |
1487 | + max_join_size=4444440000000 FOR |
1488 | + EXECUTE stmt1; |
1489 | +--echo '' |
1490 | +--echo '# Post-STATEMENT No 2 |
1491 | +SELECT @@myisam_sort_buffer_size, |
1492 | + @@myisam_repair_threads, |
1493 | + @@sort_buffer_size, |
1494 | + @@binlog_format, |
1495 | + @@keep_files_on_create, |
1496 | + @@max_join_size; |
1497 | +--echo '' |
1498 | +--echo '' |
1499 | +--echo '#------------------Test 12-----------------------#' |
1500 | +#################################################################### |
1501 | +# No 1 - Check with PROCEDURE (show variables in procedure) # |
1502 | +# No 2 - Check with CALL statement show variables in PROCEDURE # |
1503 | +#################################################################### |
1504 | +--echo '# set initial variable values |
1505 | +SET SESSION myisam_sort_buffer_size=500000, |
1506 | + myisam_repair_threads=1, |
1507 | + sort_buffer_size=100000, |
1508 | + binlog_format=mixed, |
1509 | + keep_files_on_create=ON, |
1510 | + max_join_size=2222220000000; |
1511 | +--echo '' |
1512 | +--echo '' |
1513 | +--echo '# Pre-STATEMENT variable value |
1514 | +SELECT @@myisam_sort_buffer_size, |
1515 | + @@myisam_repair_threads, |
1516 | + @@sort_buffer_size, |
1517 | + @@binlog_format, |
1518 | + @@keep_files_on_create, |
1519 | + @@max_join_size; |
1520 | +--echo '' |
1521 | +--echo '' |
1522 | +DELIMITER |; |
1523 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1524 | + myisam_repair_threads=2, |
1525 | + sort_buffer_size=200000, |
1526 | + binlog_format=row, |
1527 | + keep_files_on_create=OFF, |
1528 | + max_join_size=4444440000000 FOR |
1529 | + CREATE PROCEDURE p1() BEGIN |
1530 | + SELECT @@myisam_sort_buffer_size, |
1531 | + @@myisam_repair_threads, |
1532 | + @@sort_buffer_size, |
1533 | + @@binlog_format, |
1534 | + @@keep_files_on_create, |
1535 | + @@max_join_size; |
1536 | + END| |
1537 | +DELIMITER ;| |
1538 | +--echo '' |
1539 | +--echo 'Test No 1 Post Value & Test 2 Pre values' |
1540 | +SELECT @@myisam_sort_buffer_size, |
1541 | + @@myisam_repair_threads, |
1542 | + @@sort_buffer_size, |
1543 | + @@binlog_format, |
1544 | + @@keep_files_on_create, |
1545 | + @@max_join_size; |
1546 | +--echo '' |
1547 | +--echo '' |
1548 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1549 | + myisam_repair_threads=2, |
1550 | + sort_buffer_size=200000, |
1551 | + binlog_format=row, |
1552 | + keep_files_on_create=OFF, |
1553 | + max_join_size=4444440000000 FOR |
1554 | + CALL p1(); |
1555 | +--echo '' |
1556 | +--echo '# Post-STATEMENT No 2 |
1557 | +SELECT @@myisam_sort_buffer_size, |
1558 | + @@myisam_repair_threads, |
1559 | + @@sort_buffer_size, |
1560 | + @@binlog_format, |
1561 | + @@keep_files_on_create, |
1562 | + @@max_join_size; |
1563 | +--echo '' |
1564 | +--echo '' |
1565 | + |
1566 | +--echo '#------------------Test 13-----------------------#' |
1567 | +#################################################################### |
1568 | +# Check PROCEDURE containing SET STATEMENT FOR # |
1569 | +# p1() from test 12 will be used to display variables # |
1570 | +#################################################################### |
1571 | +--echo '# set initial variable values |
1572 | +SET SESSION myisam_sort_buffer_size=500000, |
1573 | + myisam_repair_threads=1, |
1574 | + sort_buffer_size=100000, |
1575 | + binlog_format=mixed, |
1576 | + keep_files_on_create=ON, |
1577 | + max_join_size=2222220000000; |
1578 | +--echo '' |
1579 | +--echo '' |
1580 | +DELIMITER |; |
1581 | +CREATE PROCEDURE p2() BEGIN |
1582 | + SET STATEMENT myisam_sort_buffer_size=400000, |
1583 | + myisam_repair_threads=3, |
1584 | + sort_buffer_size=300000, |
1585 | + binlog_format=mixed, |
1586 | + keep_files_on_create=OFF, |
1587 | + max_join_size=3333330000000 FOR |
1588 | + CALL p1(); |
1589 | + END| |
1590 | +DELIMITER ;| |
1591 | +--echo '' |
1592 | +--echo '# Pre-STATEMENT variable value |
1593 | +SELECT @@myisam_sort_buffer_size, |
1594 | + @@myisam_repair_threads, |
1595 | + @@sort_buffer_size, |
1596 | + @@binlog_format, |
1597 | + @@keep_files_on_create, |
1598 | + @@max_join_size; |
1599 | +--echo '' |
1600 | +--echo '' |
1601 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1602 | + myisam_repair_threads=2, |
1603 | + sort_buffer_size=200000, |
1604 | + binlog_format=row, |
1605 | + keep_files_on_create=OFF, |
1606 | + max_join_size=4444440000000 FOR |
1607 | + CALL p2(); |
1608 | +--echo '' |
1609 | +--echo '# Post-STATEMENT |
1610 | +SELECT @@myisam_sort_buffer_size, |
1611 | + @@myisam_repair_threads, |
1612 | + @@sort_buffer_size, |
1613 | + @@binlog_format, |
1614 | + @@keep_files_on_create, |
1615 | + @@max_join_size; |
1616 | +--echo '' |
1617 | +--echo '' |
1618 | +--echo '#------------------Test 14-----------------------#' |
1619 | +#################################################################### |
1620 | +# Check PROCEDURE containing compound SET STATEMENT FOR # |
1621 | +# p2() will be used as compounding statement from test 13 # |
1622 | +#################################################################### |
1623 | +--echo '# set initial variable values |
1624 | +SET SESSION myisam_sort_buffer_size=500000, |
1625 | + myisam_repair_threads=1, |
1626 | + sort_buffer_size=100000, |
1627 | + binlog_format=mixed, |
1628 | + keep_files_on_create=ON, |
1629 | + max_join_size=2222220000000; |
1630 | +--echo '' |
1631 | +--echo '' |
1632 | +DELIMITER |; |
1633 | +CREATE PROCEDURE p3() BEGIN |
1634 | + SELECT @@myisam_sort_buffer_size, |
1635 | + @@myisam_repair_threads, |
1636 | + @@sort_buffer_size, |
1637 | + @@binlog_format, |
1638 | + @@keep_files_on_create, |
1639 | + @@max_join_size; |
1640 | + SET STATEMENT myisam_sort_buffer_size=320000, |
1641 | + myisam_repair_threads=2, |
1642 | + sort_buffer_size=220022, |
1643 | + binlog_format=row, |
1644 | + keep_files_on_create=ON, |
1645 | + max_join_size=2222220000000 FOR |
1646 | + CALL p2(); |
1647 | + END| |
1648 | +DELIMITER ;| |
1649 | +--echo '' |
1650 | +--echo '# Pre-STATEMENT variable value |
1651 | +SELECT @@myisam_sort_buffer_size, |
1652 | + @@myisam_repair_threads, |
1653 | + @@sort_buffer_size, |
1654 | + @@binlog_format, |
1655 | + @@keep_files_on_create, |
1656 | + @@max_join_size; |
1657 | +--echo '' |
1658 | +--echo '' |
1659 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1660 | + myisam_repair_threads=2, |
1661 | + sort_buffer_size=200000, |
1662 | + binlog_format=row, |
1663 | + keep_files_on_create=OFF, |
1664 | + max_join_size=4444440000000 FOR |
1665 | + CALL p3(); |
1666 | +--echo '' |
1667 | +--echo '# Post-STATEMENT |
1668 | +SELECT @@myisam_sort_buffer_size, |
1669 | + @@myisam_repair_threads, |
1670 | + @@sort_buffer_size, |
1671 | + @@binlog_format, |
1672 | + @@keep_files_on_create, |
1673 | + @@max_join_size; |
1674 | +--echo '' |
1675 | +--echo '' |
1676 | + |
1677 | + --echo '' |
1678 | +--echo '' |
1679 | +--echo '#------------------Test 15-----------------------#' |
1680 | +#################################################################### |
1681 | +# Check PROCEDURE containing compound SET STATEMENT FOR # |
1682 | +# call multiple SET STATEMENT .. FOR showing SELECT # |
1683 | +#################################################################### |
1684 | +--echo '# set initial variable values |
1685 | +SET SESSION myisam_sort_buffer_size=500000, |
1686 | + myisam_repair_threads=1, |
1687 | + sort_buffer_size=100000, |
1688 | + binlog_format=mixed, |
1689 | + keep_files_on_create=ON, |
1690 | + max_join_size=2222220000000; |
1691 | +--echo '' |
1692 | +--echo '' |
1693 | +DELIMITER |; |
1694 | +CREATE PROCEDURE p4() BEGIN |
1695 | + SELECT @@myisam_sort_buffer_size, |
1696 | + @@myisam_repair_threads, |
1697 | + @@sort_buffer_size, |
1698 | + @@binlog_format, |
1699 | + @@keep_files_on_create, |
1700 | + @@max_join_size; |
1701 | + SET STATEMENT myisam_sort_buffer_size=320000, |
1702 | + myisam_repair_threads=2, |
1703 | + sort_buffer_size=220022, |
1704 | + binlog_format=row, |
1705 | + keep_files_on_create=ON, |
1706 | + max_join_size=2222220000000 FOR |
1707 | + SELECT @@myisam_sort_buffer_size, |
1708 | + @@myisam_repair_threads, |
1709 | + @@sort_buffer_size, |
1710 | + @@binlog_format, |
1711 | + @@keep_files_on_create, |
1712 | + @@max_join_size; |
1713 | + SET STATEMENT myisam_sort_buffer_size=320000, |
1714 | + myisam_repair_threads=2, |
1715 | + sort_buffer_size=220022, |
1716 | + binlog_format=row, |
1717 | + keep_files_on_create=ON, |
1718 | + max_join_size=2222220000000 FOR |
1719 | + SELECT @@myisam_sort_buffer_size, |
1720 | + @@myisam_repair_threads, |
1721 | + @@sort_buffer_size, |
1722 | + @@binlog_format, |
1723 | + @@keep_files_on_create, |
1724 | + @@max_join_size; |
1725 | + SET STATEMENT myisam_sort_buffer_size=320000, |
1726 | + myisam_repair_threads=2, |
1727 | + sort_buffer_size=220022, |
1728 | + binlog_format=row, |
1729 | + keep_files_on_create=ON, |
1730 | + max_join_size=2222220000000 FOR |
1731 | + SELECT @@myisam_sort_buffer_size, |
1732 | + @@myisam_repair_threads, |
1733 | + @@sort_buffer_size, |
1734 | + @@binlog_format, |
1735 | + @@keep_files_on_create, |
1736 | + @@max_join_size; |
1737 | + END| |
1738 | +DELIMITER ;| |
1739 | +--echo '' |
1740 | +--echo '# Pre-STATEMENT variable value |
1741 | +SELECT @@myisam_sort_buffer_size, |
1742 | + @@myisam_repair_threads, |
1743 | + @@sort_buffer_size, |
1744 | + @@binlog_format, |
1745 | + @@keep_files_on_create, |
1746 | + @@max_join_size; |
1747 | +--echo '' |
1748 | +--echo '' |
1749 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1750 | + myisam_repair_threads=2, |
1751 | + sort_buffer_size=200000, |
1752 | + binlog_format=row, |
1753 | + keep_files_on_create=OFF, |
1754 | + max_join_size=4444440000000 FOR |
1755 | + CALL p4(); |
1756 | +--echo '' |
1757 | +--echo '# Post-STATEMENT |
1758 | +SELECT @@myisam_sort_buffer_size, |
1759 | + @@myisam_repair_threads, |
1760 | + @@sort_buffer_size, |
1761 | + @@binlog_format, |
1762 | + @@keep_files_on_create, |
1763 | + @@max_join_size; |
1764 | + |
1765 | +--echo '' |
1766 | +--echo '' |
1767 | +--echo '#------------------Test 16-----------------------#' |
1768 | +#################################################################### |
1769 | +# Test Effect on parsing # |
1770 | +#################################################################### |
1771 | +--echo '' |
1772 | +--echo '# Pre-STATEMENT variable value |
1773 | +SELECT @@sql_mode; |
1774 | +--echo '' |
1775 | +--echo '' |
1776 | +SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1; |
1777 | + |
1778 | +--echo '' |
1779 | +--echo '# Post-STATEMENT |
1780 | +SELECT @@sql_mode; |
1781 | +--echo '' |
1782 | +--echo '' |
1783 | +--echo '#------------------Test 17-----------------------#' |
1784 | +#################################################################### |
1785 | +# Test effect of SET STATEMENT FOR with SET SESSION modifying # |
1786 | +# the same variables as the SET STATEMENT # |
1787 | +#################################################################### |
1788 | +--echo '# set initial variable values |
1789 | +SET SESSION myisam_sort_buffer_size=500000, |
1790 | + myisam_repair_threads=1, |
1791 | + sort_buffer_size=100000, |
1792 | + binlog_format=mixed, |
1793 | + keep_files_on_create=ON, |
1794 | + max_join_size=2222220000000; |
1795 | +--echo '' |
1796 | +--echo '# Pre-STATEMENT variable value |
1797 | +SELECT @@myisam_sort_buffer_size, |
1798 | + @@myisam_repair_threads, |
1799 | + @@sort_buffer_size, |
1800 | + @@binlog_format, |
1801 | + @@keep_files_on_create, |
1802 | + @@max_join_size; |
1803 | +--echo '' |
1804 | +--echo '' |
1805 | +SET STATEMENT myisam_sort_buffer_size=320000, |
1806 | + myisam_repair_threads=2, |
1807 | + sort_buffer_size=220022, |
1808 | + binlog_format=row, |
1809 | + keep_files_on_create=ON, |
1810 | + max_join_size=2222220000000 |
1811 | + FOR SET SESSION |
1812 | + myisam_sort_buffer_size=260000, |
1813 | + myisam_repair_threads=3, |
1814 | + sort_buffer_size=230013, |
1815 | + binlog_format=row, |
1816 | + keep_files_on_create=ON, |
1817 | + max_join_size=2323230000000; |
1818 | + |
1819 | +--echo '' |
1820 | +--echo '# Post-STATEMENT |
1821 | +SELECT @@myisam_sort_buffer_size, |
1822 | + @@myisam_repair_threads, |
1823 | + @@sort_buffer_size, |
1824 | + @@binlog_format, |
1825 | + @@keep_files_on_create, |
1826 | + @@max_join_size; |
1827 | + |
1828 | +--echo '' |
1829 | +--echo '' |
1830 | +--echo '#------------------Test 18-----------------------#' |
1831 | +#################################################################### |
1832 | +# Test effect of SET SESSION inside a stored procedure with # |
1833 | +# with a SET STATEMENT on outside variables # |
1834 | +#################################################################### |
1835 | +--echo '# set initial variable values |
1836 | +SET SESSION myisam_sort_buffer_size=500000, |
1837 | + myisam_repair_threads=1, |
1838 | + sort_buffer_size=100000, |
1839 | + binlog_format=mixed, |
1840 | + keep_files_on_create=ON, |
1841 | + max_join_size=2222220000000; |
1842 | +--echo '' |
1843 | +--echo '# Pre-STATEMENT variable value |
1844 | +SELECT @@myisam_sort_buffer_size, |
1845 | + @@myisam_repair_threads, |
1846 | + @@sort_buffer_size, |
1847 | + @@binlog_format, |
1848 | + @@keep_files_on_create, |
1849 | + @@max_join_size; |
1850 | +--echo '' |
1851 | +--echo '' |
1852 | +DELIMITER |; |
1853 | +CREATE PROCEDURE p5() BEGIN |
1854 | + SELECT @@myisam_sort_buffer_size, |
1855 | + @@myisam_repair_threads, |
1856 | + @@sort_buffer_size, |
1857 | + @@binlog_format, |
1858 | + @@keep_files_on_create, |
1859 | + @@max_join_size; |
1860 | + SET SESSION |
1861 | + myisam_sort_buffer_size=260000, |
1862 | + myisam_repair_threads=3, |
1863 | + sort_buffer_size=230013, |
1864 | + binlog_format=row, |
1865 | + keep_files_on_create=ON, |
1866 | + max_join_size=2323230000000; |
1867 | + SELECT @@myisam_sort_buffer_size, |
1868 | + @@myisam_repair_threads, |
1869 | + @@sort_buffer_size, |
1870 | + @@binlog_format, |
1871 | + @@keep_files_on_create, |
1872 | + @@max_join_size; |
1873 | + END| |
1874 | +DELIMITER ;| |
1875 | +--echo '' |
1876 | +--echo '' |
1877 | +SET STATEMENT myisam_sort_buffer_size=400000, |
1878 | + myisam_repair_threads=2, |
1879 | + sort_buffer_size=200000, |
1880 | + binlog_format=row, |
1881 | + keep_files_on_create=OFF, |
1882 | + max_join_size=4444440000000 FOR |
1883 | + CALL p5(); |
1884 | + |
1885 | +--echo '' |
1886 | +--echo '# Post-STATEMENT |
1887 | +SELECT @@myisam_sort_buffer_size, |
1888 | + @@myisam_repair_threads, |
1889 | + @@sort_buffer_size, |
1890 | + @@binlog_format, |
1891 | + @@keep_files_on_create, |
1892 | + @@max_join_size; |
1893 | + |
1894 | +--echo '' |
1895 | +--echo '' |
1896 | +--echo '#------------------Test 19-----------------------#' |
1897 | +#Test for bad syntax |
1898 | +--error ER_PARSE_ERROR |
1899 | +SET STATEMENT max_error_count=100 FOR; |
1900 | +--error ER_PARSE_ERROR |
1901 | +SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2); |
1902 | +--error ER_PARSE_ERROR |
1903 | +SET STATEMENT FOR INSERT INTO t1 VALUES (1,2); |
1904 | +--error ER_PARSE_ERROR |
1905 | +SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); |
1906 | +--error ER_PARSE_ERROR |
1907 | +SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); |
1908 | +--error ER_UNKNOWN_SYSTEM_VARIABLE |
1909 | +SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); |
1910 | +--error ER_PARSE_ERROR |
1911 | +SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); |
1912 | + |
1913 | +--echo '' |
1914 | +--echo '' |
1915 | +--echo '#------------------Test 20-----------------------#' |
1916 | +#Test for global-only variables |
1917 | +--error ER_GLOBAL_VARIABLE |
1918 | +SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2); |
1919 | + |
1920 | +--echo '' |
1921 | +--echo '' |
1922 | +--echo '#------------------Test 21-----------------------#' |
1923 | +#Test for recursion |
1924 | +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; |
1925 | +SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000 |
1926 | + FOR SET STATEMENT myisam_sort_buffer_size=200000 |
1927 | + FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; |
1928 | +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; |
1929 | + |
1930 | +--echo '' |
1931 | +--echo '' |
1932 | +--echo '#------------------Test 22-----------------------#' |
1933 | +#Test for STATEMENT keyword |
1934 | +CREATE TABLE STATEMENT(a INT); |
1935 | +DROP TABLE STATEMENT; |
1936 | + |
1937 | +--echo '' |
1938 | +--echo '# Cleanup' |
1939 | +DROP TABLE t1; |
1940 | +DROP PROCEDURE p1; |
1941 | +DROP PROCEDURE p2; |
1942 | +DROP PROCEDURE p3; |
1943 | +DROP PROCEDURE p4; |
1944 | +DROP PROCEDURE p5; |
1945 | |
1946 | === modified file 'sql/lex.h' |
1947 | --- sql/lex.h 2013-10-29 14:08:44 +0000 |
1948 | +++ sql/lex.h 2013-11-06 17:03:07 +0000 |
1949 | @@ -568,6 +568,7 @@ |
1950 | { "START", SYM(START_SYM)}, |
1951 | { "STARTING", SYM(STARTING)}, |
1952 | { "STARTS", SYM(STARTS_SYM)}, |
1953 | + { "STATEMENT", SYM(STATEMENT_SYM)}, |
1954 | { "STATS_AUTO_RECALC",SYM(STATS_AUTO_RECALC_SYM)}, |
1955 | { "STATS_PERSISTENT", SYM(STATS_PERSISTENT_SYM)}, |
1956 | { "STATS_SAMPLE_PAGES",SYM(STATS_SAMPLE_PAGES_SYM)}, |
1957 | |
1958 | === modified file 'sql/set_var.h' |
1959 | --- sql/set_var.h 2013-10-29 14:08:44 +0000 |
1960 | +++ sql/set_var.h 2013-11-06 17:03:07 +0000 |
1961 | @@ -113,6 +113,9 @@ |
1962 | */ |
1963 | bool set_default(THD *thd, set_var *var); |
1964 | bool update(THD *thd, set_var *var); |
1965 | + void stmt_update(THD *thd) { |
1966 | + on_update && on_update(this, thd, OPT_SESSION); |
1967 | + } |
1968 | |
1969 | longlong val_int(bool *is_null, THD *thd, enum_var_type type, LEX_STRING *base); |
1970 | String *val_str(String *str, THD *thd, enum_var_type type, LEX_STRING *base); |
1971 | |
1972 | === modified file 'sql/sql_lex.cc' |
1973 | --- sql/sql_lex.cc 2013-10-16 09:38:42 +0000 |
1974 | +++ sql/sql_lex.cc 2013-11-06 17:03:07 +0000 |
1975 | @@ -542,6 +542,7 @@ |
1976 | lex->reset_slave_info.all= false; |
1977 | lex->limit_rows_examined= 0; |
1978 | lex->limit_rows_examined_cnt= ULONGLONG_MAX; |
1979 | + lex->set_statement= false; |
1980 | DBUG_VOID_RETURN; |
1981 | } |
1982 | |
1983 | |
1984 | === modified file 'sql/sql_lex.h' |
1985 | --- sql/sql_lex.h 2013-10-29 14:08:44 +0000 |
1986 | +++ sql/sql_lex.h 2013-11-06 17:03:07 +0000 |
1987 | @@ -2382,6 +2382,8 @@ |
1988 | required a local context, the parser pops the top-most context. |
1989 | */ |
1990 | List<Name_resolution_context> context_stack; |
1991 | + /* true if SET STATEMENT ... FOR ... statement is use, false otherwise */ |
1992 | + bool set_statement; |
1993 | |
1994 | SQL_I_List<ORDER> proc_list; |
1995 | SQL_I_List<TABLE_LIST> auxiliary_table_list, save_list; |
1996 | |
1997 | === modified file 'sql/sql_parse.cc' |
1998 | --- sql/sql_parse.cc 2013-11-01 11:00:11 +0000 |
1999 | +++ sql/sql_parse.cc 2013-11-06 17:03:07 +0000 |
2000 | @@ -2201,6 +2201,8 @@ |
2001 | /* */ |
2002 | Rpl_filter *rpl_filter= thd->rpl_filter; |
2003 | #endif |
2004 | + struct system_variables *per_query_variables_backup; |
2005 | + |
2006 | DBUG_ENTER("mysql_execute_command"); |
2007 | #ifdef WITH_PARTITION_STORAGE_ENGINE |
2008 | thd->work_part_info= 0; |
2009 | @@ -2388,6 +2390,21 @@ |
2010 | thd->get_binlog_format(&orig_binlog_format, |
2011 | &orig_current_stmt_binlog_format); |
2012 | |
2013 | + if (lex->set_statement && !lex->var_list.is_empty()) { |
2014 | + per_query_variables_backup= copy_system_variables(&thd->variables); |
2015 | + if ((res= sql_set_variables(thd, &lex->var_list))) |
2016 | + { |
2017 | + /* |
2018 | + We encountered some sort of error, but no message was sent. |
2019 | + Send something semi-generic here since we don't know which |
2020 | + assignment in the list caused the error. |
2021 | + */ |
2022 | + if (!thd->is_error()) |
2023 | + my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET"); |
2024 | + goto error; |
2025 | + } |
2026 | + } |
2027 | + |
2028 | /* |
2029 | Force statement logging for DDL commands to allow us to update |
2030 | privilege, system or statistic tables directly without the updates |
2031 | @@ -2552,6 +2569,12 @@ |
2032 | } |
2033 | case SQLCOM_EXECUTE: |
2034 | { |
2035 | + /* |
2036 | + Deallocate free_list here to avoid assertion failure later in |
2037 | + Prepared_statement::execute_loop |
2038 | + */ |
2039 | + free_items(thd->free_list); |
2040 | + thd->free_list= NULL; |
2041 | mysql_sql_stmt_execute(thd); |
2042 | break; |
2043 | } |
2044 | @@ -5064,6 +5087,29 @@ |
2045 | thd->in_multi_stmt_transaction_mode()); |
2046 | |
2047 | |
2048 | + if (lex->set_statement && !lex->var_list.is_empty()) { |
2049 | + List_iterator_fast<set_var_base> it(thd->lex->var_list); |
2050 | + set_var *var; |
2051 | + |
2052 | + free_system_variables(&thd->variables); |
2053 | + thd->variables= *per_query_variables_backup; |
2054 | + my_free(per_query_variables_backup); |
2055 | + /* |
2056 | + When variables are restored after "SET STATEMENT ... FOR ..." statement |
2057 | + execution an update callback must be invoked for the system variables |
2058 | + to save special logic if it is. set_var_base class does not contain |
2059 | + refference to variable as it is just an interface class. But only |
2060 | + system variables are allowed to be used in "SET STATEMENT ... FOR ..." |
2061 | + statement, so cast from set_var_base* to set_var* can be used here. |
2062 | + */ |
2063 | + while ((var=(set_var *)it++)) |
2064 | + { |
2065 | + var->var->stmt_update(thd); |
2066 | + } |
2067 | + |
2068 | + thd->lex->set_statement= false; |
2069 | + } |
2070 | + |
2071 | if (! thd->in_sub_stmt) |
2072 | { |
2073 | if (thd->killed != NOT_KILLED) |
2074 | |
2075 | === modified file 'sql/sql_plugin.cc' |
2076 | --- sql/sql_plugin.cc 2013-09-21 08:14:42 +0000 |
2077 | +++ sql/sql_plugin.cc 2013-11-06 17:03:07 +0000 |
2078 | @@ -4017,3 +4017,45 @@ |
2079 | return 0; |
2080 | } |
2081 | |
2082 | + |
2083 | +/** |
2084 | + Create deep copy of system_variables instance. |
2085 | +*/ |
2086 | +struct system_variables * |
2087 | +copy_system_variables(const struct system_variables *src) |
2088 | +{ |
2089 | + struct system_variables *dst; |
2090 | + |
2091 | + DBUG_ASSERT(src); |
2092 | + |
2093 | + dst= (struct system_variables *) |
2094 | + my_malloc(sizeof(struct system_variables), MYF(MY_WME | MY_FAE)); |
2095 | + *dst = *src; |
2096 | + |
2097 | + if (dst->dynamic_variables_ptr) |
2098 | + { |
2099 | + dst->dynamic_variables_ptr= |
2100 | + (char *)my_malloc(dst->dynamic_variables_size, MYF(MY_WME | MY_FAE)); |
2101 | + memcpy(dst->dynamic_variables_ptr, |
2102 | + src->dynamic_variables_ptr, |
2103 | + src->dynamic_variables_size); |
2104 | + } |
2105 | + |
2106 | + mysql_mutex_lock(&LOCK_plugin); |
2107 | + dst->table_plugin= |
2108 | + intern_plugin_lock(NULL, src->table_plugin); |
2109 | + mysql_mutex_unlock(&LOCK_plugin); |
2110 | + |
2111 | + return dst; |
2112 | +} |
2113 | + |
2114 | +void free_system_variables(struct system_variables *v) |
2115 | +{ |
2116 | + DBUG_ASSERT(v); |
2117 | + |
2118 | + mysql_mutex_lock(&LOCK_plugin); |
2119 | + intern_plugin_unlock(NULL, v->table_plugin); |
2120 | + mysql_mutex_unlock(&LOCK_plugin); |
2121 | + |
2122 | + my_free(v->dynamic_variables_ptr); |
2123 | +} |
2124 | |
2125 | === modified file 'sql/sql_plugin.h' |
2126 | --- sql/sql_plugin.h 2013-06-03 07:57:34 +0000 |
2127 | +++ sql/sql_plugin.h 2013-11-06 17:03:07 +0000 |
2128 | @@ -178,4 +178,12 @@ |
2129 | int type, uint state_mask, void *arg); |
2130 | extern bool plugin_dl_foreach(THD *thd, const LEX_STRING *dl, |
2131 | plugin_foreach_func *func, void *arg); |
2132 | + |
2133 | +/** |
2134 | + Create deep copy of system_variables instance. |
2135 | +*/ |
2136 | +extern |
2137 | +struct system_variables * |
2138 | +copy_system_variables(const struct system_variables *src); |
2139 | +extern void free_system_variables(struct system_variables *v); |
2140 | #endif |
2141 | |
2142 | === modified file 'sql/sql_yacc.yy' |
2143 | --- sql/sql_yacc.yy 2013-11-02 16:59:16 +0000 |
2144 | +++ sql/sql_yacc.yy 2013-11-06 17:03:07 +0000 |
2145 | @@ -813,8 +813,10 @@ |
2146 | lex->one_shot_set= 0; |
2147 | lex->autocommit= 0; |
2148 | /* get_ptr() is only correct with no lookahead. */ |
2149 | - DBUG_ASSERT(no_lookahead); |
2150 | - lex->sphead->m_tmp_query= lip->get_ptr(); |
2151 | + if (no_lookahead) |
2152 | + lex->sphead->m_tmp_query= lip->get_ptr(); |
2153 | + else |
2154 | + lex->sphead->m_tmp_query= lip->get_tok_end(); |
2155 | /* Inherit from outer lex. */ |
2156 | lex->option_type= old_lex->option_type; |
2157 | } |
2158 | @@ -955,10 +957,10 @@ |
2159 | %parse-param { THD *thd } |
2160 | %lex-param { THD *thd } |
2161 | /* |
2162 | - Currently there are 186 shift/reduce conflicts. |
2163 | + Currently there are 187 shift/reduce conflicts. |
2164 | We should not introduce new conflicts any more. |
2165 | */ |
2166 | -%expect 186 |
2167 | +%expect 187 |
2168 | |
2169 | /* |
2170 | Comments for TOKENS. |
2171 | @@ -1504,6 +1506,7 @@ |
2172 | %token STARTING |
2173 | %token STARTS_SYM |
2174 | %token START_SYM /* SQL-2003-R */ |
2175 | +%token STATEMENT_SYM |
2176 | %token STATS_AUTO_RECALC_SYM |
2177 | %token STATS_PERSISTENT_SYM |
2178 | %token STATS_SAMPLE_PAGES_SYM |
2179 | @@ -14287,6 +14290,7 @@ |
2180 | | SQL_NO_CACHE_SYM {} |
2181 | | SQL_THREAD {} |
2182 | | STARTS_SYM {} |
2183 | + | STATEMENT_SYM {} |
2184 | | STATUS_SYM {} |
2185 | | STORAGE_SYM {} |
2186 | | STRING_SYM {} |
2187 | @@ -14364,8 +14368,36 @@ |
2188 | } |
2189 | start_option_value_list |
2190 | {} |
2191 | + | SET STATEMENT_SYM |
2192 | + { |
2193 | + LEX *lex= Lex; |
2194 | + mysql_init_select(lex); |
2195 | + lex->sql_command= SQLCOM_SET_OPTION; |
2196 | + /* Don't clear var_list in the case of recursive statement */ |
2197 | + if (!lex->set_statement) |
2198 | + lex->var_list.empty(); |
2199 | + lex->one_shot_set= 0; |
2200 | + lex->autocommit= 0; |
2201 | + lex->set_statement= true; |
2202 | + sp_head *sp= lex->sphead; |
2203 | + if (sp && !sp->is_invoked()) |
2204 | + { |
2205 | + sp->m_param_begin= ((yychar == YYEMPTY) ? YYLIP->get_ptr() : YYLIP->get_tok_start()); |
2206 | + sp->m_param_end= ((yychar == YYEMPTY) ? YYLIP->get_ptr() : YYLIP->get_tok_end()); |
2207 | + } |
2208 | + } |
2209 | + set_stmt_option_value_following_option_type_list FOR_SYM statement |
2210 | + {} |
2211 | ; |
2212 | |
2213 | +set_stmt_option_value_following_option_type_list: |
2214 | + /* |
2215 | + Only system variables can be used here. If this condition is changed |
2216 | + please check careful code under lex->option_type == OPT_STATEMENT |
2217 | + condition on wrong type casts. |
2218 | + */ |
2219 | + option_value_following_option_type |
2220 | + | set_stmt_option_value_following_option_type_list ',' option_value_following_option_type |
2221 | |
2222 | // Start of option value list |
2223 | start_option_value_list: |
2224 | @@ -14470,20 +14502,29 @@ |
2225 | { |
2226 | LEX *lex= Lex; |
2227 | |
2228 | - if ($1.var && $1.var != trg_new_row_fake_var) |
2229 | + /* |
2230 | + Ignore SET STATEMENT variables list on slaves because system |
2231 | + variables are not replicated except certain variables set the |
2232 | + values of whose are written to binlog event header and nothing |
2233 | + additional is required to set them. |
2234 | + */ |
2235 | + if (!thd->slave_thread || !lex->set_statement) |
2236 | { |
2237 | - /* It is a system variable. */ |
2238 | - if (set_system_variable(thd, &$1, lex->option_type, $3)) |
2239 | + if ($1.var && $1.var != trg_new_row_fake_var) |
2240 | + { |
2241 | + /* It is a system variable. */ |
2242 | + if (set_system_variable(thd, &$1, lex->option_type, $3)) |
2243 | + MYSQL_YYABORT; |
2244 | + } |
2245 | + else |
2246 | + { |
2247 | + /* |
2248 | + Not in trigger assigning value to new row, |
2249 | + and option_type preceeding local variable is illegal. |
2250 | + */ |
2251 | + my_parse_error(ER(ER_SYNTAX_ERROR)); |
2252 | MYSQL_YYABORT; |
2253 | - } |
2254 | - else |
2255 | - { |
2256 | - /* |
2257 | - Not in trigger assigning value to new row, |
2258 | - and option_type preceeding local variable is illegal. |
2259 | - */ |
2260 | - my_parse_error(ER(ER_SYNTAX_ERROR)); |
2261 | - MYSQL_YYABORT; |
2262 | + } |
2263 | } |
2264 | } |
2265 | ; |