« back to all changes in this revision
Viewing changes to conf/WL5004_sql.yy
- browse files at revision 0.67.9
- compare with another revision
- compare with revision 0.67.10
- download diff
- download tarball
- view history from revision 0.67.9
- Committer: Philip Stoev
- Date: 2009-10-03 12:26:59 UTC
- mto: (0.79.2 randgen-trunk) (2312.4.1 drizzle-randgen)
- mto: This revision was merged to the branch mainline in revision 2435.
- Revision ID: pstoev@mysql.com-20091003122659-q2f7jsoli6ghxx3e
merge from internal tree
- files added:
- conf/backup_interop.yy
- lib/GenTest/Filter
- lib/GenTest/Translator
- files modified:
- combinations.pl
added
removed
conf/WL5004_sql.yy
3
3
# Grammar for testing DML, DDL, FLUSH, LOCK/UNLOCK, transactions
4
4
#
5
5
# Created:
6
# 2009-07 Matthias Leich
7
#
8
# This is a prototype which means it might be incomplete and contain errors.
9
#
10
# General architecture rules by thumb and experiences:
11
# 1. Work on copies of the objects created by gendata.pl
12
# 2. Do not modify the objects created by by gendata.pl
13
# Hereby we prevent that we run out of tables or rows etc.
14
# 3. Use small name spaces for objects (tables etc.) so that we have a significant likelihood
15
# that a statement hits an existing object.
16
# 4. Distinct between two kinds of object name spaces and treat the corresponding objects different.
6
# 2009-07 Matthias Leich (A few grammar items were taken from other grammar files.)
7
#
8
# Attention:
9
# There are modified grammar items because of
10
# - Bug#45143 All connections hang on concurrent ALTER TABLE
11
# - Bug#46339 crash on REPAIR TABLE merge table USE_FRM
12
# - Bug#46425 crash in Diagnostics_area::set_ok_status , empty statement, DELETE IGNORE
13
# - Bug#46198 Hang after failed ALTER TABLE on partitioned table.
14
# Duplicate of Bug#40181 Partitions: hang if create index
15
# This was not fixed in 5.4 but in 6.0.
16
# - Bug#46224 HANDLER statements within a transaction might lead to deadlocks
17
# - Bug#46374 crash, INSERT INTO t1 uses function, function modifies t1
18
# - Bug#47098 assert in MDL_context::destroy on HANDLER <damaged merge table> OPEN
19
# - Bug#47107 assert in notify_shared_lock on incorrect CREATE TABLE , HANDLER
20
# - Bug#46965 crash in ha_innobase::get_auto_increment
21
#
22
# Nothing disabled till now for
23
# - Bug#45966 Crash in MDL_context::release_ticket in .\include\master-slave-reset.inc
24
# - Bug#43867 ALTER TABLE on a partitioned table causes unnecessary deadlocks
25
# - Bug#40419 Not locking metadata on alter procedure
26
# Duplicate of Bug#30977 Concurrent statement using stored function and DROP FUNCTION breaks SBR
27
#
28
#
29
# Bug#45225 Locking: hang if drop table with no timeout
30
# Reporter , LockTableKiller might help
31
#
32
# General architecture rules:
33
# ---------------------------
34
# 1. Do not modify the objects created by gendata.pl within this grammar file.
35
# Work on copies of these objects instead. Hereby we prevent that we totally run out of tables
36
# or rows etc. This minimizes also any locks on the objects created by gendata.pl.
37
# Do not
38
# - have tables of "special" types (partitioned, view, merge etc.)
39
# - variate the storage engine
40
# within your object creation grammar file.
41
# 2. Have separated name spaces for objects (tables etc.) with configurable width.
42
# - This allows to reduce the likelihood of applying a statement in general or an option to
43
# an object which is not allowed. Example: TRUNCATE TABLE <view>
44
# - Debugging of grammar and understanding server logs becomes easier if the name space
45
# for an object of some type contains type related strings like "base","temp",.. etc.
46
# Example: If there is a
47
# CREATE VIEW <name which does not belong into the VIEW name space>
48
# than something works not like intended.
49
# - The configurable name space width (-> $name_space_width) allows to influence the likelihood
50
# that some statement hits an object. This gives some control over how much the system is stressed.
51
# - The non default option to put all table related objects (base tables, views, etc.) allows
52
# some additional increase of the stress though the likelihood of failing statement raises.
53
# 3. Distinct between two kinds of object name spaces and treat the corresponding objects different.
17
54
# This is experimental and might be removed in case it does not fulfill the expectations.
18
# "long life" ("ll")
19
# - sequence: CREATE object, maybe fill in content, wait some time, DROP object
20
# - No other DDL on this object
21
# This should ensure that a thread has a chance to run several DML statements on this object
22
# before it gets dropped.
23
# "short life" ("sl")
24
# - no sequence
25
# - there are single DDL statements which CREATE, ALTER, DROP etc. this object
26
# This should ensure that a thread running a transaction has a chance to meet DDL for this
27
# object sent from another session.
28
# 5. Any statement sequence has to be in one line.
29
# 6. Be carefull when having a ';' before a '|'.
30
# 7. There must be no "superfluous" spaces between the beginning of the line and
31
# the first significant character which of course might be a space.
32
# Typical bad effect: Empty filed or table names, statements consisting of '"' etc.
33
# 8. Strange effect like above? '|' instead of ';' between statements of a sequence?
34
# 9. Names of variables must be surrounded by spaces. Otherwise the name of the variable
35
# and not it's value will be used. -> Problem with SHOW ... LIKE 'table_name'.
36
# 10. There must be no space between grammar element name and the ':'.
37
# Example: "a:" but never "a :".
38
# 11. There is some auxiliary SQL where I did not found a simple pure PERL based solution
39
# a) SET @aux = SLEEP(...) Wait some time
40
# Wait some time
41
# b) SET @aux = 'Fill variable <dollar>table_name1 { $table_name1 = $basetablenamesl } ' ;
42
# Fill the content of $basetablenamesl into $table_name1
43
# SET @var ... is better then SELECT ... because RQG seems to run for every SELECT ...
44
# an additional EXPLAIN SELECT ....
45
# Surrounding text like "FILL VARIABLE ..." should support debugging.
46
# 12. Use uppercase characters for strings in statements. This avoids any not intended treatment
47
# as grammar item.
48
#
49
# Naming convention:
50
# ------------------
51
# t1_0* base table (no merge)
52
# t1_1* temporary base table
53
# t1_2* merge table
54
# t1_3* view
55
# *_*0 "long life" objects
56
# *_*1 "short life" objects
57
58
# Done:
59
# RENAME, DROP, TRUNCATE, SELECT, select SUBQUERY, select JOIN, select UNION
60
#
61
# Missing: LOAD/UNLOAD, DO, HANDLER ....
62
#
63
rand_life_time:
64
# { $rand_life_time = $prng->int(0,10) } ;
65
0.1 ;
66
67
table_name:
68
# Get a random name from the "table" namespace.
69
# "table" name space = UNION of "base table", "temporary base table", "view", "merge table" name spaces
70
# "table" name space = UNION of "base table long life", "base table short life" name spaces
71
{ $tablename = "t1_" . $prng->int(0,3) . $prng->int(0,9) . $prng->int(0,1) } ;
72
#
73
table_name_ll:
74
# Get a random name from the "table long life" namespace.
75
{ $tablenamell = "t1_" . $prng->int(0,3) . $prng->int(0,9) . "0" } ;
76
#
77
table_name_sl:
78
# Get a random name from the "table short life" namespace.
79
{ $tablenamesl = "t1_" . $prng->int(0,3) . $prng->int(0,9) . "1" } ;
80
81
82
########## The base table name space ####################
55
# "Sequence" ("_S"):
56
# - statement sequence: CREATE object, fill in content (if applicable), COMMIT, wait some
57
# random timespan (-> SLEEP( ... * rand_val * $life_time_unit )) , DROP object
58
# - The COMMIT is intentional. It should ensure that the session running the sequence does
59
# not hold any locks on the object during the wait phase. I am aware that CREATE ... AS SELECT
60
# commits at end, but this might be changed somewhere in future.
61
# - the maximum of the random wait timespan is configurable (-> $max_table_life_time).
62
# - The object must be stored within a database created with the "_S" property.
63
# - No other DDL on this object
64
# This should ensure that other sessions have a chance to run several DML statements using this object
65
# before it gets dropped. The "Sequence" objects are of nearly no value when running with only one thread.
66
# "Normal" ("_N"):
67
# - CREATE and DROP for these objects are not within the same sequency.
68
# - Any session could run DDL (including DROP, ALTER, REPAIR etc.) on this object.
69
# - The object can be stored within any database.
70
# - It is assumed that they have a short lifetime.
71
# This should ensure that a session running a transaction with DML on this object has a chance to meet
72
# an attempt of another session to run DDL (especially ALTER) on this object.
73
# 4. There is some "generalization" (I am unsure if this is a good understandable term) of variables and
74
# a corresponding walk up of values.
75
# $database_name_* --> $database_name
76
# $base_table_name_* --> $base_table_name --> $table_name
77
# $temp_table_name_* --> $temp_table_name --> $table_name
78
# ...
79
# $part_table_name_* --> $part_table_name --> $table_name
80
# This means:
81
# If you run "table_item" which picks a table of random type (base table, temp table ...) and random lifetime
82
# and a corresponding database and automatically assigns values to variables ($database_*,$*_table_name_*)
83
# where the name cannot be predicted, you can find the generated names at least within
84
# $database_name and $table_name .
85
# Please be aware that for example a succeeding call of "procedure_item" modify the content of $database_name .
86
#
87
#
88
# Rules by thumb and experiences (important when extending this grammar file):
89
# ----------------------------------------------------------------------------
90
# 1. Any statement sequence has to be in one line.
91
# 2. Be aware of the dual use of ';'. It separates SQL statements in sequences and closes the definition block
92
# of a grammar item. So any ';' before some '|' has a significant impact.
93
# 3. Strange not intended effects: '|' or ':' instead of ';' ?
94
# 4. There is an open RQG problem with SHOW ... LIKE '<grammar item>'.
95
# 5. If there are needs to write some message into a server log than avoid the use of auxiliary SQL (SELECT <message> etc.).
96
# Use something like:
97
# /* <your text> */
98
# instead.
99
# 6. Use uppercase characters for strings and keywords in statements. This avoids any not intended
100
# treatment as grammar item.
101
# 7. Use the most simple option first in lists. This makes automatic grammar simplification
102
# which walks from right to left more efficient. Example:
103
# where:
104
# <empty> | WHERE `pk` BETWEEN _digit AND _digit | WHERE function_name_n() = _digit ;
105
#
106
107
# Naming conventions (default)
108
# ========================================================
109
#
110
# Pattern (standard configuration) | Object
111
# -----------------------------------------
112
# testdb_* | database
113
# t1_* | table/view
114
# p1_* | procedure
115
# f1_* | function
116
# p1_* | procedure
117
# tr1_* | trigger
118
119
# End of grammar item name (default) | characteristic
120
# -------------------------------------------------------
121
# _S | related to "sequence" object
122
# _N | related to "normal" object
123
#
124
# Within grammar item name | characteristic
125
# -----------------------------------------------
126
# _name | name of the object
127
# _item | <schema name> . <name of the object>
128
# _list | either single item (<schema name> . <name of the object>) or comma separated list
129
# | of such items
130
131
#
132
# Missing but not really important improvements:
133
# - Reduce the amount of cases where "sequence" objects have "normal" objects within their definition.
134
# --> views,functions,procedures
135
# - Reduce the amount of cases where the wrong table types occur within object definitions
136
# Example: TABLE for a TRIGGER or VIEW definition. Temporary tables could be computed but are not allowed.
137
138
139
# Section of easy changeable items with high impact on the test =============================================#
140
query_init:
141
# Default setting:
142
# init_basics ; stress_optimization ; event_scheduler_on ; have_some_initial_objects ;
143
init_basics ; collapse_names_for_table_types ; stress_optimization ; event_scheduler_on ; have_some_initial_objects ;
144
# Setting for more stress and but also more statements failing because the target object is of wrong
145
# - table type (Example: VIEW where a base TABLE is expected) -> collapse_names_for_table_types
146
# - object type (Example: TABLE where a PROCEDURE is expected) -> collapse_names_for_objects
147
# init_basics ; collapse_names_for_table_types ; collapse_names_for_objects ; stress_optimization ; have_some_initial_objects ;
148
# You might also try:
149
# init_basics ; collapse_names_for_table_types ; collapse_names_for_normal_seq ; stress_optimization ; have_some_initial_objects ;
150
151
event_scheduler_on:
152
SET GLOBAL EVENT_SCHEDULER = ON;
153
154
event_scheduler_off:
155
SET GLOBAL EVENT_SCHEDULER = OFF;
156
157
init_basics:
158
# This initializes everything for default ( number of threads > 0 ) use.
159
# If this grammar element is in "query_init" than following grammar elements might change these settings.
160
# 1. $life_time_unit = maximum lifetime of a table created within a CREATE, wait, DROP sequence.
161
#
162
# A reasonable value is bigger than any "wait for <whatever> lock" timeout.
163
#
164
# There are till now not sufficient experiences about the impact of different values on the outcome of the test.
165
#
166
# sequence object | lifetime
167
# --------------------------------------------
168
# database | 2 * RAND() * $life_time_unit
169
# table (no view) | 1 * RAND() * $life_time_unit
170
# view | 0.5 * RAND() * $life_time_unit
171
# procedure | 0.5 * RAND() * $life_time_unit
172
# function | 0.5 * RAND() * $life_time_unit
173
# trigger | 0.5 * RAND() * $life_time_unit
174
#
175
# A DML statement using SLEEP will use 0.5 * RAND() * $life_time_unit
176
#
177
# one_thread_correction will correct $life_time_unit to 0 if we have only one "worker" thread.
178
#
179
# 2. $name_space_width = Width of a name space
180
#
181
# Smaller numbers cause a
182
# - lower fraction of statements failing because of missing object
183
# - higher fraction of clashes when running with multiple sessions
184
#
185
{ $life_time_unit = 1 ; $name_space_width = 2 ; return undef } ; separated_name_spaces ; nothing_disabled ; init_object_prefixes ; system_table_stuff ;
186
187
init_object_prefixes:
188
{ $database_prefix="testdb" ; $table_prefix="t1_" ; $procedure_prefix="p1_" ; $function_prefix="f1_" ; $trigger_prefix="tr1_" ; $event_prefix="e1_" ; return undef } ;
189
190
collapse_names_for_objects:
191
{ $database_prefix="o1_" ; $table_prefix="o1_" ; $procedure_prefix="o1_" ; $function_prefix="o1_" ; $trigger_prefix="o1_" ; $event_prefix="o1_" ; return undef } ;
192
193
separated_name_spaces:
194
# Name spaces for
195
# - tables separated by type of table
196
# - objects in general separated by use in CREATE/wait/DROP sequence or not.
197
{ $base_piece="base_" ; $temp_piece="temp_" ; $merge_piece="merge_" ; $part_piece="part_" ; $view_piece="view_" ; $sequence_piece="_S" ; $normal_piece="_N" ; return undef } ;
198
199
collapse_names_for_table_types:
200
# Name spaces for tables not separated by type of table. The part of the name dealing with use around CREATE/DROP will be not modified.
201
# Expected impact:
202
# - maybe higher load on tables of all types in general (depends on size of name space)
203
# - a significant fraction of statements will fail with
204
# 1. 1064 "You have an error in your SQL syntax ..."
205
# Example: TRUNCATE <view>
206
# 2. <number <> 1064> <This option/whatever is not applicable to the current object/situation/whatever>
207
# This might look a bit ugly but it has the benefit that these statements are at least tried.
208
# The goal is not to check the parse process, but there might be temporary MDL locks or in worst
209
# case remaining permanent MDL lock. Effects of these locks should be also checked.
210
# Just as a reminder:
211
# A CREATE VIEW which fails with an error <> "You have an error in your SQL syntax" causes an implicit COMMIT
212
# of the current transaction.
213
{ $base_piece="all_" ; $temp_piece="all_" ; $merge_piece="all_" ; $part_piece="all_" ; $view_piece="all_" ; $sequence_piece="_S" ; $normal_piece="_N" ; return undef } ;
214
215
collapse_names_for_normal_seq:
216
# There are till now no experiences with this setting.
217
{ $sequence_piece="_A" ; $normal_piece="_A" ; return undef } ;
218
219
have_some_initial_objects:
220
# It is assumed that this reduces the likelihood of "Table does not exist" significant when running with a small number of "worker" threads.
221
# The amount of create_..._table items within the some_..._tables should depend a bit on the value in $name_space_width but I currently
222
# do not know how to express this in the grammar.
223
create_database ; some_base_tables ; some_temp_tables ; some_merge_tables ; some_part_tables ; some_view_tables ;
224
# create_database ; some_base_tables ; some_temp_tables ; some_merge_tables ; some_part_tables ; create_view ; create_view ; create_view ;
225
some_base_tables:
226
create_base_table ; create_base_table ; create_base_table ; create_base_table ;
227
some_temp_tables:
228
create_temp_table ; create_temp_table ; create_temp_table ; create_temp_table ;
229
some_merge_tables:
230
create_merge_table ; create_merge_table ; create_merge_table ; create_merge_table ;
231
some_part_tables:
232
create_part_table ; create_part_table ; create_part_table ; create_part_table ;
233
some_view_tables:
234
create_view ; create_view ; create_view ; create_view ;
235
236
stress_optimization:
237
# 1. Disable the CREATE/wait/DROP sequence objects beacuse there is no parallel thread which can use them.
238
# 2. Compact the namespaces by removing the sequence object names
239
{ if ( $ENV{RQG_THREADS} == 1 ) { $life_time_unit = 0 ; $sequence_piece="_A" ; $normal_piece="_A" ; $sequence_begin = "/* Start disabled stuff " ; $sequence_end = " End disabled stuff */"} ; return undef } ;
240
241
nothing_disabled:
242
{ $sequence_begin = "/* Sequence start */" ; $sequence_end = "/* Sequence end */" ; return undef } ;
243
244
system_table_stuff:
245
# This is used in "grant_revoke".
246
CREATE USER otto@localhost;
247
248
249
# Useful grammar items ====================================================================================#
250
251
rand_val:
252
{ $rand_val = $prng->int(0,100) / 100 } ;
253
254
255
# Name spaces of objects ==========================================================================#
256
# An explanation of the name space concept is on top of this file.
257
#
258
# 1. The database name space ##########################################################################
259
database_name_s:
260
{ $database_name_s = $database_prefix . $sequence_piece ; $database_name = $database_name_s } ;
261
database_name_n:
262
{ $database_name_n = $database_prefix . $normal_piece ; $database_name = $database_name_n } ;
263
database_name:
264
# Get a random name from the "database" name space.
265
# $database_name gets automatically filled when database_name_s or database_name_n is executed.
266
database_name_s | database_name_n ;
267
268
269
# 2. The base table name space ########################################################################
270
base_table_name_s:
271
# Get a random name from the "base table long life" name space.
272
{ $base_table_name_s = $table_prefix . $base_piece . $prng->int(1,$name_space_width) . $sequence_piece ; $base_table_name = $base_table_name_s ; $table_name = $base_table_name } ;
273
base_table_name_n:
274
# Get a random name from the "base table short life" name space.
275
{ $base_table_name_n = $table_prefix . $base_piece . $prng->int(1,$name_space_width) . $normal_piece ; $base_table_name = $base_table_name_n ; $table_name = $base_table_name } ;
83
276
base_table_name:
84
277
# Get a random name from the "base table" name space.
85
# "base table" name space = UNION of "base table long life" and "base table short life" name spaces
86
{ $basetablename = "t1_" . "0" . $prng->int(0,9) . $prng->int(0,1) } ;
87
#
88
base_table_name_ll:
89
# Get a random name from the "base table long life" name space.
90
{ $basetablenamell = "t1_" . "0" . $prng->int(0,9) . "0" } ;
91
#
92
base_table_name_sl:
93
# Get a random name from the "base table short life" name space.
94
{ $basetablenamesl = "t1_" . "0" . $prng->int(0,9) . "1" } ;
95
#
96
base_table_list:
97
base_table_name | base_table_name, base_table_name ;
98
base_table_list_ll:
99
base_table_name_ll | base_table_name_ll, base_table_name_ll ;
100
base_table_list_sl:
101
base_table_name_sl | base_table_name_sl, base_table_name_sl ;
102
103
########## The temp table name space ####################
278
base_table_name_s | base_table_name_n ;
279
280
# Sometimes useful stuff:
281
base_table_item_s:
282
database_name_s . base_table_name_s { $base_table_item_s = $database_name_s . " . " . $base_table_name_s ; $base_table_item = $base_table_item_s ; return undef } ;
283
base_table_item_n:
284
database_name . base_table_name_n { $base_table_item_n = $database_name . " . " . $base_table_name_n ; $base_table_item = $base_table_item_n ; return undef } ;
285
base_table_item:
286
base_table_item_s | base_table_item_n ;
287
base_table_item_list_s:
288
base_table_item_s | base_table_item_s , base_table_item_s ;
289
base_table_item_list_n:
290
base_table_item_n | base_table_item_n , base_table_item_n ;
291
base_table_item_list:
292
base_table_item | base_table_item , base_table_item ;
293
294
295
# 3. The temp table name space ########################################################################
296
# Please note that TEMPORARY merge tables will be not generated.
297
temp_table_name_s:
298
# Get a random name from the "temp table long life" name space.
299
{ $temp_table_name_s = $table_prefix . $temp_piece . $prng->int(1,$name_space_width) . $sequence_piece ; $temp_table_name = $temp_table_name_s ; $table_name = $temp_table_name } ;
300
temp_table_name_n:
301
# Get a random name from the "temp table short life" name space.
302
{ $temp_table_name_n = $table_prefix . $temp_piece . $prng->int(1,$name_space_width) . $normal_piece ; $temp_table_name = $temp_table_name_n ; $table_name = $temp_table_name } ;
104
303
temp_table_name:
105
304
# Get a random name from the "temp table" name space.
106
# "temp table" name space = UNION of "temp table long life" and "temp table short life" name spaces
107
{ $temptablename = "t1_" . "1" . $prng->int(0,9) . $prng->int(0,1) } ;
108
#
109
temp_table_name_ll:
110
# Get a random name from the "temp table long life" name space.
111
{ $temptablenamell = "t1_" . "1" . $prng->int(0,9) . "0" } ;
112
#
113
temp_table_name_sl:
114
# Get a random name from the "temp table short life" name space.
115
{ $temptablenamesl = "t1_" . "1" . $prng->int(0,9) . "1" } ;
116
#
117
temp_table_list:
118
temp_table_name | temp_table_name, temp_table_name ;
119
temp_table_list_ll:
120
temp_table_name_ll | temp_table_name_ll, temp_table_name_ll ;
121
temp_table_list_sl:
122
temp_table_name_sl | temp_table_name_sl, temp_table_name_sl ;
123
124
########## The merge table name space ####################
305
temp_table_name_s | temp_table_name_n ;
306
307
# Sometimes useful stuff:
308
temp_table_item_s:
309
database_name_s . temp_table_name_s { $temp_table_item_s = $database_name_s . " . " . $temp_table_name_s ; $temp_table_item = $temp_table_item_s ; return undef };
310
temp_table_item_n:
311
database_name . temp_table_name_n { $temp_table_item_n = $database_name . " . " . $temp_table_name_n ; $temp_table_item = $temp_table_item_n ; return undef };
312
temp_table_item:
313
temp_table_item_s | temp_table_item_n ;
314
temp_table_item_list_s:
315
temp_table_item_s | temp_table_item_s , temp_table_item_s ;
316
temp_table_item_list_n:
317
temp_table_item_n | temp_table_item_n , temp_table_item_n ;
318
temp_table_item_list:
319
temp_table_item | temp_table_item , temp_table_item ;
320
321
322
# 4. The merge table name space #######################################################################
323
# Please note that TEMPORARY merge tables will be not generated.
324
merge_table_name_s:
325
# Get a random name from the "merge table long life" name space.
326
{ $merge_table_name_s = $table_prefix . $merge_piece . $prng->int(1,$name_space_width) . $sequence_piece ; $merge_table_name = $merge_table_name_s ; $table_name = $merge_table_name } ;
327
merge_table_name_n:
328
# Get a random name from the "merge table short life" name space.
329
{ $merge_table_name_n = $table_prefix . $merge_piece . $prng->int(1,$name_space_width) . $normal_piece ; $merge_table_name = $merge_table_name_n ; $table_name = $merge_table_name } ;
125
330
merge_table_name:
126
331
# Get a random name from the "merge table" name space.
127
# "merge table" name space = UNION of "merge table long life" and "merge table short life" name spaces
128
{ $mergetablename = "t1_" . "2" . $prng->int(0,9) . $prng->int(0,1) } ;
129
#
130
merge_table_name_ll:
131
# Get a random name from the "merge table long life" name space.
132
{ $mergetablenamell = "t1_" . "2" . $prng->int(0,9) . "0" } ;
133
#
134
merge_table_name_sl:
135
# Get a random name from the "merge table short life" name space.
136
{ $mergetablenamesl = "t1_" . "2" . $prng->int(0,9) . "1" } ;
137
#
138
merge_table_list:
139
merge_table_name | merge_table_name, merge_table_name ;
140
merge_table_list_ll:
141
merge_table_name_ll | merge_table_name_ll, merge_table_name_ll ;
142
merge_table_list_sl:
143
merge_table_name_sl | merge_table_name_sl, merge_table_name_sl ;
144
145
146
#
147
base_temp_table_name:
148
{ $base_temp_table_name = "t1_" . $prng->int(0,1) . $prng->int(0,9) . $prng->int(0,1) } ;
149
#
150
base_temp_table_name_ll:
151
{ $temptablenamesl = "t1_" . $prng->int(0,1) . $prng->int(0,9) . "0" } ;
152
#
153
base_temp_table_name_sl:
154
{ $temptablenamesl = "t1_" . $prng->int(0,1) . $prng->int(0,9) . "1" } ;
155
#
156
base_temp_table_list:
157
base_temp_table_name | base_temp_table_name, base_temp_table_name ;
158
#
159
base_temp_table_list_ll:
160
base_temp_table_name_ll | base_temp_table_name_ll, base_temp_table_name_ll ;
161
#
162
base_temp_table_list_sl:
163
base_temp_table_name_sl | base_temp_table_name_sl, base_temp_table_name_sl ;
164
#
165
166
view_name:
167
# Get a random name from the "view table" name space.
168
# "view table" name space = UNION of "view table long life" and "view table short life" name spaces
169
{ $viewname = "t1_" . "3" . $prng->int(0,9) . $prng->int(0,1) } ;
170
#
171
view_name_ll:
332
merge_table_name_s | merge_table_name_n ;
333
334
# Sometimes useful stuff:
335
merge_table_item_s:
336
database_name_s . merge_table_name_s { $merge_table_item_s = $database_name_s . " . " . $merge_table_name_s ; $merge_table_item = $merge_table_item_s ; return undef };
337
merge_table_item_n:
338
database_name . merge_table_name_n { $merge_table_item_n = $database_name . " . " . $merge_table_name_n ; $merge_table_item = $merge_table_item_n ; return undef };
339
merge_table_item:
340
merge_table_item_s | merge_table_item_n ;
341
merge_table_item_list_s:
342
merge_table_item_s | merge_table_item_s , merge_table_item_s ;
343
merge_table_item_list_n:
344
merge_table_item_n | merge_table_item_n , merge_table_item_n ;
345
merge_table_item_list:
346
merge_table_item | merge_table_item , merge_table_item ;
347
348
349
# 5. The view table name space ########################################################################
350
view_table_name_s:
172
351
# Get a random name from the "view table long life" name space.
173
{ $viewnamell = "t1_" . "3" . $prng->int(0,9) . "0" } ;
174
#
175
view_name_sl:
352
{ $view_table_name_s = $table_prefix . $view_piece . $prng->int(1,$name_space_width) . $sequence_piece ; $view_table_name = $view_table_name_s ; $table_name = $view_table_name } ;
353
view_table_name_n:
176
354
# Get a random name from the "view table short life" name space.
177
{ $viewnamesl = "t1_" . "3" . $prng->int(0,9) . "1" } ;
178
355
{ $view_table_name_n = $table_prefix . $view_piece . $prng->int(1,$name_space_width) . $normal_piece ; $view_table_name = $view_table_name_n ; $table_name = $view_table_name } ;
356
view_table_name:
357
# Get a random name from the "view table" name space.
358
view_table_name_s | view_table_name_n ;
359
360
# Sometimes useful stuff:
361
view_table_item_s:
362
database_name_s . view_table_name_s { $view_table_item_s = $database_name_s . " . " . $view_table_name_s ; $view_table_item = $view_table_item_s ; return undef };
363
view_table_item_n:
364
database_name . view_table_name_n { $view_table_item_n = $database_name . " . " . $view_table_name_n ; $view_table_item = $view_table_item_n ; return undef };
365
view_table_item:
366
view_table_item_s | view_table_item_n ;
367
view_table_item_list_s:
368
view_table_item_s | view_table_item_s , view_table_item_s ;
369
view_table_item_list_n:
370
view_table_item_n | view_table_item_n , view_table_item_n ;
371
view_table_item_list:
372
view_table_item | view_table_item , view_table_item ;
373
374
375
# 6. The partitioned table name space #################################################################
376
part_table_name_s:
377
# Get a random name from the "part table long life" name space.
378
{ $part_table_name_s = $table_prefix . $part_piece . $prng->int(1,$name_space_width) . $sequence_piece ; $part_table_name = $part_table_name_s ; $table_name = $part_table_name } ;
379
part_table_name_n:
380
# Get a random name from the "part table short life" name space.
381
{ $part_table_name_n = $table_prefix . $part_piece . $prng->int(1,$name_space_width) . $normal_piece ; $part_table_name = $part_table_name_n ; $table_name = $part_table_name } ;
382
part_table_name:
383
# Get a random name from the "part table" name space.
384
part_table_name_s | part_table_name_n ;
385
386
# Sometimes useful stuff:
387
part_table_item_s:
388
database_name_s . part_table_name_s { $part_table_item_s = $database_name_s . " . " . $part_table_name_s ; $part_table_item = $part_table_item_s ; return undef };
389
part_table_item_n:
390
database_name . part_table_name_n { $part_table_item_n = $database_name . " . " . $part_table_name_n ; $part_table_item = $part_table_item_n ; return undef };
391
part_table_item:
392
part_table_item_s | part_table_item_n ;
393
part_table_item_list_s:
394
part_table_item_s | part_table_item_s , part_table_item_s ;
395
part_table_item_list_n:
396
part_table_item_n | part_table_item_n , part_table_item_n ;
397
part_table_item_list:
398
part_table_item | part_table_item , part_table_item ;
399
400
401
# 7. Mixed name spaces of tables ################################################################
402
403
# 7.1 All tables ( base/temp/merge tables + views + ... #########################################
404
table_item_s:
405
base_table_item_s | temp_table_item_s | merge_table_item_s | view_table_item_s | part_table_item_s ;
406
table_item_n:
407
base_table_item_n | temp_table_item_n | merge_table_item_n | view_table_item_n | part_table_item_n ;
408
table_item:
409
table_item_s | table_item_n ;
410
411
table_list:
412
# Less likelihood for lists, because they
413
# - are most probably less often used
414
# - cause a higher likelihood of "table does not exist" errors.
415
table_item | table_item | table_item | table_item | table_item | table_item | table_item | table_item | table_item |
416
table_item , table_item ;
417
418
419
# 7.2 All tables but no views #######################################################################
420
table_no_view_item_s:
421
base_table_item_s | temp_table_item_s | merge_table_item_s | part_table_item_s ;
422
table_no_view_item_n:
423
base_table_item_n | temp_table_item_n | merge_table_item_n | part_table_item_n ;
424
table_no_view_item:
425
table_no_view_item_s | table_no_view_item_n ;
426
427
428
# 7.3 All base and temp tables + views ##############################################################
429
# These grammar elements is used to avoid some partioning related bugs.
430
base_temp_view_table_item_s:
431
base_table_item_s | temp_table_item_s | view_table_item_s | part_table_item_s ;
432
base_temp_view_table_item_n:
433
base_table_item_n | temp_table_item_n | view_table_item_n | part_table_item_n ;
434
base_temp_view_table_item:
435
base_temp_view_table_item_s | base_temp_view_table_item ;
436
437
438
# 8. Other name spaces ###############################################################
179
439
template_table_name:
180
# Get the name of one of the template tables
181
{ $templatetablename = $prng->arrayElement($executors->[0]->tables()) } ;
182
440
# Get the name of one of the template tables which were generated via gendata.pl.
441
{ $template_table_name = $prng->arrayElement($executors->[0]->tables()) } ;
442
template_table_item:
443
test . template_table_name { $template_table_item = "test . " . $template_table_name ; return undef } ;
444
445
446
procedure_name_s:
447
# Get a random name from the "procedure long life" namespace.
448
{ $procedure_name_s = $procedure_prefix . $prng->int(1,$name_space_width) . $sequence_piece ; $procedure_name = $procedure_name_s } ;
449
procedure_name_n:
450
# Get a random name from the "procedure short life" namespace.
451
{ $procedure_name_n = $procedure_prefix . $prng->int(1,$name_space_width) . $normal_piece ; $procedure_name = $procedure_name_n } ;
183
452
procedure_name:
184
453
# Get a random name from the "procedure" namespace.
185
# "procedure" name space = UNION of "procedure long life", "procedure short life" name spaces
186
{ $procedurename = "p1_" . $prng->int(0,9) . $prng->int(0,1) } ;
187
188
procedure_name_ll:
189
# Get a random name from the "procedure long life" namespace.
190
{ $procedurenamell = "p1_" . $prng->int(0,9) . "0" } ;
191
192
procedure_name_sl:
193
# Get a random name from the "procedure short life" namespace.
194
{ $procedurenamesl = "p1_" . $prng->int(0,9) . "1" } ;
195
454
procedure_name_s | procedure_name_n ;
455
456
# Sometimes useful stuff:
457
procedure_item_s:
458
database_name_s . procedure_name_s { $procedure_item_s = $database_name_s . " . " . $procedure_name_s ; $procedure_item = $procedure_item_s ; return undef } ;
459
procedure_item_n:
460
database_name . procedure_name_n { $procedure_item_n = $database_name . " . " . $procedure_name_n ; $procedure_item = $procedure_item_n ; return undef } ;
461
procedure_item:
462
procedure_item_s | procedure_item_n ;
463
464
function_name_s:
465
# Get a random name from the "function long life" namespace.
466
{ $function_name_s = $function_prefix . $prng->int(1,$name_space_width) . $sequence_piece ; $function_name = $function_name_s } ;
467
function_name_n:
468
# Get a random name from the "function short life" namespace.
469
{ $function_name_n = $function_prefix . $prng->int(1,$name_space_width) . $normal_piece ; $function_name = $function_name_n } ;
196
470
function_name:
197
471
# Get a random name from the "function" namespace.
198
# "function" name space = UNION of "function long life", "function short life" name spaces
199
{ $functionname = "f1_" . $prng->int(0,9) . $prng->int(0,1) } ;
200
201
function_name_ll:
202
# Get a random name from the "function long life" namespace.
203
{ $functionnamell = "f1_" . $prng->int(0,9) . "0" } ;
204
205
function_name_sl:
206
# Get a random name from the "function short life" namespace.
207
{ $functionnamesl = "f1_" . $prng->int(0,9) . "1" } ;
208
472
function_name_s | function_name_n ;
473
474
function_item_s:
475
database_name_s . function_name_s { $function_item_s = $database_name_s . " . " . $function_name_s ; $function_item = $function_item_s ; return undef } ;
476
function_item_n:
477
database_name . function_name_n { $function_item_n = $database_name . " . " . $function_name_n ; $function_item = $function_item_n ; return undef };
478
function_item:
479
function_item_s | function_item_n ;
480
481
trigger_name_s:
482
# Get a random name from the "trigger long life" namespace.
483
{ $trigger_name_s = $trigger_prefix . $prng->int(1,$name_space_width) . $sequence_piece ; $trigger_name = $trigger_name_s } ;
484
trigger_name_n:
485
# Get a random name from the "trigger short life" namespace.
486
{ $trigger_name_n = $trigger_prefix . $prng->int(1,$name_space_width) . $normal_piece ; $trigger_name = $trigger_name_n } ;
209
487
trigger_name:
210
488
# Get a random name from the "trigger" namespace.
211
# "trigger" name space = UNION of "trigger long life", "trigger short life" name spaces
212
{ $triggername = "tr1_" . $prng->int(0,9) . $prng->int(0,1) } ;
213
214
trigger_name_ll:
215
# Get a random name from the "trigger long life" namespace.
216
{ $triggernamell = "tr1_" . $prng->int(0,9) . "0" } ;
217
218
trigger_name_sl:
219
# Get a random name from the "trigger short life" namespace.
220
{ $triggernamesl = "tr1_" . $prng->int(0,9) . "1" } ;
489
trigger_name_s | trigger_name_n ;
490
491
trigger_item_s:
492
database_name_s . trigger_name_s { $trigger_item_s = $database_name_s . " . " . $trigger_name_s ; $trigger_item = $trigger_item_s ; return undef };
493
trigger_item_n:
494
database_name . trigger_name_n { $trigger_item_n = $database_name . " . " . $trigger_name_n ; $trigger_item = $trigger_item_n ; return undef };
495
trigger_item:
496
trigger_item_s | trigger_item_n ;
497
498
event_name_s:
499
# Get a random name from the "event long life" namespace.
500
{ $event_name_s = $event_prefix . $prng->int(1,$name_space_width) . $sequence_piece ; $event_name = $event_name_s } ;
501
event_name_n:
502
# Get a random name from the "event short life" namespace.
503
{ $event_name_n = $event_prefix . $prng->int(1,$name_space_width) . $normal_piece ; $event_name = $event_name_n } ;
504
event_name:
505
# Get a random name from the "event" namespace.
506
event_name_s | event_name_n ;
507
508
event_item_s:
509
database_name_s . event_name_s { $event_item_s = $database_name_s . " . " . $event_name_s ; $event_item = $event_item_s ; return undef };
510
event_item_n:
511
database_name . event_name_n { $event_item_n = $database_name . " . " . $event_name_n ; $event_item = $event_item_n ; return undef };
512
event_item:
513
event_item_s | event_item_n ;
514
515
# Here starts the core of the test grammar ========================================================#
221
516
222
517
query:
223
ddl | dml | transaction | show ;
224
# ddl | dml | lock | transaction ;
225
# ddl | dml | lock | transaction | flush;
226
# FLUSH TABLES WITH/(without) READ LOCK removed because of Bug#45066
518
# Use if
519
# Bug#46224 HANDLER statements within a transaction might lead to deadlocks
520
# is fixed (various -> handler removed)
521
# Handler lets currently also runs with one worker session hang.
522
# dml | dml | dml | dml | ddl | transaction | lock_unlock | flush | handler ;
523
dml | dml | dml | dml | ddl | transaction | lock_unlock | flush ;
524
525
########## TRANSACTIONS ####################
227
526
228
527
transaction:
229
START TRANSACTION | COMMIT | ROLLBACK | SAVEPOINT A | ROLLBACK TO SAVEPOINT A ;
528
start_transaction | commit | rollback |
529
start_transaction | commit | rollback |
530
start_transaction | commit | rollback |
531
SAVEPOINT savepoint_id | RELEASE SAVEPOINT savepoint_id | ROLLBACK work_or_empty TO savepoint_or_empty savepoint_id |
532
BEGIN work_or_empty | set_autocommit | kill_query_or_session ;
533
# No impact on mdl.cc , lock.cc ..... set_isolation_level ;
534
535
set_isolation_level:
536
SET SESSION TX_ISOLATION = TRIM(' isolation_level ');
537
538
isolation_level:
539
REPEATABLE-READ | READ-COMMITTED | SERIALIZABLE ;
540
541
542
start_transaction:
543
START TRANSACTION with_consistent_snapshot ;
544
with_consistent_snapshot:
545
| | | | | | | | | WITH CONSISTENT SNAPSHOT ;
546
547
commit:
548
COMMIT work_or_empty chain release ;
549
rollback:
550
ROLLBACK work_or_empty chain release ;
551
chain:
552
| | | | AND no_or_empty CHAIN ;
553
release:
554
| | | | | | | | | no_or_empty RELEASE ;
555
556
set_autocommit:
557
SET AUTOCOMMIT = zero_or_one ;
558
559
kill_query_or_session:
560
lower_id ; KILL query_or_session @kill_id |
561
own_id ; KILL query_or_session @kill_id |
562
higher_id ; KILL query_or_session @kill_id ;
563
# Note(mleich):
564
# 1. The scenario of a KILL failing because the session does not exist is covered by:
565
# a) Current session id is minimum or maximum of all existing id's and lower_id or higher_id
566
# computes NULL as value for @kill_id. "KILL NULL" gets valuated as "KILL 0".
567
# b) A parallel session kills the session with the just computed id.
568
# 2. It is intentional that I do not use "KILL _digit".
569
# In case the RQG test crashes in a scenario with thread=1, it is very likely that during analysis
570
# the test gets converted to a script for mysqltest. Most probably the mysqltest simplifier gets
571
# applied and than it is very likely that the session id's change. The use of a computation
572
# based on CONNECTION_ID(), MIN and MAX leads to more stable results.
573
lower_id:
574
SELECT MAX(id) INTO @kill_id FROM information_schema.processlist WHERE id < CONNECTION_ID();
575
own_id:
576
SET @kill_id = CONNECTION_ID() ;
577
higher_id:
578
SELECT MIN(id) INTO @kill_id FROM information_schema.processlist WHERE id > CONNECTION_ID();
579
query_or_session:
580
# MLML QUERY | QUERY | ;
581
;
582
583
savepoint_id:
584
A | B ;
230
585
231
586
ddl:
232
TRUNCATE TABLE base_table_name_sl |
233
create_base_temp_table |
234
drop_base_temp_table |
235
alter_base_temp_table |
236
rename_base_temp_table |
237
base_table_sequence |
238
create_merge_table |
239
drop_merge_table |
240
alter_merge_table |
241
merge_table_sequence |
242
create_view |
243
drop_view |
244
alter_view |
245
rename_view |
246
view_sequence |
247
create_procedure |
248
drop_procedure |
249
alter_procedure |
250
procedure_sequence |
251
create_function |
252
drop_function |
253
alter_function |
254
function_sequence |
255
create_trigger |
256
drop_trigger |
257
trigger_sequence |
258
analyze_table |
259
optimize_table |
260
checksum_table |
261
check_table |
262
repair_table ;
263
587
database_ddl |
588
base_table_ddl | base_table_ddl | base_table_ddl |
589
temp_table_ddl | temp_table_ddl | temp_table_ddl |
590
merge_table_ddl | merge_table_ddl | merge_table_ddl |
591
part_table_ddl | part_table_ddl | part_table_ddl |
592
view_ddl | view_ddl | view_ddl |
593
procedure_ddl | procedure_ddl | procedure_ddl |
594
function_ddl | function_ddl | function_ddl |
595
trigger_ddl | trigger_ddl | trigger_ddl |
596
event_ddl |
597
truncate_table |
598
drop_table_list |
599
rename_table |
600
table_maintenance_ddl |
601
dump_load_data_sequence |
602
grant_revoke |
603
rename_column |
604
sql_mode ;
605
# "dump_load_data_sequence" with SELECT ... INTO OUTFILE ...; LOAD DATA ... INFILE
606
# consists more of DML statements, but we place this here under "ddl" because the
607
# statements in "dml" are often executed as prepared statements. And the text after
608
# PREPARE st1 FOR must not contain multiple statements.
609
610
611
########## HANDLER ####################
612
handler:
613
handler_open | handler_read | handler_close ;
614
615
handler_open:
616
HANDLER table_no_view_item OPEN with_alias ;
617
618
with_alias:
619
| as A ;
620
621
handler_read:
622
# The use of indexes is omitted
623
HANDLER table_no_view_item READ FIRST handler_read_part ;
624
handler_read_part:
625
| where ;
626
627
handler_close:
628
HANDLER table_no_view_item CLOSE ;
629
630
631
########## SHOW ####################
632
# We run here only object related SHOW commands except SHOW STATUS which checks counters
633
# of OPEN tables etc.
264
634
show:
265
show_tables |
266
show_create_table |
267
show_table_status |
268
show_columns |
269
show_create_view |
270
show_create_function |
271
show_function_code |
272
show_function_status |
273
show_create_procedure |
274
show_procedure_code |
275
show_procedure_status |
276
show_triggers |
277
show_create_trigger ;
635
database_show |
636
table_show |
637
routine_show |
638
SHOW STATUS ;
639
640
database_show:
641
show_databases | show_create_database ;
642
643
show_databases:
644
SHOW databases_schemas ;
645
databases_schemas:
646
DATABASES | SCHEMAS ;
647
648
show_create_database:
649
SHOW CREATE database_schema database_name ;
650
651
#----------------------------------
652
653
table_show:
654
show_tables | show_tables |
655
show_table_status | show_table_status |
656
show_create_table | show_create_view |
657
show_open_tables | show_columns ;
278
658
279
659
show_tables:
280
SHOW TABLES show_tables_part ;
281
#
282
show_tables_part:
283
# FIXME: Add "WHERE"
284
| like_table_name ;
285
#
286
like_table_name:
287
# all tables, base table name space | temp table name space | .....
288
# Attention: LIKE 'table_name' does not work.
289
LIKE 't1_0%' | LIKE 't1_1%' | LIKE 't1_2%' | LIKE 't1_3%' ;
290
#
660
SHOW TABLES;
661
291
662
show_create_table:
292
663
# Works also for views
293
SHOW CREATE TABLE table_name ;
294
#
295
#
664
SHOW CREATE TABLE table_item ;
665
666
show_open_tables:
667
SHOW OPEN TABLES IN database_name ;
668
296
669
show_table_status:
297
670
# Works also for views
298
# FIXME: Add "WHERE"
299
SHOW TABLE STATUS show_table_status_part ;
300
#
301
show_table_status_part:
302
# FIXME: Add "WHERE"
303
| like_table_name ;
304
#
671
SHOW TABLE STATUS;
672
305
673
show_columns:
306
SHOW full COLUMNS from_in table_name show_columns_part ;
307
#
674
SHOW full COLUMNS from_in table_item show_columns_part ;
308
675
full:
309
| | FULL ;
310
#
676
# Only 20 %
677
| | | | FULL ;
311
678
from_in:
312
FROM | IN ;
679
FROM | IN ;
313
680
show_columns_part:
314
681
# Attention: LIKE '_field' does not work, because RQG does not expand _field.
315
682
# LIKE '%int%' does not work, because RQG expands it to something like LIKE '%822214656%'.
316
683
# FIXME: Add "WHERE"
317
| LIKE '%INT%' ;
318
#
319
#
684
| LIKE '%INT%' ;
685
320
686
show_create_view:
321
SHOW CREATE VIEW view_name ;
322
#
323
#
687
SHOW CREATE VIEW view_table_item ;
688
689
#----------------------------------
690
routine_show:
691
show_create_function | show_function_code | show_function_status |
692
show_create_procedure | show_procedure_code | show_procedure_status |
693
show_triggers | show_create_trigger |
694
show_events | show_create_event ;
695
324
696
show_create_function:
325
SHOW CREATE FUNCTION function_name ;
697
SHOW CREATE FUNCTION function_item ;
698
326
699
show_function_code:
327
SHOW FUNCTION CODE function_name ;
700
SHOW FUNCTION CODE function_item ;
701
328
702
show_function_status:
329
SHOW FUNCTION STATUS show_function_status_part ;
330
show_function_status_part:
331
# FIXME: Add "WHERE"
332
| LIKE 'f1_%' ;
333
#
334
#
703
SHOW FUNCTION STATUS;
704
335
705
show_create_procedure:
336
SHOW CREATE PROCEDURE procedure_name ;
706
SHOW CREATE PROCEDURE procedure_item ;
707
337
708
show_procedure_code:
338
SHOW PROCEDURE CODE procedure_name ;
709
SHOW PROCEDURE CODE procedure_item ;
710
339
711
show_procedure_status:
340
SHOW PROCEDURE STATUS show_procedure_status_part ;
341
show_procedure_status_part:
342
# FIXME: Add "WHERE"
343
| LIKE 'p1_%' ;
344
#
345
#
346
#
712
SHOW PROCEDURE STATUS;
713
347
714
show_triggers:
348
SHOW TRIGGERS show_triggers_part ;
349
show_triggers_part:
350
# FIXME: Add "WHERE"
351
| LIKE 'tr1_%';
715
SHOW TRIGGERS;
352
716
353
717
show_create_trigger:
354
SHOW CREATE TRIGGER trigger_name ;
355
356
357
########## Base an temporary tables ####################
358
create_base_temp_table:
359
CREATE TABLE base_table_name_sl create_table_part |
360
CREATE TEMPORARY TABLE temp_table_name_sl create_table_part ;
718
SHOW CREATE TRIGGER trigger_item ;
719
720
show_events:
721
SHOW EVENTS from_in database_name ;
722
723
show_create_event:
724
SHOW CREATE EVENT event_item_s ;
725
726
########## SELECTS ON THE INFORMATION_SCHEMA ####################
727
# We run here only object related SELECTs.
728
is_selects:
729
is_schemata | is_tables ;
730
is_schemata:
731
/* database_name */ SELECT * FROM information_schema . schemata WHERE schema_name = TRIM(' $database_name ');
732
is_tables:
733
/* table_item */ SELECT * FROM information_schema . tables WHERE table_schema = TRIM(' $database_name ') AND table_name = TRIM(' $table_name ');
734
is_columns:
735
/* table_item */ SELECT * FROM information_columns . tables WHERE table_schema = TRIM(' $database_name ') AND table_name = TRIM(' $table_name ') AND column_name = TRIM(' _field ');
736
# 19.1. The INFORMATION_SCHEMA SCHEMATA Table
737
# 19.2. The INFORMATION_SCHEMA TABLES Table
738
# 19.3. The INFORMATION_SCHEMA COLUMNS Table
739
# 19.4. The INFORMATION_SCHEMA STATISTICS Table
740
# 19.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table
741
# 19.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
742
# 19.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
743
# 19.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
744
# 19.9. The INFORMATION_SCHEMA CHARACTER_SETS Table
745
# 19.10. The INFORMATION_SCHEMA COLLATIONS Table
746
# 19.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
747
# 19.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
748
# 19.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
749
# 19.14. The INFORMATION_SCHEMA ROUTINES Table
750
# 19.15. The INFORMATION_SCHEMA VIEWS Table
751
# 19.16. The INFORMATION_SCHEMA TRIGGERS Table
752
# 19.17. The INFORMATION_SCHEMA PLUGINS Table
753
# 19.18. The INFORMATION_SCHEMA ENGINES Table
754
# 19.19. The INFORMATION_SCHEMA PARTITIONS Table
755
# 19.20. The INFORMATION_SCHEMA EVENTS Table
756
# 19.21. The INFORMATION_SCHEMA FILES Table
757
# 19.22. The INFORMATION_SCHEMA TABLESPACES Table
758
# 19.23. The INFORMATION_SCHEMA PROCESSLIST Table
759
# 19.24. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
760
# 19.25. The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
761
# 19.26. The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
762
# 19.27. The INFORMATION_SCHEMA PARAMETERS Table
763
# 19.28. The INFORMATION_SCHEMA PROFILING Table
764
# 19.29. Other INFORMATION_SCHEMA Tables
361
765
#
766
767
768
########## DATABASE ####################
769
database_ddl:
770
create_database | create_database | create_database |
771
drop_database | alter_database |
772
database_sequence ;
773
774
create_database:
775
CREATE database_schema if_not_exists database_name_n database_spec ;
776
777
database_schema:
778
DATABASE | SCHEMA ;
779
780
database_spec:
781
# We do not want to test CHARACTER SETs and COLLATIONs, but we need something for ALTER DATABASE.
782
default_word CHARACTER SET equal utf8 | default_word COLLATE equal utf8_bin ;
783
784
drop_database:
785
DROP database_schema if_exists database_name_n ;
786
787
alter_database:
788
ALTER database_schema database_name_n database_spec ;
789
790
database_sequence:
791
# Have a bigger lifetime for databases because the objects with extended lifetime are stored there.
792
$sequence_begin CREATE database_schema database_name_s ; wait_till_drop_database ; DROP database_schema $database_name_s $sequence_end ;
793
wait_till_drop_database:
794
SELECT SLEEP( 2 * rand_val * $life_time_unit ) ;
795
796
797
########## BASE AND TEMPORARY TABLES ####################
798
base_table_ddl:
799
create_base_table | create_base_table | create_base_table | create_base_table | create_base_table | create_base_table |
800
drop_base_table | alter_base_table |
801
base_table_sequence ;
802
803
create_base_table:
804
CREATE TABLE if_not_exists base_table_item_n create_table_part ;
362
805
create_table_part:
363
LIKE template_table_name |
364
AS used_select ;
365
#
366
#
367
drop_base_temp_table:
368
DROP TABLE base_table_list_ll |
369
DROP TEMPORARY TABLE temp_table_list_sl ;
370
#
371
#
372
alter_base_temp_table:
373
ALTER ignore TABLE base_temp_table_name_sl alter_base_temp_table_part ;
374
# "online" removed because of Bug#45143
375
#
806
LIKE template_table_item ; ALTER TABLE $base_table_item_n ENGINE = engine ; INSERT INTO $base_table_item_n SELECT * FROM $template_table_item |
807
AS used_select ;
808
809
drop_base_table:
810
# DROP two tables is in "drop_table_list"
811
DROP TABLE if_exists base_table_item_n restrict_cascade ;
812
813
alter_base_table:
814
ALTER online ignore TABLE base_table_item_n alter_base_temp_table_part ;
815
376
816
alter_base_temp_table_part:
377
ENGINE = engine |
378
COMMENT = 'UPDATED NOW()' ;
379
#
380
#
817
# Reasons why "ENGINE = engine" should be rather rare:
818
# 1. ALTER ... ENGINE = <engine> is rather rare within a production system running under DML load
819
# 2. ALTER ... ENGINE = <engine != MyISAM> "damages" any MERGE table using the affected table as base table.
820
# As a consequence nerly all statements on the MERGE table will fail.
821
COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' |
822
COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' |
823
ENGINE = engine ;
824
825
base_table_sequence:
826
$sequence_begin CREATE TABLE if_not_exists base_table_item_s LIKE template_table_item ; ALTER TABLE $base_table_item_s ENGINE = engine ; INSERT INTO $base_table_item_s SELECT * FROM $template_table_item ; COMMIT ; wait_till_drop_table ; DROP TABLE $base_table_item_s $sequence_end ;
827
828
wait_till_drop_table:
829
SELECT SLEEP( rand_val * $life_time_unit ) ;
830
831
temp_table_ddl:
832
# Attention: temp_table_sequence is intentionally omitted, because no other session will be
833
# able to use this table.
834
create_temp_table | create_temp_table | create_temp_table | create_temp_table | create_temp_table | create_temp_table |
835
drop_temp_table | alter_temp_table ;
836
837
create_temp_table:
838
CREATE TEMPORARY TABLE if_not_exists temp_table_item_n create_table_part ;
839
840
drop_temp_table:
841
# DROP two tables is in "drop_table_list"
842
# A pure DROP TABLE is allowed, but we get no implicit COMMITs for that.
843
DROP TEMPORARY TABLE if_exists temp_table_item_n |
844
DROP TABLE if_exists temp_table_item_n ;
845
846
alter_temp_table:
847
ALTER online ignore TABLE temp_table_item_n alter_base_temp_table_part ;
848
849
########## MAINTENANCE FOR ANY TABLE ####################
850
# The server accepts these statements for all table types (VIEWs, base tables, ...) though they
851
# should have partially no effect. We run them on all table types because everything which gets
852
# accepted has to be checked even if the command should do nothing.
853
# Example:
854
# OPTIMIZE ... TABLE <view> ...
855
# Table Op Msg_type Msg_text
856
# test.v1 optimize Error Table 'test.v1' doesn't exist
857
# test.v1 optimize status Operation failed
858
# OPTIMIZE ... TABLE <merge table> ...
859
# Table Op Msg_type Msg_text
860
# test.t1m optimize note The storage engine for the table doesn't support optimize
861
#
862
table_maintenance_ddl:
863
analyze_table | optimize_table | checksum_table | check_table | repair_table ;
864
381
865
analyze_table:
382
ANALYZE not_to_binlog_local TABLE base_table_list ;
383
#
866
ANALYZE not_to_binlog_local TABLE table_list ;
384
867
not_to_binlog_local:
385
| NO_WRITE_TO_BINLOG | LOCAL ;
386
#
868
| NO_WRITE_TO_BINLOG | LOCAL ;
869
387
870
optimize_table:
388
# VIEW : Statement not disallowed but server response is
389
# Table Op Msg_type Msg_text
390
# test.v1 optimize Error Table 'test.v1' doesn't exist
391
# test.v1 optimize status Operation failed
392
# MERGE TABLE : Statement allowed, but no efffect.
393
# Table Op Msg_type Msg_text
394
# +test.t1m optimize note The storage engine for the table doesn't support optimize
395
# -> We run this for all table types.
396
OPTIMIZE not_to_binlog_local TABLE base_table_list ;
397
#
871
OPTIMIZE not_to_binlog_local TABLE table_list ;
872
398
873
checksum_table:
399
CHECKSUM TABLE base_table_list quick_extended ;
400
#
874
CHECKSUM TABLE table_list quick_extended ;
401
875
quick_extended:
402
| QUICK | EXTENDED ;
403
#
876
| quick | extended ;
404
877
extended:
878
# Only 10 %
405
879
| | | | | | | | | EXTENDED ;
406
# Only 10 %
407
#
880
408
881
check_table:
409
CHECK TABLE base_table_list check_table_options ;
410
#
882
CHECK TABLE table_list check_table_options ;
411
883
check_table_options:
412
884
| FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED ;
413
#
414
#
885
415
886
repair_table:
416
REPAIR not_to_binlog_local TABLE base_table_list quick extended use_frm ;
417
#
418
rename_base_temp_table:
419
# RENAME TABLE works also on VIEWs but we do not generate it here.
420
# FIXME: Reduce the redundancy if possible.
421
RENAME TABLE base_table_name_sl TO base_table_name_sl |
422
RENAME TABLE base_table_name_sl TO base_table_name_sl, base_table_name_sl TO base_table_name_sl |
423
RENAME TABLE base_table_name_sl TO base_table_name_sl |
424
RENAME TABLE base_table_name_sl TO base_table_name_sl, temp_table_name_sl TO temp_table_name_sl ;
425
#
426
#
427
base_table_sequence:
428
CREATE TABLE base_table_name_ll LIKE template_table_name ; INSERT INTO $basetablenamell SELECT * FROM $templatetablename ; COMMIT ; SET @aux = SLEEP( rand_life_time ) ; DROP TABLE $basetablenamell |
429
CREATE TABLE base_table_name_ll AS used_select ; SET @aux = SLEEP( rand_life_time ) ; DROP TABLE $basetablenamell ;
430
#
431
432
########## Merge table ####################
887
# Use if
888
# Bug#46339 crash on REPAIR TABLE merge table USE_FRM
889
# is fixed (base_temp_table_view_list instead of table_list because of Bug#46339)
890
# REPAIR not_to_binlog_local TABLE table_list quick extended use_frm ;
891
REPAIR not_to_binlog_local TABLE table_list quick extended ;
892
893
use_frm:
894
# Only 10 %
895
| | | | | | | | | USE_FRM ;
896
897
898
########## MIXED TABLE RELATED DDL #################################
899
truncate_table:
900
TRUNCATE table_word table_no_view_item_n ;
901
table_word:
902
| TABLE ;
903
904
drop_table_list:
905
# DROP one table is in "drop_*table"
906
# 1. We mix here all tables except VIEWs up.
907
# 2. We have an increased likelihood that the statement fails because of use of
908
# - "temporary" (only correct in case of a temporary table)
909
# - two tables (some might not exist)
910
DROP temporary TABLE if_exists table_no_view_item_n , table_no_view_item_n restrict_cascade ;
911
912
rename_table:
913
# RENAME TABLE works also on all types of tables (includes VIEWs)
914
RENAME TABLE rename_item_list ;
915
rename_item_list:
916
rename_item | rename_item , rename_item ;
917
rename_item:
918
# Preserve the object type (base,temp,....) and type (Normal) otherwise debugging becomes difficult and
919
# the concept with different lifetimes gets broken.
920
base_table_item_n TO base_table_item_n |
921
temp_table_item_n TO temp_table_item_n |
922
merge_table_item_n TO merge_table_item_n |
923
part_table_item_n TO part_table_item_n ;
924
925
rename_column:
926
ALTER TABLE table_no_view_item_s CHANGE COLUMN column_to_change my_column INT |
927
ALTER TABLE table_no_view_item_s CHANGE COLUMN my_column column_to_change INT;
928
929
column_to_change:
930
`int` | `int_key` | `pk` ;
931
932
933
########## MERGE TABLE DDL ####################
934
merge_table_ddl:
935
create_merge_table | create_merge_table | create_merge_table | create_merge_table | create_merge_table | create_merge_table |
936
drop_merge_table | alter_merge_table |
937
merge_table_sequence ;
938
433
939
create_merge_table:
434
940
# There is a high risk that the tables which we pick for merging do not fit together because they
435
941
# have different structures. We try to reduce this risk to end up with no merge table at all
439
945
# 2. Let the merge table be based on the first base table.
440
946
# ALTER TABLE <merge table> ENGINE = MERGE UNION(<first base table>)
441
947
# 3. Add the second base table to the merge table.
442
# ALTER TABLE <merge table> UNION(<first base table>, <second merge table>)
443
pick_table_name1 ; pick_table_name2 ; CREATE TABLE merge_table_name_sl LIKE $table_name1 ; convert_to_merge_table ;
444
pick_table_name1:
445
# Fill $table_name1
446
SET @aux = 'MERGE TABLE WILL USE base_table_name_sl FOR THE BASE'; SET @aux = 'FILL VARIABLE <dollar>table_name1 { $table_name1 = $basetablenamesl } ' ;
447
pick_table_name2:
448
# Fill $table_name2
449
SET @aux = 'MERGE TABLE WILL USE base_table_name_sl FOR THE BASE'; SET @aux = 'FILL VARIABLE <dollar>table_name2 { $table_name2 = $basetablenamesl } ' ;
450
convert_to_merge_table:
451
ALTER TABLE $mergetablenamesl ENGINE = MERGE UNION ( $table_name1 ) ; ALTER TABLE $mergetablenamesl ENGINE = MERGE UNION ( $table_name1 , $table_name2 ) insert_method ;
948
# ALTER TABLE <merge table> UNION(<first base table>, <second merge table>)
949
merge_init_n build_partner1 ; build_partner2 ; create_merge ;
950
452
951
insert_method:
453
| INSERT_METHOD = insert_method_value | INSERT_METHOD = insert_method_value | | INSERT_METHOD = insert_method_value ;
952
| INSERT_METHOD = insert_method_value | INSERT_METHOD = insert_method_value | INSERT_METHOD = insert_method_value ;
454
953
insert_method_value:
455
NO | FIRST | LAST ;
954
NO | FIRST | LAST ;
955
456
956
drop_merge_table:
457
DROP TABLE merge_table_name_sl ;
957
# DROP two tables is in "drop_table_list"
958
DROP TABLE if_exists merge_table_item_n ;
959
458
960
merge_table_sequence:
459
961
# Notes:
460
962
# There is a significant likelihood that a random picked table names as base for the merge table cannot
461
963
# be used for the creation of a merge table because the corresponding tables
462
964
# - must exist
463
965
# - use the storage engine MyISAM
464
# - have the same layout.
966
# - have the same layout
465
967
# Therefore we create here all we need.
466
# But the use of "base_table_name_sl" for the tables to be merged guarantees that these tables
968
# The use of "base_table_name_n" for the tables to be merged guarantees that these tables
467
969
# are under full DDL/DML load.
468
# I do not DROP the underlying tables at sequence end because I hope that "drop_base_temp_table" will do this sooner or later.
469
pick_template_name ; create_table1_for_merging ; create_table2_for_merging ; create_merge_table_ll ;
470
create_table1_for_merging:
471
pick_table_name1 ; CREATE TABLE $table_name1 LIKE $templatetablename ; ALTER TABLE $table_name1 ENGINE = MyISAM ; INSERT INTO $table_name1 SELECT * FROM $templatetablename ;
472
create_table2_for_merging:
473
pick_table_name2 ; CREATE TABLE $table_name2 LIKE $templatetablename ; ALTER TABLE $table_name2 ENGINE = MyISAM ; INSERT INTO $table_name2 SELECT * FROM $templatetablename ;
474
create_merge_table_ll:
475
CREATE TABLE merge_table_name_ll LIKE $templatetablename ; ALTER TABLE $mergetablenamell ENGINE = MERGE UNION ( $table_name1 , $table_name2 ) insert_method ;
476
pick_template_name:
477
# Fill $templatetablename
478
SET @aux = 'WILL USE template_table_name AS TEMPLATE FOR MERGE TABLES' ;
479
#
480
#
970
# I do not DROP the underlying tables at sequence end because I hope that "drop_base_table" or similar will do this sooner or later.
971
$sequence_begin merge_init_s build_partner1 ; build_partner2 ; create_merge ; wait_till_drop_table ; DROP TABLE $mt $sequence_end ;
972
481
973
alter_merge_table:
482
# "online" removed because of Bug#45143
483
# We do not chenage here the UNION because of the high risk that this fails.
484
# And we have already ALTER ... UNION within convert_to_merge_table. There the base tables could be already under DML/DDL load.
485
ALTER ignore TABLE merge_table_name_ll COMMENT = 'UPDATED NOW()' ;
486
487
########## Views ####################
974
# We do not change here the UNION because of the high risk that this fails.
975
# A simple change of the insert_method_value is also not doable because we
976
# would need to mention also the UNION.
977
# It is intentional that we use merge_table_name and not merge_table_name_n.
978
ALTER online ignore TABLE merge_table_item_n COMMENT = 'UPDATED NOW()' ;
979
980
merge_init_s:
981
/* merge_table_item_s { $mt = $merge_table_item_s ; return undef } consists of ( base_table_item_s { $mp1 = $base_table_item_s ; return undef } , base_table_item_s { $mp2 = $base_table_item_s ; return undef } ) based on template_table_item */ ;
982
merge_init_n:
983
/* merge_table_item_n { $mt = $merge_table_item_n ; return undef } consists of ( base_table_item_n { $mp1 = $base_table_item_n ; return undef } , base_table_item_n { $mp2 = $base_table_item_n ; return undef } ) based on template_table_item */ ;
984
build_partner1:
985
# This also initializes $database_name and $base_table_name which gets used by the other commands within the sequence.
986
CREATE TABLE if_not_exists $mp1 LIKE $template_table_item ; ALTER TABLE $mp1 ENGINE = MyISAM ; INSERT INTO $mp1 SELECT * FROM $template_table_item ;
987
build_partner2:
988
# This also initializes $database_name and $base_table_name which gets used by the other commands within the sequence.
989
CREATE TABLE if_not_exists $mp2 LIKE $template_table_item ; ALTER TABLE $mp2 ENGINE = MyISAM ; INSERT INTO $mp2 SELECT * FROM $template_table_item ;
990
create_merge:
991
CREATE TABLE if_not_exists $mt LIKE $template_table_item ; ALTER TABLE $mt ENGINE = MERGE UNION ( $mp1 , $mp2 ); COMMIT ;
992
993
994
########## PARTITIONED TABLE DDL ####################
995
part_table_ddl:
996
create_part_table | create_part_table | create_part_table | create_part_table | create_part_table | create_part_table |
997
drop_part_table |
998
# If
999
# Bug#46198 Hang after failed ALTER TABLE on partitioned table.
1000
# is fixed (alter_part_table removed because of Bug#46198)
1001
# alter_part_table |
1002
part_table_sequence ;
1003
1004
create_part_table:
1005
# We do not need sophisticated partitioning here.
1006
CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM PARTITION BY KEY (pk) PARTITIONS 2 AS used_select ;
1007
1008
drop_part_table:
1009
# DROP two tables is in "drop_table_list"
1010
DROP TABLE if_exists part_table_item_n ;
1011
1012
alter_part_table:
1013
ALTER online ignore TABLE part_table_item_n alter_part_table_part ;
1014
1015
alter_part_table_part:
1016
PARTITION BY LINEAR HASH(pk) PARTITIONS 3 |
1017
COMMENT = 'UPDATED NOW()' ;
1018
1019
part_table_sequence:
1020
$sequence_begin CREATE TABLE if_not_exists part_table_item_s AS SELECT * FROM template_table_item ; COMMIT ; wait_till_drop_table ; DROP TABLE $part_table_item_s $sequence_end ;
1021
1022
1023
########## VIEW DDL ####################
1024
view_ddl:
1025
create_view | create_view | create_view | create_view | create_view | create_view | create_view | create_view |
1026
drop_view | alter_view |
1027
view_sequence ;
1028
488
1029
create_view:
489
CREATE ALGORITHM = view_algoritm VIEW view_name_sl AS used_select ;
490
#
1030
CREATE view_replace ALGORITHM = view_algoritm VIEW view_table_item_n AS used_select ;
1031
view_replace:
1032
# Only 20 %
1033
| | | | OR REPLACE ;
491
1034
view_algoritm:
492
1035
UNDEFINED | MERGE | TEMPTABLE ;
493
#
494
view_replace:
495
| | OR REPLACE ;
496
#
497
#
1036
498
1037
drop_view:
499
DROP VIEW view_list restrict_cascade ;
500
#
501
view_list:
502
view_name_sl | view_name_sl, view_name_sl ;
503
#
1038
DROP VIEW if_exists view_table_item_n restrict_cascade ;
1039
504
1040
restrict_cascade:
505
1041
# RESTRICT and CASCADE, if given, are parsed and ignored.
506
1042
| RESTRICT | CASCADE ;
507
#
508
#
1043
509
1044
alter_view:
510
1045
# Attention: Only changing the algorithm is not allowed.
511
ALTER ALGORITHM = view_algoritm VIEW view_name_sl AS used_select ;
512
#
513
#
514
rename_view:
515
# RENAME TABLE works also on VIEWs as long as the SCHEMA is not changed.
516
# RENAME VIEW does not exist.
517
RENAME TABLE view_name_sl TO view_name_sl |
518
RENAME TABLE view_name_sl TO view_name_sl, view_name_sl TO view_name_sl ;
519
#
520
#
1046
ALTER ALGORITHM = view_algoritm VIEW view_table_item_n AS used_select ;
1047
521
1048
view_sequence:
522
CREATE ALGORITHM = view_algoritm VIEW view_name_ll AS used_select ; SET @aux = SLEEP( rand_life_time ) ; DROP VIEW $viewnamell ;
523
524
525
########## Stored procedure ####################
1049
$sequence_begin CREATE ALGORITHM = view_algoritm VIEW view_table_item_s AS used_select ; COMMIT ; SELECT wait_short ; DROP VIEW $view_table_item_s $sequence_end ;
1050
1051
1052
########## STORED PROCEDURE DDL ####################
1053
procedure_ddl:
1054
create_procedure | create_procedure |
1055
drop_procedure | alter_procedure |
1056
procedure_sequence ;
1057
526
1058
create_procedure:
527
CREATE PROCEDURE procedure_name_sl () BEGIN proc_stmt ; proc_stmt ; END ;
528
#
1059
CREATE PROCEDURE procedure_item_n () BEGIN proc_stmt ; proc_stmt ; END ;
1060
proc_stmt:
1061
select | update ;
1062
529
1063
drop_procedure:
530
DROP PROCEDURE IF EXISTS procedure_name_sl ;
531
#
532
proc_stmt:
533
select | insert ;
534
#
1064
DROP PROCEDURE if_exists procedure_item_n ;
1065
535
1066
alter_procedure:
536
ALTER PROCEDURE procedure_name_sl COMMENT 'UPDATED NOW()';
537
#
1067
ALTER PROCEDURE procedure_item_n COMMENT 'UPDATED NOW()' ;
1068
538
1069
procedure_sequence:
539
CREATE PROCEDURE procedure_name_ll () BEGIN proc_stmt ; proc_stmt ; END ; SET @aux = SLEEP( rand_life_time ) ; DROP PROCEDURE $procedurenamell ;
540
541
########## Function ####################
1070
# FIXME: The PROCEDURE should touch base_table_name_s only .
1071
$sequence_begin CREATE PROCEDURE procedure_item_s () BEGIN proc_stmt ; proc_stmt ; END ; COMMIT ; SELECT wait_short ; DROP PROCEDURE $procedure_item_s $sequence_end ;
1072
1073
1074
########## STORED FUNCTION DDL ####################
1075
function_ddl:
1076
create_function | create_function |
1077
drop_function | alter_function |
1078
function_sequence ;
1079
542
1080
create_function:
543
CREATE FUNCTION function_name_sl () RETURNS INTEGER BEGIN func_statement ; func_statement ; RETURN 1 ; END ;
544
1081
CREATE FUNCTION function_item_n () RETURNS INTEGER BEGIN func_statement ; func_statement ; RETURN 1 ; END ;
545
1082
func_statement:
546
1083
# All result sets of queries within a function must be processed within the function.
547
1084
# -> Use a CURSOR or SELECT ... INTO ....
548
insert | delete | SELECT MAX(_field) FROM table_name INTO @aux ;
549
550
# RETURNS CHAR(50) DETERMINISTIC
551
# -> RETURN CONCAT('Hello, ',s,'!') ;
1085
# Use if
1086
# Bug#46374 crash, INSERT INTO t1 uses function, function modifies t1
1087
# is fixed (insert, delete removed because of Bug#46374)
1088
# SET @my_var = 1 | SELECT MAX( _field ) FROM table_item INTO @my_var | insert | delete ;
1089
SET @my_var = 1 | SELECT MAX( _field ) FROM table_item INTO @my_var ;
552
1090
553
1091
drop_function:
554
DROP FUNCTION function_name_sl ;
555
#
1092
DROP FUNCTION if_exists function_item_n ;
1093
556
1094
alter_function:
557
ALTER FUNCTION function_name_sl COMMENT 'UPDATED NOW()' ;
558
#
1095
ALTER FUNCTION function_item_n COMMENT 'UPDATED NOW()' ;
1096
559
1097
function_sequence:
560
CREATE FUNCTION function_name_ll () RETURNS INTEGER RETURN ( SELECT MOD(COUNT(DISTINCT _field),10) FROM base_table_name_sl ) ; SET @aux = SLEEP( rand_life_time ) ; DROP PROCEDURE $functionnamell ;
561
562
########## Trigger ####################
1098
$sequence_begin CREATE FUNCTION function_item_s () RETURNS INTEGER RETURN ( SELECT MOD( COUNT( DISTINCT _field ) , 10 ) FROM table_item_s ) ; COMMIT ; SELECT wait_short ; DROP FUNCTION $function_item_s $sequence_end ;
1099
1100
1101
########## TRIGGER DDL ####################
1102
trigger_ddl:
1103
create_trigger | create_trigger |
1104
drop_trigger |
1105
trigger_sequence ;
1106
563
1107
create_trigger:
564
CREATE TRIGGER trigger_name_sl trigger_time trigger_event ON base_table_name_sl FOR EACH ROW BEGIN trigger_action ; END ;
565
#
1108
CREATE TRIGGER trigger_item_n trigger_time trigger_event ON base_table_name_n FOR EACH ROW BEGIN trigger_action ; END ;
566
1109
trigger_time:
567
1110
BEFORE | AFTER ;
568
#
569
1111
trigger_event:
570
1112
INSERT | DELETE ;
571
#
572
1113
trigger_action:
573
insert | replace | delete | update | call procedure_name ;
574
#
575
#
1114
insert | replace | delete | update | CALL procedure_item ;
1115
576
1116
drop_trigger:
577
DROP TRIGGER trigger_name_sl ;
578
#
1117
DROP TRIGGER if_exists trigger_item_n ;
1118
579
1119
trigger_sequence:
580
CREATE TRIGGER trigger_name_ll trigger_time trigger_event ON base_table_name_sl FOR EACH ROW BEGIN trigger_action ; END ; SET @aux = SLEEP( rand_life_time ) ; DROP TRIGGER $triggernamell ;
581
1120
# FIXME: The action within the trigger should touch base_table_name_s only.
1121
$sequence_begin CREATE TRIGGER trigger_item_s trigger_time trigger_event ON table_item_s FOR EACH ROW BEGIN trigger_action ; END ; COMMIT ; SELECT wait_short ; DROP TRIGGER $trigger_item_s $sequence_end ;
1122
1123
1124
########## EVENT DDL ####################
1125
event_ddl:
1126
create_event | create_event | create_event | create_event | create_event | create_event | create_event | create_event |
1127
drop_event | alter_event | drop_event | alter_event | drop_event | alter_event | drop_event | alter_event |
1128
event_scheduler_on | event_scheduler_off ;
1129
create_event:
1130
CREATE EVENT if_not_exists event_item_s ON SCHEDULE EVERY 10 SECOND STARTS NOW() ENDS NOW() + INTERVAL 21 SECOND completion_handling DO SELECT * FROM table_item LIMIT 1;
1131
completion_handling:
1132
ON COMPLETION not_or_empty PRESERVE ;
1133
drop_event:
1134
DROP EVENT if_exists event_item_s ;
1135
alter_event:
1136
ALTER EVENT event_item_s COMMENT 'UPDATED NOW()';
1137
1138
########## DML ####################
582
1139
583
1140
dml:
584
PREPARE st1 FROM " dml2 " ; EXECUTE st1 ; DEALLOCATE PREPARE st1 |
585
dml2 ;
1141
# Have only 10 % prepared statements.
1142
# SQL Statements to be handled via PREPARE, EXECUTE and DEALLOCATE cause a bigger amount of
1143
# failing statements than SQL statements which are executed in non prepared mode.
1144
# The reason is that we run the EXECUTE and DEALLOCATE independent of the outcome of the
1145
# PREPARE. So if the PREPARE fails because some table is missing, we loose the old
1146
# prepared statement handle, if there was any, and get no new one. Therefore the succeeding
1147
# EXECUTE and DEALLOCATE will also failcw because of missing statement handle.
1148
dml2 | dml2 | dml2 | dml2 | dml2 | dml2 | dml2 | dml2 | dml2 |
1149
PREPARE st1 FROM " dml2 " ; EXECUTE st1 ; DEALLOCATE PREPARE st1 ;
586
1150
587
1151
dml2:
588
select | insert | replace | delete | update | call procedure_name | show ;
589
1152
select | select | select |
1153
do | insert | replace | delete | update | CALL procedure_item | show | is_selects ;
1154
1155
########## DO ####################
1156
do:
1157
# A lot options like HIGH_PRIORITY etc. are not allowed in connection with DO.
1158
# The SELECT must give one column.
1159
SELECT COUNT(*) FROM table_item WHERE `pk` BETWEEN _digit[invariant] AND _digit[invariant] + 20 ;
1160
1161
########## SELECT ####################
590
1162
select:
1163
select_normal | select_normal | select_normal | select_normal | select_with_sleep ;
1164
1165
select_normal:
591
1166
# select = Just a query = A statement starting with "SELECT".
592
select_part1 addition into for_update_lock_in_share_mode ;
1167
select_part1 addition into for_update_lock_in_share_mode ;
1168
1169
select_with_sleep:
1170
# Run a SELECT which holds locks (if there are any) longer.
1171
SELECT 1 FROM table_item WHERE wait_short = 0 LIMIT 1;
593
1172
594
1173
used_select:
595
# used_select = The SELECT used in CREATE VIEW/TABLE ... AS <SELECT>, INSERT INTO ... SELECT
1174
# used_select = The SELECT used in CREATE VIEW/TABLE ... AS SELECT, INSERT INTO ... SELECT
596
1175
# "PROCEDURE ANALYSE" and "INTO DUMPFILE/OUTFILE/@var" are not generated because they
597
1176
# are partially disallowed or cause garbage (PROCEDURE).
598
select_part1 addition_no_procedure for_update_lock_in_share_mode ;
1177
select_part1 addition_no_procedure ;
599
1178
600
1179
select_part1:
601
SELECT high_priority cache_results * FROM first_table_in_select AS A ;
602
603
high_priority:
604
| | | | HIGH_PRIORITY;
605
# Only 20 %
1180
SELECT high_priority cache_results table_field_list FROM table_in_select as A ;
606
1181
607
1182
cache_results:
608
| | | | SQL_CACHE | | | | | SQL_NO_CACHE ;
609
# Only 20 %
1183
| sql_no_cache | sql_cache ;
1184
sql_no_cache:
1185
# Only 10 %
1186
| | | | | | | | | SQL_NO_CACHE ;
1187
sql_cache:
1188
# Only 10 %
1189
| | | | | | | | | SQL_CACHE ;
610
1190
611
first_table_in_select:
612
# Attention: Derived tables are disallowed in views. Therefore they should be rare.
613
table_name | table_name | table_name | table_name | table_name | (SELECT * FROM table_name) ;
1191
table_in_select:
1192
# Attention: In case of CREATE VIEW a subquery in the FROM clause (derived table) is disallowed.
1193
# Therefore they should be rare.
1194
table_item | table_item | table_item | table_item | table_item | ( SELECT table_field_list FROM table_item ) ;
614
1195
615
1196
addition:
616
1197
# Involve one (simple where condition) or two tables (subquery | join | union)
617
where procedure | subquery procedure | join where procedure | procedure union where ;
1198
where procedure_analyze | subquery procedure_analyze | join where procedure_analyze | procedure_analyze union where ;
618
1199
619
1200
addition_no_procedure:
620
1201
# Involve one (simple where condition) or two tables (subquery | join | union)
621
# Don't add procedure.
622
where | subquery | join where | union where ;
1202
# Don't add procedure_analyze.
1203
where | where | where | where | where | where | where |
1204
subquery | join where | union where ;
623
1205
624
1206
where:
625
| WHERE `pk` BETWEEN _digit AND _digit | WHERE function_name_sl() = _digit ;
1207
# The very selective condition is intentional.
1208
# It should ensure that
1209
# - result sets (just SELECT) do not become too big because this affects the performance in general and
1210
# the memery consumption of RQG (I had a ~ 3.5 GB virt memory RQG perl process during some simplifier run!)
1211
# - tables (INSERT ... SELECT, REPLACE) do not become too big
1212
# - tables (DELETE) do not become permanent empty
1213
# Please not that there are some cases where LIMIT cannot be used.
1214
# mleich: Temporary omit functions
1215
# WHERE `pk` BETWEEN _digit[invariant] AND _digit[invariant] + 1 | WHERE function_item () = _digit AND `pk` = _digit ;
1216
WHERE `pk` BETWEEN _digit[invariant] AND _digit[invariant] + 1 ;
626
1217
627
1218
union:
628
UNION SELECT * FROM table_name AS B;
1219
UNION SELECT * FROM table_in_select as B ;
629
1220
630
1221
join:
631
1222
# Do not place a where condition here.
632
NATURAL JOIN table_name B ;
1223
NATURAL JOIN table_item B ;
633
1224
634
1225
subquery:
635
1226
correlated | non_correlated ;
636
637
1227
subquery_part1:
638
WHERE A.`pk` IN (SELECT `pk` FROM table_name AS B WHERE B.`pk` = ;
639
1228
WHERE A.`pk` IN ( SELECT `pk` FROM table_item AS B WHERE B.`pk` = ;
640
1229
correlated:
641
1230
subquery_part1 A.`pk` ) ;
642
# WHERE A.`pk` IN (SELECT `pk` FROM table_name AS B WHERE A.`pk` = B.`pk`);
643
644
1231
non_correlated:
645
1232
subquery_part1 _digit ) ;
646
# WHERE A.`pk` IN (SELECT `pk` FROM table_name AS B WHERE A.`pk` = _digit);
647
648
procedure:
649
# procedure disabled because of Bug#46184 Crash, SELECT ... FROM derived table procedure analyze
650
# | | | | | | | | | PROCEDURE ANALYSE(10, 2000);
651
# Only 10 %
652
;
653
654
# Correct place of PROCEDURE ANALYSE(10, 2000)
1233
1234
procedure_analyze:
1235
# Correct place of PROCEDURE ANALYSE( 10 , 2000 )
655
1236
# 0. Attention: The result set of the SELECT gets replaced by PROCEDURE ANALYSE output.
656
1237
# 1. WHERE ... PROCEDURE (no UNION of JOIN)
657
1238
# 2. SELECT ... PROCEDURE UNION SELECT ... (never after UNION)
662
1243
# 6. CREATE TABLE ... AS SELECT PROCEDURE -> The table contains the PROCEDURE result set.
663
1244
# 7. INSERT ... SELECT ... PROCEDURE -> It's tried to INSERT the PROCEDURE result set.
664
1245
# High likelihood of ER_WRONG_VALUE_COUNT_ON_ROW
1246
# Only 10 %
1247
| | | | | | | | | PROCEDURE ANALYSE( 10 , 2000 ) ;
665
1248
666
1249
into:
667
| | | | | | | | | | INTO into_object;
668
1250
# Only 10 %
1251
| | | | | | | | | INTO into_object ;
669
1252
670
1253
into_object:
671
1254
# INSERT ... SELECT ... INTO DUMPFILE/OUTFILE/@var is not allowed
674
1257
# -> @_letter disabled till I find a solution.
675
1258
# 2. DUMPFILE requires a result set of one row
676
1259
# Therefore 1172 Result consisted of more than one row is very likely.
677
# OUTFILE _tmpnam | DUMPFILE _tmpnam | @_letter;
1260
# OUTFILE _tmpnam | DUMPFILE _tmpnam | @_letter ;
678
1261
OUTFILE _tmpnam ;
679
1262
680
1263
for_update_lock_in_share_mode:
681
| | | | | | | | | | | | | | | | | | FOR UPDATE | LOCK IN SHARE MODE ;
682
1264
| for_update | lock_share ;
1265
for_update:
1266
# Only 10 %
1267
| | | | | | | | | FOR UPDATE ;
1268
lock_share:
1269
# Only 10 %
1270
| | | | | | | | | LOCK IN SHARE MODE ;
1271
1272
1273
########## INSERT ####################
683
1274
insert:
684
# FIXME: Case with one assigned row is missing.
685
INSERT low_priority_delayed_high_priority INTO table_name used_select on_duplicate_key_update ;
686
# INSERT low_priority_delayed_high_priority INTO table_name ( _field ) used_select on_duplicate_key_update |
687
# INSERT low_priority_delayed_high_priority INTO table_name ( _field ) VALUES ( _digit ) on_duplicate_key_update ;
688
1275
insert_normal | insert_normal | insert_normal | insert_normal | insert_with_sleep ;
1276
insert_normal:
1277
INSERT low_priority_delayed_high_priority into_word table_item simple_or_complicated on_duplicate_key_update ;
1278
simple_or_complicated:
1279
( _field ) VALUES ( digit_or_null ) |
1280
braced_table_field_list used_select LIMIT 1 ;
1281
on_duplicate_key_update:
1282
# Only 10 %
1283
| | | | | | | | | ON DUPLICATE KEY UPDATE _field = _digit ;
1284
insert_with_sleep:
1285
INSERT INTO table_item ( _field_list[invariant] ) SELECT _field_list[invariant] FROM table_item WHERE wait_short = 0 LIMIT 1;
1286
1287
1288
########## REPLACE ####################
689
1289
replace:
690
1290
# 1. No ON DUPLICATE .... option. In case of DUPLICATE key it runs DELETE old row INSERT new row.
691
1291
# 2. HIGH_PRIORITY is not allowed
692
# FIXME: Case with one assigned row is missing.
693
REPLACE low_priority_delayed INTO table_name used_select ;
694
# REPLACE low_priority_delayed INTO table_name ( _field ) VALUES ( _digit ) ;
695
696
on_duplicate_key_update:
697
| | | | | | | | | ON DUPLICATE KEY UPDATE _field = _digit ;
698
# Only 10 %
699
1292
REPLACE low_priority_delayed into_word table_item simple_or_complicated ;
1293
1294
1295
########## DUMP_LOAD_DATA ####################
1296
dump_load_data_sequence:
1297
# We omit a lot stuff which could be assigned after the table name. This stuff should
1298
# be important for locking tests.
1299
# We generate an outfile so that we have a chance to find an infile.
1300
# Go with the next command as soon as "LOCAL" is supported. (not supported in 5.4)
1301
# generate_outfile ; LOAD DATA low_priority_concurrent local_or_empty INFILE tmpnam replace_ignore INTO TABLE table_item ;
1302
generate_outfile ; LOAD DATA low_priority_concurrent INFILE tmpnam replace_ignore INTO TABLE table_item ;
1303
generate_outfile:
1304
SELECT * FROM template_table_name INTO OUTFILE _tmpnam ;
1305
low_priority_concurrent:
1306
| low_priority | concurrent ;
1307
concurrent:
1308
# Only 20 % <> empty.
1309
| | | | CONCURRENT ;
1310
replace_ignore:
1311
| replace_option | ignore ;
1312
1313
1314
########## GRANT_REVOKE ####################
1315
# We mix here some trouble I can imagine on mysql.tables_priv. It's basically how we access it's content.
1316
grant_revoke:
1317
GRANT ALL ON table_item TO otto@localhost |
1318
REVOKE ALL ON table_item FROM otto@localhost |
1319
SELECT COUNT(*) FROM mysql.tables_priv WHERE user = LOWER('OTTO') |
1320
DELETE FROM mysql.tables_priv WHERE user = LOWER('OTTO') ; FLUSH PRIVILEGES |
1321
/* table_item */ INSERT INTO mysql.tables_priv (host,db,user,table_name,grantor,table_priv) VALUES (LOWER('LOCALHOST'),TRIM(' $database '),LOWER('OTTO'),TRIM(' $table_name '),LOWER('ROOT@LOCALHOST'),'Select') ; FLUSH PRIVILEGES |
1322
SELECT COUNT(*) FROM information_schema.table_privileges WHERE grantee LIKE '%OTTO%' |
1323
SHOW GRANTS FOR otto@localhost ;
1324
1325
########## SQL MODE ########################
1326
sql_mode:
1327
empty_mode | empty_mode | empty_mode | empty_mode |
1328
empty_mode | empty_mode | empty_mode | empty_mode |
1329
empty_mode | empty_mode | empty_mode | empty_mode |
1330
traditional_mode ;
1331
empty_mode:
1332
SET SESSION SQL_MODE='' ;
1333
traditional_mode:
1334
SET SESSION SQL_MODE=LOWER('TRADITIONAL');
1335
1336
1337
########## DELETE ####################
700
1338
delete:
701
# LIMIT row_count is disallowed in case we have multi table delete.
702
# Example: DELETE low_priority quick ignore A , B FROM table_name AS A join where LIMIT _digit |
1339
delete_normal | delete_normal | delete_normal | delete_normal | delete_with_sleep ;
1340
delete_normal:
1341
# LIMIT row_count is disallowed in case we have a multi table delete.
1342
# Example: DELETE low_priority quick ignore A , B FROM table_item AS A join where LIMIT _digit |
703
1343
# DELETE is ugly because a table alias is not allowed.
704
DELETE low_priority quick ignore FROM table_name WHERE `pk` > _digit LIMIT _digit |
705
DELETE low_priority quick ignore A , B FROM table_name AS A join where |
706
DELETE low_priority quick ignore A FROM table_name AS A where_subquery ;
707
1344
# If
1345
# Bug#46425 crash in Diagnostics_area::set_ok_status , empty statement, DELETE IGNORE
1346
# is fixed (ignore removed because of Bug#46425)
1347
# DELETE low_priority quick ignore FROM table_item WHERE `pk` > _digit LIMIT 1 |
1348
# DELETE low_priority quick ignore A , B FROM table_item AS A join where |
1349
# DELETE low_priority quick ignore A FROM table_item AS A where_subquery ;
1350
DELETE low_priority quick FROM table_item WHERE `pk` > _digit LIMIT 1 |
1351
DELETE low_priority quick A , B FROM table_item AS A join where |
1352
DELETE low_priority quick A FROM table_item AS A where_subquery ;
708
1353
where_subquery:
709
where | subquery ;
710
1354
where | subquery ;
1355
delete_with_sleep:
1356
DELETE low_priority quick FROM table_item WHERE `pk` + wait_short = _digit ;
1357
1358
1359
########## UPDATE ####################
711
1360
update:
712
UPDATE low_priority ignore table_name SET _field = _digit WHERE `pk` > _digit LIMIT _digit |
713
UPDATE low_priority ignore table_name AS A join SET A._field = _digit, B._field = _digit;
714
715
quick:
716
| | | | | | | | | QUICK ;
717
# Only 10 %
718
719
engine:
720
MEMORY | MyISAM | InnoDB ;
721
722
online:
723
ONLINE | ;
724
1361
update_normal | update_normal | update_normal | update_normal | update_with_sleep ;
1362
update_normal:
1363
UPDATE low_priority ignore table_item SET _field = _digit WHERE `pk` > _digit LIMIT _digit |
1364
UPDATE low_priority ignore table_item AS A join SET A. _field = _digit , B. _field = _digit ;
1365
update_with_sleep:
1366
UPDATE low_priority ignore table_item SET _field = _digit WHERE wait_short = 0 LIMIT 1 ;
1367
1368
1369
########## LOCK/UNLOCK ####################
1370
lock_unlock:
1371
lock | unlock | unlock | unlock | unlock ;
725
1372
lock:
726
LOCK TABLE lock_list |
727
UNLOCK TABLES ;
728
729
flush:
730
FLUSH TABLES WITH READ LOCK |
731
FLUSH TABLE table_name , table_name ;
732
1373
LOCK TABLES lock_list ;
733
1374
lock_list:
734
lock_item |
1375
# Less likelihood for lists, because they
1376
# - are most probably less often used
1377
# - cause a higher likelihood of "table does not exist" errors.
1378
lock_item | lock_item | lock_item | lock_item | lock_item | lock_item | lock_item | lock_item | lock_item |
735
1379
lock_item , lock_item ;
736
737
1380
lock_item:
738
table_name lock_type ;
739
1381
# Have a low risk to get a clash of same table alias.
1382
table_item AS _letter lock_type ;
740
1383
lock_type:
741
READ local |
742
low_priority WRITE ;
743
744
local:
745
LOCAL | ;
1384
READ local_or_empty |
1385
low_priority WRITE |
1386
IN SHARE MODE nowait |
1387
IN EXCLUSIVE MODE nowait ;
1388
nowait:
1389
NOWAIT | ;
1390
1391
unlock:
1392
UNLOCK TABLES ;
1393
1394
1395
########## FLUSH ####################
1396
flush:
1397
# WITH READ LOCK causes that nearly all following statements will fail with
1398
# Can't execute the query because you have a conflicting read lock
1399
# Therefore it should
1400
# - be rare
1401
# - last only very short time
1402
# So I put it into a sequence with FLUSH ... ; wait a bit ; UNLOCK TABLES
1403
FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list |
1404
FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list |
1405
FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list |
1406
FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list |
1407
FLUSH TABLES WITH READ LOCK ;
1408
1409
1410
########## TINY GRAMMAR ITEMS USED AT MANY PLACES ###########
1411
as:
1412
| AS ;
1413
1414
braced_table_field_list:
1415
# In case of <empty> for braced_table_field_list we have a significant fraction of
1416
# INSERT/REPLACE INTO <table> <no field list>
1417
# failing with: 1394 Can not insert into join view 'test.t1_view_0_S' without fields list
1418
# Therefore <empty> is only 20 %.
1419
( _field_list ) | ( _field_list ) | ( _field_list ) | ( _field_list ) | ;
1420
1421
default_word:
1422
| DEFAULT ;
1423
1424
digit_or_null:
1425
_digit | _digit | _digit | _digit | _digit | _digit | _digit | _digit | _digit | NULL ;
1426
1427
engine:
1428
MEMORY | MyISAM | InnoDB ;
1429
1430
equal:
1431
| = ;
1432
1433
delayed:
1434
# Only 10 %
1435
| | | | | | | | | DELAYED ;
1436
1437
high_priority:
1438
# Only 20 %
1439
| | | | HIGH_PRIORITY ;
1440
1441
ignore:
1442
# Only 10 %
1443
| | | | | | | | | IGNORE ;
1444
1445
if_exists:
1446
# 90 %, this reduces the amount of failing DROPs
1447
| IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS ;
1448
1449
if_not_exists:
1450
# 90 %, this reduces the amount of failing CREATEs
1451
| IF NOT EXISTS | IF NOT EXISTS | IF NOT EXISTS | IF NOT EXISTS | IF NOT EXISTS | IF NOT EXISTS | IF NOT EXISTS | IF NOT EXISTS | IF NOT EXISTS ;
1452
1453
into_word:
1454
# Only 50 %
1455
| INTO ;
1456
1457
local_or_empty:
1458
# Only 20%
1459
| | | | LOCAL ;
1460
1461
low_priority_delayed_high_priority:
1462
| low_priority | delayed | high_priority ;
1463
1464
low_priority_delayed:
1465
| low_priority | delayed ;
746
1466
747
1467
low_priority:
1468
# Only 10 %
748
1469
| | | | | | | | | LOW_PRIORITY ;
1470
1471
no_or_empty:
1472
| NO ;
1473
1474
not_or_empty:
1475
| NOT ;
1476
1477
online:
1478
# Use if
1479
# Bug#45143 All connections hang on concurrent ALTER TABLE
1480
# is fixed (ONLINE removed because of Bug#45143)
1481
# Only 20 %
1482
# | | | | ONLINE ;
1483
;
1484
1485
quick:
749
1486
# Only 10 %
1487
| | | | | | | | | QUICK ;
1488
1489
replace_option:
1490
# Only 20 % <> empty.
1491
| | | | REPLACE ;
1492
1493
savepoint_or_empty:
1494
SAVEPOINT | ;
750
1495
751
1496
sql_buffer_result:
752
SQL_BUFFER_RESULT | ;
753
754
sql_cache:
755
SQL_CACHE | SQL_NO_CACHE | ;
756
757
low_priority_delayed_high_priority:
758
| | | | | | | | | | | | | | | | | | | | | | | | | | | LOW_PRIORITY | DELAYED | HIGH_PRIORITY ;
759
# Only 10 %
760
# DELAYED removed because of Bug#45067
761
762
low_priority_delayed:
763
| | | | | | | | | | | | | | | | | | LOW_PRIORITY | DELAYED ;
764
# Only 10 %
765
# DELAYED removed because of Bug#45067
766
767
ignore:
768
| | | | | | | | | IGNORE ;
769
# Only 10 %
1497
# Only 50%
1498
| SQL_BUFFER_RESULT ;
1499
1500
table_field_list:
1501
_field_list | _field_list | _field_list | _field_list | * ;
1502
1503
temporary:
1504
# Attention:
1505
# Do not apply CREATE/DROP TEMPORARY on "long life" whatever tables.
1506
# Use "short life" (-> <whatever>_n) tables only.
1507
# 1. In case of "long life" (-> <whatever>_s) tables the CREATE and DROP must be within
1508
# a sequence with some "wait_till_drop_table" between. TEMPORARY tables are session specific.
1509
# So no other session can use this table.
1510
# 2. In case of "short life" tables the CREATE and DROP are isolated. So the session
1511
# which created the table will pick a random statement and maybe do something on
1512
# the table <> DROP.
1513
# Only 10 % because no other session can use this table.
1514
| | | | | | | | | TEMPORARY ;
1515
1516
wait_short:
1517
SLEEP( 0.5 * rand_val * $life_time_unit ) ;
1518
1519
work_or_empty:
1520
| WORK ;
1521
1522
zero_or_one:
1523
0 | 1 ;
770
1524
Loggerhead is a web-based interface for Breezy
Version: 2.0.1