37
37
# .. there are a lot more please search for open bugs reported by Matthias Leich
40
# - Correct spelling is "namespace" and not "name space"
40
# - Adjust grammar to new open and old fixed bugs
41
# - Add INDEXES and HANDLER ... NEXT | PREV | LAST
42
# (Bug#51355 handler stmt cause assertion in bool MDL_context::try_acquire_lock(MDL_request*))
41
43
# - Add TRUNCATE PARTITION and check if we are missing any other related DDL.
42
44
# (Bug #49907 ALTER TABLE ... TRUNCATE PARTITION does not wait for locks on the table)
43
# - Observation of Philip when using greater values for name_space_width
44
# - the database never actually gets the expected number of objects.
45
# Even if the DROPs are removed ,then still the database grows very slowly towards the name_space size.
46
# - There are a lot of CREATE TABLE table2 SELECT * FROM table1 and similar constructs in order to clone database objects.
47
# Unfortunately, at higher name_space values, table1 is not very likely to exist, and therefore table2 is also unlikely to be created.
49
# - reduce the likelihood of DROP TABLE <table with short lifetime>
50
# --> such tables get an a bit longer lifetime
51
# - reduce the likelihood that CREATE TABLE <table with short lifetime> uses "used_select"
45
# - Check the impact of the latest modifications (use "used_select" less often) on the issues
47
# When using greater values for namespace_width
48
# - the database never actually gets the expected number of objects.
49
# Even if the DROPs are removed ,then still the database grows very slowly towards the namespace size.
50
# - There are a lot of CREATE TABLE table2 SELECT * FROM table1 and similar constructs in order to clone
51
# database objects. Unfortunately, at higher namespace values, table1 is not very likely to exist, and
52
# therefore table2 is also unlikely to be created.
52
53
# - Add subtest for
53
54
# Bug #48315 Metadata lock is not taken for merged views that use an INFORMATION_SCHEMA table
60
61
# PROCEDURES etc. are complete missing.
61
62
# Could I inject this in a subquery?
62
63
# - Simplify grammar:
63
# Name space concept is good for grammar development, avoiding failing statements,
64
# Namespace concept is good for grammar development, avoiding failing statements,
64
65
# understanding statement logs but bad for grammar simplification speed.
66
67
# Bug#45225 Locking: hang if drop table with no timeout
75
76
# - have tables of "special" types (partitioned, view, merge etc.)
76
77
# - variate the storage engine
77
78
# within your object creation grammar file.
78
# 2. Have separated name spaces for objects (tables etc.) with configurable width.
79
# 2. Have separated namespaces for objects (tables etc.) with configurable width.
79
80
# - This allows to reduce the likelihood of applying a statement in general or an option to
80
81
# an object which is not allowed. Example: TRUNCATE TABLE <view>
81
# - Debugging of grammar and understanding server logs becomes easier if the name space
82
# - Debugging of grammar and understanding server logs becomes easier if the namespace
82
83
# for an object of some type contains type related strings like "base","temp",.. etc.
83
84
# Example: If there is a
84
# CREATE VIEW <name which does not belong into the VIEW name space>
85
# CREATE VIEW <name which does not belong into the VIEW namespace>
85
86
# than something works not like intended.
86
# - The configurable name space width (-> $name_space_width) allows to influence the likelihood
87
# - The configurable namespace width (-> $namespace_width) allows to influence the likelihood
87
88
# that some statement hits an object. This gives some control over how much the system is stressed.
88
89
# - The non default option to put all table related objects (base tables, views, etc.) allows
89
90
# some additional increase of the stress though the likelihood of failing statement raises.
90
# 3. Distinct between two kinds of object name spaces and treat the corresponding objects different.
91
# 3. Distinct between two kinds of object namespaces and treat the corresponding objects different.
91
92
# This is experimental and might be removed in case it does not fulfill the expectations.
92
93
# "Sequence" ("_S"):
93
94
# - statement sequence: CREATE object, fill in content (if applicable), COMMIT, wait some
180
181
# Advantage: Less failing (table does not exist ...) statements within the first phase of the test.
181
# init_basics : init_name_spaces ; event_scheduler_on ; have_some_initial_objects ;
182
# init_basics : init_namespaces ; event_scheduler_on ; have_some_initial_objects ;
183
184
# Advantage: Better performance during bug hunt, test simplification etc. because objects are created at
184
185
# on place (<object>_ddl) only and not also in "have_some_initial_objects".
185
init_basics ; init_name_spaces ;
186
init_basics ; init_namespaces ;
188
189
# 1. $life_time_unit = maximum lifetime of a table created within a CREATE, wait, DROP sequence.
205
206
# one_thread_correction will correct $life_time_unit to 0 if we have only one "worker" thread.
207
# 2. $name_space_width = Width of a name space
208
# 2. $namespace_width = Width of a namespace
209
210
# Smaller numbers cause a
210
211
# - lower fraction of statements failing because of missing object
214
215
# - In case of one thread a $life_time_unit <> 0 does not make sense, because there is no parallel
215
216
# "worker" thread which could do something with the object during the "wait" period.
216
{ $life_time_unit = 1 ; $name_space_width = 2 ; if ( $ENV{RQG_THREADS} == 1 ) { $life_time_unit = 0 } ; return undef } avoid_bugs ; nothing_disabled ; system_table_stuff ;
217
{ $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 ;
219
220
# Please choose between the following alternatives
220
221
# separate_objects -- no_separate_objects
221
222
# separate_normal_sequence -- no_separate_normal_sequence
239
240
{ $base_piece="base" ; $temp_piece="temp" ; $merge_piece="merge" ; $part_piece="part" ; $view_piece="view" ; return undef } ;
240
241
no_separate_table_types:
241
242
# Expected impact:
242
# - maybe higher load on tables of all types in general (depends on size of name space)
243
# - maybe higher load on tables of all types in general (depends on size of namespace)
243
244
# - a significant fraction of statements will fail with
244
245
# 1. 1064 "You have an error in your SQL syntax ..."
245
246
# Example: TRUNCATE <view>
288
289
have_some_initial_objects:
289
290
# It is assumed that this reduces the likelihood of "Table does not exist" significant when running with a small number of "worker" threads.
290
# The amount of create_..._table items within the some_..._tables should depend a bit on the value in $name_space_width but I currently
291
# The amount of create_..._table items within the some_..._tables should depend a bit on the value in $namespace_width but I currently
291
292
# do not know how to express this in the grammar.
293
294
# Bug#47633 assert in ha_myisammrg::info during OPTIMIZE
329
330
{ $rand_val = $prng->int(0,100) / 100 } ;
332
# Name spaces of objects ==========================================================================#
333
# An explanation of the name space concept is on top of this file.
333
# Namespaces of objects ==========================================================================#
334
# An explanation of the namespace concept is on top of this file.
335
# 1. The database name space ##########################################################################
336
# 1. The database namespace ##########################################################################
337
338
{ $database_name_s = $database_prefix . $sequence_piece ; $database_name = $database_name_s } ;
339
340
{ $database_name_n = $database_prefix . $normal_piece ; $database_name = $database_name_n } ;
341
# Get a random name from the "database" name space.
342
# Get a random name from the "database" namespace.
342
343
# $database_name gets automatically filled when database_name_s or database_name_n is executed.
343
344
database_name_s | database_name_n ;
346
# 2. The base table name space ########################################################################
347
# 2. The base table namespace ########################################################################
347
348
base_table_name_s:
348
# Get a random name from the "base table long life" name space.
349
{ $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 } ;
349
# Get a random name from the "base table long life" namespace.
350
{ $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 } ;
350
351
base_table_name_n:
351
# Get a random name from the "base table short life" name space.
352
{ $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 } ;
352
# Get a random name from the "base table short life" namespace.
353
{ $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 } ;
354
# Get a random name from the "base table" name space.
355
# Get a random name from the "base table" namespace.
355
356
base_table_name_s | base_table_name_n ;
357
358
# Sometimes useful stuff:
369
370
base_table_item | base_table_item , base_table_item ;
372
# 3. The temp table name space ########################################################################
373
# 3. The temp table namespace ########################################################################
373
374
# Please note that TEMPORARY merge tables will be not generated.
374
375
temp_table_name_s:
375
# Get a random name from the "temp table long life" name space.
376
{ $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 } ;
376
# Get a random name from the "temp table long life" namespace.
377
{ $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 } ;
377
378
temp_table_name_n:
378
# Get a random name from the "temp table short life" name space.
379
{ $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 } ;
379
# Get a random name from the "temp table short life" namespace.
380
{ $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 } ;
381
# Get a random name from the "temp table" name space.
382
# Get a random name from the "temp table" namespace.
382
383
temp_table_name_s | temp_table_name_n ;
384
385
# Sometimes useful stuff:
396
397
temp_table_item | temp_table_item , temp_table_item ;
399
# 4. The merge table name space #######################################################################
400
# 4. The merge table namespace #######################################################################
400
401
# Please note that TEMPORARY merge tables will be not generated.
401
402
merge_table_name_s:
402
# Get a random name from the "merge table long life" name space.
403
{ $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 } ;
403
# Get a random name from the "merge table long life" namespace.
404
{ $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 } ;
404
405
merge_table_name_n:
405
# Get a random name from the "merge table short life" name space.
406
{ $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 } ;
406
# Get a random name from the "merge table short life" namespace.
407
{ $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 } ;
407
408
merge_table_name:
408
# Get a random name from the "merge table" name space.
409
# Get a random name from the "merge table" namespace.
409
410
merge_table_name_s | merge_table_name_n ;
411
412
# Sometimes useful stuff:
423
424
merge_table_item | merge_table_item , merge_table_item ;
426
# 5. The view table name space ########################################################################
427
# 5. The view table namespace ########################################################################
427
428
view_table_name_s:
428
# Get a random name from the "view table long life" name space.
429
{ $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 } ;
429
# Get a random name from the "view table long life" namespace.
430
{ $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 } ;
430
431
view_table_name_n:
431
# Get a random name from the "view table short life" name space.
432
{ $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 } ;
432
# Get a random name from the "view table short life" namespace.
433
{ $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 } ;
434
# Get a random name from the "view table" name space.
435
# Get a random name from the "view table" namespace.
435
436
view_table_name_s | view_table_name_n ;
437
438
# Sometimes useful stuff:
449
450
view_table_item | view_table_item , view_table_item ;
452
# 6. The partitioned table name space #################################################################
453
# 6. The partitioned table namespace #################################################################
453
454
part_table_name_s:
454
# Get a random name from the "part table long life" name space.
455
{ $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 } ;
455
# Get a random name from the "part table long life" namespace.
456
{ $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 } ;
456
457
part_table_name_n:
457
# Get a random name from the "part table short life" name space.
458
{ $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 } ;
458
# Get a random name from the "part table short life" namespace.
459
{ $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 } ;
460
# Get a random name from the "part table" name space.
461
# Get a random name from the "part table" namespace.
461
462
part_table_name_s | part_table_name_n ;
463
464
# Sometimes useful stuff:
475
476
part_table_item | part_table_item , part_table_item ;
478
# 7. Mixed name spaces of tables ################################################################
479
# 7. Mixed namespaces of tables ################################################################
480
481
# 7.1 All tables ( base/temp/merge tables + views + ... #########################################
512
513
base_temp_view_table_item_s | base_temp_view_table_item ;
515
# 8. Other name spaces ##############################################################a
516
# 8. Other namespaces ##############################################################a
516
517
template_table_item:
517
518
{ $template_table_item = "test.table0_int_autoinc" } |
518
519
{ $template_table_item = "test.table1_int_autoinc" } |
522
523
procedure_name_s:
523
524
# Get a random name from the "procedure long life" namespace.
524
{ $procedure_name_s = $procedure_prefix . $prng->int(1,$name_space_width) . $sequence_piece ; $procedure_name = $procedure_name_s } ;
525
{ $procedure_name_s = $procedure_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $procedure_name = $procedure_name_s } ;
525
526
procedure_name_n:
526
527
# Get a random name from the "procedure short life" namespace.
527
{ $procedure_name_n = $procedure_prefix . $prng->int(1,$name_space_width) . $normal_piece ; $procedure_name = $procedure_name_n } ;
528
{ $procedure_name_n = $procedure_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $procedure_name = $procedure_name_n } ;
529
530
# Get a random name from the "procedure" namespace.
530
531
procedure_name_s | procedure_name_n ;
541
542
# Get a random name from the "function long life" namespace.
542
{ $function_name_s = $function_prefix . $prng->int(1,$name_space_width) . $sequence_piece ; $function_name = $function_name_s } ;
543
{ $function_name_s = $function_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $function_name = $function_name_s } ;
544
545
# Get a random name from the "function short life" namespace.
545
{ $function_name_n = $function_prefix . $prng->int(1,$name_space_width) . $normal_piece ; $function_name = $function_name_n } ;
546
{ $function_name_n = $function_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $function_name = $function_name_n } ;
547
548
# Get a random name from the "function" namespace.
548
549
function_name_s | function_name_n ;
558
559
# Get a random name from the "trigger long life" namespace.
559
{ $trigger_name_s = $trigger_prefix . $prng->int(1,$name_space_width) . $sequence_piece ; $trigger_name = $trigger_name_s } ;
560
{ $trigger_name_s = $trigger_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $trigger_name = $trigger_name_s } ;
561
562
# Get a random name from the "trigger short life" namespace.
562
{ $trigger_name_n = $trigger_prefix . $prng->int(1,$name_space_width) . $normal_piece ; $trigger_name = $trigger_name_n } ;
563
{ $trigger_name_n = $trigger_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $trigger_name = $trigger_name_n } ;
564
565
# Get a random name from the "trigger" namespace.
565
566
trigger_name_s | trigger_name_n ;
575
576
# Get a random name from the "event long life" namespace.
576
{ $event_name_s = $event_prefix . $prng->int(1,$name_space_width) . $sequence_piece ; $event_name = $event_name_s } ;
577
{ $event_name_s = $event_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $event_name = $event_name_s } ;
578
579
# Get a random name from the "event short life" namespace.
579
{ $event_name_n = $event_prefix . $prng->int(1,$name_space_width) . $normal_piece ; $event_name = $event_name_n } ;
580
{ $event_name_n = $event_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $event_name = $event_name_n } ;
581
582
# Get a random name from the "event" namespace.
582
583
event_name_s | event_name_n ;
906
908
DROP TABLE if_exists base_table_item_n restrict_cascade ;
908
910
alter_base_table:
909
ALTER online ignore TABLE base_table_item_n alter_base_temp_table_part ;
911
ALTER ignore TABLE base_table_item_n alter_base_temp_table_part ;
911
913
alter_base_temp_table_part:
912
914
# Reasons why "ENGINE = engine" should be rather rare:
939
941
DROP TABLE if_exists temp_table_item_n ;
941
943
alter_temp_table:
942
ALTER online ignore TABLE temp_table_item_n alter_base_temp_table_part ;
944
ALTER ignore TABLE temp_table_item_n alter_base_temp_table_part ;
944
946
########## MAINTENANCE FOR ANY TABLE ####################
945
947
# The server accepts these statements for all table types (VIEWs, base tables, ...) though they
1070
1072
# A simple change of the insert_method_value is also not doable because we
1071
1073
# would need to mention also the UNION.
1072
1074
# It is intentional that we use merge_table_name and not merge_table_name_n.
1073
ALTER online ignore TABLE merge_table_item_n COMMENT = 'UPDATED NOW()' ;
1075
ALTER ignore TABLE merge_table_item_n COMMENT = 'UPDATED NOW()' ;
1076
1078
/* 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 */ ;
1094
1096
part_table_sequence ;
1096
1098
create_part_table:
1099
CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM partition_algorithm AS SELECT * FROM template_table_item |
1100
CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM partition_algorithm AS SELECT * FROM template_table_item |
1101
CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM partition_algorithm AS used_select ;
1103
partition_algorithm:
1097
1104
# We do not need sophisticated partitioning here.
1098
CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM PARTITION BY KEY (pk) PARTITIONS 2 AS used_select ;
1105
PARTITION BY KEY (pk) PARTITIONS 2 |
1106
PARTITION BY LINEAR HASH(pk) PARTITIONS 3 ;
1100
1108
drop_part_table:
1101
1109
# DROP two tables is in "drop_table_list"
1102
1110
DROP TABLE if_exists part_table_item_n ;
1104
1112
alter_part_table:
1105
ALTER online ignore TABLE part_table_item_n alter_part_table_part ;
1113
ALTER ignore TABLE part_table_item_n alter_part_table_part ;
1107
1115
alter_part_table_part:
1108
PARTITION BY LINEAR HASH(pk) PARTITIONS 3 |
1109
COMMENT = 'UPDATED NOW()' ;
1116
partition_algorithm |
1117
COMMENT = 'UPDATED NOW()' ;
1111
1119
part_table_sequence:
1112
$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 ;
1120
$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 ;
1115
1123
########## VIEW DDL ####################