~drizzle-trunk/drizzle/development : revision 0.99.30

1

2

# Use is subject to license terms.

3

#

4

# This program is free software; you can redistribute it and/or modify

5

# it under the terms of the GNU General Public License as published by

6

# the Free Software Foundation; version 2 of the License.

7

#

8

# This program is distributed in the hope that it will be useful, but

9

# WITHOUT ANY WARRANTY; without even the implied warranty of

10

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU

11

# General Public License for more details.

12

#

13

# You should have received a copy of the GNU General Public License

14

# along with this program; if not, write to the Free Software

15

# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301

16

# USA

17

18

# WL#5004 Comprehensive Locking Stress Test for Azalea

19

#

20

# Grammar for testing DML, DDL, FLUSH, LOCK/UNLOCK, transactions

21

#

22

# Created:

23

# 2009-07 Matthias Leich (A few grammar items were taken from other grammar files.)

24

#

25

# Attention:

26

# There are modified grammar items because of

27

# - Bug#46339 crash on REPAIR TABLE merge table USE_FRM

28

# - Bug#46425 crash in Diagnostics_area::set_ok_status , empty statement, DELETE IGNORE

29

# - Bug#46224 HANDLER statements within a transaction might lead to deadlocks

30

# - Bug#46965 crash in ha_innobase::get_auto_increment

31

# - Bug#47633 assert in ha_myisammrg::info during OPTIMIZE

32

#

33

# Nothing disabled till now for

34

# - Bug#45966 Crash in MDL_context::release_ticket in .\include\master-slave-reset.inc

35

# - Bug#40419 Not locking metadata on alter procedure

36

# Duplicate of Bug#30977 Concurrent statement using stored function and DROP FUNCTION breaks SBR

37

# .. there are a lot more please search for open bugs reported by Matthias Leich

38

#

39

# TODO:

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*))

43

# - Add TRUNCATE PARTITION and check if we are missing any other related DDL.

44

# (Bug #49907 ALTER TABLE ... TRUNCATE PARTITION does not wait for locks on the table)

45

# - Check the impact of the latest modifications (use "used_select" less often) on the issues

46

# reported by Philip

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.

53

# - Add subtest for

54

# Bug #48315 Metadata lock is not taken for merged views that use an INFORMATION_SCHEMA table

55

# Simple:

56

# Philip: using an I_S in a meaningless subselect would be best, just have

57

# ( SELECT user + 0 FROM INFORMATION_SCHEMA.USERS LIMIT 1)

58

# Complete:

59

# mleich:

60

# But IS tables used in VIEWs, SELECT, DELETE/UPDATE subqueries/join,

61

# PROCEDURES etc. are complete missing.

62

# Could I inject this in a subquery?

63

# - Simplify grammar:

64

# Namespace concept is good for grammar development, avoiding failing statements,

65

# understanding statement logs but bad for grammar simplification speed.

66

#

67

# Bug#45225 Locking: hang if drop table with no timeout

68

# Reporter , LockTableKiller might help

69

#

70

# General architecture rules:

71

# ---------------------------

72

# 1. Do not modify the objects created by gendata.pl within this grammar file.

73

# Work on copies of these objects instead. Hereby we prevent that we totally run out of tables

74

# or rows etc. This minimizes also any locks on the objects created by gendata.pl.

75

# Do not

76

# - have tables of "special" types (partitioned, view, merge etc.)

77

# - variate the storage engine

78

# within your object creation grammar file.

79

# 2. Have separated namespaces for objects (tables etc.) with configurable width.

80

# - This allows to reduce the likelihood of applying a statement in general or an option to

81

# an object which is not allowed. Example: TRUNCATE TABLE <view>

82

# - Debugging of grammar and understanding server logs becomes easier if the namespace

83

# for an object of some type contains type related strings like "base","temp",.. etc.

84

# Example: If there is a

85

# CREATE VIEW <name which does not belong into the VIEW namespace>

86

# than something works not like intended.

87

# - The configurable namespace width (-> $namespace_width) allows to influence the likelihood

88

# that some statement hits an object. This gives some control over how much the system is stressed.

89

# - The non default option to put all table related objects (base tables, views, etc.) allows

90

# some additional increase of the stress though the likelihood of failing statement raises.

91

# 3. Distinct between two kinds of object namespaces and treat the corresponding objects different.

92

# This is experimental and might be removed in case it does not fulfill the expectations.

93

# "Sequence" ("_S"):

94

# - statement sequence: CREATE object, fill in content (if applicable), COMMIT, wait some

95

# random timespan (-> SLEEP( ... * rand_val * $life_time_unit )) , DROP object

96

# - The COMMIT is intentional. It should ensure that the session running the sequence does

97

# not hold any locks on the object during the wait phase. I am aware that CREATE ... AS SELECT

98

# commits at end, but this might be changed somewhere in future.

99

# - the maximum of the random wait timespan is configurable (-> $max_table_life_time).

100

# - The object must be stored within a database created with the "_S" property.

101

# - No other DDL on this object

102

# This should ensure that other sessions have a chance to run several DML statements using this object

103

# before it gets dropped. The "Sequence" objects are of nearly no value when running with only one thread.

104

# "Normal" ("_N"):

105

# - CREATE and DROP for these objects are not within the same sequency.

106

# - Any session could run DDL (including DROP, ALTER, REPAIR etc.) on this object.

107

# - The object can be stored within any database.

108

# - It is assumed that they have a short lifetime.

109

# This should ensure that a session running a transaction with DML on this object has a chance to meet

110

# an attempt of another session to run DDL (especially ALTER) on this object.

111

# 4. There is some "generalization" (I am unsure if this is a good understandable term) of variables and

112

# a corresponding walk up of values.

113

# $database_name_* --> $database_name

114

# $base_table_name_* --> $base_table_name --> $table_name

115

# $temp_table_name_* --> $temp_table_name --> $table_name

116

# ...

117

# $part_table_name_* --> $part_table_name --> $table_name

118

# This means:

119

# If you run "table_item" which picks a table of random type (base table, temp table ...) and random lifetime

120

# and a corresponding database and automatically assigns values to variables ($database_*,$*_table_name_*)

121

# where the name cannot be predicted, you can find the generated names at least within

122

# $database_name and $table_name .

123

# Please be aware that for example a succeeding call of "procedure_item" modify the content of $database_name .

124

#

125

#

126

# Rules by thumb and experiences (important when extending this grammar file):

127

# ----------------------------------------------------------------------------

128

# 1. Any statement sequence has to be in one line.

129

# 2. Be aware of the dual use of ';'. It separates SQL statements in sequences and closes the definition block

130

# of a grammar item. So any ';' before some '|' has a significant impact.

131

# 3. Strange not intended effects: '|' or ':' instead of ';' ?

132

# 4. There is an open RQG problem with SHOW ... LIKE '<grammar item>'.

133

# 5. If there are needs to write some message into a server log than avoid the use of auxiliary SQL (SELECT <message> etc.).

134

# Use something like:

135

# /* <your text> */

136

# instead.

137

# 6. Use uppercase characters for strings and keywords in statements. This avoids any not intended

138

# treatment as grammar item.

139

# 7. Use the most simple option first in lists. This makes automatic grammar simplification

140

# which walks from right to left more efficient. Example:

141

# where:

142

# <empty> | WHERE `pk` BETWEEN _digit AND _digit | WHERE function_name_n() = _digit ;

143

#

144

145

# Naming conventions (default)

146

# ========================================================

147

#

148

# Pattern (standard configuration) | Object

149

# -----------------------------------------

150

# testdb_* | database

151

# t1_* | table/view

152

# p1_* | procedure

153

# f1_* | function

154

# p1_* | procedure

155

# tr1_* | trigger

156

157

# End of grammar item name (default) | characteristic

158

# -------------------------------------------------------

159

# _S | related to "sequence" object

160

# _N | related to "normal" object

161

#

162

# Within grammar item name | characteristic

163

# -----------------------------------------------

164

# _name | name of the object

165

# _item | <schema name> . <name of the object>

166

# _list | either single item (<schema name> . <name of the object>) or comma separated list

167

# | of such items

168

169

#

170

# Missing but not really important improvements:

171

# - Reduce the amount of cases where "sequence" objects have "normal" objects within their definition.

172

# --> views,functions,procedures

173

# - Reduce the amount of cases where the wrong table types occur within object definitions

174

# Example: TABLE for a TRIGGER or VIEW definition. Names of temporary tables could be computed but are not allowed.

175

#

176

177

178

# Section of easy changeable items with high impact on the test =============================================#

179

query_init:

180

# Variant 1:

181

# Advantage: Less failing (table does not exist ...) statements within the first phase of the test.

182

# init_basics : init_namespaces ; event_scheduler_on ; have_some_initial_objects ;

183

# Variant 2:

184

# Advantage: Better performance during bug hunt, test simplification etc. because objects are created at

185

# on place (<object>_ddl) only and not also in "have_some_initial_objects".

186

init_basics ; init_namespaces ;

187

188

init_basics:

189

# 1. $life_time_unit = maximum lifetime of a table created within a CREATE, wait, DROP sequence.

190

#

191

# A reasonable value is bigger than any "wait for <whatever> lock" timeout.

192

#

193

# There are till now not sufficient experiences about the impact of different values on the outcome of the test.

194

#

195

# sequence object | lifetime

196

# ------------------------------------------------

197

# database | 2 * RAND() * $life_time_unit

198

# table (no view) | 1 * RAND() * $life_time_unit

199

# view | 0.5 * RAND() * $life_time_unit

200

# procedure | 0.5 * RAND() * $life_time_unit

201

# function | 0.5 * RAND() * $life_time_unit

202

# trigger | 0.5 * RAND() * $life_time_unit

203

#

204

# A DML statement using SLEEP will use 0.5 * RAND() * $life_time_unit

205

#

206

# one_thread_correction will correct $life_time_unit to 0 if we have only one "worker" thread.

207

#

208

# 2. $namespace_width = Width of a namespace

209

#

210

# Smaller numbers cause a

211

# - lower fraction of statements failing because of missing object

212

# - higher fraction of clashes when running with multiple sessions

213

#

214

# Some notes:

215

# - In case of one thread a $life_time_unit <> 0 does not make sense, because there is no parallel

216

# "worker" thread which could do something with the object during the "wait" period.

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 ;

218

219

init_namespaces:

220

# Please choose between the following alternatives

221

# separate_objects -- no_separate_objects

222

# separate_normal_sequence -- no_separate_normal_sequence

223

# separate_table_types -- no_separate_table_types

224

# 1. Low amount of failing statements, low risk to run into known not locking related crashes

225

separate_objects ; separate_normal_sequence ; separate_table_types ;

226

# 2. Higher amount of failing statements, risk to run into known temporary table related crashes

227

# separate_objects ; separate_normal_sequence ; no_separate_table_types ;

228

# 3. Total chaos

229

# High amount of failing statements, risk to run into known temporary table related crashes

230

# no_separate_objects ; separate_normal_sequence ; no_separate_table_types ;

231

232

separate_table_types:

233

# Effect: Distinction between

234

# - base, temporary, merge and partioned tables + views

235

# - tables of any type and functions,procedures,triggers,events

236

# Only statements which are applicable to this type of table will be generated.

237

# Example: ALTER VIEW <existing partitioned table> ... should be not generated.

238

# Advantage: Less failing statements, logs are much easier to read

239

# Disadvantage: The avoided suitations are not tested.

240

{ $base_piece="base" ; $temp_piece="temp" ; $merge_piece="merge" ; $part_piece="part" ; $view_piece="view" ; return undef } ;

241

no_separate_table_types:

242

# Expected impact:

243

# - maybe higher load on tables of all types in general (depends on size of namespace)

244

# - a significant fraction of statements will fail with

245

# 1. 1064 "You have an error in your SQL syntax ..."

246

# Example: TRUNCATE <view>

247

# 2. <number <> 1064> <This option/whatever is not applicable to the current object/situation/whatever>

248

# This might look a bit ugly but it has the benefit that these statements are at least tried.

249

# The goal is not to check the parse process, but there might be temporary MDL locks or in worst

250

# case remaining permanent MDL lock. Effects of these locks should be also checked.

251

# Just as a reminder:

252

# A CREATE VIEW which fails with an error <> "You have an error in your SQL syntax" causes an implicit COMMIT

253

# of the current transaction.

254

{ $base_piece="" ; $temp_piece="" ; $merge_piece="" ; $part_piece="" ; $view_piece="" ; return undef } ;

255

separate_normal_sequence:

256

# Advantages/Disadvantages: To be discovered

257

{ $sequence_piece="_S" ; $normal_piece="_N" ; return undef } ;

258

no_separate_normal_sequence:

259

# Advantages/Disadvantages: To be discovered

260

{ $sequence_piece="" ; $normal_piece="" ; return undef } ;

261

separate_objects:

262

# Effect: Distinction between schemas, tables, functions, triggers, procedures and events

263

# Only statements which are applicable to this type of object will be generated.

264

# Example: CALL <existing partitioned table> ... should be not generated.

265

# Advantage: Less failing statements, logs are much easier to read

266

# Disadvantage: The avoided suitations are not tested.

267

{ $database_prefix="testdb" ; $table_prefix="t1_" ; $procedure_prefix="p1_" ; $function_prefix="f1_" ; $trigger_prefix="tr1_" ; $event_prefix="e1_" ; return undef } ;

268

no_separate_objects:

269

# Effect: At least no distinction between functions, triggers, procedures and events

270

# If no_separate_table_types is added, than also tables are no more separated.

271

# Example: CALL <existing partitioned table> ... should be not generated.

272

# Advantage: More coverage

273

# Disadvantage: More failing statements

274

{ $database_prefix="o1_1" ; $table_prefix="o1_" ; $procedure_prefix="o1_" ; $function_prefix="o1_" ; $trigger_prefix="o1_" ; $event_prefix="o1_" ; return undef } ;

275

276

avoid_bugs:

277

# Set this grammar item to "empty" if

278

# Bug#47338 assertion in handler::ha_external_lock --> optimizer_use_mrr='disable'

279

# Bug#47367 Crash in Name_resolution_context::process_error --> semijoin=off

280

# are fixed.

281

SET GLOBAL optimizer_use_mrr='disable' ; SET SESSION optimizer_use_mrr='disable' ; SET GLOBAL optimizer_switch = 'semijoin=off' ; SET SESSION optimizer_switch = 'semijoin=off' ;

282

283

event_scheduler_on:

284

SET GLOBAL EVENT_SCHEDULER = ON ;

285

286

event_scheduler_off:

287

SET GLOBAL EVENT_SCHEDULER = OFF ;

288

289

have_some_initial_objects:

290

# It is assumed that this reduces the likelihood of "Table does not exist" significant when running with a small number of "worker" threads.

291

# The amount of create_..._table items within the some_..._tables should depend a bit on the value in $namespace_width but I currently

292

# do not know how to express this in the grammar.

293

# Use if

294

# Bug#47633 assert in ha_myisammrg::info during OPTIMIZE

295

# is fixed (merge tables disabled)

296

# 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 ;

297

some_databases ; some_base_tables ; some_temp_tables ; some_part_tables ; some_view_tables ; some_functions ; some_procedures ; some_trigger ; some_events ;

298

some_databases:

299

create_database ; create_database ; create_database ; create_database ;

300

some_base_tables:

301

create_base_table ; create_base_table ; create_base_table ; create_base_table ;

302

some_temp_tables:

303

create_temp_table ; create_temp_table ; create_temp_table ; create_temp_table ;

304

some_merge_tables:

305

create_merge_table ; create_merge_table ; create_merge_table ; create_merge_table ;

306

some_part_tables:

307

create_part_table ; create_part_table ; create_part_table ; create_part_table ;

308

some_view_tables:

309

create_view ; create_view ; create_view ; create_view ;

310

some_functions:

311

create_function ; create_function ; create_function ; create_function ;

312

some_procedures:

313

create_procedure ; create_procedure ; create_procedure ; create_procedure ;

314

some_trigger:

315

create_trigger ; create_trigger ; create_trigger ; create_trigger ;

316

some_events:

317

create_event ; create_event ; create_event ; create_event ;

318

319

nothing_disabled:

320

{ $sequence_begin = "/* Sequence start */" ; $sequence_end = "/* Sequence end */" ; return undef } ;

321

322

system_table_stuff:

323

# This is used in "grant_revoke".

324

CREATE USER otto@localhost ;

325

326

327

# Useful grammar items ====================================================================================#

328

329

rand_val:

330

{ $rand_val = $prng->int(0,100) / 100 } ;

331

332

333

# Namespaces of objects ==========================================================================#

334

# An explanation of the namespace concept is on top of this file.

335

#

336

# 1. The database namespace ##########################################################################

337

database_name_s:

338

{ $database_name_s = $database_prefix . $sequence_piece ; $database_name = $database_name_s } ;

339

database_name_n:

340

{ $database_name_n = $database_prefix . $normal_piece ; $database_name = $database_name_n } ;

341

database_name:

342

# Get a random name from the "database" namespace.

343

# $database_name gets automatically filled when database_name_s or database_name_n is executed.

344

database_name_s | database_name_n ;

345

346

347

# 2. The base table namespace ########################################################################

348

base_table_name_s:

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 } ;

351

base_table_name_n:

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

base_table_name:

355

# Get a random name from the "base table" namespace.

356

base_table_name_s | base_table_name_n ;

357

358

# Sometimes useful stuff:

359

base_table_item_s:

360

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 } ;

361

base_table_item_n:

362

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 } ;

363

base_table_item:

364

base_table_item_s | base_table_item_n ;

365

base_table_item_list_s:

366

base_table_item_s | base_table_item_s , base_table_item_s ;

367

base_table_item_list_n:

368

base_table_item_n | base_table_item_n , base_table_item_n ;

369

base_table_item_list:

370

base_table_item | base_table_item , base_table_item ;

371

372

373

# 3. The temp table namespace ########################################################################

374

# Please note that TEMPORARY merge tables will be not generated.

375

temp_table_name_s:

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 } ;

378

temp_table_name_n:

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

temp_table_name:

382

# Get a random name from the "temp table" namespace.

383

temp_table_name_s | temp_table_name_n ;

384

385

# Sometimes useful stuff:

386

temp_table_item_s:

387

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 } ;

388

temp_table_item_n:

389

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 } ;

390

temp_table_item:

391

temp_table_item_s | temp_table_item_n ;

392

temp_table_item_list_s:

393

temp_table_item_s | temp_table_item_s , temp_table_item_s ;

394

temp_table_item_list_n:

395

temp_table_item_n | temp_table_item_n , temp_table_item_n ;

396

temp_table_item_list:

397

temp_table_item | temp_table_item , temp_table_item ;

398

399

400

# 4. The merge table namespace #######################################################################

401

# Please note that TEMPORARY merge tables will be not generated.

402

merge_table_name_s:

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 } ;

405

merge_table_name_n:

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 } ;

408

merge_table_name:

409

# Get a random name from the "merge table" namespace.

410

merge_table_name_s | merge_table_name_n ;

411

412

# Sometimes useful stuff:

413

merge_table_item_s:

414

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 } ;

415

merge_table_item_n:

416

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 } ;

417

merge_table_item:

418

merge_table_item_s | merge_table_item_n ;

419

merge_table_item_list_s:

420

merge_table_item_s | merge_table_item_s , merge_table_item_s ;

421

merge_table_item_list_n:

422

merge_table_item_n | merge_table_item_n , merge_table_item_n ;

423

merge_table_item_list:

424

merge_table_item | merge_table_item , merge_table_item ;

425

426

427

# 5. The view table namespace ########################################################################

428

view_table_name_s:

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 } ;

431

view_table_name_n:

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

view_table_name:

435

# Get a random name from the "view table" namespace.

436

view_table_name_s | view_table_name_n ;

437

438

# Sometimes useful stuff:

439

view_table_item_s:

440

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 };

441

view_table_item_n:

442

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 };

443

view_table_item:

444

view_table_item_s | view_table_item_n ;

445

view_table_item_list_s:

446

view_table_item_s | view_table_item_s , view_table_item_s ;

447

view_table_item_list_n:

448

view_table_item_n | view_table_item_n , view_table_item_n ;

449

view_table_item_list:

450

view_table_item | view_table_item , view_table_item ;

451

452

453

# 6. The partitioned table namespace #################################################################

454

part_table_name_s:

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 } ;

457

part_table_name_n:

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

part_table_name:

461

# Get a random name from the "part table" namespace.

462

part_table_name_s | part_table_name_n ;

463

464

# Sometimes useful stuff:

465

part_table_item_s:

466

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 };

467

part_table_item_n:

468

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 };

469

part_table_item:

470

part_table_item_s | part_table_item_n ;

471

part_table_item_list_s:

472

part_table_item_s | part_table_item_s , part_table_item_s ;

473

part_table_item_list_n:

474

part_table_item_n | part_table_item_n , part_table_item_n ;

475

part_table_item_list:

476

part_table_item | part_table_item , part_table_item ;

477

478

479

# 7. Mixed namespaces of tables ################################################################

480

481

# 7.1 All tables ( base/temp/merge tables + views + ... #########################################

482

table_item_s:

483

base_table_item_s | temp_table_item_s | merge_table_item_s | view_table_item_s | part_table_item_s ;

484

table_item_n:

485

base_table_item_n | temp_table_item_n | merge_table_item_n | view_table_item_n | part_table_item_n ;

486

table_item:

487

table_item_s | table_item_n ;

488

489

table_list:

490

# Less likelihood for lists, because they

491

# - are most probably less often used

492

# - cause a higher likelihood of "table does not exist" errors.

493

494

table_item , table_item ;

495

496

497

# 7.2 All tables but no views #######################################################################

498

table_no_view_item_s:

499

base_table_item_s | temp_table_item_s | merge_table_item_s | part_table_item_s ;

500

table_no_view_item_n:

501

base_table_item_n | temp_table_item_n | merge_table_item_n | part_table_item_n ;

502

table_no_view_item:

503

table_no_view_item_s | table_no_view_item_n ;

504

505

506

# 7.3 All base and temp tables + views ##############################################################

507

# These grammar elements is used to avoid some partioning related bugs.

508

base_temp_view_table_item_s:

509

base_table_item_s | temp_table_item_s | view_table_item_s | part_table_item_s ;

510

base_temp_view_table_item_n:

511

base_table_item_n | temp_table_item_n | view_table_item_n | part_table_item_n ;

512

base_temp_view_table_item:

513

base_temp_view_table_item_s | base_temp_view_table_item ;

514

515

516

# 8. Other namespaces ##############################################################a

517

template_table_item:

518

{ $template_table_item = "test.table0_int_autoinc" } |

519

{ $template_table_item = "test.table1_int_autoinc" } |

520

{ $template_table_item = "test.table10_int_autoinc" } ;

521

522

523

procedure_name_s:

524

# Get a random name from the "procedure long life" namespace.

525

{ $procedure_name_s = $procedure_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $procedure_name = $procedure_name_s } ;

526

procedure_name_n:

527

# Get a random name from the "procedure short life" namespace.

528

{ $procedure_name_n = $procedure_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $procedure_name = $procedure_name_n } ;

529

procedure_name:

530

# Get a random name from the "procedure" namespace.

531

procedure_name_s | procedure_name_n ;

532

533

# Sometimes useful stuff:

534

procedure_item_s:

535

database_name_s . procedure_name_s { $procedure_item_s = $database_name_s . " . " . $procedure_name_s ; $procedure_item = $procedure_item_s ; return undef } ;

536

procedure_item_n:

537

database_name . procedure_name_n { $procedure_item_n = $database_name . " . " . $procedure_name_n ; $procedure_item = $procedure_item_n ; return undef } ;

538

procedure_item:

539

procedure_item_s | procedure_item_n ;

540

541

function_name_s:

542

# Get a random name from the "function long life" namespace.

543

{ $function_name_s = $function_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $function_name = $function_name_s } ;

544

function_name_n:

545

# Get a random name from the "function short life" namespace.

546

{ $function_name_n = $function_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $function_name = $function_name_n } ;

547

function_name:

548

# Get a random name from the "function" namespace.

549

function_name_s | function_name_n ;

550

551

function_item_s:

552

database_name_s . function_name_s { $function_item_s = $database_name_s . " . " . $function_name_s ; $function_item = $function_item_s ; return undef } ;

553

function_item_n:

554

database_name . function_name_n { $function_item_n = $database_name . " . " . $function_name_n ; $function_item = $function_item_n ; return undef } ;

555

function_item:

556

function_item_s | function_item_n ;

557

558

trigger_name_s:

559

# Get a random name from the "trigger long life" namespace.

560

{ $trigger_name_s = $trigger_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $trigger_name = $trigger_name_s } ;

561

trigger_name_n:

562

# Get a random name from the "trigger short life" namespace.

563

{ $trigger_name_n = $trigger_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $trigger_name = $trigger_name_n } ;

564

trigger_name:

565

# Get a random name from the "trigger" namespace.

566

trigger_name_s | trigger_name_n ;

567

568

trigger_item_s:

569

database_name_s . trigger_name_s { $trigger_item_s = $database_name_s . " . " . $trigger_name_s ; $trigger_item = $trigger_item_s ; return undef } ;

570

trigger_item_n:

571

database_name . trigger_name_n { $trigger_item_n = $database_name . " . " . $trigger_name_n ; $trigger_item = $trigger_item_n ; return undef } ;

572

trigger_item:

573

trigger_item_s | trigger_item_n ;

574

575

event_name_s:

576

# Get a random name from the "event long life" namespace.

577

{ $event_name_s = $event_prefix . $prng->int(1,$namespace_width) . $sequence_piece ; $event_name = $event_name_s } ;

578

event_name_n:

579

# Get a random name from the "event short life" namespace.

580

{ $event_name_n = $event_prefix . $prng->int(1,$namespace_width) . $normal_piece ; $event_name = $event_name_n } ;

581

event_name:

582

# Get a random name from the "event" namespace.

583

event_name_s | event_name_n ;

584

585

event_item_s:

586

database_name_s . event_name_s { $event_item_s = $database_name_s . " . " . $event_name_s ; $event_item = $event_item_s ; return undef } ;

587

event_item_n:

588

database_name . event_name_n { $event_item_n = $database_name . " . " . $event_name_n ; $event_item = $event_item_n ; return undef } ;

589

event_item:

590

event_item_s | event_item_n ;

591

592

# Here starts the core of the test grammar ========================================================#

593

594

query:

595

# Use if

596

# Bug#46224 HANDLER statements within a transaction might lead to deadlocks

597

# is fixed (various -> handler removed)

598

# Handler lets currently also runs with one worker session hang.

599

# dml | dml | dml | dml | ddl | transaction | lock_unlock | flush | handler ;

600

dml | dml | dml | dml | ddl | transaction | lock_unlock | flush ;

601

602

########## TRANSACTIONS ####################

603

604

transaction:

605

start_transaction | commit | rollback |

606

start_transaction | commit | rollback |

607

start_transaction | commit | rollback |

608

SAVEPOINT savepoint_id | RELEASE SAVEPOINT savepoint_id | ROLLBACK work_or_empty TO savepoint_or_empty savepoint_id |

609

BEGIN work_or_empty | set_autocommit | kill_query_or_session ;

610

# No impact on mdl.cc , lock.cc ..... set_isolation_level ;

611

612

set_isolation_level:

613

SET SESSION TX_ISOLATION = TRIM(' isolation_level ');

614

615

isolation_level:

616

REPEATABLE-READ | READ-COMMITTED | SERIALIZABLE ;

617

618

619

start_transaction:

620

START TRANSACTION with_consistent_snapshot ;

621

with_consistent_snapshot:

622

623

624

commit:

625

COMMIT work_or_empty chain release ;

626

rollback:

627

ROLLBACK work_or_empty chain release ;

628

chain:

629

| | | | AND no_or_empty CHAIN ;

630

release:

631

632

633

set_autocommit:

634

SET AUTOCOMMIT = zero_or_one ;

635

636

kill_query_or_session:

637

lower_id ; KILL query_or_session @kill_id |

638

own_id ; KILL query_or_session @kill_id |

639

higher_id ; KILL query_or_session @kill_id ;

640

# Note(mleich):

641

# 1. The scenario of a KILL failing because the session does not exist is covered by:

642

# a) Current session id is minimum or maximum of all existing id's and lower_id or higher_id

643

# does not exist -> NULL as value for @kill_id. "KILL NULL" gets valuated as "KILL 0".

644

# b) A parallel session kills the session with the just computed id before we run the KILL.

645

# 2. It is intentional that I do not use "KILL _digit".

646

# 2.1 In case the RQG test crashes in a scenario with thread=1, it is very likely that during analysis

647

# the test gets converted to a script for mysqltest. Most probably the mysqltest simplifier gets

648

# applied and than it is very likely that the session id's change. The use of a computation

649

# based on CONNECTION_ID(), MIN and MAX leads to more stable results.

650

# 2.2 There is the risk that we compute the id iand KILL an auxiliary RQG session (reporter,validator).

651

# 2.2.1 The impact of such an operation on RQG (automatic judgement about test result, deadlock detection

652

# etc. is currently unknown.

653

# 2.2.2 Effects caused by killing of such an auxiliary RQG session are out of testing scope.

654

# Therefore we avoid this by the grammar item "pick_executors_only"

655

# etc. is currently unknown.

656

# 2.3 We must avoid to kill a test executor session within an early phase when it probably pulls

657

# meta data (table names, column names, data types, ...). This could end up with RQG exit status 255.

658

# Philip mentioned "Can't use an undefined value as an ARRAY reference at lib/GenTest/Generator/FromGrammar.pm line 269."

659

lower_id:

660

SELECT MAX(id) INTO @kill_id FROM information_schema.processlist WHERE id < CONNECTION_ID() pick_executors_only ensure_all_up ;

661

own_id:

662

SET @kill_id = CONNECTION_ID() ensure_all_up ;

663

higher_id:

664

SELECT MIN(id) INTO @kill_id FROM information_schema.processlist WHERE id > CONNECTION_ID() pick_executors_only ensure_all_up ;

665

query_or_session:

666

QUERY | QUERY | ;

667

pick_executors_only:

668

AND (INFO LIKE CONCAT('%',TRIM(' database_name_s '),'%') OR INFO LIKE CONCAT('%',TRIM(' database_name_n '),'%')) ;

669

ensure_all_up:

670

# In case of MAX(id) > _thread_count it is very likely that the majority of executor sessions are started.

671

# In case of 'Uptime' it is likely that

672

AND _thread_count + 3 < (SELECT MAX(id) FROM information_schema.processlist)

673

AND 10 > (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Uptime') ;

674

675

676

savepoint_id:

677

A | B ;

678

679

ddl:

680

database_ddl |

681

base_table_ddl | base_table_ddl | base_table_ddl |

682

temp_table_ddl | temp_table_ddl | temp_table_ddl |

683

# Use if

684

# Bug#47633 assert in ha_myisammrg::info during OPTIMIZE

685

# is fixed (merge tables disabled)

686

# merge_table_ddl | merge_table_ddl | merge_table_ddl |

687

part_table_ddl | part_table_ddl | part_table_ddl |

688

view_ddl | view_ddl | view_ddl |

689

procedure_ddl | procedure_ddl | procedure_ddl |

690

function_ddl | function_ddl | function_ddl |

691

trigger_ddl | trigger_ddl | trigger_ddl |

692

event_ddl |

693

truncate_table |

694

drop_table_list |

695

rename_table |

696

table_maintenance_ddl |

697

dump_load_data_sequence |

698

grant_revoke |

699

rename_column |

700

sql_mode ;

701

# "dump_load_data_sequence" with SELECT ... INTO OUTFILE ...; LOAD DATA ... INFILE

702

# consists more of DML statements, but we place this here under "ddl" because the

703

# statements in "dml" are often executed as prepared statements. And the text after

704

# PREPARE st1 FOR must not contain multiple statements.

705

706

707

########## HANDLER ####################

708

handler:

709

handler_open | handler_read | handler_close ;

710

711

handler_open:

712

HANDLER table_no_view_item OPEN with_alias ;

713

714

with_alias:

715

| as A ;

716

717

handler_read:

718

# The use of indexes is omitted

719

HANDLER table_no_view_item READ FIRST handler_read_part ;

720

handler_read_part:

721

| where ;

722

723

handler_close:

724

HANDLER table_no_view_item CLOSE ;

725

726

727

########## SHOW ####################

728

# We run here only object related SHOW commands except SHOW STATUS which checks counters

729

# of OPEN tables etc.

730

show:

731

database_show |

732

table_show |

733

routine_show |

734

SHOW STATUS ;

735

736

database_show:

737

show_databases | show_create_database ;

738

739

show_databases:

740

SHOW databases_schemas ;

741

databases_schemas:

742

DATABASES | SCHEMAS ;

743

744

show_create_database:

745

SHOW CREATE database_schema database_name ;

746

747

#----------------------------------

748

749

table_show:

750

show_tables | show_tables |

751

show_table_status | show_table_status |

752

show_create_table | show_create_view |

753

show_open_tables | show_columns ;

754

755

show_tables:

756

SHOW TABLES;

757

758

show_create_table:

759

# Works also for views

760

SHOW CREATE TABLE table_item ;

761

762

show_open_tables:

763

SHOW OPEN TABLES IN database_name ;

764

765

show_table_status:

766

# Works also for views

767

SHOW TABLE STATUS ;

768

769

show_columns:

770

SHOW full COLUMNS from_in table_item show_columns_part ;

771

full:

772

# Only 20 %

773

| | | | FULL ;

774

from_in:

775

FROM | IN ;

776

show_columns_part:

777

# Attention: LIKE '_field' does not work, because RQG does not expand _field.

778

# LIKE '%int%' does not work, because RQG expands it to something like LIKE '%822214656%'.

779

# FIXME: Add "WHERE"

780

| LIKE '%INT%' ;

781

782

show_create_view:

783

SHOW CREATE VIEW view_table_item ;

784

785

#----------------------------------

786

routine_show:

787

show_create_function | show_function_code | show_function_status |

788

show_create_procedure | show_procedure_code | show_procedure_status |

789

show_triggers | show_create_trigger |

790

show_events | show_create_event ;

791

792

show_create_function:

793

SHOW CREATE FUNCTION function_item ;

794

795

show_function_code:

796

SHOW FUNCTION CODE function_item ;

797

798

show_function_status:

799

SHOW FUNCTION STATUS;

800

801

show_create_procedure:

802

SHOW CREATE PROCEDURE procedure_item ;

803

804

show_procedure_code:

805

SHOW PROCEDURE CODE procedure_item ;

806

807

show_procedure_status:

808

SHOW PROCEDURE STATUS;

809

810

show_triggers:

811

SHOW TRIGGERS;

812

813

show_create_trigger:

814

SHOW CREATE TRIGGER trigger_item ;

815

816

show_events:

817

SHOW EVENTS from_in database_name ;

818

819

show_create_event:

820

SHOW CREATE EVENT event_item_s ;

821

822

########## SELECTS ON THE INFORMATION_SCHEMA ####################

823

# We run here only object related SELECTs.

824

is_selects:

825

is_schemata | is_tables | is_columns ;

826

is_schemata:

827

/* database_name */ SELECT * FROM information_schema . schemata WHERE schema_name = TRIM(' $database_name ') ;

828

is_tables:

829

/* table_item */ SELECT * FROM information_schema . tables WHERE table_schema = TRIM(' $database_name ') AND table_name = TRIM(' $table_name ') ;

830

is_columns:

831

/* 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 ;

832

# 19.1. The INFORMATION_SCHEMA SCHEMATA Table

833

# 19.2. The INFORMATION_SCHEMA TABLES Table

834

# 19.3. The INFORMATION_SCHEMA COLUMNS Table

835

# 19.4. The INFORMATION_SCHEMA STATISTICS Table

836

# 19.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table

837

# 19.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table

838

# 19.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table

839

# 19.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table

840

# 19.9. The INFORMATION_SCHEMA CHARACTER_SETS Table

841

# 19.10. The INFORMATION_SCHEMA COLLATIONS Table

842

# 19.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table

843

# 19.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table

844

# 19.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

845

# 19.14. The INFORMATION_SCHEMA ROUTINES Table

846

# 19.15. The INFORMATION_SCHEMA VIEWS Table

847

# 19.16. The INFORMATION_SCHEMA TRIGGERS Table

848

# 19.17. The INFORMATION_SCHEMA PLUGINS Table

849

# 19.18. The INFORMATION_SCHEMA ENGINES Table

850

# 19.19. The INFORMATION_SCHEMA PARTITIONS Table

851

# 19.20. The INFORMATION_SCHEMA EVENTS Table

852

# 19.21. The INFORMATION_SCHEMA FILES Table

853

# 19.22. The INFORMATION_SCHEMA TABLESPACES Table

854

# 19.23. The INFORMATION_SCHEMA PROCESSLIST Table

855

# 19.24. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table

856

# 19.25. The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables

857

# 19.26. The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables

858

# 19.27. The INFORMATION_SCHEMA PARAMETERS Table

859

# 19.28. The INFORMATION_SCHEMA PROFILING Table

860

# 19.29. Other INFORMATION_SCHEMA Tables

861

#

862

863

864

########## DATABASE ####################

865

database_ddl:

866

create_database | create_database | create_database |

867

drop_database | alter_database |

868

database_sequence ;

869

870

create_database:

871

CREATE database_schema if_not_exists database_name_n database_spec ;

872

873

database_schema:

874

DATABASE | SCHEMA ;

875

876

database_spec:

877

# We do not want to test CHARACTER SETs and COLLATIONs, but we need something for ALTER DATABASE.

878

default_word CHARACTER SET equal utf8 | default_word COLLATE equal utf8_bin ;

879

880

drop_database:

881

DROP database_schema if_exists database_name_n ;

882

883

alter_database:

884

ALTER database_schema database_name_n database_spec ;

885

886

database_sequence:

887

# Have a bigger lifetime for databases because the objects with extended lifetime are stored there.

888

$sequence_begin CREATE database_schema database_name_s ; wait_till_drop_database ; DROP database_schema $database_name_s $sequence_end ;

889

wait_till_drop_database:

890

SELECT SLEEP( 2 * rand_val * $life_time_unit ) ;

891

892

893

########## BASE AND TEMPORARY TABLES ####################

894

base_table_ddl:

895

896

drop_base_table | alter_base_table |

897

base_table_sequence ;

898

899

create_base_table:

900

CREATE TABLE if_not_exists base_table_item_n create_table_part ;

901

create_table_part:

902

LIKE template_table_item ; ALTER TABLE $base_table_item_n ENGINE = engine ; INSERT INTO $base_table_item_n SELECT * FROM $template_table_item |

903

LIKE template_table_item ; ALTER TABLE $base_table_item_n ENGINE = engine ; INSERT INTO $base_table_item_n SELECT * FROM $template_table_item |

904

AS used_select ;

905

906

drop_base_table:

907

# DROP two tables is in "drop_table_list"

908

DROP TABLE if_exists base_table_item_n restrict_cascade ;

909

910

alter_base_table:

911

ALTER ignore TABLE base_table_item_n alter_base_temp_table_part ;

912

913

alter_base_temp_table_part:

914

# Reasons why "ENGINE = engine" should be rather rare:

915

# 1. ALTER ... ENGINE = <engine> is rather rare within a production system running under DML load

916

# 2. ALTER ... ENGINE = <engine != MyISAM> "damages" any MERGE table using the affected table as base table.

917

# As a consequence nerly all statements on the MERGE table will fail.

918

919

COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' | COMMENT = 'UPDATED NOW()' |

920

ENGINE = engine ;

921

922

base_table_sequence:

923

$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 ;

924

925

wait_till_drop_table:

926

SELECT SLEEP( rand_val * $life_time_unit ) ;

927

928

temp_table_ddl:

929

# Attention: temp_table_sequence is intentionally omitted, because no other session will be

930

# able to use this table.

931

932

drop_temp_table | alter_temp_table ;

933

934

create_temp_table:

935

CREATE TEMPORARY TABLE if_not_exists temp_table_item_n create_table_part ;

936

937

drop_temp_table:

938

# DROP two tables is in "drop_table_list"

939

# A pure DROP TABLE is allowed, but we get an implicit COMMITs for that.

940

DROP TEMPORARY TABLE if_exists temp_table_item_n |

941

DROP TABLE if_exists temp_table_item_n ;

942

943

alter_temp_table:

944

ALTER ignore TABLE temp_table_item_n alter_base_temp_table_part ;

945

946

########## MAINTENANCE FOR ANY TABLE ####################

947

# The server accepts these statements for all table types (VIEWs, base tables, ...) though they

948

# should have partially no effect. We run them on all table types because everything which gets

949

# accepted has to be checked even if the command should do nothing.

950

# Example:

951

# OPTIMIZE ... TABLE <view> ...

952

# Table Op Msg_type Msg_text

953

# test.v1 optimize Error Table 'test.v1' doesn't exist

954

# test.v1 optimize status Operation failed

955

# OPTIMIZE ... TABLE <merge table> ...

956

# Table Op Msg_type Msg_text

957

# test.t1m optimize note The storage engine for the table doesn't support optimize

958

#

959

table_maintenance_ddl:

960

analyze_table | optimize_table | checksum_table | check_table | repair_table ;

961

962

analyze_table:

963

ANALYZE not_to_binlog_local TABLE table_list ;

964

not_to_binlog_local:

965

| NO_WRITE_TO_BINLOG | LOCAL ;

966

967

optimize_table:

968

OPTIMIZE not_to_binlog_local TABLE table_list ;

969

970

checksum_table:

971

CHECKSUM TABLE table_list quick_extended ;

972

quick_extended:

973

| quick | extended ;

974

extended:

975

# Only 10 %

976

977

978

check_table:

979

CHECK TABLE table_list check_table_options ;

980

check_table_options:

981

982

983

repair_table:

984

# Use if

985

# Bug#46339 crash on REPAIR TABLE merge table USE_FRM

986

# is fixed (base_temp_table_view_list instead of table_list because of Bug#46339)

987

# REPAIR not_to_binlog_local TABLE table_list quick extended use_frm ;

988

REPAIR not_to_binlog_local TABLE table_list quick extended ;

989

990

use_frm:

991

# Only 10 %

992

993

994

995

########## MIXED TABLE RELATED DDL #################################

996

truncate_table:

997

TRUNCATE table_word table_no_view_item_n ;

998

table_word:

999

| TABLE ;

1000

1001

drop_table_list:

1002

# DROP one table is in "drop_*table"

1003

# 1. We mix here all tables except VIEWs up.

1004

# 2. We have an increased likelihood that the statement fails because of use of

1005

# - "temporary" (only correct in case of a temporary table)

1006

# - two tables (some might not exist)

1007

DROP temporary TABLE if_exists table_no_view_item_n , table_no_view_item_n restrict_cascade ;

1008

1009

rename_table:

1010

# RENAME TABLE works also on all types of tables (includes VIEWs)

1011

RENAME TABLE rename_item_list ;

1012

rename_item_list:

1013

rename_item | rename_item , rename_item ;

1014

rename_item:

1015

# Preserve the object type (base,temp,....) and type (Normal) otherwise debugging becomes difficult and

1016

# the concept with different lifetimes gets broken.

1017

base_table_item_n TO base_table_item_n |

1018

temp_table_item_n TO temp_table_item_n |

1019

merge_table_item_n TO merge_table_item_n |

1020

part_table_item_n TO part_table_item_n ;

1021

1022

rename_column:

1023

ALTER TABLE table_no_view_item_s CHANGE COLUMN column_to_change my_column INT |

1024

ALTER TABLE table_no_view_item_s CHANGE COLUMN my_column column_to_change INT ;

1025

1026

column_to_change:

1027

`col_int` | `col_int_key` | `pk` ;

1028

1029

1030

########## MERGE TABLE DDL ####################

1031

merge_table_ddl:

1032

1033

drop_merge_table | alter_merge_table |

1034

merge_table_sequence ;

1035

1036

create_merge_table:

1037

# There is a high risk that the tables which we pick for merging do not fit together because they

1038

# have different structures. We try to reduce this risk to end up with no merge table at all

1039

# by the following:

1040

# 1. Let the merge table have the structure of the first base table.

1041

# CREATE TABLE <merge table> LIKE <first base table>

1042

# 2. Let the merge table be based on the first base table.

1043

# ALTER TABLE <merge table> ENGINE = MERGE UNION(<first base table>)

1044

# 3. Add the second base table to the merge table.

1045

# ALTER TABLE <merge table> UNION(<first base table>, <second merge table>)

1046

merge_init_n build_partner1 ; build_partner2 ; create_merge ;

1047

1048

insert_method:

1049

| INSERT_METHOD = insert_method_value | INSERT_METHOD = insert_method_value | INSERT_METHOD = insert_method_value ;

1050

insert_method_value:

1051

NO | FIRST | LAST ;

1052

1053

drop_merge_table:

1054

# DROP two tables is in "drop_table_list"

1055

DROP TABLE if_exists merge_table_item_n ;

1056

1057

merge_table_sequence:

1058

# Notes:

1059

# There is a significant likelihood that a random picked table names as base for the merge table cannot

1060

# be used for the creation of a merge table because the corresponding tables

1061

# - must exist

1062

# - use the storage engine MyISAM

1063

# - have the same layout

1064

# Therefore we create here all we need.

1065

# The use of "base_table_name_n" for the tables to be merged guarantees that these tables

1066

# are under full DDL/DML load.

1067

# 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.

1068

$sequence_begin merge_init_s build_partner1 ; build_partner2 ; create_merge ; wait_till_drop_table ; DROP TABLE $mt $sequence_end ;

1069

1070

alter_merge_table:

1071

# We do not change here the UNION because of the high risk that this fails.

1072

# A simple change of the insert_method_value is also not doable because we

1073

# would need to mention also the UNION.

1074

# It is intentional that we use merge_table_name and not merge_table_name_n.

1075

ALTER ignore TABLE merge_table_item_n COMMENT = 'UPDATED NOW()' ;

1076

1077

merge_init_s:

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 */ ;

1079

merge_init_n:

1080

/* 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 */ ;

1081

build_partner1:

1082

# This also initializes $database_name and $base_table_name which gets used by the other commands within the sequence.

1083

CREATE TABLE if_not_exists $mp1 LIKE $template_table_item ; ALTER TABLE $mp1 ENGINE = MyISAM ; INSERT INTO $mp1 SELECT * FROM $template_table_item ;

1084

build_partner2:

1085

# This also initializes $database_name and $base_table_name which gets used by the other commands within the sequence.

1086

CREATE TABLE if_not_exists $mp2 LIKE $template_table_item ; ALTER TABLE $mp2 ENGINE = MyISAM ; INSERT INTO $mp2 SELECT * FROM $template_table_item ;

1087

create_merge:

1088

CREATE TABLE if_not_exists $mt LIKE $template_table_item ; ALTER TABLE $mt ENGINE = MERGE UNION ( $mp1 , $mp2 ); COMMIT ;

1089

1090

1091

########## PARTITIONED TABLE DDL ####################

1092

part_table_ddl:

1093

1094

drop_part_table |

1095

alter_part_table |

1096

part_table_sequence ;

1097

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 ;

1102

1103

partition_algorithm:

1104

# We do not need sophisticated partitioning here.

1105

PARTITION BY KEY (pk) PARTITIONS 2 |

1106

PARTITION BY LINEAR HASH(pk) PARTITIONS 3 ;

1107

1108

drop_part_table:

1109

# DROP two tables is in "drop_table_list"

1110

DROP TABLE if_exists part_table_item_n ;

1111

1112

alter_part_table:

1113

ALTER ignore TABLE part_table_item_n alter_part_table_part ;

1114

1115

alter_part_table_part:

1116

partition_algorithm |

1117

COMMENT = 'UPDATED NOW()' ;

1118

1119

part_table_sequence:

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 ;

1121

1122

1123

########## VIEW DDL ####################

1124

view_ddl:

1125

1126

drop_view | alter_view |

1127

view_sequence ;

1128

1129

create_view:

1130

CREATE view_replace ALGORITHM = view_algoritm VIEW view_table_item_n AS used_select ;

1131

view_replace:

1132

# Only 20 %

1133

| | | | OR REPLACE ;

1134

view_algoritm:

1135

UNDEFINED | MERGE | TEMPTABLE ;

1136

1137

drop_view:

1138

DROP VIEW if_exists view_table_item_n restrict_cascade ;

1139

1140

restrict_cascade:

1141

# RESTRICT and CASCADE, if given, are parsed and ignored.

1142

| RESTRICT | CASCADE ;

1143

1144

alter_view:

1145

# Attention: Only changing the algorithm is not allowed.

1146

ALTER ALGORITHM = view_algoritm VIEW view_table_item_n AS used_select ;

1147

1148

view_sequence:

1149

$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 ;

1150

1151

1152

########## STORED PROCEDURE DDL ####################

1153

procedure_ddl:

1154

create_procedure | create_procedure |

1155

drop_procedure | alter_procedure |

1156

procedure_sequence ;

1157

1158

create_procedure:

1159

CREATE PROCEDURE procedure_item_n () BEGIN proc_stmt ; proc_stmt ; END ;

1160

proc_stmt:

1161

select | update ;

1162

1163

drop_procedure:

1164

DROP PROCEDURE if_exists procedure_item_n ;

1165

1166

alter_procedure:

1167

ALTER PROCEDURE procedure_item_n COMMENT 'UPDATED NOW()' ;

1168

1169

procedure_sequence:

1170

# FIXME: The PROCEDURE should touch base_table_name_s only .

1171

$sequence_begin CREATE PROCEDURE procedure_item_s () BEGIN proc_stmt ; proc_stmt ; END ; COMMIT ; SELECT wait_short ; DROP PROCEDURE $procedure_item_s $sequence_end ;

1172

1173

1174

########## STORED FUNCTION DDL ####################

1175

function_ddl:

1176

create_function | create_function |

1177

drop_function | alter_function |

1178

function_sequence ;

1179

1180

create_function:

1181

CREATE FUNCTION function_item_n () RETURNS INTEGER BEGIN func_statement ; func_statement ; RETURN 1 ; END ;

1182

func_statement:

1183

# All result sets of queries within a function must be processed within the function.

1184

# -> Use a CURSOR or SELECT ... INTO ....

1185

SET @my_var = 1 | SELECT MAX( random_field_quoted1 ) FROM table_item INTO @my_var | insert | delete ;

1186

1187

drop_function:

1188

DROP FUNCTION if_exists function_item_n ;

1189

1190

alter_function:

1191

ALTER FUNCTION function_item_n COMMENT 'UPDATED NOW()' ;

1192

1193

function_sequence:

1194

$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 ;

1195

1196

1197

########## TRIGGER DDL ####################

1198

trigger_ddl:

1199

create_trigger | create_trigger |

1200

drop_trigger |

1201

trigger_sequence ;

1202

1203

create_trigger:

1204

CREATE TRIGGER trigger_item_n trigger_time trigger_event ON base_table_name_n FOR EACH ROW BEGIN trigger_action ; END ;

1205

trigger_time:

1206

BEFORE | AFTER ;

1207

trigger_event:

1208

INSERT | DELETE ;

1209

trigger_action:

1210

insert | replace | delete | update | CALL procedure_item ;

1211

1212

drop_trigger:

1213

DROP TRIGGER if_exists trigger_item_n ;

1214

1215

trigger_sequence:

1216

# FIXME: The action within the trigger should touch base_table_name_s only.

1217

$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 ;

1218

1219

1220

########## EVENT DDL ####################

1221

event_ddl:

1222

1223

1224

event_scheduler_on | event_scheduler_off ;

1225

create_event:

1226

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;

1227

completion_handling:

1228

ON COMPLETION not_or_empty PRESERVE ;

1229

drop_event:

1230

DROP EVENT if_exists event_item_s ;

1231

alter_event:

1232

ALTER EVENT event_item_s COMMENT 'UPDATED NOW()';

1233

1234

########## DML ####################

1235

1236

dml:

1237

# Have only 10 % prepared statements.

1238

# SQL Statements to be handled via PREPARE, EXECUTE and DEALLOCATE cause a bigger amount of

1239

# failing statements than SQL statements which are executed in non prepared mode.

1240

# The reason is that we run the EXECUTE and DEALLOCATE independent of the outcome of the

1241

# PREPARE. So if the PREPARE fails because some table is missing, we loose the old

1242

# prepared statement handle, if there was any, and get no new one. Therefore the succeeding

1243

# EXECUTE and DEALLOCATE will also failcw because of missing statement handle.

1244

dml2 | dml2 | dml2 | dml2 | dml2 | dml2 | dml2 | dml2 | dml2 |

1245

PREPARE st1 FROM " dml2 " ; EXECUTE st1 ; DEALLOCATE PREPARE st1 ;

1246

1247

dml2:

1248

select | select | select |

1249

1250

1251

########## DO ####################

1252

do:

1253

DO 1 |

1254

# A lot options like HIGH_PRIORITY (after SELECT ) etc. are not allowed in connection with DO.

1255

# The SELECT must give one column.

1256

DO ( SELECT COUNT(*) FROM table_item WHERE `pk` BETWEEN _digit[invariant] AND _digit[invariant] + 20 ) |

1257

DO user_lock_action ;

1258

1259

user_lock_action:

1260

IS_FREE_LOCK(TRIM(' _digit ')) |

1261

IS_USED_LOCK(TRIM(' _digit ')) |

1262

RELEASE_LOCK(TRIM(' _digit ')) |

1263

GET_LOCK(TRIM(' _digit '), 0.5 * rand_val * $life_time_unit ) ;

1264

1265

########## SELECT ####################

1266

select:

1267

select_normal | select_normal | select_normal | select_normal | select_with_sleep ;

1268

1269

select_normal:

1270

# select = Just a query = A statement starting with "SELECT".

1271

select_part1 addition into for_update_lock_in_share_mode ;

1272

1273

select_with_sleep:

1274

# Run a SELECT which holds locks (if there are any) longer.

1275

SELECT 1 FROM table_item WHERE wait_short = 0 LIMIT 1;

1276

1277

used_select:

1278

# used_select = The SELECT used in CREATE VIEW/TABLE ... AS SELECT, INSERT INTO ... SELECT

1279

# "PROCEDURE ANALYSE" and "INTO DUMPFILE/OUTFILE/@var" are not generated because they

1280

# are partially disallowed or cause garbage (PROCEDURE).

1281

select_part1 addition_no_procedure ;

1282

1283

select_part1:

1284

SELECT high_priority cache_results table_field_list_or_star FROM table_in_select as A ;

1285

1286

cache_results:

1287

| sql_no_cache | sql_cache ;

1288

sql_no_cache:

1289

# Only 10 %

1290

| | | | | | | | |

1291

SQL_NO_CACHE ;

1292

sql_cache:

1293

# Only 10 %

1294

| | | | | | | | |

1295

SQL_CACHE ;

1296

1297

table_in_select:

1298

# Attention: In case of CREATE VIEW a subquery in the FROM clause (derived table) is disallowed.

1299

# Therefore they should be rare.

1300

1301

( SELECT table_field_list_or_star FROM table_item ) ;

1302

1303

addition:

1304

# Involve one (simple where condition) or two tables (subquery | join | union)

1305

where procedure_analyze | subquery procedure_analyze | join where procedure_analyze | procedure_analyze union where ;

1306

1307

addition_no_procedure:

1308

# Involve one (simple where condition) or two tables (subquery | join | union)

1309

# Don't add procedure_analyze.

1310

1311

subquery | join where | union where ;

1312

1313

where:

1314

# The very selective condition is intentional.

1315

# It should ensure that

1316

# - result sets (just SELECT) do not become too big because this affects the performance in general and

1317

# the memery consumption of RQG (I had a ~ 3.5 GB virt memory RQG perl process during some simplifier run!)

1318

# - tables (INSERT ... SELECT, REPLACE) do not become too big

1319

# - tables (DELETE) do not become permanent empty

1320

# Please note that there are some cases where LIMIT cannot be used.

1321

# mleich: Temporary omit functions

1322

# WHERE `pk` BETWEEN _digit[invariant] AND _digit[invariant] + 1 | WHERE function_item () = _digit AND `pk` = _digit ;

1323

WHERE `pk` BETWEEN _digit[invariant] AND _digit[invariant] + 1 ;

1324

1325

union:

1326

UNION SELECT * FROM table_in_select as B ;

1327

1328

join:

1329

# Do not place a where condition here.

1330

NATURAL JOIN table_item B ;

1331

1332

subquery:

1333

correlated | non_correlated ;

1334

subquery_part1:

1335

WHERE A.`pk` IN ( SELECT `pk` FROM table_item AS B WHERE B.`pk` = ;

1336

correlated:

1337

subquery_part1 A.`pk` ) ;

1338

non_correlated:

1339

subquery_part1 _digit ) ;

1340

1341

procedure_analyze:

1342

# Correct place of PROCEDURE ANALYSE( 10 , 2000 )

1343

# 0. Attention: The result set of the SELECT gets replaced by PROCEDURE ANALYSE output.

1344

# 1. WHERE ... PROCEDURE (no UNION of JOIN)

1345

# 2. SELECT ... PROCEDURE UNION SELECT ... (never after UNION)

1346

# 3. SELECT ... FROM ... PROCEDURE ... JOIN (never at statement end)

1347

# 4. Never in a SELECT which does not use a table

1348

# 5. Any INTO DUMPFILE/OUTFILE/@var must be after PROCEDURE ANALYSE.

1349

# The content of DUMPFILE/OUTFILE/@var is from the PROCEDURE ANALYSE result set.

1350

# 6. CREATE TABLE ... AS SELECT PROCEDURE -> The table contains the PROCEDURE result set.

1351

# 7. INSERT ... SELECT ... PROCEDURE -> It's tried to INSERT the PROCEDURE result set.

1352

# High likelihood of ER_WRONG_VALUE_COUNT_ON_ROW

1353

# Only 10 %

1354

| | | | | | | | |

1355

PROCEDURE ANALYSE( 10 , 2000 ) ;

1356

1357

into:

1358

# Only 10 %

1359

| | | | | | | | |

1360

INTO into_object ;

1361

1362

into_object:

1363

# INSERT ... SELECT ... INTO DUMPFILE/OUTFILE/@var is not allowed

1364

# This also applies to CREATE TABLE ... AS SELECT ... INTO DUMPFILE/OUTFILE/@var

1365

# 1. @_letter is in average not enough variables compared to the column list.

1366

# -> @_letter disabled till I find a solution.

1367

# 2. DUMPFILE requires a result set of one row

1368

# Therefore 1172 Result consisted of more than one row is very likely.

1369

# OUTFILE _tmpnam | DUMPFILE _tmpnam | @_letter ;

1370

OUTFILE _tmpnam ;

1371

1372

for_update_lock_in_share_mode:

1373

| for_update | lock_share ;

1374

for_update:

1375

# Only 10 %

1376

| | | | | | | | |

1377

FOR UPDATE ;

1378

lock_share:

1379

# Only 10 %

1380

| | | | | | | | |

1381

LOCK IN SHARE MODE ;

1382

1383

1384

########## INSERT ####################

1385

# FIXME: INSERT IGNORE is missing

1386

insert:

1387

insert_normal | insert_normal | insert_normal | insert_normal | insert_with_sleep ;

1388

insert_normal:

1389

INSERT low_priority_delayed_high_priority into_word table_item simple_or_complicated on_duplicate_key_update ;

1390

simple_or_complicated:

1391

( random_field_quoted1 ) VALUES ( digit_or_null ) |

1392

braced_table_field_list used_select LIMIT 1 ;

1393

on_duplicate_key_update:

1394

# Only 10 %

1395

| | | | | | | | |

1396

ON DUPLICATE KEY UPDATE random_field_quoted1 = _digit ;

1397

insert_with_sleep:

1398

INSERT INTO table_item ( table_field_list ) SELECT $table_field_list FROM table_item WHERE wait_short = 0 LIMIT 1;

1399

1400

1401

########## REPLACE ####################

1402

replace:

1403

# 1. No ON DUPLICATE .... option. In case of DUPLICATE key it runs DELETE old row INSERT new row.

1404

# 2. HIGH_PRIORITY is not allowed

1405

REPLACE low_priority_delayed into_word table_item simple_or_complicated ;

1406

1407

1408

########## DUMP_LOAD_DATA ####################

1409

dump_load_data_sequence:

1410

# We omit a lot stuff which could be assigned after the table name. This stuff should

1411

# be important for locking tests.

1412

# We generate an outfile so that we have a chance to find an infile.

1413

# Go with the next command as soon as "LOCAL" is supported. (not supported in 5.4)

1414

# generate_outfile ; LOAD DATA low_priority_concurrent local_or_empty INFILE tmpnam replace_ignore INTO TABLE table_item ;

1415

generate_outfile ; LOAD DATA low_priority_concurrent INFILE tmpnam replace_ignore INTO TABLE table_item ;

1416

generate_outfile:

1417

SELECT * FROM template_table_item INTO OUTFILE _tmpnam ;

1418

low_priority_concurrent:

1419

| low_priority | concurrent ;

1420

concurrent:

1421

# Only 20 % <> empty.

1422

| | | | CONCURRENT ;

1423

replace_ignore:

1424

| replace_option | ignore ;

1425

1426

1427

########## GRANT_REVOKE ####################

1428

# We mix here some trouble I can imagine on mysql.tables_priv. It's basically how we access it's content.

1429

grant_revoke:

1430

GRANT ALL ON table_item TO otto@localhost |

1431

REVOKE ALL ON table_item FROM otto@localhost |

1432

SELECT COUNT(*) FROM mysql.tables_priv WHERE user = LOWER('OTTO') |

1433

DELETE FROM mysql.tables_priv WHERE user = LOWER('OTTO') ; FLUSH PRIVILEGES |

1434

/* 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 |

1435

SELECT COUNT(*) FROM information_schema.table_privileges WHERE grantee LIKE '%OTTO%' |

1436

SHOW GRANTS FOR otto@localhost ;

1437

1438

########## SQL MODE ########################

1439

sql_mode:

1440

empty_mode | empty_mode | empty_mode | empty_mode |

1441

empty_mode | empty_mode | empty_mode | empty_mode |

1442

empty_mode | empty_mode | empty_mode | empty_mode |

1443

traditional_mode ;

1444

empty_mode:

1445

SET SESSION SQL_MODE='' ;

1446

traditional_mode:

1447

SET SESSION SQL_MODE=LOWER('TRADITIONAL');

1448

1449

1450

########## DELETE ####################

1451

# FIXME: DELETE IGNORE is missing

1452

delete:

1453

delete_normal | delete_normal | delete_normal | delete_normal | delete_with_sleep ;

1454

delete_normal:

1455

# LIMIT row_count is disallowed in case we have a multi table delete.

1456

# Example: DELETE low_priority quick ignore A , B FROM table_item AS A join where LIMIT _digit |

1457

# DELETE is ugly because a table alias is not allowed.

1458

# If

1459

# Bug#46425 crash in Diagnostics_area::set_ok_status , empty statement, DELETE IGNORE

1460

# is fixed (ignore removed because of Bug#46425)

1461

# DELETE low_priority quick ignore FROM table_item WHERE `pk` > _digit LIMIT 1 |

1462

# DELETE low_priority quick ignore A , B FROM table_item AS A join where |

1463

# DELETE low_priority quick ignore A FROM table_item AS A where_subquery ;

1464

DELETE low_priority quick FROM table_item WHERE `pk` > _digit LIMIT 1 |

1465

DELETE low_priority quick A , B FROM table_item AS A join where |

1466

DELETE low_priority quick A FROM table_item AS A where_subquery ;

1467

where_subquery:

1468

where | subquery ;

1469

delete_with_sleep:

1470

DELETE low_priority quick FROM table_item WHERE `pk` + wait_short = _digit ;

1471

1472

1473

########## UPDATE ####################

1474

update:

1475

update_normal | update_normal | update_normal | update_normal | update_with_sleep ;

1476

update_normal:

1477

UPDATE low_priority ignore table_item SET random_field_quoted1 = _digit WHERE `pk` > _digit LIMIT _digit |

1478

UPDATE low_priority ignore table_item AS A join SET A. random_field_quoted1 = _digit , B. random_field_quoted1 = _digit ;

1479

update_with_sleep:

1480

UPDATE low_priority ignore table_item SET random_field_quoted1 = _digit WHERE wait_short = 0 LIMIT 1 ;

1481

1482

1483

########## LOCK/UNLOCK ####################

1484

lock_unlock:

1485

lock | unlock | unlock | unlock | unlock ;

1486

lock:

1487

LOCK TABLES lock_list ;

1488

lock_list:

1489

# Less likelihood for lists, because they

1490

# - are most probably less often used

1491

# - cause a higher likelihood of "table does not exist" errors.

1492

1493

lock_item , lock_item ;

1494

lock_item:

1495

# Have a low risk to get a clash of same table alias.

1496

table_item AS _letter lock_type ;

1497

lock_type:

1498

READ local_or_empty |

1499

low_priority WRITE ;

1500

# Transactional locking is not relevant for Celosia (no backport of fixes)

1501

# IN SHARE MODE nowait |

1502

# IN EXCLUSIVE MODE nowait ;

1503

nowait:

1504

NOWAIT | ;

1505

1506

unlock:

1507

UNLOCK TABLES ;

1508

1509

1510

########## FLUSH ####################

1511

flush:

1512

# WITH READ LOCK causes that nearly all following statements will fail with

1513

# Can't execute the query because you have a conflicting read lock

1514

# Therefore it should

1515

# - be rare

1516

# - last only very short time

1517

# So I put it into a sequence with FLUSH ... ; wait a bit ; UNLOCK TABLES

1518

1519

1520

1521

FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list | FLUSH TABLE table_list ;

1522

# temporary disabled FLUSH TABLES WITH READ LOCK ; SELECT wait_short ; UNLOCK TABLES ;

1523

1524

1525

########## TINY GRAMMAR ITEMS USED AT MANY PLACES ###########

1526

as:

1527

| AS ;

1528

1529

braced_table_field_list:

1530

# In case of <empty> for braced_table_field_list we have a significant fraction of

1531

# INSERT/REPLACE INTO <table> <no field list>

1532

# failing with: 1394 Can not insert into join view 'test.t1_view_0_S' without fields list

1533

# Therefore <empty> is only 20 %.

1534

( table_field_list ) | ( table_field_list ) | ( table_field_list ) | ( table_field_list ) | ;

1535

1536

default_word:

1537

| DEFAULT ;

1538

1539

digit_or_null:

1540

1541

NULL ;

1542

1543

engine:

1544

# Use if

1545

# Bug#46965 crash in ha_innobase::get_auto_increment

1546

# is fixed (InnoDB Disabled because of Bug#46965)

1547

# MEMORY | MyISAM | InnoDB ;

1548

MEMORY | MyISAM ;

1549

1550

equal:

1551

| = ;

1552

1553

delayed:

1554

# Only 10 %

1555

1556

;

1557

1558

high_priority:

1559

# Only 20 %

1560

| | | | HIGH_PRIORITY ;

1561

1562

ignore:

1563

# Only 10 %

1564

| | | | | | | | |

1565

IGNORE ;

1566

1567

if_exists:

1568

# 90 %, this reduces the amount of failing DROPs

1569

1570

1571

if_not_exists:

1572

# 90 %, this reduces the amount of failing CREATEs

1573

1574

1575

into_word:

1576

# Only 50 %

1577

| INTO ;

1578

1579

local_or_empty:

1580

# Only 20%

1581

| | | | LOCAL ;

1582

1583

low_priority_delayed_high_priority:

1584

| low_priority | delayed | high_priority ;

1585

1586

low_priority_delayed:

1587

| low_priority | delayed ;

1588

1589

low_priority:

1590

# Only 10 %

1591

| | | | | | | | |

1592

LOW_PRIORITY ;

1593

1594

no_or_empty:

1595

| NO ;

1596

1597

not_or_empty:

1598

| NOT ;

1599

1600

quick:

1601

# Only 10 %

1602

| | | | | | | | |

1603

QUICK ;

1604

1605

random_field_quoted:

1606

'int_key' | 'int' | 'pk' ;

1607

1608

random_field_quoted1:

1609

`col_int_key` | `col_int` | `pk` ;

1610

1611

replace_option:

1612

# Only 20 % <> empty.

1613

| | | | REPLACE ;

1614

1615

savepoint_or_empty:

1616

SAVEPOINT | ;

1617

1618

sql_buffer_result:

1619

# Only 50%

1620

| SQL_BUFFER_RESULT ;

1621

1622

table_field_list_or_star:

1623

table_field_list | table_field_list | table_field_list | table_field_list |

1624

{ $table_field_list = "*" } ;

1625

1626

table_field_list:

1627

{ $table_field_list = "`col_int_key` , `col_int` , `pk` "} |

1628

{ $table_field_list = "`col_int_key` , `pk` , `col_int` "} |

1629

{ $table_field_list = "`col_int` , `pk` , `col_int_key` "} |

1630

{ $table_field_list = "`col_int` , `col_int_key` , `pk` "} |

1631

{ $table_field_list = "`pk` , `col_int` , `col_int_key` "} |

1632

{ $table_field_list = "`pk` , `col_int_key` , `pk` "} ;

1633

1634

temporary:

1635

# Attention:

1636

# Do not apply CREATE/DROP TEMPORARY on "long life" whatever tables.

1637

# Use "short life" (-> <whatever>_n) tables only.

1638

# 1. In case of "long life" (-> <whatever>_s) tables the CREATE and DROP must be within

1639

# a sequence with some "wait_till_drop_table" between. TEMPORARY tables are session specific.

1640

# So no other session can use this table.

1641

# 2. In case of "short life" tables the CREATE and DROP are isolated. So the session

1642

# which created the table will pick a random statement and maybe do something on

1643

# the table <> DROP.

1644

# Only 10 % because no other session can use this table.

1645

| | | | | | | | |

1646

TEMPORARY ;

1647

1648

wait_short:

1649

SLEEP( 0.5 * rand_val * $life_time_unit ) ;

1650

1651

work_or_empty:

1652

| WORK ;

1653

1654

zero_or_one:

1655

0 | 1 ;

1656