~ubuntu-branches/ubuntu/vivid/drizzle/vivid-proposed

CREATE TABLE IF NOT EXISTS test . executors (id BIGINT, PRIMARY KEY(id)) ENGINE = MEMORY ; INSERT HIGH_PRIORITY IGNORE INTO test.executors SET id = CONNECTION_ID() ; COMMIT ;

191

192

init_basics1:

193

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

194

195

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

196

197

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

198

199

# sequence object | lifetime

200

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

201

# database | 2 * RAND() * $life_time_unit

202

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

203

# view | 0.5 * RAND() * $life_time_unit

204

# procedure | 0.5 * RAND() * $life_time_unit

205

# function | 0.5 * RAND() * $life_time_unit

206

# trigger | 0.5 * RAND() * $life_time_unit

207

208

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

209

210

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

211

212

# 2. $namespace_width = Width of a namespace

213

214

# Smaller numbers cause a

215

# - lower fraction of statements failing because of missing object

216

# - higher fraction of clashes when running with multiple sessions

217

218

# Some notes:

219

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

220

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

221

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

222

223

init_basics2:

224

# Store information if we have a debug server in $out_file.

225

# 1. The "Home" directory of

226

# - the server would be <vardir>/master-data/

227

# <vardir> is alculated by MTR and affected by options given to runall.pl

228

# - RQG is <RQG install directory>

229

# 2. The environment does not contain any variable pointing to <vardir> or RQG directories

230

# Therefore we need a $outfile with absolute path.

231

{$out_file='/tmp/'.$$.'.tmp' ; unlink($out_file); return undef} SELECT VERSION() LIKE '%debug%' INTO OUTFILE {return "'".$out_file."'"};

232

233

init_namespaces:

234

# Please choose between the following alternatives

235

# separate_objects -- no_separate_objects

236

# separate_normal_sequence -- no_separate_normal_sequence

237

# separate_table_types -- no_separate_table_types

238

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

239

separate_objects ; separate_normal_sequence ; separate_table_types ;

240

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

241

# separate_objects ; separate_normal_sequence ; no_separate_table_types ;

242

# 3. Total chaos

243

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

244

# no_separate_objects ; separate_normal_sequence ; no_separate_table_types ;

245

246

separate_table_types:

247

# Effect: Distinction between

248

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

249

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

250

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

251

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

252

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

253

# Disadvantage: The avoided suitations are not tested.

254

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

255

no_separate_table_types:

256

# Expected impact:

257

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

258

# - a significant fraction of statements will fail with

259

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

260

# Example: TRUNCATE <view>

261

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

262

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

263

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

264

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

265

# Just as a reminder:

266

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

267

# of the current transaction.

268

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

269

separate_normal_sequence:

270

# Advantages/Disadvantages: To be discovered

271

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

272

no_separate_normal_sequence:

273

# Advantages/Disadvantages: To be discovered

274

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

275

separate_objects:

276

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

277

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

278

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

279

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

280

# Disadvantage: The avoided suitations are not tested.

281

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

282

no_separate_objects:

283

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

284

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

285

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

286

# Advantage: More coverage

287

# Disadvantage: More failing statements

288

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

289

290

avoid_bugs:

291

# Set this grammar rule to "empty" if for example no optimizer related server system variable has to be switched.

292

;

293

294

event_scheduler_on:

295

SET GLOBAL EVENT_SCHEDULER = ON ;

296

297

event_scheduler_off:

298

SET GLOBAL EVENT_SCHEDULER = OFF ;

299

300

have_some_initial_objects:

301

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

302

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

303

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

304

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 ;

305

some_databases:

306

create_database ; create_database ; create_database ; create_database ;

307

some_base_tables:

308

create_base_table ; create_base_table ; create_base_table ; create_base_table ;

309

some_temp_tables:

310

create_temp_table ; create_temp_table ; create_temp_table ; create_temp_table ;

311

some_merge_tables:

312

create_merge_table ; create_merge_table ; create_merge_table ; create_merge_table ;

313

some_part_tables:

314

create_part_table ; create_part_table ; create_part_table ; create_part_table ;

315

some_view_tables:

316

create_view ; create_view ; create_view ; create_view ;

317

some_functions:

318

create_function ; create_function ; create_function ; create_function ;

319

some_procedures:

320

create_procedure ; create_procedure ; create_procedure ; create_procedure ;

321

some_trigger:

322

create_trigger ; create_trigger ; create_trigger ; create_trigger ;

323

some_events:

324

create_event ; create_event ; create_event ; create_event ;

325

326

nothing_disabled:

327

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

328

329

system_table_stuff:

330

# This is used in "grant_revoke".

331

CREATE USER otto@localhost ;

332

333

334

# Useful grammar rules ====================================================================================#

335

336

rand_val:

337

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

338

339

340

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

341

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

342

343

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

344

database_name_s:

345

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

346

database_name_n:

347

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

348

database_name:

349

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

350

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

351

database_name_s | database_name_n ;

352

353

354

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

355

base_table_name_s:

356

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

357

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

358

base_table_name_n:

359

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

360

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

361

base_table_name:

362

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

363

base_table_name_s | base_table_name_n ;

364

365

# Sometimes useful stuff:

366

base_table_item_s:

367

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

368

base_table_item_n:

369

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

370

base_table_item:

371

base_table_item_s | base_table_item_n ;

372

base_table_item_list_s:

373

base_table_item_s | base_table_item_s , base_table_item_s ;

374

base_table_item_list_n:

375

base_table_item_n | base_table_item_n , base_table_item_n ;

376

base_table_item_list:

377

base_table_item | base_table_item , base_table_item ;

378

379

380

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

381

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

382

temp_table_name_s:

383

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

384

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

385

temp_table_name_n:

386

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

387

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

388

temp_table_name:

389

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

390

temp_table_name_s | temp_table_name_n ;

391

392

# Sometimes useful stuff:

393

temp_table_item_s:

394

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

395

temp_table_item_n:

396

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

397

temp_table_item:

398

temp_table_item_s | temp_table_item_n ;

399

temp_table_item_list_s:

400

temp_table_item_s | temp_table_item_s , temp_table_item_s ;

401

temp_table_item_list_n:

402

temp_table_item_n | temp_table_item_n , temp_table_item_n ;

403

temp_table_item_list:

404

temp_table_item | temp_table_item , temp_table_item ;

405

406

407

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

408

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

409

merge_table_name_s:

410

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

411

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

412

merge_table_name_n:

413

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

414

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

415

merge_table_name:

416

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

417

merge_table_name_s | merge_table_name_n ;

418

419

# Sometimes useful stuff:

420

merge_table_item_s:

421

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

422

merge_table_item_n:

423

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

424

merge_table_item:

425

merge_table_item_s | merge_table_item_n ;

426

merge_table_item_list_s:

427

merge_table_item_s | merge_table_item_s , merge_table_item_s ;

428

merge_table_item_list_n:

429

merge_table_item_n | merge_table_item_n , merge_table_item_n ;

430

merge_table_item_list:

431

merge_table_item | merge_table_item , merge_table_item ;

432

433

434

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

435

view_table_name_s:

436

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

437

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

438

view_table_name_n:

439

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

440

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

441

view_table_name:

442

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

443

view_table_name_s | view_table_name_n ;

444

445

# Sometimes useful stuff:

446

view_table_item_s:

447

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

448

view_table_item_n:

449

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

450

view_table_item:

451

view_table_item_s | view_table_item_n ;

452

view_table_item_list_s:

453

view_table_item_s | view_table_item_s , view_table_item_s ;

454

view_table_item_list_n:

455

view_table_item_n | view_table_item_n , view_table_item_n ;

456

view_table_item_list:

457

view_table_item | view_table_item , view_table_item ;

458

459

460

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

461

part_table_name_s:

462

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

463

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

464

part_table_name_n:

465

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

466

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

467

part_table_name:

468

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

469

part_table_name_s | part_table_name_n ;

470

471

# Sometimes useful stuff:

472

part_table_item_s:

473

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

474

part_table_item_n:

475

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

476

part_table_item:

477

part_table_item_s | part_table_item_n ;

478

part_table_item_list_s:

479

part_table_item_s | part_table_item_s , part_table_item_s ;

480

part_table_item_list_n:

481

part_table_item_n | part_table_item_n , part_table_item_n ;

482

part_table_item_list:

483

part_table_item | part_table_item , part_table_item ;

484

485

486

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

487

488

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

489

table_item_s:

490

base_table_item_s | temp_table_item_s | merge_table_item_s | view_table_item_s | part_table_item_s ;

491

table_item_n:

492

base_table_item_n | temp_table_item_n | merge_table_item_n | view_table_item_n | part_table_item_n ;

493

table_item:

494

table_item_s | table_item_n ;

495

496

table_list:

497

# Less likelihood for lists, because they

498

# - are most probably less often used

499

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

500

501

table_item , table_item ;

502

503

504

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

505

table_no_view_item_s:

506

base_table_item_s | temp_table_item_s | merge_table_item_s | part_table_item_s ;

507

table_no_view_item_n:

508

base_table_item_n | temp_table_item_n | merge_table_item_n | part_table_item_n ;

509

table_no_view_item:

510

table_no_view_item_s | table_no_view_item_n ;

511

512

513

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

514

# These grammar rules could be used to avoid some partioning or merge table related bugs.

515

base_temp_view_table_item_s:

516

base_table_item_s | temp_table_item_s | view_table_item_s | part_table_item_s ;

517

base_temp_view_table_item_n:

518

base_table_item_n | temp_table_item_n | view_table_item_n | part_table_item_n ;

519

base_temp_view_table_item:

520

base_temp_view_table_item_s | base_temp_view_table_item ;

521

522

523

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

524

template_table_item:

525

# The disabled names are for future use. They cannot work with the current properties of .zz grammars.

526

# The problem is that we get in some scenarios tables with differing numnber of columns.

527

# { $template_table_item = "test.table0" } |

528

# { $template_table_item = "test.table1" } |

529

# { $template_table_item = "test.table10" } |

530

# { $template_table_item = "test.table0_int" } |

531

{ $template_table_item = "test.table0_int" } |

532

{ $template_table_item = "test.table1_int" } |

533

{ $template_table_item = "test.table10_int" } |

534

{ $template_table_item = "test.table0_int_autoinc" } |

535

{ $template_table_item = "test.table1_int_autoinc" } |

536

{ $template_table_item = "test.table10_int_autoinc" } ;

537

538

539

procedure_name_s:

540

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

541

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

542

procedure_name_n:

543

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

544

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

545

procedure_name:

546

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

547

procedure_name_s | procedure_name_n ;

548

549

# Sometimes useful stuff:

550

procedure_item_s:

551

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

552

procedure_item_n:

553

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

554

procedure_item:

555

procedure_item_s | procedure_item_n ;

556

557

function_name_s:

558

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

559

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

560

function_name_n:

561

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

562

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

563

function_name:

564

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

565

function_name_s | function_name_n ;

566

567

function_item_s:

568

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

569

function_item_n:

570

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

571

function_item:

572

function_item_s | function_item_n ;

573

574

trigger_name_s:

575

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

576

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

577

trigger_name_n:

578

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

579

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

580

trigger_name:

581

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

582

trigger_name_s | trigger_name_n ;

583

584

trigger_item_s:

585

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

586

trigger_item_n:

587

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

588

trigger_item:

589

trigger_item_s | trigger_item_n ;

590

591

event_name_s:

592

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

593

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

594

event_name_n:

595

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

596

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

597

event_name:

598

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

599

event_name_s | event_name_n ;

600

601

event_item_s:

602

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

603

event_item_n:

604

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

605

event_item:

606

event_item_s | event_item_n ;

607

608

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

609

610

query:

611

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

612

613

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

614

615

transaction:

616

start_transaction | commit | rollback |

617

start_transaction | commit | rollback |

618

start_transaction | commit | rollback |

619

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

620

BEGIN work_or_empty | set_autocommit | kill_query_or_session ;

621

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

622

623

savepoint_id:

624

A | B ;

625

626

set_isolation_level:

627

SET SESSION TX_ISOLATION = TRIM(' isolation_level ');

628

629

isolation_level:

630

REPEATABLE-READ | READ-COMMITTED | SERIALIZABLE ;

631

632

633

start_transaction:

634

START TRANSACTION with_consistent_snapshot ;

635

with_consistent_snapshot:

636

637

638

# COMMIT/ROLLBACK

639

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

640

# 1. RELEASE should be rare

641

# 2. AND CHAIN RELEASE is nonsense and will get an error

642

# 3. COMMIT [ WORK ] [ AND [ NO ] CHAIN ] [RELEASE]

643

# AND NO CHAIN is the default, no RELEASE is the default

644

# 4. ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [RELEASE]

645

# [ TO SAVEPOINT <savepoint specifier> ]

646

# You may specify only one of:

647

# "[AND [NO] CHAIN]" or "RELEASE" or "TO SAVEPOINT ...".

648

# AND NO CHAIN is the default, no RELEASE is the default

649

650

commit:

651

COMMIT work_or_empty no_chain release_or_empty |

652

COMMIT work_or_empty AND CHAIN ;

653

no_chain:

654

| | | |

655

AND NO CHAIN ;

656

release_or_empty:

657

658

659

rollback:

660

ROLLBACK work_or_empty no_chain release_or_empty |

661

ROLLBACK work_or_empty AND CHAIN ;

662

663

set_autocommit:

664

SET AUTOCOMMIT = zero_or_one ;

665

666

kill_query_or_session:

667

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

668

# I expect in case

669

# - a query gets killed that most locks hold by this query are automatically removed.

670

# Metadata locks might survive till transaction end.

671

# - a session gets killed that any lock hold by this session gets automatically removed.

672

# We will not check the removal here via SQL or sophisticated PERL code.

673

# We just hope that forgotten locks lead sooner or later to nice deadlocks.

674

675

# Attention:

676

# There is some unfortunate sideeffect of KILL <session> .

677

# It reduces the probability to detect deadlocks because it

678

# might hit a participating session.

679

680

# Killing a query or session must NOT affect an

681

# - auxiliary RQG session (= non executor) because this can fool RQG's judgement

682

# about the test outcome

683

# - executor session which is within the early phase when it pulls meta data (table names, column names,

684

# data types, ...). This could end up with RQG exit status 255.

685

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

686

# This is ensured by the following:

687

# Every executor pulls meta data and runs after that "query_init".

688

# Within "query_init" this executor writes his own CONNECTION_ID() into the table test.executors.

689

# When running KILL for another session only the id's found in test.executors must be selected.

690

# In case it is planned to kill

691

# - another session AFTER

692

# - own session (suicide) BEFORE

693

# the KILL statement the entry within test.executors has to be removed.

694

695

# Depending on scenario (a session might run COMMIT/ROLLBACK RELEASE) and whatever other effects it might happen that

696

# 1. A session disappears but the entry is not removed.

697

# This is harmless because somewhere later another session will pick the id from test.executors

698

# try the kill session and remove the entry.

699

# 2. A session entry in test.executors does not exist but the session is alife.

700

# This is harmless because somewhere later this session will try to remove its' no more existing

701

# entry from test.executors and kill himself.

702

703

# Scenarios covered:

704

# 1. S1 kills S2

705

# 2. S1 kills S1

706

# 3. S1 try to kill S3 which no more exists.

707

# 4. Various combinations of sessions running 1., 2. or 3.

708

709

# The various COMMITs should ensure that locking effects caused by activity on test.executors are minimal.

710

COMMIT ; own_id ; delete_executor_entry ; COMMIT ; KILL @kill_id |

711

COMMIT ; own_id ; ; COMMIT ; KILL QUERY @kill_id |

712

COMMIT ; minimal_id ; ; COMMIT ; KILL @kill_id ; delete_executor_entry ; COMMIT |

713

COMMIT ; minimal_id ; ; COMMIT ; KILL QUERY @kill_id ;

714

own_id:

715

SET @kill_id = CONNECTION_ID() ;

716

minimal_id:

717

SELECT MIN(id) INTO @kill_id FROM test . executors ;

718

delete_executor_entry:

719

DELETE FROM test . executors WHERE id = @kill_id ;

720

721

ddl:

722

database_ddl |

723

base_table_ddl | base_table_ddl | base_table_ddl |

724

temp_table_ddl | temp_table_ddl | temp_table_ddl |

725

merge_table_ddl | merge_table_ddl | merge_table_ddl |

726

part_table_ddl | part_table_ddl | part_table_ddl |

727

view_ddl | view_ddl | view_ddl |

728

procedure_ddl | procedure_ddl | procedure_ddl |

729

function_ddl | function_ddl | function_ddl |

730

trigger_ddl | trigger_ddl | trigger_ddl |

731

event_ddl |

732

truncate_table |

733

drop_table_list |

734

rename_table |

735

# Bug#54486 assert in my_seek, concurrent DROP/CREATE SCHEMA, CREATE TABLE, REPAIR

736

# affects table_maintenance_ddl in mysql-5.1.

737

# The problem seems to have disappeared in higher MySQL versions.

738

table_maintenance_ddl |

739

dump_load_data_sequence |

740

grant_revoke |

741

rename_column |

742

sql_mode ;

743

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

744

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

745

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

746

# PREPARE st1 FOR must not contain multiple statements.

747

748

749

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

750

# The alias within HANDLER ... OPEN is optional. Unfortunately the HANDLER ... READ/CLOSE ... statements

751

# do not accept SCHEMA names. Therefore the tablename must be either a table within the current SCHEMA

752

# or an alias. We go with alias all time.

753

754

handler:

755

handler_open |

756

handler_read |

757

handler_close ;

758

759

handler_open:

760

HANDLER table_no_view_item OPEN as handler_a ;

761

762

handler_read:

763

HANDLER handler_a READ handler_index comparison_operator ( _digit ) handler_read_part |

764

HANDLER handler_a READ handler_index first_next_prev_last handler_read_part |

765

HANDLER handler_a READ first_next handler_read_part ;

766

handler_index:

767

`PRIMARY` |

768

`col_int_key` ;

769

handler_read_part:

770

| where ;

771

first_next:

772

FIRST |

773

NEXT ;

774

first_next_prev_last:

775

FIRST |

776

NEXT |

777

PREV |

778

LAST ;

779

780

handler_close:

781

HANDLER handler_a CLOSE ;

782

783

784

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

785

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

786

# of OPEN tables etc.

787

show:

788

database_show |

789

table_show |

790

routine_show |

791

SHOW STATUS ;

792

793

database_show:

794

show_databases | show_create_database ;

795

796

show_databases:

797

SHOW databases_schemas ;

798

databases_schemas:

799

DATABASES | SCHEMAS ;

800

801

show_create_database:

802

SHOW CREATE database_schema database_name ;

803

804

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

805

806

table_show:

807

show_tables | show_tables |

808

show_table_status | show_table_status |

809

show_create_table | show_create_view |

810

show_open_tables | show_columns ;

811

812

show_tables:

813

SHOW TABLES ;

814

815

show_create_table:

816

# Works also for views

817

SHOW CREATE TABLE table_item ;

818

819

show_open_tables:

820

SHOW OPEN TABLES IN database_name ;

821

822

show_table_status:

823

# Works also for views

824

SHOW TABLE STATUS ;

825

826

show_columns:

827

SHOW full COLUMNS from_in table_item show_columns_part ;

828

full:

829

# Only 20 %

830

| | | | FULL ;

831

from_in:

832

FROM | IN ;

833

show_columns_part:

834

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

835

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

836

# FIXME: Add "WHERE"

837

| LIKE '%INT%' ;

838

839

show_create_view:

840

SHOW CREATE VIEW view_table_item ;

841

842

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

843

routine_show:

844

show_create_function | show_function_code | show_function_status |

845

show_create_procedure | show_procedure_code | show_procedure_status |

846

show_triggers | show_create_trigger |

847

show_events | show_create_event ;

848

849

show_create_function:

850

SHOW CREATE FUNCTION function_item ;

851

852

show_function_code:

853

is_debug1 is_debug2 { return $m1 } SHOW FUNCTION CODE function_item { return $m2 };

854

855

show_function_status:

856

SHOW FUNCTION STATUS ;

857

858

show_create_procedure:

859

SHOW CREATE PROCEDURE procedure_item ;

860

861

show_procedure_code:

862

is_debug1 is_debug2 { return $m1 } SHOW PROCEDURE CODE procedure_item { return $m2 };

863

864

is_debug1:

865

# Calculate if we have a debug server.

866

{ $is_debug_server = -1; open($my_file,'<'.$out_file); read($my_file,$is_debug_server,1000); close($my_file); return undef };

867

868

is_debug2:

869

# Set the marker according if we have a debug server or not.

870

{ $m1='/*'; $m2='*/'; if ( $is_debug_server == 1 ) { $m1=''; $m2='' }; return undef } ;

871

872

show_procedure_status:

873

SHOW PROCEDURE STATUS ;

874

875

show_triggers:

876

SHOW TRIGGERS ;

877

878

show_create_trigger:

879

SHOW CREATE TRIGGER trigger_item ;

880

881

show_events:

882

SHOW EVENTS from_in database_name ;

883

884

show_create_event:

885

SHOW CREATE EVENT event_item_s ;

886

887

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

888

# We run here only object related SELECTs.

889

is_selects:

890

is_schemata | is_tables | is_columns ;

891

is_schemata:

892

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

893

is_tables:

894

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

895

is_columns:

896

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

897

# 19.1. The INFORMATION_SCHEMA SCHEMATA Table

898

# 19.2. The INFORMATION_SCHEMA TABLES Table

899

# 19.3. The INFORMATION_SCHEMA COLUMNS Table

900

# 19.4. The INFORMATION_SCHEMA STATISTICS Table

901

# 19.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table

902

# 19.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table

903

# 19.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table

904

# 19.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table

905

# 19.9. The INFORMATION_SCHEMA CHARACTER_SETS Table

906

# 19.10. The INFORMATION_SCHEMA COLLATIONS Table

907

# 19.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table

908

# 19.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table

909

# 19.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

910

# 19.14. The INFORMATION_SCHEMA ROUTINES Table

911

# 19.15. The INFORMATION_SCHEMA VIEWS Table

912

# 19.16. The INFORMATION_SCHEMA TRIGGERS Table

913

# 19.17. The INFORMATION_SCHEMA PLUGINS Table

914

# 19.18. The INFORMATION_SCHEMA ENGINES Table

915

# 19.19. The INFORMATION_SCHEMA PARTITIONS Table

916

# 19.20. The INFORMATION_SCHEMA EVENTS Table

917

# 19.21. The INFORMATION_SCHEMA FILES Table

918

# 19.22. The INFORMATION_SCHEMA TABLESPACES Table

919

# 19.23. The INFORMATION_SCHEMA PROCESSLIST Table

920

# 19.24. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table

921

# 19.25. The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables

922

# 19.26. The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables

923

# 19.27. The INFORMATION_SCHEMA PARAMETERS Table

924

# 19.28. The INFORMATION_SCHEMA PROFILING Table

925

# 19.29. Other INFORMATION_SCHEMA Tables

926

927

928

929

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

930

database_ddl:

931

create_database | create_database | create_database |

932

drop_database | alter_database |

933

database_sequence ;

934

935

create_database:

936

CREATE database_schema if_not_exists database_name_n database_spec ;

937

938

database_schema:

939

DATABASE | SCHEMA ;

940

941

database_spec:

942

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

943

default_word CHARACTER SET equal utf8 | default_word COLLATE equal utf8_bin ;

944

945

drop_database:

946

DROP database_schema if_exists database_name_n ;

947

948

alter_database:

949

ALTER database_schema database_name_n database_spec ;

950

951

database_sequence:

952

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

953

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

954

wait_till_drop_database:

955

SELECT SLEEP( 2 * rand_val * $life_time_unit ) ;

956

957

958

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

959

base_table_ddl:

960

961

drop_base_table | alter_base_table |

962

base_table_sequence ;

963

964

create_base_table:

965

CREATE TABLE if_not_exists base_table_item_n { $create_table_item = $base_table_item_n ; return undef } create_table_part ;

966

create_table_part:

967

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

968

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

969

AS used_select ;

970

971

drop_base_table:

972

# DROP two tables is in "drop_table_list"

973

DROP TABLE if_exists base_table_item_n restrict_cascade ;

974

975

alter_base_table:

976

ALTER ignore TABLE base_table_item_n alter_base_temp_table_part ;

977

978

alter_base_temp_table_part:

979

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

980

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

981

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

982

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

983

984

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

985

ENGINE = engine ;

986

987

base_table_sequence:

988

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

989

990

wait_till_drop_table:

991

SELECT SLEEP( rand_val * $life_time_unit ) ;

992

993

temp_table_ddl:

994

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

995

# able to use this table.

996

997

drop_temp_table | alter_temp_table ;

998

999

create_temp_table:

1000

CREATE TEMPORARY TABLE if_not_exists temp_table_item_n { $create_table_item = $temp_table_item_n ; return undef } create_table_part ;

1001

1002

drop_temp_table:

1003

# DROP two tables is in "drop_table_list"

1004

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

1005

DROP TEMPORARY TABLE if_exists temp_table_item_n |

1006

DROP TABLE if_exists temp_table_item_n ;

1007

1008

alter_temp_table:

1009

ALTER ignore TABLE temp_table_item_n alter_base_temp_table_part ;

1010

1011

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

1012

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

1013

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

1014

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

1015

# Example:

1016

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

1017

# Table Op Msg_type Msg_text

1018

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

1019

# test.v1 optimize status Operation failed

1020

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

1021

# Table Op Msg_type Msg_text

1022

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

1023

1024

table_maintenance_ddl:

1025

analyze_table | optimize_table | checksum_table | check_table | repair_table ;

1026

1027

analyze_table:

1028

ANALYZE not_to_binlog_local TABLE table_list ;

1029

not_to_binlog_local:

1030

| NO_WRITE_TO_BINLOG | LOCAL ;

1031

1032

optimize_table:

1033

OPTIMIZE not_to_binlog_local TABLE table_list ;

1034

1035

checksum_table:

1036

CHECKSUM TABLE table_list quick_extended ;

1037

quick_extended:

1038

| quick | extended ;

1039

extended:

1040

# Only 10 %

1041

1042

1043

check_table:

1044

CHECK TABLE table_list check_table_options ;

1045

check_table_options:

1046

1047

1048

repair_table:

1049

REPAIR not_to_binlog_local TABLE table_list quick extended use_frm ;

1050

1051

use_frm:

1052

# Only 10 %

1053

1054

1055

1056

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

1057

truncate_table:

1058

TRUNCATE table_word table_no_view_item_n ;

1059

table_word:

1060

| TABLE ;

1061

1062

drop_table_list:

1063

# DROP one table is in "drop_*table"

1064

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

1065

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

1066

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

1067

# - two tables (some might not exist)

1068

DROP temporary TABLE if_exists table_no_view_item_n , table_no_view_item_n restrict_cascade ;

1069

1070

rename_table:

1071

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

1072

RENAME TABLE rename_item_list ;

1073

rename_item_list:

1074

rename_item | rename_item , rename_item ;

1075

rename_item:

1076

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

1077

# the concept with different lifetimes gets broken.

1078

base_table_item_n TO base_table_item_n |

1079

temp_table_item_n TO temp_table_item_n |

1080

merge_table_item_n TO merge_table_item_n |

1081

part_table_item_n TO part_table_item_n ;

1082

1083

rename_column:

1084

ALTER TABLE table_no_view_item_s CHANGE COLUMN column_to_change my_column INT |

1085

ALTER TABLE table_no_view_item_s CHANGE COLUMN my_column column_to_change INT ;

1086

1087

column_to_change:

1088

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

1089

1090

1091

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

1092

merge_table_ddl:

1093

1094

drop_merge_table | alter_merge_table |

1095

merge_table_sequence ;

1096

1097

create_merge_table:

1098

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

1099

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

1100

# by the following:

1101

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

1102

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

1103

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

1104

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

1105

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

1106

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

1107

merge_init_n build_partner1 ; build_partner2 ; create_merge ;

1108

1109

insert_method:

1110

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

1111

insert_method_value:

1112

NO | FIRST | LAST ;

1113

1114

drop_merge_table:

1115

# DROP two tables is in "drop_table_list"

1116

DROP TABLE if_exists merge_table_item_n ;

1117

1118

merge_table_sequence:

1119

# Notes:

1120

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

1121

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

1122

# - must exist

1123

# - use the storage engine MyISAM

1124

# - have the same layout

1125

# Therefore we create here all we need.

1126

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

1127

# are under full DDL/DML load.

1128

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

1129

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

1130

1131

alter_merge_table:

1132

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

1133

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

1134

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

1135

ALTER TABLE merge_table_item_n INSERT_METHOD = insert_method_value ;

1136

1137

merge_init_s:

1138

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

1139

merge_init_n:

1140

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

1141

build_partner1:

1142

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

1143

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

1144

build_partner2:

1145

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

1146

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

1147

create_merge:

1148

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

1149

1150

1151

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

1152

part_table_ddl:

1153

1154

drop_part_table |

1155

alter_part_table |

1156

part_table_sequence ;

1157

1158

create_part_table:

1159

CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM partition_algorithm AS SELECT * FROM template_table_item |

1160

CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM partition_algorithm AS SELECT * FROM template_table_item |

1161

CREATE TABLE if_not_exists part_table_item_n ENGINE = MyISAM partition_algorithm AS used_select ;

1162

1163

partition_algorithm:

1164

# We do not need sophisticated partitioning here.

1165

PARTITION BY KEY (pk) PARTITIONS 2 |

1166

PARTITION BY LINEAR HASH(pk) PARTITIONS 3 ;

1167

1168

drop_part_table:

1169

# DROP two tables is in "drop_table_list"

1170

DROP TABLE if_exists part_table_item_n ;

1171

1172

alter_part_table:

1173

ALTER ignore TABLE part_table_item_n alter_part_table_part ;

1174

1175

alter_part_table_part:

1176

partition_algorithm |

1177

COMMENT = 'UPDATED NOW()' ;

1178

1179

part_table_sequence:

1180

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

1181

1182

1183

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

1184

view_ddl:

1185

1186

drop_view | alter_view |

1187

view_sequence ;

1188

1189

create_view:

1190

CREATE view_replace ALGORITHM = view_algoritm VIEW view_table_item_n AS used_select ;

1191

view_replace:

1192

# Only 20 %

1193

| | | | OR REPLACE ;

1194

view_algoritm:

1195

UNDEFINED | MERGE | TEMPTABLE ;

1196

1197

drop_view:

1198

DROP VIEW if_exists view_table_item_n restrict_cascade ;

1199

1200

restrict_cascade:

1201

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

1202

| RESTRICT | CASCADE ;

1203

1204

alter_view:

1205

# Attention: Only changing the algorithm is not allowed.

1206

ALTER ALGORITHM = view_algoritm VIEW view_table_item_n AS used_select ;

1207

1208

view_sequence:

1209

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

1210

1211

1212

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

1213

procedure_ddl:

1214

create_procedure | create_procedure |

1215

drop_procedure | alter_procedure |

1216

procedure_sequence ;

1217

1218

create_procedure:

1219

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

1220

proc_stmt:

1221

select | update ;

1222

1223

drop_procedure:

1224

DROP PROCEDURE if_exists procedure_item_n ;

1225

1226

alter_procedure:

1227

ALTER PROCEDURE procedure_item_n COMMENT 'UPDATED NOW()' ;

1228

1229

procedure_sequence:

1230

# FIXME: The PROCEDURE should touch base_table_name_s only .

1231

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

1232

1233

1234

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

1235

function_ddl:

1236

create_function | create_function |

1237

drop_function | alter_function |

1238

function_sequence ;

1239

1240

create_function:

1241

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

1242

func_statement:

1243

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

1244

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

1245

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

1246

1247

drop_function:

1248

DROP FUNCTION if_exists function_item_n ;

1249

1250

alter_function:

1251

ALTER FUNCTION function_item_n COMMENT 'UPDATED NOW()' ;

1252

1253

function_sequence:

1254

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

1255

1256

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

1257

trigger_ddl:

1258

create_trigger | create_trigger |

1259

drop_trigger |

1260

trigger_sequence ;

1261

1262

create_trigger:

1263

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

1264

trigger_time:

1265

BEFORE | AFTER ;

1266

trigger_event:

1267

INSERT | DELETE | UPDATE ;

1268

trigger_action:

1269

insert | replace | delete | update | CALL procedure_item ;

1270

1271

drop_trigger:

1272

DROP TRIGGER if_exists trigger_item_n ;

1273

1274

trigger_sequence:

1275

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

1276

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

1277

1278

1279

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

1280

event_ddl:

1281

1282

1283

event_scheduler_on | event_scheduler_off ;

1284

create_event:

1285

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 ;

1286

completion_handling:

1287

ON COMPLETION not_or_empty PRESERVE ;

1288

drop_event:

1289

DROP EVENT if_exists event_item_s ;

1290

alter_event:

1291

ALTER EVENT event_item_s COMMENT 'UPDATED NOW()';

1292

1293

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

1294

1295

dml:

1296

# Have only 10 % prepared statements.

1297

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

1298

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

1299

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

1300

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

1301

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

1302

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

1303

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

1304

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

1305

1306

dml2:

1307

select | select | select |

1308

1309

1310

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

1311

do:

1312

DO 1 |

1313

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

1314

# The SELECT must give one column.

1315

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

1316

DO user_lock_action ;

1317

1318

user_lock_action:

1319

IS_FREE_LOCK(TRIM(' _digit ')) |

1320

IS_USED_LOCK(TRIM(' _digit ')) |

1321

RELEASE_LOCK(TRIM(' _digit ')) |

1322

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

1323

1324

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

1325

select:

1326

select_normal | select_normal | select_normal | select_normal | select_with_sleep ;

1327

1328

select_normal:

1329

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

1330

select_part1 addition into for_update_lock_in_share_mode ;

1331

1332

select_with_sleep:

1333

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

1334

SELECT 1 FROM table_item WHERE wait_short = 0 LIMIT 1 ;

1335

1336

used_select:

1337

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

1338

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

1339

# are partially disallowed or cause garbage (PROCEDURE).

1340

select_part1 addition_no_procedure ;

1341

1342

select_part1:

1343

SELECT high_priority cache_results table_field_list_or_star FROM table_in_select as A ;

1344

1345

cache_results:

1346

| sql_no_cache | sql_cache ;

1347

sql_no_cache:

1348

# Only 10 %

1349

| | | | | | | | |

1350

SQL_NO_CACHE ;

1351

sql_cache:

1352

# Only 10 %

1353

| | | | | | | | |

1354

SQL_CACHE ;

1355

1356

table_in_select:

1357

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

1358

# Therefore they should be rare.

1359

1360

( SELECT table_field_list_or_star FROM table_item ) ;

1361

1362

addition:

1363

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

1364

where procedure_analyze |

1365

subquery procedure_analyze |

1366

join where procedure_analyze |

1367

procedure_analyze union where ;

1368

1369

addition_no_procedure:

1370

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

1371

# Don't add procedure_analyze.

1372

1373

subquery |

1374

join where |

1375

union where ;

1376

1377

where:

1378

# The very selective condition is intentional.

1379

# It should ensure that

1380

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

1381

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

1382

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

1383

# - tables (DELETE) do not become permanent empty

1384

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

1385

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

1386

WHERE function_item () = _digit AND `pk` = _digit ;

1387

1388

1389

union:

1390

UNION SELECT * FROM table_in_select as B ;

1391

1392

join:

1393

# Do not place a where condition here.

1394

NATURAL JOIN table_item B ;

1395

1396

subquery:

1397

correlated |

1398

non_correlated ;

1399

subquery_part1:

1400

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

1401

correlated:

1402

subquery_part1 A.`pk` ) ;

1403

non_correlated:

1404

subquery_part1 _digit ) ;

1405

1406

procedure_analyze:

1407

# Correct place of PROCEDURE ANALYSE( 10 , 2000 )

1408

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

1409

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

1410

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

1411

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

1412

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

1413

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

1414

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

1415

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

1416

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

1417

# High likelihood of ER_WRONG_VALUE_COUNT_ON_ROW

1418

# Only 10 %

1419

| | | | | | | | |

1420

PROCEDURE ANALYSE( 10 , 2000 ) ;

1421

1422

into:

1423

# Only 10 %

1424

| | | | | | | | |

1425

INTO into_object ;

1426

1427

into_object:

1428

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

1429

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

1430

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

1431

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

1432

# 2. DUMPFILE requires a result set of one row

1433

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

1434

# OUTFILE _tmpnam | DUMPFILE _tmpnam | @_letter ;

1435

OUTFILE _tmpnam ;

1436

1437

for_update_lock_in_share_mode:

1438

| for_update | lock_share ;

1439

for_update:

1440

# Only 10 %

1441

| | | | | | | | |

1442

FOR UPDATE ;

1443

lock_share:

1444

# Only 10 %

1445

| | | | | | | | |

1446

LOCK IN SHARE MODE ;

1447

1448

1449

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

1450

insert:

1451

insert_normal | insert_normal | insert_normal | insert_normal | insert_with_sleep ;

1452

insert_normal:

1453

INSERT low_priority_delayed_high_priority ignore into_word table_item simple_or_complicated on_duplicate_key_update ;

1454

simple_or_complicated:

1455

( random_field_quoted1 ) VALUES ( digit_or_null ) |

1456

braced_table_field_list used_select LIMIT 1 ;

1457

on_duplicate_key_update:

1458

# Only 10 %

1459

| | | | | | | | |

1460

ON DUPLICATE KEY UPDATE random_field_quoted1 = _digit ;

1461

insert_with_sleep:

1462

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

1463

1464

1465

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

1466

replace:

1467

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

1468

# 2. HIGH_PRIORITY is not allowed

1469

REPLACE low_priority_delayed into_word table_item simple_or_complicated ;

1470

1471

1472

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

1473

dump_load_data_sequence:

1474

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

1475

# be important for locking tests.

1476

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

1477

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

1478

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

1479

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

1480

generate_outfile:

1481

SELECT * FROM template_table_item INTO OUTFILE _tmpnam ;

1482

low_priority_concurrent:

1483

| low_priority | concurrent ;

1484

concurrent:

1485

# Only 20 % <> empty.

1486

| | | | CONCURRENT ;

1487

replace_ignore:

1488

| replace_option | ignore ;

1489

1490

1491

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

1492

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

1493

grant_revoke:

1494

GRANT ALL ON table_item TO otto@localhost |

1495

REVOKE ALL ON table_item FROM otto@localhost |

1496

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

1497

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

1498

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

1499

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

1500

SHOW GRANTS FOR otto@localhost ;

1501

1502

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

1503

sql_mode:

1504

empty_mode | empty_mode | empty_mode | empty_mode |

1505

empty_mode | empty_mode | empty_mode | empty_mode |

1506

empty_mode | empty_mode | empty_mode | empty_mode |

1507

traditional_mode ;

1508

empty_mode:

1509

SET SESSION SQL_MODE='' ;

1510

traditional_mode:

1511

SET SESSION SQL_MODE=LOWER('TRADITIONAL');

1512

1513

1514

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

1515

# FIXME: DELETE IGNORE is missing

1516

delete:

1517

delete_normal | delete_normal | delete_normal | delete_normal | delete_with_sleep ;

1518

delete_normal:

1519

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

1520

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

1521

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

1522

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

1523

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

1524

DELETE low_priority quick ignore A FROM table_item AS A where_subquery ;

1525

where_subquery:

1526

where | subquery ;

1527

delete_with_sleep:

1528

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

1529

1530

1531

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

1532

update:

1533

update_normal | update_normal | update_normal | update_normal | update_with_sleep ;

1534

update_normal:

1535

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

1536

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

1537

update_with_sleep:

1538

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

1539

1540

1541

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

1542

lock_unlock:

1543

lock | unlock | unlock | unlock | unlock ;

1544

lock:

1545

LOCK TABLES lock_list ;

1546

lock_list:

1547

# Less likelihood for lists, because they

1548

# - are most probably less often used

1549

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

1550

1551

lock_item , lock_item ;

1552

lock_item:

1553

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

1554

table_item AS _letter lock_type ;

1555

lock_type:

1556

READ local_or_empty |

1557

low_priority WRITE ;

1558

1559

unlock:

1560

UNLOCK TABLES ;

1561

1562

1563

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

1564

flush:

1565

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

1566

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

1567

# Therefore it should

1568

# - be rare

1569

# - last only very short time

1570

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

1571

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

1572

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

1573

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

1574

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

1575

FLUSH TABLES | FLUSH TABLES | FLUSH TABLES |

1576

FLUSH TABLES | FLUSH TABLES | FLUSH TABLES |

1577

FLUSH TABLES table_list WITH READ LOCK | UNLOCK TABLES | UNLOCK TABLES |

1578

FLUSH TABLES WITH READ LOCK ; SELECT wait_short ; UNLOCK TABLES ;

1579

1580

1581

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

1582

as:

1583

| AS ;

1584

1585

braced_table_field_list:

1586

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

1587

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

1588

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

1589

# Therefore <empty> is only 20 %.

1590

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

1591

1592

comparison_operator:

1593

= |

1594

<= |

1595

>= |

1596

< |

1597

> ;

1598

1599

1600

default_word:

1601

| DEFAULT ;

1602

1603

digit_or_null:

1604

1605

NULL ;

1606

1607

engine:

1608

MEMORY | MyISAM | InnoDB ;

1609

1610

equal:

1611

| = ;

1612

1613

delayed:

1614

# Only 10 %

1615

1616

1617

high_priority:

1618

# Only 20 %

1619

| | | | HIGH_PRIORITY ;

1620

1621

ignore:

1622

# Only 10 %

1623

| | | | | | | | |

1624

IGNORE ;

1625

1626

if_exists:

1627

# 90 %, this reduces the amount of failing DROPs

1628

1629

1630

if_not_exists:

1631

# 90 %, this reduces the amount of failing CREATEs

1632

1633

1634

into_word:

1635

# Only 50 %

1636

| INTO ;

1637

1638

local_or_empty:

1639

# Only 20%

1640

| | | | LOCAL ;

1641

1642

low_priority_delayed_high_priority:

1643

| low_priority | delayed | high_priority ;

1644

1645

low_priority_delayed:

1646

| low_priority | delayed ;

1647

1648

low_priority:

1649

# Only 10 %

1650

| | | | | | | | |

1651

LOW_PRIORITY ;

1652

1653

no_or_empty:

1654

| NO ;

1655

1656

not_or_empty:

1657

| NOT ;

1658

1659

quick:

1660

# Only 10 %

1661

| | | | | | | | |

1662

QUICK ;

1663

1664

random_field_quoted:

1665

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

1666

1667

random_field_quoted1:

1668

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

1669

1670

replace_option:

1671

# Only 20 % <> empty.

1672

| | | | REPLACE ;

1673

1674

savepoint_or_empty:

1675

SAVEPOINT | ;

1676

1677

sql_buffer_result:

1678

# Only 50%

1679

| SQL_BUFFER_RESULT ;

1680

1681

table_field_list_or_star:

1682

table_field_list | table_field_list | table_field_list | table_field_list |

1683

{ $table_field_list = "*" } ;

1684

1685

table_field_list:

1686

# It is intentional that the next line will lead to ER_FIELD_SPECIFIED_TWICE

1687

# in case it is used in INSERT INTO <table> ( table_field_list )

1688

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

1689

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

1690

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

1691

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

1692

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

1693

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

1694

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

1695

1696

temporary:

1697

# Attention:

1698

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

1699

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

1700

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

1701

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

1702

# So no other session can use this table.

1703

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

1704

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

1705

# the table <> DROP.

1706

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

1707

| | | | | | | | |

1708

TEMPORARY ;

1709

1710

wait_short:

1711

SLEEP( 0.5 * rand_val * $life_time_unit ) ;

1712

1713

work_or_empty:

1714

| WORK ;

1715

1716

zero_or_one:

1717

0 | 1 ;

1718

Older »