1
# Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
3
# This program is free software; you can redistribute it and/or modify
4
# it under the terms of the GNU General Public License as published by
5
# the Free Software Foundation; version 2 of the License.
7
# This program is distributed in the hope that it will be useful,
8
# but WITHOUT ANY WARRANTY; without even the implied warranty of
9
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10
# GNU General Public License for more details.
12
# You should have received a copy of the GNU General Public License
13
# along with this program; if not, write to the Free Software Foundation,
14
# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
16
# Grammar for testing DML, DDL, FLUSH, LOCK/UNLOCK, transactions
19
# 2009-07 Matthias Leich
20
# WL#5004 Comprehensive Locking Stress Test for Azalea
21
# A few grammar rules were taken from other grammar files.
23
# 2011-03 Matthias Leich
24
# Prevent the generation of SHOW PROCEDURE/FUNCTION CODE in case
25
# we do not have a server compiled with debug
28
# 1. There are modified grammar rules because of open bugs.
29
# Please search case insensitive for "disable".
30
# 2. Some locking statements will get an syntax error as long as
31
# WL#3561 transactional LOCK TABLE
35
# - Adjust grammar to new open and old fixed bugs
36
# - Add TRUNCATE PARTITION and check if we are missing any other related DDL.
37
# (Bug#49907 ALTER TABLE ... TRUNCATE PARTITION does not wait for locks on the table)
38
# (2010-05 Analysing + should be fixed by patch for
39
# (Bug#42643 InnoDB does not support replication of TRUNCATE TABLE)
40
# (2010-05 Patch pending)
41
# - Add the corresponding DDL when
42
# WL#4445 Import/Export tables to/from partitioned tables
43
# is ready for testing
44
# - Check the impact of the latest modifications (use "used_select" less often) on the issues
46
# When using greater values for namespace_width
47
# - the database never actually gets the expected number of objects.
48
# Even if the DROPs are removed ,then still the database grows very slowly towards the namespace size.
49
# - There are a lot of CREATE TABLE table2 SELECT * FROM table1 and similar constructs in order to clone
50
# database objects. Unfortunately, at higher namespace values, table1 is not very likely to exist, and
51
# therefore table2 is also unlikely to be created.
53
# Bug#48315 Metadata lock is not taken for merged views that use an INFORMATION_SCHEMA table
55
# Philip: using an I_S in a meaningless subselect would be best, just have
56
# ( SELECT user + 0 FROM INFORMATION_SCHEMA.USERS LIMIT 1)
59
# But IS tables used in VIEWs, SELECT, DELETE/UPDATE subqueries/join,
60
# PROCEDURES etc. are complete missing.
61
# Could I inject this in a subquery?
63
# Namespace concept is good for grammar development, avoiding failing statements,
64
# understanding statement logs but bad for grammar simplification speed.
66
# General architecture rules:
67
# ---------------------------
68
# 1. Do not modify the objects created by gendata.pl within this grammar file.
69
# Work on copies of these objects instead. Hereby we prevent that we totally run out of tables
70
# or rows etc. This minimizes also any locks on the objects created by gendata.pl.
72
# - have tables of "special" types (partitioned, view, merge etc.)
73
# - variate the storage engine
74
# within your object creation grammar file (*.zz).
75
# 2. Have separated namespaces for objects (tables etc.) with configurable width.
76
# - This allows to reduce the likelihood of applying a statement in general or an option to
77
# an object which is not allowed. Example: TRUNCATE TABLE <view>
78
# - Debugging of grammar and understanding server logs becomes easier if the namespace
79
# for an object of some type contains type related strings like "base","temp",.. etc.
80
# Example: If there is a
81
# CREATE VIEW <name which does not belong into the VIEW namespace>
82
# than something works not like intended.
83
# - The configurable namespace width (-> $namespace_width) allows to influence the likelihood
84
# that some statement hits an object. This gives some control over how much the system is stressed.
85
# - The non default option to put all table related objects (base tables, views, etc.) allows
86
# some additional increase of the stress though the likelihood of failing statement raises.
87
# 3. Distinct between two kinds of object namespaces and treat the corresponding objects different.
88
# This is experimental and might be removed in case it does not fulfill the expectations.
90
# - statement sequence: CREATE object, fill in content (if applicable), COMMIT, wait some
91
# random timespan (-> SLEEP( ... * rand_val * $life_time_unit )) , DROP object
92
# - The COMMIT is intentional. It should ensure that the session running the sequence does
93
# not hold any locks on the object during the wait phase. I am aware that CREATE ... AS SELECT
94
# commits at end, but this might be changed somewhere in future.
95
# - the maximum of the random wait timespan is configurable (-> $max_table_life_time).
96
# - The object must be stored within a database created with the "_S" property.
97
# - No other DDL on this object
98
# This should ensure that other sessions have a chance to run several DML statements using this object
99
# before it gets dropped. The "Sequence" objects are of nearly no value when running with only one thread.
101
# - CREATE and DROP for these objects are not within the same sequency.
102
# - Any session could run DDL (including DROP, ALTER, REPAIR etc.) on this object.
103
# - The object can be stored within any database.
104
# - It is assumed that they have a short lifetime.
105
# This should ensure that a session running a transaction with DML on this object has a chance to meet
106
# an attempt of another session to run DDL (especially ALTER) on this object.
107
# 4. There is some "generalization" (I am unsure if this is a good understandable term) of variables and
108
# a corresponding walk up of values.
109
# $database_name_* --> $database_name
110
# $base_table_name_* --> $base_table_name --> $table_name
111
# $temp_table_name_* --> $temp_table_name --> $table_name
113
# $part_table_name_* --> $part_table_name --> $table_name
115
# If you run "table_item" which picks a table of random type (base table, temp table ...) and random lifetime
116
# and a corresponding database and automatically assigns values to variables ($database_*,$*_table_name_*)
117
# where the name cannot be predicted, you can find the generated names at least within
118
# $database_name and $table_name .
119
# Please be aware that for example a succeeding call of "procedure_item" modifies the content of $database_name.
122
# Hints and experiences (important when extending this grammar ):
123
# -------------------------------------------------------------------
124
# 1. Any statement sequence has to be in one line.
125
# 2. Be aware of the dual use of ';'. It separates SQL statements in sequences and closes the definition block
126
# of a grammar rules. So any ';' before some '|' has a significant impact.
127
# 3. Strange not intended effects might be caused by '|' or ':' instead of ';'
128
# 4. There is an open RQG problem with SHOW ... LIKE '<grammar rule>'.
129
# 5. In general: There should be spaces whenever a grammar rule is mentioned in some grammar rule component.
130
# Example: "my_table" and "where" are grammar rules.
131
# SELECT MAX(f1) FROM my_table where ;
132
# 6. If there are needs to write some message into a server log than avoid the use of auxiliary SQL (SELECT <message> etc.).
133
# Use something like:
134
# /* <your text> */ <SQL statement belonging to the test> ;
136
# 7. Use uppercase characters for strings and keywords in statements. This avoids any not intended
137
# treatment as grammar rule.
138
# 8. Use the most simple option first in lists. This makes automatic grammar simplification
139
# which walks basically from right to left more efficient.
142
# <empty> | WHERE `pk` BETWEEN _digit AND _digit | WHERE function_name_n() = _digit ;
143
# 9. The RQG "Reporter" LockTableKiller can help to accelerate "deadlocked" tests.
147
# Naming conventions (default)
148
# ========================================================
150
# Pattern (standard configuration) | Object
151
# -----------------------------------------
152
# testdb_* | database
159
# End of grammar rule name (default) | characteristic
160
# -------------------------------------------------------
161
# _S | related to "sequence" object
162
# _N | related to "normal" object
164
# Within grammar rule name | characteristic
165
# -----------------------------------------------
166
# _name | name of the object
167
# _item | <schema name> . <name of the object>
168
# _list | either single rule (<schema name> . <name of the object>) or comma separated list
172
# Missing but not really important improvements:
173
# - Reduce the amount of cases where "sequence" objects have "normal" objects within their definition.
174
# --> views,functions,procedures
175
# - Reduce the amount of cases where the wrong table types occur within object definitions
176
# Example: TABLE for a TRIGGER or VIEW definition. Names of temporary tables could be computed but are not allowed.
180
# Section of easy changeable rules with high impact on the test =============================================#
183
# Advantage: Less failing (table does not exist ...) statements within the first phase of the test.
184
# init_basics1 : init_basics2 ; init_namespaces ; init_executor_table ; event_scheduler_on ; have_some_initial_objects ;
186
# Advantage: Better performance during bug hunt, test simplification etc. because objects are created at
187
# one place (<object>_ddl) only and not also in "have_some_initial_objects".
188
init_basics1 ; init_basics2 ; init_namespaces ; init_executor_table ;
191
# This table is used in kill_query_or_session.
192
CREATE TABLE IF NOT EXISTS test . executors (id BIGINT, PRIMARY KEY(id)) ENGINE = MEMORY ; INSERT HIGH_PRIORITY IGNORE INTO test.executors SET id = CONNECTION_ID() ; COMMIT ;
195
# 1. $life_time_unit = maximum lifetime of a table created within a CREATE, wait, DROP sequence.
197
# A reasonable value is bigger than any "wait for <whatever> lock" timeout.
199
# There are till now not sufficient experiences about the impact of different values on the outcome of the test.
201
# sequence object | lifetime
202
# ------------------------------------------------
203
# database | 2 * RAND() * $life_time_unit
204
# table (no view) | 1 * RAND() * $life_time_unit
205
# view | 0.5 * RAND() * $life_time_unit
206
# procedure | 0.5 * RAND() * $life_time_unit
207
# function | 0.5 * RAND() * $life_time_unit
208
# trigger | 0.5 * RAND() * $life_time_unit
210
# A DML statement using SLEEP will use 0.5 * RAND() * $life_time_unit seconds.
212
# one_thread_correction will correct $life_time_unit to 0 if we have only one "worker" thread.
214
# 2. $namespace_width = Width of a namespace
216
# Smaller numbers cause a
217
# - lower fraction of statements failing because of missing object
218
# - higher fraction of clashes when running with multiple sessions
221
# - In case of one thread a $life_time_unit <> 0 does not make sense, because there is no parallel
222
# "worker" thread which could do something with the object during the "wait" period.
223
{ $life_time_unit = 1 ; $namespace_width = 2 ; if ( $ENV{RQG_THREADS} == 1 ) { $life_time_unit = 0 } ; return undef } avoid_bugs ; nothing_disabled ; system_table_stuff ;
226
# Store information if we have a debug server in $out_file.
227
# 1. The "Home" directory of
228
# - the server would be <vardir>/master-data/
229
# <vardir> is alculated by MTR and affected by options given to runall.pl
230
# - RQG is <RQG install directory>
231
# 2. The environment does not contain any variable pointing to <vardir> or RQG directories
232
# Therefore we need a $outfile with absolute path.
233
{$out_file='/tmp/'.$$.'.tmp' ; unlink($out_file); return undef} SELECT VERSION() LIKE '%debug%' INTO OUTFILE {return "'".$out_file."'"};
236
# Please choose between the following alternatives
237
# separate_objects -- no_separate_objects
238
# separate_normal_sequence -- no_separate_normal_sequence
239
# separate_table_types -- no_separate_table_types
240
# 1. Low amount of failing statements, low risk to run into known not locking related crashes
241
separate_objects ; separate_normal_sequence ; separate_table_types ;
242
# 2. Higher amount of failing statements, risk to run into known temporary table related crashes
243
# separate_objects ; separate_normal_sequence ; no_separate_table_types ;
245
# High amount of failing statements, especially risk to run into known temporary table related crashes and asserts.
246
# no_separate_objects ; separate_normal_sequence ; no_separate_table_types ;
248
separate_table_types:
249
# Effect: Distinction between
250
# - base, temporary, merge and partioned tables + views
251
# - tables of any type and functions,procedures,triggers,events
252
# Only statements which are applicable to this type of table will be generated.
253
# Example: ALTER VIEW <existing partitioned table> ... should be not generated.
254
# Advantage: Less failing statements, logs are much easier to read
255
# Disadvantage: The avoided suitations are not tested.
256
{ $base_piece="base" ; $temp_piece="temp" ; $merge_piece="merge" ; $part_piece="part" ; $view_piece="view" ; return undef } ;
257
no_separate_table_types:
259
# - maybe higher load on tables of all types in general (depends on size of namespace)
260
# - a significant fraction of statements will fail with
261
# 1. 1064 "You have an error in your SQL syntax ..."
262
# Example: TRUNCATE <view>
263
# 2. <number <> 1064> <This option/whatever is not applicable to the current object/situation/whatever>
264
# This might look a bit ugly but it has the benefit that these statements are at least tried.
265
# The goal is not to check the parse process, but there might be temporary MDL locks or in worst
266
# case remaining permanent MDL lock. Effects of these locks should be also checked.
267
# Just as a reminder:
268
# A CREATE VIEW which fails with an error <> "You have an error in your SQL syntax" causes an implicit COMMIT
269
# of the current transaction.
270
{ $base_piece="" ; $temp_piece="" ; $merge_piece="" ; $part_piece="" ; $view_piece="" ; return undef } ;
271
separate_normal_sequence:
272
# Advantages/Disadvantages: To be discovered
273
{ $sequence_piece="_S" ; $normal_piece="_N" ; return undef } ;
274
no_separate_normal_sequence:
275
# Advantages/Disadvantages: To be discovered
276
{ $sequence_piece="" ; $normal_piece="" ; return undef } ;
278
# Effect: Distinction between schemas, tables, functions, triggers, procedures and events
279
# Only statements which are applicable to this type of object will be generated.
280
# Example: CALL <existing partitioned table> ... should be not generated.
281
# Advantage: Less failing statements, logs are much easier to read
282
# Disadvantage: The avoided suitations are not tested.
283
{ $database_prefix="testdb" ; $table_prefix="t1_" ; $procedure_prefix="p1_" ; $function_prefix="f1_" ; $trigger_prefix="tr1_" ; $event_prefix="e1_" ; return undef } ;
285
# Effect: At least no distinction between functions, triggers, procedures and events
286
# If no_separate_table_types is added, than also tables are no more separated.
287
# Example: CALL <existing partitioned table> ... should be not generated.
288
# Advantage: More coverage
289
# Disadvantage: More failing statements
290
{ $database_prefix="o1_1" ; $table_prefix="o1_" ; $procedure_prefix="o1_" ; $function_prefix="o1_" ; $trigger_prefix="o1_" ; $event_prefix="o1_" ; return undef } ;
293
# Set this grammar rule to "empty" if for example no optimizer related server system variable has to be switched.
297
SET GLOBAL EVENT_SCHEDULER = ON ;
300
SET GLOBAL EVENT_SCHEDULER = OFF ;
302
have_some_initial_objects:
303
# It is assumed that this reduces the likelihood of "Table does not exist" significant when running with a small number of "worker" threads.
304
# The amount of create_..._table rules within the some_..._tables should depend a bit on the value in $namespace_width but I currently
305
# do not know how to express this in the grammar.
306
some_databases ; some_base_tables ; some_temp_tables ; some_merge_tables ; some_part_tables ; some_view_tables ; some_functions ; some_procedures ; some_trigger ; some_events ;
308
create_database ; create_database ; create_database ; create_database ;
310
create_base_table ; create_base_table ; create_base_table ; create_base_table ;
312
create_temp_table ; create_temp_table ; create_temp_table ; create_temp_table ;
314
create_merge_table ; create_merge_table ; create_merge_table ; create_merge_table ;
316
create_part_table ; create_part_table ; create_part_table ; create_part_table ;
318
create_view ; create_view ; create_view ; create_view ;
320
create_function ; create_function ; create_function ; create_function ;
322
create_procedure ; create_procedure ; create_procedure ; create_procedure ;
324
create_trigger ; create_trigger ; create_trigger ; create_trigger ;
326
create_event ; create_event ; create_event ; create_event ;
329
{ $sequence_begin = "/* Sequence start */" ; $sequence_end = "/* Sequence end */" ; return undef } ;
332
# This is used in "grant_revoke".
333
CREATE USER otto@localhost ;
336
# Useful grammar rules ====================================================================================#
339
{ $rand_val = $prng->int(0,100) / 100 } ;
342
# Namespaces of objects ==========================================================================#
343
# An explanation of the namespace concept is on top of this file.
345
# 1. The database namespace ##########################################################################
347
{ $database_name_s = $database_prefix . $sequence_piece ; $database_name = $database_name_s } ;
349
{ $database_name_n = $database_prefix . $normal_piece ; $database_name = $database_name_n } ;
351
# Get a random name from the "database" namespace.
352
# $database_name gets automatically filled when database_name_s or database_name_n is executed.
353
database_name_s | database_name_n ;
356
# 2. The base table namespace ########################################################################
358
# Get a random name from the "base table long life" namespace.
359
{ $base_table_name_s = $table_prefix . $base_piece . $prng->int(1,$namespace_width) . $sequence_piece ; $base_table_name = $base_table_name_s ; $table_name = $base_table_name } ;
361
# Get a random name from the "base table short life" namespace.
362
{ $base_table_name_n = $table_prefix . $base_piece . $prng->int(1,$namespace_width) . $normal_piece ; $base_table_name = $base_table_name_n ; $table_name = $base_table_name } ;
364
# Get a random name from the "base table" namespace.
365
base_table_name_s | base_table_name_n ;
367
# Sometimes useful stuff:
369
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 } ;
371
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 } ;
373
base_table_item_s | base_table_item_n ;
374
base_table_item_list_s:
375
base_table_item_s | base_table_item_s , base_table_item_s ;
376
base_table_item_list_n:
377
base_table_item_n | base_table_item_n , base_table_item_n ;
378
base_table_item_list:
379
base_table_item | base_table_item , base_table_item ;
382
# 3. The temp table namespace ########################################################################
383
# Please note that TEMPORARY merge tables will be not generated.
385
# Get a random name from the "temp table long life" namespace.
386
{ $temp_table_name_s = $table_prefix . $temp_piece . $prng->int(1,$namespace_width) . $sequence_piece ; $temp_table_name = $temp_table_name_s ; $table_name = $temp_table_name } ;
388
# Get a random name from the "temp table short life" namespace.
389
{ $temp_table_name_n = $table_prefix . $temp_piece . $prng->int(1,$namespace_width) . $normal_piece ; $temp_table_name = $temp_table_name_n ; $table_name = $temp_table_name } ;
391
# Get a random name from the "temp table" namespace.
392
temp_table_name_s | temp_table_name_n ;
394
# Sometimes useful stuff:
396
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 } ;
398
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 } ;
400
temp_table_item_s | temp_table_item_n ;
401
temp_table_item_list_s:
402
temp_table_item_s | temp_table_item_s , temp_table_item_s ;
403
temp_table_item_list_n:
404
temp_table_item_n | temp_table_item_n , temp_table_item_n ;
405
temp_table_item_list:
406
temp_table_item | temp_table_item , temp_table_item ;
409
# 4. The merge table namespace #######################################################################
410
# Please note that TEMPORARY merge tables will be not generated.
412
# Get a random name from the "merge table long life" namespace.
413
{ $merge_table_name_s = $table_prefix . $merge_piece . $prng->int(1,$namespace_width) . $sequence_piece ; $merge_table_name = $merge_table_name_s ; $table_name = $merge_table_name } ;
415
# Get a random name from the "merge table short life" namespace.
416
{ $merge_table_name_n = $table_prefix . $merge_piece . $prng->int(1,$namespace_width) . $normal_piece ; $merge_table_name = $merge_table_name_n ; $table_name = $merge_table_name } ;
418
# Get a random name from the "merge table" namespace.
419
merge_table_name_s | merge_table_name_n ;
421
# Sometimes useful stuff:
423
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 } ;
425
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 } ;
427
merge_table_item_s | merge_table_item_n ;
428
merge_table_item_list_s:
429
merge_table_item_s | merge_table_item_s , merge_table_item_s ;
430
merge_table_item_list_n:
431
merge_table_item_n | merge_table_item_n , merge_table_item_n ;
432
merge_table_item_list:
433
merge_table_item | merge_table_item , merge_table_item ;
436
# 5. The view table namespace ########################################################################
438
# Get a random name from the "view table long life" namespace.
439
{ $view_table_name_s = $table_prefix . $view_piece . $prng->int(1,$namespace_width) . $sequence_piece ; $view_table_name = $view_table_name_s ; $table_name = $view_table_name } ;
441
# Get a random name from the "view table short life" namespace.
442
{ $view_table_name_n = $table_prefix . $view_piece . $prng->int(1,$namespace_width) . $normal_piece ; $view_table_name = $view_table_name_n ; $table_name = $view_table_name } ;
444
# Get a random name from the "view table" namespace.
445
view_table_name_s | view_table_name_n ;
447
# Sometimes useful stuff:
449
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 };
451
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 };
453
view_table_item_s | view_table_item_n ;
454
view_table_item_list_s:
455
view_table_item_s | view_table_item_s , view_table_item_s ;
456
view_table_item_list_n:
457
view_table_item_n | view_table_item_n , view_table_item_n ;
458
view_table_item_list:
459
view_table_item | view_table_item , view_table_item ;
462
# 6. The partitioned table namespace #################################################################
464
# Get a random name from the "part table long life" namespace.
465
{ $part_table_name_s = $table_prefix . $part_piece . $prng->int(1,$namespace_width) . $sequence_piece ; $part_table_name = $part_table_name_s ; $table_name = $part_table_name } ;
467
# Get a random name from the "part table short life" namespace.
468
{ $part_table_name_n = $table_prefix . $part_piece . $prng->int(1,$namespace_width) . $normal_piece ; $part_table_name = $part_table_name_n ; $table_name = $part_table_name } ;
470
# Get a random name from the "part table" namespace.
471
part_table_name_s | part_table_name_n ;
473
# Sometimes useful stuff:
475
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 };
477
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 };
479
part_table_item_s | part_table_item_n ;
480
part_table_item_list_s:
481
part_table_item_s | part_table_item_s , part_table_item_s ;
482
part_table_item_list_n:
483
part_table_item_n | part_table_item_n , part_table_item_n ;
484
part_table_item_list:
485
part_table_item | part_table_item , part_table_item ;
488
# 7. Mixed namespaces of tables ################################################################
490
# 7.1 All tables ( base/temp/merge tables + views + ... #########################################
492
base_table_item_s | temp_table_item_s | merge_table_item_s | view_table_item_s | part_table_item_s ;
494
base_table_item_n | temp_table_item_n | merge_table_item_n | view_table_item_n | part_table_item_n ;
496
table_item_s | table_item_n ;
499
# Less likelihood for lists, because they
500
# - are most probably less often used
501
# - cause a higher likelihood of "table does not exist" errors.
502
table_item | table_item | table_item | table_item | table_item | table_item | table_item | table_item | table_item |
503
table_item , table_item ;
506
# 7.2 All tables but no views #######################################################################
507
table_no_view_item_s:
508
base_table_item_s | temp_table_item_s | merge_table_item_s | part_table_item_s ;
509
table_no_view_item_n:
510
base_table_item_n | temp_table_item_n | merge_table_item_n | part_table_item_n ;
512
table_no_view_item_s | table_no_view_item_n ;
515
# 7.3 All base and temp tables + views ##############################################################
516
# These grammar rules could be used to avoid some partioning or merge table related bugs.
517
base_temp_view_table_item_s:
518
base_table_item_s | temp_table_item_s | view_table_item_s | part_table_item_s ;
519
base_temp_view_table_item_n:
520
base_table_item_n | temp_table_item_n | view_table_item_n | part_table_item_n ;
521
base_temp_view_table_item:
522
base_temp_view_table_item_s | base_temp_view_table_item ;
525
# 8. Other namespaces ##############################################################a
527
# The disabled names are for future use. They cannot work with the current properties of .zz grammars.
528
# The problem is that we get in some scenarios tables with differing numnber of columns.
529
# { $template_table_item = "test.table0" } |
530
# { $template_table_item = "test.table1" } |
531
# { $template_table_item = "test.table10" } |
532
# { $template_table_item = "test.table0_int" } |
533
{ $template_table_item = "test.table0_int" } |
534
{ $template_table_item = "test.table1_int" } |
535
{ $template_table_item = "test.table10_int" } |
536
{ $template_table_item = "test.table0_int_autoinc" } |
537
{ $template_table_item = "test.table1_int_autoinc" } |
538
{ $template_table_item = "test.table10_int_autoinc" } ;
542
# Get a random name from the "procedure long life" namespace.
543
{ $procedure_name_s = $procedure_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $procedure_name = $procedure_name_s } ;
545
# Get a random name from the "procedure short life" namespace.
546
{ $procedure_name_n = $procedure_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $procedure_name = $procedure_name_n } ;
548
# Get a random name from the "procedure" namespace.
549
procedure_name_s | procedure_name_n ;
551
# Sometimes useful stuff:
553
database_name_s . procedure_name_s { $procedure_item_s = $database_name_s . " . " . $procedure_name_s ; $procedure_item = $procedure_item_s ; return undef } ;
555
database_name . procedure_name_n { $procedure_item_n = $database_name . " . " . $procedure_name_n ; $procedure_item = $procedure_item_n ; return undef } ;
557
procedure_item_s | procedure_item_n ;
560
# Get a random name from the "function long life" namespace.
561
{ $function_name_s = $function_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $function_name = $function_name_s } ;
563
# Get a random name from the "function short life" namespace.
564
{ $function_name_n = $function_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $function_name = $function_name_n } ;
566
# Get a random name from the "function" namespace.
567
function_name_s | function_name_n ;
570
database_name_s . function_name_s { $function_item_s = $database_name_s . " . " . $function_name_s ; $function_item = $function_item_s ; return undef } ;
572
database_name . function_name_n { $function_item_n = $database_name . " . " . $function_name_n ; $function_item = $function_item_n ; return undef } ;
574
function_item_s | function_item_n ;
577
# Get a random name from the "trigger long life" namespace.
578
{ $trigger_name_s = $trigger_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $trigger_name = $trigger_name_s } ;
580
# Get a random name from the "trigger short life" namespace.
581
{ $trigger_name_n = $trigger_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $trigger_name = $trigger_name_n } ;
583
# Get a random name from the "trigger" namespace.
584
trigger_name_s | trigger_name_n ;
587
database_name_s . trigger_name_s { $trigger_item_s = $database_name_s . " . " . $trigger_name_s ; $trigger_item = $trigger_item_s ; return undef } ;
589
database_name . trigger_name_n { $trigger_item_n = $database_name . " . " . $trigger_name_n ; $trigger_item = $trigger_item_n ; return undef } ;
591
trigger_item_s | trigger_item_n ;
594
# Get a random name from the "event long life" namespace.
595
{ $event_name_s = $event_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $event_name = $event_name_s } ;
597
# Get a random name from the "event short life" namespace.
598
{ $event_name_n = $event_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $event_name = $event_name_n } ;
600
# Get a random name from the "event" namespace.
601
event_name_s | event_name_n ;
604
database_name_s . event_name_s { $event_item_s = $database_name_s . " . " . $event_name_s ; $event_item = $event_item_s ; return undef } ;
606
database_name . event_name_n { $event_item_n = $database_name . " . " . $event_name_n ; $event_item = $event_item_n ; return undef } ;
608
event_item_s | event_item_n ;
610
# Here starts the core of the test grammar ========================================================#
613
dml | dml | dml | dml | ddl | transaction | lock_unlock | lock_unlock | flush | handler ;
615
########## TRANSACTIONS ####################
618
start_transaction | commit | rollback |
619
start_transaction | commit | rollback |
620
start_transaction | commit | rollback |
621
SAVEPOINT savepoint_id | RELEASE SAVEPOINT savepoint_id | ROLLBACK work_or_empty TO savepoint_or_empty savepoint_id |
622
BEGIN work_or_empty | set_autocommit | kill_query_or_session ;
623
# No impact on mdl.cc , lock.cc ..... set_isolation_level ;
629
SET SESSION TX_ISOLATION = TRIM(' isolation_level ');
632
REPEATABLE-READ | READ-COMMITTED | SERIALIZABLE ;
636
START TRANSACTION with_consistent_snapshot ;
637
with_consistent_snapshot:
638
| | | | | | | | | WITH CONSISTENT SNAPSHOT ;
642
# 1. RELEASE should be rare
643
# 2. AND CHAIN RELEASE is nonsense and will get an error
644
# 3. COMMIT [ WORK ] [ AND [ NO ] CHAIN ] [RELEASE]
645
# AND NO CHAIN is the default, no RELEASE is the default
646
# 4. ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [RELEASE]
647
# [ TO SAVEPOINT <savepoint specifier> ]
648
# You may specify only one of:
649
# "[AND [NO] CHAIN]" or "RELEASE" or "TO SAVEPOINT ...".
650
# AND NO CHAIN is the default, no RELEASE is the default
653
COMMIT work_or_empty no_chain release_or_empty |
654
COMMIT work_or_empty AND CHAIN ;
659
| | | | | | | | | RELEASE ;
662
ROLLBACK work_or_empty no_chain release_or_empty |
663
ROLLBACK work_or_empty AND CHAIN ;
666
SET AUTOCOMMIT = zero_or_one ;
668
kill_query_or_session:
669
#---------------------
671
# - a query gets killed that most locks hold by this query are automatically removed.
672
# Metadata locks might survive till transaction end.
673
# - a session gets killed that any lock hold by this session gets automatically removed.
674
# We will not check the removal here via SQL or sophisticated PERL code.
675
# We just hope that forgotten locks lead sooner or later to nice deadlocks.
678
# There is some unfortunate sideeffect of KILL <session> .
679
# It reduces the probability to detect deadlocks because it
680
# might hit a participating session.
682
# Killing a query or session must NOT affect an
683
# - auxiliary RQG session (= non executor) because this can fool RQG's judgement
684
# about the test outcome
685
# - executor session which is within the early phase when it pulls meta data (table names, column names,
686
# data types, ...). This could end up with RQG exit status 255.
687
# -> "Can't use an undefined value as an ARRAY reference at lib/GenTest/Generator/FromGrammar.pm line 269."
688
# This is ensured by the following:
689
# Every executor pulls meta data and runs after that "query_init".
690
# Within "query_init" this executor writes his own CONNECTION_ID() into the table test.executors.
691
# When running KILL for another session only the id's found in test.executors must be selected.
692
# In case it is planned to kill
693
# - another session AFTER
694
# - own session (suicide) BEFORE
695
# the KILL statement the entry within test.executors has to be removed.
697
# Depending on scenario (a session might run COMMIT/ROLLBACK RELEASE) and whatever other effects it might happen that
698
# 1. A session disappears but the entry is not removed.
699
# This is harmless because somewhere later another session will pick the id from test.executors
700
# try the kill session and remove the entry.
701
# 2. A session entry in test.executors does not exist but the session is alife.
702
# This is harmless because somewhere later this session will try to remove its' no more existing
703
# entry from test.executors and kill himself.
708
# 3. S1 try to kill S3 which no more exists.
709
# 4. Various combinations of sessions running 1., 2. or 3.
711
# The various COMMITs should ensure that locking effects caused by activity on test.executors are minimal.
712
COMMIT ; own_id ; delete_executor_entry ; COMMIT ; KILL @kill_id |
713
COMMIT ; own_id ; ; COMMIT ; KILL QUERY @kill_id |
714
COMMIT ; minimal_id ; ; COMMIT ; KILL @kill_id ; delete_executor_entry ; COMMIT |
715
COMMIT ; minimal_id ; ; COMMIT ; KILL QUERY @kill_id ;
717
SET @kill_id = CONNECTION_ID() ;
719
SELECT MIN(id) INTO @kill_id FROM test . executors ;
720
delete_executor_entry:
721
DELETE FROM test . executors WHERE id = @kill_id ;
725
base_table_ddl | base_table_ddl | base_table_ddl |
726
temp_table_ddl | temp_table_ddl | temp_table_ddl |
727
merge_table_ddl | merge_table_ddl | merge_table_ddl |
728
part_table_ddl | part_table_ddl | part_table_ddl |
729
view_ddl | view_ddl | view_ddl |
730
procedure_ddl | procedure_ddl | procedure_ddl |
731
function_ddl | function_ddl | function_ddl |
732
trigger_ddl | trigger_ddl | trigger_ddl |
737
# Bug#54486 assert in my_seek, concurrent DROP/CREATE SCHEMA, CREATE TABLE, REPAIR
738
# affects table_maintenance_ddl in mysql-5.1.
739
# The problem seems to have disappeared in higher MySQL versions.
740
table_maintenance_ddl |
741
dump_load_data_sequence |
745
# "dump_load_data_sequence" with SELECT ... INTO OUTFILE ...; LOAD DATA ... INFILE
746
# consists more of DML statements, but we place this here under "ddl" because the
747
# statements in "dml" are often executed as prepared statements. And the text after
748
# PREPARE st1 FOR must not contain multiple statements.
751
########## HANDLER ####################
752
# The alias within HANDLER ... OPEN is optional. Unfortunately the HANDLER ... READ/CLOSE ... statements
753
# do not accept SCHEMA names. Therefore the tablename must be either a table within the current SCHEMA
754
# or an alias. We go with alias all time.
762
HANDLER table_no_view_item OPEN as handler_a ;
765
HANDLER handler_a READ handler_index comparison_operator ( _digit ) handler_read_part |
766
HANDLER handler_a READ handler_index first_next_prev_last handler_read_part |
767
HANDLER handler_a READ first_next handler_read_part ;
776
first_next_prev_last:
783
HANDLER handler_a CLOSE ;
786
########## SHOW ####################
787
# We run here only object related SHOW commands except SHOW STATUS which checks counters
788
# of OPEN tables etc.
796
show_databases | show_create_database ;
799
SHOW databases_schemas ;
801
DATABASES | SCHEMAS ;
803
show_create_database:
804
SHOW CREATE database_schema database_name ;
806
#----------------------------------
809
show_tables | show_tables |
810
show_table_status | show_table_status |
811
show_create_table | show_create_view |
812
show_open_tables | show_columns ;
818
# Works also for views
819
SHOW CREATE TABLE table_item ;
822
SHOW OPEN TABLES IN database_name ;
825
# Works also for views
829
SHOW full COLUMNS from_in table_item show_columns_part ;
836
# Attention: LIKE '_field' does not work, because RQG does not expand _field.
837
# LIKE '%int%' does not work, because RQG expands it to something like LIKE '%822214656%'.
842
SHOW CREATE VIEW view_table_item ;
844
#----------------------------------
846
show_create_function | show_function_code | show_function_status |
847
show_create_procedure | show_procedure_code | show_procedure_status |
848
show_triggers | show_create_trigger |
849
show_events | show_create_event ;
851
show_create_function:
852
SHOW CREATE FUNCTION function_item ;
855
is_debug1 is_debug2 { return $m1 } SHOW FUNCTION CODE function_item { return $m2 };
857
show_function_status:
858
SHOW FUNCTION STATUS ;
860
show_create_procedure:
861
SHOW CREATE PROCEDURE procedure_item ;
864
is_debug1 is_debug2 { return $m1 } SHOW PROCEDURE CODE procedure_item { return $m2 };
867
# Calculate if we have a debug server.
868
{ $is_debug_server = -1; open($my_file,'<'.$out_file); read($my_file,$is_debug_server,1000); close($my_file); return undef };
871
# Set the marker according if we have a debug server or not.
872
{ $m1='/*'; $m2='*/'; if ( $is_debug_server == 1 ) { $m1=''; $m2='' }; return undef } ;
874
show_procedure_status:
875
SHOW PROCEDURE STATUS ;
881
SHOW CREATE TRIGGER trigger_item ;
884
SHOW EVENTS from_in database_name ;
887
SHOW CREATE EVENT event_item_s ;
889
########## SELECTS ON THE INFORMATION_SCHEMA ####################
890
# We run here only object related SELECTs.
892
is_schemata | is_tables | is_columns ;
894
/* database_name */ SELECT * FROM information_schema . schemata WHERE schema_name = TRIM(' $database_name ') ;
896
/* table_item */ SELECT * FROM information_schema . tables WHERE table_schema = TRIM(' $database_name ') AND table_name = TRIM(' $table_name ') ;
898
/* table_item */ SELECT * FROM information_schema . columns WHERE table_schema = TRIM(' $database_name ') AND table_name = TRIM(' $table_name ') AND column_name = random_field_quoted ;
899
# 19.1. The INFORMATION_SCHEMA SCHEMATA Table
900
# 19.2. The INFORMATION_SCHEMA TABLES Table
901
# 19.3. The INFORMATION_SCHEMA COLUMNS Table
902
# 19.4. The INFORMATION_SCHEMA STATISTICS Table
903
# 19.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table
904
# 19.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
905
# 19.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
906
# 19.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
907
# 19.9. The INFORMATION_SCHEMA CHARACTER_SETS Table
908
# 19.10. The INFORMATION_SCHEMA COLLATIONS Table
909
# 19.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
910
# 19.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
911
# 19.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
912
# 19.14. The INFORMATION_SCHEMA ROUTINES Table
913
# 19.15. The INFORMATION_SCHEMA VIEWS Table
914
# 19.16. The INFORMATION_SCHEMA TRIGGERS Table
915
# 19.17. The INFORMATION_SCHEMA PLUGINS Table
916
# 19.18. The INFORMATION_SCHEMA ENGINES Table
917
# 19.19. The INFORMATION_SCHEMA PARTITIONS Table
918
# 19.20. The INFORMATION_SCHEMA EVENTS Table
919
# 19.21. The INFORMATION_SCHEMA FILES Table
920
# 19.22. The INFORMATION_SCHEMA TABLESPACES Table
921
# 19.23. The INFORMATION_SCHEMA PROCESSLIST Table
922
# 19.24. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
923
# 19.25. The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
924
# 19.26. The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
925
# 19.27. The INFORMATION_SCHEMA PARAMETERS Table
926
# 19.28. The INFORMATION_SCHEMA PROFILING Table
927
# 19.29. Other INFORMATION_SCHEMA Tables
931
########## DATABASE ####################
933
create_database | create_database | create_database |
934
drop_database | alter_database |
938
CREATE database_schema if_not_exists database_name_n database_spec ;
944
# We do not want to test CHARACTER SETs and COLLATIONs, but we need something for ALTER DATABASE.
945
default_word CHARACTER SET equal utf8 | default_word COLLATE equal utf8_bin ;
948
DROP database_schema if_exists database_name_n ;
951
ALTER database_schema database_name_n database_spec ;
954
# Have a bigger lifetime for databases because the objects with extended lifetime are stored there.
955
$sequence_begin CREATE database_schema database_name_s ; wait_till_drop_database ; DROP database_schema $database_name_s $sequence_end ;
956
wait_till_drop_database:
957
SELECT SLEEP( 2 * rand_val * $life_time_unit ) ;
960
########## BASE AND TEMPORARY TABLES ####################
962
create_base_table | create_base_table | create_base_table | create_base_table | create_base_table | create_base_table |
963
drop_base_table | alter_base_table |
964
base_table_sequence ;
967
CREATE TABLE if_not_exists base_table_item_n { $create_table_item = $base_table_item_n ; return undef } create_table_part ;
969
LIKE template_table_item ; ALTER TABLE $create_table_item ENGINE = engine ; INSERT INTO $create_table_item SELECT * FROM $template_table_item |
970
LIKE template_table_item ; ALTER TABLE $create_table_item ENGINE = engine ; INSERT INTO $create_table_item SELECT * FROM $template_table_item |
974
# DROP two tables is in "drop_table_list"
975
DROP TABLE if_exists base_table_item_n restrict_cascade ;
978
ALTER ignore TABLE base_table_item_n alter_base_temp_table_part ;
980
alter_base_temp_table_part:
981
# Reasons why "ENGINE = engine" should be rather rare:
982
# 1. ALTER ... ENGINE = <engine> is rather rare within a production system running under DML load
983
# 2. ALTER ... ENGINE = <engine != MyISAM> "damages" any MERGE table using the affected table as base table.
984
# As a consequence nerly all statements on the MERGE table will fail.
985
COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' |
986
COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' |
990
$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 ;
992
wait_till_drop_table:
993
SELECT SLEEP( rand_val * $life_time_unit ) ;
996
# Attention: temp_table_sequence is intentionally omitted, because no other session will be
997
# able to use this table.
998
create_temp_table | create_temp_table | create_temp_table | create_temp_table | create_temp_table | create_temp_table |
999
drop_temp_table | alter_temp_table ;
1002
CREATE TEMPORARY TABLE if_not_exists temp_table_item_n { $create_table_item = $temp_table_item_n ; return undef } create_table_part ;
1005
# DROP two tables is in "drop_table_list"
1006
# A pure DROP TABLE is allowed, but we get an implicit COMMITs for that.
1007
DROP TEMPORARY TABLE if_exists temp_table_item_n |
1008
DROP TABLE if_exists temp_table_item_n ;
1011
ALTER ignore TABLE temp_table_item_n alter_base_temp_table_part ;
1013
########## MAINTENANCE FOR ANY TABLE ####################
1014
# The server accepts these statements for all table types (VIEWs, base tables, ...) though they
1015
# should have partially no effect. We run them on all table types because everything which gets
1016
# accepted has to be checked even if the command should do nothing.
1018
# OPTIMIZE ... TABLE <view> ...
1019
# Table Op Msg_type Msg_text
1020
# test.v1 optimize Error Table 'test.v1' doesn't exist
1021
# test.v1 optimize status Operation failed
1022
# OPTIMIZE ... TABLE <merge table> ...
1023
# Table Op Msg_type Msg_text
1024
# test.t1m optimize note The storage engine for the table doesn't support optimize
1026
table_maintenance_ddl:
1027
analyze_table | optimize_table | checksum_table | check_table | repair_table ;
1030
ANALYZE not_to_binlog_local TABLE table_list ;
1031
not_to_binlog_local:
1032
| NO_WRITE_TO_BINLOG | LOCAL ;
1035
OPTIMIZE not_to_binlog_local TABLE table_list ;
1038
CHECKSUM TABLE table_list quick_extended ;
1040
| quick | extended ;
1043
| | | | | | | | | EXTENDED ;
1046
CHECK TABLE table_list check_table_options ;
1047
check_table_options:
1048
| FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED ;
1051
REPAIR not_to_binlog_local TABLE table_list quick extended use_frm ;
1055
| | | | | | | | | USE_FRM ;
1058
########## MIXED TABLE RELATED DDL #################################
1060
TRUNCATE table_word table_no_view_item_n ;
1065
# DROP one table is in "drop_*table"
1066
# 1. We mix here all tables except VIEWs up.
1067
# 2. We have an increased likelihood that the statement fails because of use of
1068
# - "temporary" (only correct in case of a temporary table)
1069
# - two tables (some might not exist)
1070
DROP temporary TABLE if_exists table_no_view_item_n , table_no_view_item_n restrict_cascade ;
1073
# RENAME TABLE works also on all types of tables (includes VIEWs)
1074
RENAME TABLE rename_item_list ;
1076
rename_item | rename_item , rename_item ;
1078
# Preserve the object type (base,temp,....) and type (Normal) otherwise debugging becomes difficult and
1079
# the concept with different lifetimes gets broken.
1080
base_table_item_n TO base_table_item_n |
1081
temp_table_item_n TO temp_table_item_n |
1082
merge_table_item_n TO merge_table_item_n |
1083
part_table_item_n TO part_table_item_n ;
1086
ALTER TABLE table_no_view_item_s CHANGE COLUMN column_to_change my_column INT |
1087
ALTER TABLE table_no_view_item_s CHANGE COLUMN my_column column_to_change INT ;
1090
`col_int` | `col_int_key` | `pk` ;
1093
########## MERGE TABLE DDL ####################
1095
create_merge_table | create_merge_table | create_merge_table | create_merge_table | create_merge_table | create_merge_table |
1096
drop_merge_table | alter_merge_table |
1097
merge_table_sequence ;
1100
# There is a high risk that the tables which we pick for merging do not fit together because they
1101
# have different structures. We try to reduce this risk to end up with no merge table at all
1103
# 1. Let the merge table have the structure of the first base table.
1104
# CREATE TABLE <merge table> LIKE <first base table>
1105
# 2. Let the merge table be based on the first base table.
1106
# ALTER TABLE <merge table> ENGINE = MERGE UNION(<first base table>)
1107
# 3. Add the second base table to the merge table.
1108
# ALTER TABLE <merge table> UNION(<first base table>, <second merge table>)
1109
merge_init_n build_partner1 ; build_partner2 ; create_merge ;
1112
| INSERT_METHOD = insert_method_value | INSERT_METHOD = insert_method_value | INSERT_METHOD = insert_method_value ;
1113
insert_method_value:
1117
# DROP two tables is in "drop_table_list"
1118
DROP TABLE if_exists merge_table_item_n ;
1120
merge_table_sequence:
1122
# There is a significant likelihood that random picked table names as base for the merge table cannot
1123
# be used for the creation of a merge table because the corresponding tables
1125
# - use the storage engine MyISAM
1126
# - have the same layout
1127
# Therefore we create here all we need.
1128
# The use of "base_table_name_n" for the tables to be merged guarantees that these tables
1129
# are under full DDL/DML load.
1130
# 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.
1131
$sequence_begin merge_init_s build_partner1 ; build_partner2 ; create_merge ; wait_till_drop_table ; DROP TABLE $mt $sequence_end ;
1134
# We do not change here the UNION because of the high risk that this fails.
1135
# It is intentional that we use merge_table_name and not merge_table_name_n.
1136
ALTER ignore TABLE merge_table_item_n COMMENT = 'UPDATED NOW()' |
1137
ALTER TABLE merge_table_item_n INSERT_METHOD = insert_method_value ;
1140
/* 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 */ ;
1142
/* 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 */ ;
1144
# This also initializes $database_name and $base_table_name which gets used by the other commands within the sequence.
1145
CREATE TABLE if_not_exists $mp1 LIKE $template_table_item ; ALTER TABLE $mp1 ENGINE = MyISAM ; INSERT INTO $mp1 SELECT * FROM $template_table_item ;
1147
# This also initializes $database_name and $base_table_name which gets used by the other commands within the sequence.
1148
CREATE TABLE if_not_exists $mp2 LIKE $template_table_item ; ALTER TABLE $mp2 ENGINE = MyISAM ; INSERT INTO $mp2 SELECT * FROM $template_table_item ;
1150
CREATE TABLE if_not_exists $mt LIKE $template_table_item ; ALTER TABLE $mt ENGINE = MERGE UNION ( $mp1 , $mp2 ); COMMIT ;
1153
########## PARTITIONED TABLE DDL ####################
1155
create_part_table | create_part_table | create_part_table | create_part_table | create_part_table | create_part_table |
1158
part_table_sequence ;
1161
CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM partition_algorithm AS SELECT * FROM template_table_item |
1162
CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM partition_algorithm AS SELECT * FROM template_table_item |
1163
CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM partition_algorithm AS used_select ;
1165
partition_algorithm:
1166
# We do not need sophisticated partitioning here.
1167
PARTITION BY KEY (pk) PARTITIONS 2 |
1168
PARTITION BY LINEAR HASH(pk) PARTITIONS 3 ;
1171
# DROP two tables is in "drop_table_list"
1172
DROP TABLE if_exists part_table_item_n ;
1175
ALTER ignore TABLE part_table_item_n alter_part_table_part ;
1177
alter_part_table_part:
1178
partition_algorithm |
1179
COMMENT = 'UPDATED NOW()' ;
1181
part_table_sequence:
1182
$sequence_begin CREATE TABLE if_not_exists part_table_item_s ENGINE = MyISAM partition_algorithm AS SELECT * FROM template_table_item ; COMMIT ; wait_till_drop_table ; DROP TABLE $part_table_item_s $sequence_end ;
1185
########## VIEW DDL ####################
1187
create_view | create_view | create_view | create_view | create_view | create_view | create_view | create_view |
1188
drop_view | alter_view |
1192
CREATE view_replace ALGORITHM = view_algoritm VIEW view_table_item_n AS used_select ;
1195
| | | | OR REPLACE ;
1197
UNDEFINED | MERGE | TEMPTABLE ;
1200
DROP VIEW if_exists view_table_item_n restrict_cascade ;
1203
# RESTRICT and CASCADE, if given, are parsed and ignored.
1204
| RESTRICT | CASCADE ;
1207
# Attention: Only changing the algorithm is not allowed.
1208
ALTER ALGORITHM = view_algoritm VIEW view_table_item_n AS used_select ;
1211
$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 ;
1214
########## STORED PROCEDURE DDL ####################
1216
create_procedure | create_procedure |
1217
drop_procedure | alter_procedure |
1218
procedure_sequence ;
1221
CREATE PROCEDURE procedure_item_n () BEGIN proc_stmt ; proc_stmt ; END ;
1226
DROP PROCEDURE if_exists procedure_item_n ;
1229
ALTER PROCEDURE procedure_item_n COMMENT 'UPDATED NOW()' ;
1232
# FIXME: The PROCEDURE should touch base_table_name_s only .
1233
$sequence_begin CREATE PROCEDURE procedure_item_s () BEGIN proc_stmt ; proc_stmt ; END ; COMMIT ; SELECT wait_short ; DROP PROCEDURE $procedure_item_s $sequence_end ;
1236
########## STORED FUNCTION DDL ####################
1238
create_function | create_function |
1239
drop_function | alter_function |
1243
CREATE FUNCTION function_item_n () RETURNS INTEGER BEGIN func_statement ; func_statement ; RETURN 1 ; END ;
1245
# All result sets of queries within a function must be processed within the function.
1246
# -> Use a CURSOR or SELECT ... INTO ....
1247
SET @my_var = 1 | SELECT MAX( random_field_quoted1 ) FROM table_item INTO @my_var | insert | delete ;
1250
DROP FUNCTION if_exists function_item_n ;
1253
ALTER FUNCTION function_item_n COMMENT 'UPDATED NOW()' ;
1256
$sequence_begin CREATE FUNCTION function_item_s () RETURNS INTEGER RETURN ( SELECT MOD( COUNT( DISTINCT random_field_quoted1 ) , 10 ) FROM table_item_s ) ; COMMIT ; SELECT wait_short ; DROP FUNCTION $function_item_s $sequence_end ;
1259
########## TRIGGER DDL ####################
1261
create_trigger | create_trigger |
1266
CREATE TRIGGER trigger_item_n trigger_time trigger_event ON base_table_name_n FOR EACH ROW BEGIN trigger_action ; END ;
1270
INSERT | DELETE | UPDATE ;
1272
insert | replace | delete | update | CALL procedure_item ;
1275
DROP TRIGGER if_exists trigger_item_n ;
1278
# FIXME: The action within the trigger should touch base_table_name_s only.
1279
$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 ;
1282
########## EVENT DDL ####################
1284
create_event | create_event | create_event | create_event | create_event | create_event | create_event | create_event |
1285
drop_event | alter_event | drop_event | alter_event | drop_event | alter_event | drop_event | alter_event |
1286
event_scheduler_on | event_scheduler_off ;
1288
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 ;
1289
completion_handling:
1290
ON COMPLETION not_or_empty PRESERVE ;
1292
DROP EVENT if_exists event_item_s ;
1294
ALTER EVENT event_item_s COMMENT 'UPDATED NOW()';
1296
########## DML ####################
1299
# Have only 10 % prepared statements.
1300
# SQL Statements to be handled via PREPARE, EXECUTE and DEALLOCATE cause a bigger amount of
1301
# failing statements than SQL statements which are executed in non prepared mode.
1302
# The reason is that we run the EXECUTE and DEALLOCATE independent of the outcome of the
1303
# PREPARE. So if the PREPARE fails because some table is missing, we loose the old
1304
# prepared statement handle, if there was any, and get no new one. Therefore the succeeding
1305
# EXECUTE and DEALLOCATE will also failcw because of missing statement handle.
1306
dml2 | dml2 | dml2 | dml2 | dml2 | dml2 | dml2 | dml2 | dml2 |
1307
PREPARE st1 FROM " dml2 " ; EXECUTE st1 ; DEALLOCATE PREPARE st1 ;
1310
select | select | select |
1311
do | insert | replace | delete | update | CALL procedure_item | show | is_selects ;
1313
########## DO ####################
1316
# A lot options like HIGH_PRIORITY (after SELECT ) etc. are not allowed in connection with DO.
1317
# The SELECT must give one column.
1318
DO ( SELECT COUNT(*) FROM table_item WHERE `pk` BETWEEN _digit[invariant] AND _digit[invariant] + 20 ) |
1319
DO user_lock_action ;
1322
IS_FREE_LOCK(TRIM(' _digit ')) |
1323
IS_USED_LOCK(TRIM(' _digit ')) |
1324
RELEASE_LOCK(TRIM(' _digit ')) |
1325
GET_LOCK(TRIM(' _digit '), 0.5 * rand_val * $life_time_unit ) ;
1327
########## SELECT ####################
1329
select_normal | select_normal | select_normal | select_normal | select_with_sleep ;
1332
# select = Just a query = A statement starting with "SELECT".
1333
select_part1 addition into for_update_lock_in_share_mode ;
1336
# Run a SELECT which holds locks (if there are any) longer.
1337
SELECT 1 FROM table_item WHERE wait_short = 0 LIMIT 1 ;
1340
# used_select = The SELECT used in CREATE VIEW/TABLE ... AS SELECT, INSERT INTO ... SELECT
1341
# "PROCEDURE ANALYSE" and "INTO DUMPFILE/OUTFILE/@var" are not generated because they
1342
# are partially disallowed or cause garbage (PROCEDURE).
1343
select_part1 addition_no_procedure ;
1346
SELECT high_priority cache_results table_field_list_or_star FROM table_in_select as A ;
1349
| sql_no_cache | sql_cache ;
1360
# Attention: In case of CREATE VIEW a subquery in the FROM clause (derived table) is disallowed.
1361
# Therefore they should be rare.
1362
table_item | table_item | table_item | table_item | table_item |
1363
( SELECT table_field_list_or_star FROM table_item ) ;
1366
# Involve one (simple where condition) or two tables (subquery | join | union)
1367
where procedure_analyze |
1368
subquery procedure_analyze |
1369
join where procedure_analyze |
1370
procedure_analyze union where ;
1372
addition_no_procedure:
1373
# Involve one (simple where condition) or two tables (subquery | join | union)
1374
# Don't add procedure_analyze.
1375
where | where | where | where | where | where | where |
1381
# The very selective condition is intentional.
1382
# It should ensure that
1383
# - result sets (just SELECT) do not become too big because this affects the performance in general and
1384
# the memery consumption of RQG (I had a ~ 3.5 GB virt memory RQG perl process during some simplifier run!)
1385
# - tables (INSERT ... SELECT, REPLACE) do not become too big
1386
# - tables (DELETE) do not become permanent empty
1387
# Please note that there are some cases where LIMIT cannot be used.
1388
WHERE `pk` BETWEEN _digit[invariant] AND _digit[invariant] + 1 |
1389
WHERE function_item () = _digit AND `pk` = _digit ;
1392
UNION SELECT * FROM table_in_select as B ;
1395
# Do not place a where condition here.
1396
NATURAL JOIN table_item B ;
1402
WHERE A.`pk` IN ( SELECT `pk` FROM table_item AS B WHERE B.`pk` = ;
1404
subquery_part1 A.`pk` ) ;
1406
subquery_part1 _digit ) ;
1409
# Correct place of PROCEDURE ANALYSE( 10 , 2000 )
1410
# 0. Attention: The result set of the SELECT gets replaced by PROCEDURE ANALYSE output.
1411
# 1. WHERE ... PROCEDURE (no UNION of JOIN)
1412
# 2. SELECT ... PROCEDURE UNION SELECT ... (never after UNION)
1413
# 3. SELECT ... FROM ... PROCEDURE ... JOIN (never at statement end)
1414
# 4. Never in a SELECT which does not use a table
1415
# 5. Any INTO DUMPFILE/OUTFILE/@var must be after PROCEDURE ANALYSE.
1416
# The content of DUMPFILE/OUTFILE/@var is from the PROCEDURE ANALYSE result set.
1417
# 6. CREATE TABLE ... AS SELECT PROCEDURE -> The table contains the PROCEDURE result set.
1418
# 7. INSERT ... SELECT ... PROCEDURE -> It's tried to INSERT the PROCEDURE result set.
1419
# High likelihood of ER_WRONG_VALUE_COUNT_ON_ROW
1422
PROCEDURE ANALYSE( 10 , 2000 ) ;
1430
# INSERT ... SELECT ... INTO DUMPFILE/OUTFILE/@var is not allowed
1431
# This also applies to CREATE TABLE ... AS SELECT ... INTO DUMPFILE/OUTFILE/@var
1432
# 1. @_letter is in average not enough variables compared to the column list.
1433
# -> @_letter disabled till I find a solution.
1434
# 2. DUMPFILE requires a result set of one row
1435
# Therefore 1172 Result consisted of more than one row is very likely.
1436
# OUTFILE _tmpnam | DUMPFILE _tmpnam | @_letter ;
1439
for_update_lock_in_share_mode:
1440
| for_update | lock_share ;
1448
LOCK IN SHARE MODE ;
1451
########## INSERT ####################
1453
insert_normal | insert_normal | insert_normal | insert_normal | insert_with_sleep ;
1455
INSERT low_priority_delayed_high_priority ignore into_word table_item simple_or_complicated on_duplicate_key_update ;
1456
simple_or_complicated:
1457
( random_field_quoted1 ) VALUES ( digit_or_null ) |
1458
braced_table_field_list used_select LIMIT 1 ;
1459
on_duplicate_key_update:
1462
ON DUPLICATE KEY UPDATE random_field_quoted1 = _digit ;
1464
INSERT ignore INTO table_item ( table_field_list ) SELECT $table_field_list FROM table_item WHERE wait_short = 0 LIMIT 1 ;
1467
########## REPLACE ####################
1469
# 1. No ON DUPLICATE .... option. In case of DUPLICATE key it runs DELETE old row INSERT new row.
1470
# 2. HIGH_PRIORITY is not allowed
1471
REPLACE low_priority_delayed into_word table_item simple_or_complicated ;
1474
########## DUMP_LOAD_DATA ####################
1475
dump_load_data_sequence:
1476
# We omit a lot stuff which could be assigned after the table name. This stuff should
1477
# be important for locking tests.
1478
# We generate an outfile so that we have a chance to find an infile.
1479
# Go with the next command as soon as "LOCAL" is supported. (not supported in 5.4)
1480
# generate_outfile ; LOAD DATA low_priority_concurrent local_or_empty INFILE tmpnam replace_ignore INTO TABLE table_item ;
1481
generate_outfile ; LOAD DATA low_priority_concurrent INFILE tmpnam replace_ignore INTO TABLE table_item ;
1483
SELECT * FROM template_table_item INTO OUTFILE _tmpnam ;
1484
low_priority_concurrent:
1485
| low_priority | concurrent ;
1487
# Only 20 % <> empty.
1488
| | | | CONCURRENT ;
1490
| replace_option | ignore ;
1493
########## GRANT_REVOKE ####################
1494
# We mix here some trouble I can imagine on mysql.tables_priv. It's basically how we access it's content.
1496
GRANT ALL ON table_item TO otto@localhost |
1497
REVOKE ALL ON table_item FROM otto@localhost |
1498
SELECT COUNT(*) FROM mysql.tables_priv WHERE user = LOWER('OTTO') |
1499
DELETE FROM mysql.tables_priv WHERE user = LOWER('OTTO') ; FLUSH PRIVILEGES |
1500
/* 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 |
1501
SELECT COUNT(*) FROM information_schema.table_privileges WHERE grantee LIKE '%OTTO%' |
1502
SHOW GRANTS FOR otto@localhost ;
1504
########## SQL MODE ########################
1506
empty_mode | empty_mode | empty_mode | empty_mode |
1507
empty_mode | empty_mode | empty_mode | empty_mode |
1508
empty_mode | empty_mode | empty_mode | empty_mode |
1511
SET SESSION SQL_MODE='' ;
1513
SET SESSION SQL_MODE=LOWER('TRADITIONAL');
1516
########## DELETE ####################
1517
# FIXME: DELETE IGNORE is missing
1519
delete_normal | delete_normal | delete_normal | delete_normal | delete_with_sleep ;
1521
# LIMIT row_count is disallowed in case we have a multi table delete.
1522
# Example: DELETE low_priority quick ignore A , B FROM table_item AS A join where LIMIT _digit |
1523
# DELETE is ugly because a table alias is not allowed.
1524
DELETE low_priority quick ignore FROM table_item WHERE `pk` > _digit LIMIT 1 |
1525
DELETE low_priority quick ignore A , B FROM table_item AS A join where |
1526
DELETE low_priority quick ignore A FROM table_item AS A where_subquery ;
1530
DELETE low_priority quick FROM table_item WHERE `pk` + wait_short = _digit ;
1533
########## UPDATE ####################
1535
update_normal | update_normal | update_normal | update_normal | update_with_sleep ;
1537
UPDATE low_priority ignore table_item SET random_field_quoted1 = _digit WHERE `pk` > _digit LIMIT _digit |
1538
UPDATE low_priority ignore table_item AS A join SET A. random_field_quoted1 = _digit , B. random_field_quoted1 = _digit ;
1540
UPDATE low_priority ignore table_item SET random_field_quoted1 = _digit WHERE wait_short = 0 LIMIT 1 ;
1543
########## LOCK/UNLOCK ####################
1545
lock | unlock | unlock | unlock | unlock ;
1547
LOCK TABLES lock_list ;
1549
# Less likelihood for lists, because they
1550
# - are most probably less often used
1551
# - cause a higher likelihood of "table does not exist" errors.
1552
lock_item | lock_item | lock_item | lock_item | lock_item | lock_item | lock_item | lock_item | lock_item |
1553
lock_item , lock_item ;
1555
# Have a low risk to get a clash of same table alias.
1556
table_item AS _letter lock_type ;
1558
READ local_or_empty |
1559
low_priority WRITE |
1560
IN SHARE MODE nowait |
1561
IN SHARE MODE nowait |
1562
IN SHARE MODE nowait |
1563
IN EXCLUSIVE MODE nowait |
1564
IN EXCLUSIVE MODE nowait |
1565
IN EXCLUSIVE MODE nowait ;
1573
########## FLUSH ####################
1575
# WITH READ LOCK causes that nearly all following statements will fail with
1576
# Can't execute the query because you have a conflicting read lock
1577
# Therefore it should
1579
# - last only very short time
1580
# So I put it into a sequence with FLUSH ... ; wait a bit ; UNLOCK TABLES
1581
FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list |
1582
FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list |
1583
FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list |
1584
FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list |
1585
FLUSH TABLES | FLUSH TABLES | FLUSH TABLES |
1586
FLUSH TABLES | FLUSH TABLES | FLUSH TABLES |
1587
FLUSH TABLES table_list WITH READ LOCK | UNLOCK TABLES | UNLOCK TABLES |
1588
FLUSH TABLES WITH READ LOCK ; SELECT wait_short ; UNLOCK TABLES ;
1591
########## TINY GRAMMAR ITEMS USED AT MANY PLACES ###########
1595
braced_table_field_list:
1596
# In case of <empty> for braced_table_field_list we have a significant fraction of
1597
# INSERT/REPLACE INTO <table> <no field list>
1598
# failing with: 1394 Can not insert into join view 'test.t1_view_0_S' without fields list
1599
# Therefore <empty> is only 20 %.
1600
( table_field_list ) | ( table_field_list ) | ( table_field_list ) | ( table_field_list ) | ;
1602
comparison_operator:
1614
_digit | _digit | _digit | _digit | _digit | _digit | _digit | _digit | _digit |
1618
MEMORY | MyISAM | InnoDB ;
1625
| | | | | | | | | DELAYED ;
1629
| | | | HIGH_PRIORITY ;
1637
# 90 %, this reduces the amount of failing DROPs
1638
| IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS | IF EXISTS ;
1641
# 90 %, this reduces the amount of failing CREATEs
1642
| 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 ;
1652
low_priority_delayed_high_priority:
1653
| low_priority | delayed | high_priority ;
1655
low_priority_delayed:
1656
| low_priority | delayed ;
1674
random_field_quoted:
1675
'int_key' | 'int' | 'pk' ;
1677
random_field_quoted1:
1678
`col_int_key` | `col_int` | `pk` ;
1681
# Only 20 % <> empty.
1689
| SQL_BUFFER_RESULT ;
1691
table_field_list_or_star:
1692
table_field_list | table_field_list | table_field_list | table_field_list |
1693
{ $table_field_list = "*" } ;
1696
# It is intentional that the next line will lead to ER_FIELD_SPECIFIED_TWICE
1697
# in case it is used in INSERT INTO <table> ( table_field_list )
1698
{ $table_field_list = "`pk` , `col_int_key` , `pk` "} |
1699
{ $table_field_list = "`col_int_key` , `col_int` , `pk` "} |
1700
{ $table_field_list = "`col_int_key` , `pk` , `col_int` "} |
1701
{ $table_field_list = "`col_int` , `pk` , `col_int_key` "} |
1702
{ $table_field_list = "`col_int` , `col_int_key` , `pk` "} |
1703
{ $table_field_list = "`pk` , `col_int` , `col_int_key` "} |
1704
{ $table_field_list = "`pk` , `col_int_key` , `col_int` "} ;
1708
# Do not apply CREATE/DROP TEMPORARY on "long life" whatever tables.
1709
# Use "short life" (-> <whatever>_n) tables only.
1710
# 1. In case of "long life" (-> <whatever>_s) tables the CREATE and DROP must be within
1711
# a sequence with some "wait_till_drop_table" between. TEMPORARY tables are session specific.
1712
# So no other session can use this table.
1713
# 2. In case of "short life" tables the CREATE and DROP are isolated. So the session
1714
# which created the table will pick a random statement and maybe do something on
1715
# the table <> DROP.
1716
# Only 10 % because no other session can use this table.
1721
SLEEP( 0.5 * rand_val * $life_time_unit ) ;