~drizzle-trunk/drizzle/development : revision 1

1

################################################################################

2

# inc/partition.pre #

3

# #

4

# Purpose: #

5

# Auxiliary script creating prerequisites needed by the partitioning tests #

6

# The name of the toplevel scripts sourcing this one is #

7

# t/partition_<feature>_<storage engine>.test #

8

# #

9

# Several parameters have to be set before this file is sourced. #

10

# Please refer to the README. #

11

# #

12

# The README for the partitioning testcases is at the end of this file. #

13

# #

14

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

15

# Original Author: mleich #

16

# Original Date: 2006-03-05 #

17

# Change Author: mleich #

18

# Change Date: 2007-10-08 #

19

# Change: Minor cleanup and fix for #

20

# Bug#31243 Test "partition_basic_myisam" truncates path names#

21

# - Blow column file_list up to VARBINARY(10000) #

22

# - remove reference to fixed bugs #17455, #19305 #

23

################################################################################

24

25

# Set the session storage engine

26

eval SET @@session.storage_engine = $engine;

27

28

##### Disabled/affected testcases, because of open bugs #####

29

# --echo

30

# --echo #------------------------------------------------------------------------

31

# --echo # There are several testcases disabled because of the open bugs

32

# if (`SELECT @@session.storage_engine IN('ndbcluster')`)

33

# {

34

# --echo # #18730

35

# }

36

# --echo #------------------------------------------------------------------------

37

# # Attention: Only bugs appearing in all storage engines should be mentioned above.

38

# # The top level test wrapper (example: t/partition_basic_ndb.test)

39

# # may set the $fixed_bug<nnnnn> variable to 0 after sourcing

40

# # this file.

41

# # Bug#18730: Partitions: NDB, crash on SELECT MIN(<unique column>)

42

# # Attention: NDB testcases set this variable later to 0

43

# let $fixed_bug18730= 1;

44

45

--echo

46

--echo #------------------------------------------------------------------------

47

--echo # 0. Setting of auxiliary variables + Creation of an auxiliary tables

48

--echo # needed in many testcases

49

--echo #------------------------------------------------------------------------

50

# Set the variable $no_debug depending on the current value of $debug;

51

--disable_query_log

52

eval SET @aux = $debug;

53

let $no_debug= `SELECT @aux = 0`;

54

--enable_query_log

55

if ($debug)

56

{

57

--echo # Attention: Script debugging is swiched on.

58

--echo # - all statements will be protocolled

59

--echo # - some additional will be executed

60

--echo # It is to be expected, that we get huge differences.

61

}

62

63

let $ER_DUP_KEY= 1022;

64

let $ER_GET_ERRNO= 1030;

65

let $ER_BAD_NULL_ERROR= 1048;

66

let $ER_DUP_ENTRY= 1062;

67

let $ER_PARSE_ERROR= 1064;

68

let $ER_TOO_MANY_PARTITIONS_ERROR= 1499;

69

let $ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF= 1503;

70

let $ER_NO_PARTS_ERROR= 1504;

71

let $ER_DROP_PARTITION_NON_EXISTENT= 1507;

72

let $ER_SAME_NAME_PARTITION= 1517;

73

let $ER_NO_PARTITION_FOR_GIVEN_VALUE= 1526;

74

75

# Set the variable $engine_other to a storage engine <> $engine

76

--disable_query_log

77

eval SELECT UPPER($engine) = 'MEMORY' INTO @aux;

78

let $aux= `SELECT @aux`;

79

if ($aux)

80

{

81

let $engine_other= 'MyISAM';

82

}

83

if (!$aux)

84

{

85

let $engine_other= 'MEMORY';

86

}

87

--enable_query_log

88

89

# Numbers used for

90

# - partitioning Example: ... PARTITION part1 VALUES LESS THAN ($max_row_div2)

91

# - INSERT/SELECT/UPDATE/DELETE Example: ... WHERE f_int1 > @max_row_div3

92

let $max_row= `SELECT @max_row`;

93

SELECT @max_row DIV 2 INTO @max_row_div2;

94

let $max_row_div2= `SELECT @max_row_div2`;

95

SELECT @max_row DIV 3 INTO @max_row_div3;

96

let $max_row_div3= `SELECT @max_row_div3`;

97

SELECT @max_row DIV 4 INTO @max_row_div4;

98

let $max_row_div4= `SELECT @max_row_div4`;

99

SET @max_int_4 = 2147483647;

100

let $max_int_4= `SELECT @max_int_4`;

101

102

# Three insert statements used in many testcases.

103

let $insert_first_half= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)

104

SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template

105

WHERE f_int1 BETWEEN 1 AND @max_row_div2 - 1;

106

let $insert_second_half= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)

107

SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template

108

WHERE f_int1 BETWEEN @max_row_div2 AND @max_row;

109

#

110

let $insert_first_third= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)

111

SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template

112

WHERE f_int1 BETWEEN 1 AND @max_row_div3 - 1;

113

let $insert_second_third= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)

114

SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template

115

WHERE f_int1 BETWEEN @max_row_div3 AND 2 * @max_row_div3 - 1;

116

let $insert_third_third= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)

117

SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template

118

WHERE f_int1 BETWEEN 2 * @max_row_div3 AND @max_row;

119

#

120

let $insert_all= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)

121

SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;

122

123

# Column list with definition for all tables to be checked

124

let $column_list= f_int1 INTEGER,

125

f_int2 INTEGER,

126

f_char1 CHAR(20),

127

f_char2 CHAR(20),

128

f_charbig VARCHAR(1000);

129

130

# Currently (April 2006) the default compiled NDB cannot manage

131

# no_of_partitions (no subpartitioning) > 8

132

# no_of_partitions * no_of_subpartitions > 8

133

# This NDB specific limitation will cause

134

# 1005: Can't create table 'test.t1' (errno: 1224)

135

# in partition_methods[1|2].inc and partition_alter_1[1|3].inc

136

# when $sub_part_no is set to >= 3.

137

let $sub_part_no= 3;

138

if (`SELECT @@session.storage_engine = 'ndbcluster'`)

139

{

140

let $sub_part_no= 2;

141

}

142

143

# Auxiliary table used for many experiments (INSERT INTO t1 ... SELECT ...)

144

# on the tables to be checked

145

--disable_warnings

146

DROP TABLE IF EXISTS t0_template;

147

--enable_warnings

148

eval CREATE TABLE t0_template (

149

$column_list ,

150

PRIMARY KEY(f_int1))

151

ENGINE = MEMORY;

152

--echo # Logging of <max_row> INSERTs into t0_template suppressed

153

--disable_query_log

154

let $num= `SELECT @max_row`;

155

while ($num)

156

{

157

eval INSERT INTO t0_template

158

SET f_int1 = $num, f_int2 = $num, f_char1 = '$num', f_char2 = '$num',

159

f_charbig = '===$num===';

160

161

dec $num;

162

}

163

--enable_query_log

164

165

# Auxiliary table used for comparisons of table definitions and file lists

166

--disable_warnings

167

DROP TABLE IF EXISTS t0_definition;

168

--enable_warnings

169

CREATE TABLE t0_definition (

170

state CHAR(3),

171

create_command VARBINARY(5000),

172

file_list VARBINARY(10000),

173

PRIMARY KEY (state)

174

) ENGINE = MEMORY;

175

176

# Auxiliary table used for trigger experiments

177

--disable_warnings

178

DROP TABLE IF EXISTS t0_aux;

179

--enable_warnings

180

eval CREATE TABLE t0_aux ( $column_list )

181

ENGINE = MEMORY;

182

183

# Prevent that a change of defaults breaks the tests.

184

SET AUTOCOMMIT= 1;

185

SET @@session.sql_mode= '';

186

187

--echo # End of basic preparations needed for all tests

188

--echo #-----------------------------------------------

189

190

if (0)

191

{

192

# README for the partioning tests (t/partition_<feature>_<engine>.test)

193

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

194

#

195

# 1. Explanation of the variables to be assigned in the top-level storage engine

196

# specific scripts

197

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

198

#

199

# Options, for mostly test(script+logic+result) debugging support:

200

# $debug= 0 (default)

201

# --> The protocolling of auxiliary stuff is suppressed.

202

# The file with expected results fits to this setting.

203

# $debug= 1

204

# --> All executed statements will be printed into the protocol.

205

# That means statements which

206

# - are most time of low interest and do auxiliary stuff

207

# like generating the next SQL statement to be executed

208

# - additional statements giving informations about table

209

# contents or the value of some variables

210

# You will get huge differences, because the file with the

211

# expected results was created with $debug = 0 .

212

#

213

# $with_partitioning= 1 (default)

214

# --> Do the test with really partitioned tables.

215

# $with_partitioning= 0

216

# --> Do not use partitioned tables. This means omit the

217

# "PARTITION BY ... SUBPARTITION BY ..." part of the CREATE TABLE

218

# statement. This setting has only an effect on tests where

219

# partition_methods1.inc and/or partition_methods2.inc are sourced.

220

#

221

# You will get differences when the CREATE TABLE statements

222

# and table related files are printed or testcases check

223

# partition borders, but most server responses and result

224

# sets should be usable as reference for the test with the

225

# partioned tables.

226

# Please make a run with $with_partitioning= 0, whenever

227

# - you do not trust the scripts (routines checking server codes/

228

# result sets)

229

# - fear that there is a new bug affecting partitioned and non

230

# partitioned tables

231

#

232

#

233

# Execute the test of "table" files

234

# $do_file_tests= 1 (default for

235

# - all storage engines within the extended QA test

236

# - only MyISAM within the main regression tests)

237

# --> Collect the file list and compare the file list before and after

238

# OPTIMIZE/REPAIR/TRUNCATE

239

# $do_file_tests= 0 (default for non MyISAM storage engines within the

240

# main regression tests)

241

# --> Do not collect the file list.

242

# Only MyISAM has files per PARTITION/SUBPARTITION, PRIMARY KEY, INDEX, ..

243

# There is a low probability that this tests detects bugs when used in

244

# connection with other storage engines.

245

#

246

# Option, for displaying files:

247

# $ls= 1 (default)

248

# --> Display the table related directory content via

249

# "ls $MYSQLTEST_VARDIR/master-data/test/t1*"

250

# if these informations were collected.

251

# This is probably not portable to some OS.

252

# $ls= 0

253

# --> Omit displaying the directory

254

#

255

#

256

# Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments

257

# on partitioned tables:

258

# @max_row is the number of rows which will be filled into the auxiliary

259

# MEMORY table t0_template. This table is used for INSERT ... SELECT

260

# experiments. The typical test table t1 contains most of the time

261

# about @max_row DIV 2 rows.

262

# Too small values of @max_row should be avoided, because some statements

263

# should affect several rows and partitions.

264

# Too big values of @max_row should be avoided, because of runtime issues.

265

# @max_row= 20 (default for the main regression tests)

266

# The file with expected results fits to this amount of rows.

267

# @max_row= 300 (default for extended QA test)

268

# --> Use <number rows>.

269

# There should be only a few systematic differences to the file

270

# with expected results, because most SQL statements use @max_row and

271

# variables like max_row_div2 instead of a constant with the actual

272

# number of rows.

273

# I assume a value of 300 rows should be

274

#

275

#

276

# Perform the variant with extended tests:

277

# $more_trigger_tests, $more_pk_ui_tests(PK=PRIMARY KEY,UI=UNIQUE INDEX),

278

# =0 (default for the main regression tests)

279

# - There is a very low probability, that the omitted tests reveal a

280

# bug which cannot be detected with the other tests.

281

# - Limiting the partitioning tests solves issues with runtime and

282

# protocol size.

283

# =1 (default for extended QA test)

284

#

285

#

286

# Perform PRIMARY KEY specific tests:

287

# $do_pk_tests= 0;

288

# --> Do not execute the PRIMARY KEY related tests.

289

# $do_pk_tests= 1 (default for extended QA test)

290

# --> Execute the PRIMARY KEY related tests.

291

# The default setting for the main regression tests depends on the

292

# storage engine. The PRIMARY KEY tests must be executed for every storage

293

# engine, where the existence of a PRIMARY KEY affects the kind how the

294

# table rows are stored.

295

# Examples for the main rgression tests:

296

# InnoDB - The PRIMARY KEY is a clustered index where the data for the

297

# rows are stored. $do_pk_tests= 1

298

# NDB - The PRIMARY KEY is used for implicit partitioning (NDB).

299

# $do_pk_tests= 1

300

# MyISAM - AFAIK there is no effect on the tree containing the rows.

301

# $do_pk_tests= 0

302

#

303

# Assign a big number smaller than the maximum value for partitions

304

# and smaller than the maximum value of SIGNED INTEGER

305

# The NDB handler only supports 32 bit integers in VALUES

306

# 2147483647 seems to be too big.

307

# $MAX_VALUE= (2147483646);

308

#

309

#

310

# 2. Typical architecture of a test:

311

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

312

# 2.1. storage engine specific script on top level

313

# (t/partition_<feature>_<engine>.test)

314

# a) General not engine specific settings and requirements

315

# $debug, $ls, @max_row, $more_trigger_tests, .....

316

# --source inc/have_partition.inc

317

# b) Engine specific settings and requirements

318

# $do_pk_tests, $MAX_VALUE, $engine

319

# SET SESSION storage_engine

320

# $engine_other

321

# c) Generate the prerequisites ($variables, @variables, tables) needed

322

# via

323

# --source inc/partition.pre

324

# d) Set "fixed_bug<number>" variables to 1 if there are open engine

325

# specific bugs which need worarounds.

326

# e) Execute the feature specific testscript via

327

# --source inc/partition_<feature>.inc

328

# f) Perform a cleanup by removing all objects created within the tests

329

# --source inc/partition_cleanup.inc

330

#

331

# 2.2. script generating the prerequisites needed in all tests

332

# (inc/partition.pre)

333

# a) Message about open bugs causing that

334

# - some testcases are disabled

335

# - it cannot be avoided that the file with expected results suffers

336

# from open bugs

337

# This should not occur often !

338

# Example: There is extreme often an auxiliary testscript sourced,

339

# but the the conditions vary. We get under a certain combination

340

# of conditions a wrong result set or server response.

341

# b) Set "fixed_bug<number>" variables to 0 if there are open engine

342

# specific bugs. They are later set to 1 within the toplevel script.

343

# Set "fixed_bug<number>" variables to 1 if there are open NOT engine

344

# specific bugs.

345

# c) Setting of auxiliary variables

346

# d) Creation of auxiliary tables ....

347

#

348

# 2.3. script checking a feature

349

# (inc/partition_<feature.inc>.inc)

350

# Example:

351

# a) "set/compute" a CREATE TABLE t1 .. and an ALTER TABLE ... statement

352

# b) CREATE TABLE t1 ...

353

# c) INSERT INTO t1 (.....) SELECT .... FROM t0_template WHERE ...

354

# The first 50 % of all t0_template rows will be inserted into t1.

355

# d) ALTER TABLE t1 (Example: ADD/DROP UNIQUE INDEX)

356

# e) INSERT INTO t1 (.....) SELECT .... FROM t0_template WHERE ...

357

# The second 50 % of all t0_template rows will be inserted into t1.

358

# Now t1 and t0_template should have the same content.

359

# f) Check the "usability" of the current table t1

360

# via

361

# --source inc/partition_check.pre

362

# g) DROP TABLE t1

363

# Switch to other CREATE and ALTER statements and run sequence a)-g) again

364

# ...

365

#

366

# 2.4. script checking if a certain table shows the expected behaviour

367

# ("usability" check): inc/partition_check.inc

368

# - SELECT/INSERT/UPDATE/DELETE affecting single and multiple records

369

# - check of values of special interest like NULL etc.

370

# - INSERT/UPDATE with BEFORE/AFTER triggers

371

# - violations of UNIQUE constraints, if there are any defined

372

# - transactions ...

373

# - TRUNCATE/OPTIMIZE/..

374

# - ...

375

#

376

#

377

# 2.5. There are some auxiliary scripts with sub tests where we cannot predict

378

# if we get an error and if we get one, which one.

379

# Example: INSERT a record where the value for a certain column equals

380

# some existing record.

381

# Depending on existing/not existing PRIMARY KEYs, UNIQUE INDEXes

382

# the response might be "no error", ER_DUP_KEY, ER_DUP_ENTRY.

383

# Our requirements:

384

# 1. We cannot abort whenever get an error message from the server.

385

# 2. We want the exact server message into the protocol.

386

# 3. We want abort testing if we know that a certain error must not happen.

387

# Common but unusable Solutions:

388

# a) --error 0, ER_DUP_KEY, ER_DUP_ENTRY

389

# <statment>

390

# We get no error message even if the statement fails.

391

# b) --error ER_DUP_KEY, ER_DUP_ENTRY

392

# <statment>

393

# We might get "got one of the expected errors".

394

# There are situations where the statement must be successful.

395

# c) --disable_abort_on_error

396

# <statment>

397

# --enable_abort_on_error

398

# And nothing extra

399

# We do not abort in case of unexpected server errors.

400

#

401

# Final solution:

402

# --disable_abort_on_error

403

# <statment>

404

# --enable_abort_on_error

405

# Check via error number if the error is not totally unexpected.

406

# The sub tests use $ER_DUP_KEY, $ER_DUP_ENTRY, etc.

407

# Assignment of values happen in this file.

408

#

409

#

410

# 3. How to analyze a partitioning bug revealed with these tests/ How to build

411

# a small replay script from the monstrous protocols ?

412

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

413

# a) crash -- use the file var/master-data/mysql/general_log.CSV

414

# b) no crash, but unexpected server response (there is no "reject file)

415

# -- use the file r/<testcase>.log

416

# Please be aware that the option $debug= 0 suppresses the

417

# protocolling of some queries.

418

# c) no crash, but unexpected result set

419

# -- use the file r/<testcase>.reject

420

# Please be aware that the option $debug= 0 suppresses the

421

# protocolling of some queries.

422

# In most cases you will find that the r/<testcase>.<log/reject> contains at

423

# least a line "# # check <something>: 0".

424

# That means that a check within inc/partition_check did not got the

425

# expected result.

426

# A good start for a replay script would be

427

# 1. Copy t/partition_<feature>_<engine>.test to t/my_test.test

428

# 2. Edit t/my_test.test

429

# - set $debug to 1

430

# - replace the line

431

# "--source inc/partition_<feature>.inc"

432

# with all statements between the last

433

# CREATE TABLE t1 statement (included this)

434

# and the line

435

# "# Start usability test (inc/partition_check.inc)"

436

# - add the content of inc/partition_check.inc at the end.

437

#

438

# Please excuse that the partitioning tests generate such huge protocols which

439

# and are not very handy when it comes to bug analysis. I tried to squeez out

440

# as much test coverage as possible by writing some hopefully smart routines

441

# and reusing them in various combinations.

442

#

443

# Matthias

444

#

445

}