1
################################################################################
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 #
9
# Several parameters have to be set before this file is sourced. #
10
# Please refer to the README. #
12
# The README for the partitioning testcases is at the end of this file. #
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
################################################################################
25
# Set the session storage engine
26
eval SET @@session.storage_engine = $engine;
28
##### Disabled/affected testcases, because of open bugs #####
30
# --echo #------------------------------------------------------------------------
31
# --echo # There are several testcases disabled because of the open bugs
32
# if (`SELECT @@session.storage_engine IN('ndbcluster')`)
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
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;
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;
52
eval SET @aux = $debug;
53
let $no_debug= `SELECT @aux = 0`;
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.
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;
75
# Set the variable $engine_other to a storage engine <> $engine
77
eval SELECT UPPER($engine) = 'MEMORY' INTO @aux;
78
let $aux= `SELECT @aux`;
81
let $engine_other= 'MyISAM';
85
let $engine_other= 'MEMORY';
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`;
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;
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;
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;
123
# Column list with definition for all tables to be checked
124
let $column_list= f_int1 INTEGER,
128
f_charbig VARCHAR(1000);
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.
138
if (`SELECT @@session.storage_engine = 'ndbcluster'`)
143
# Auxiliary table used for many experiments (INSERT INTO t1 ... SELECT ...)
144
# on the tables to be checked
146
DROP TABLE IF EXISTS t0_template;
148
eval CREATE TABLE t0_template (
152
--echo # Logging of <max_row> INSERTs into t0_template suppressed
154
let $num= `SELECT @max_row`;
157
eval INSERT INTO t0_template
158
SET f_int1 = $num, f_int2 = $num, f_char1 = '$num', f_char2 = '$num',
159
f_charbig = '===$num===';
165
# Auxiliary table used for comparisons of table definitions and file lists
167
DROP TABLE IF EXISTS t0_definition;
169
CREATE TABLE t0_definition (
171
create_command VARBINARY(5000),
172
file_list VARBINARY(10000),
176
# Auxiliary table used for trigger experiments
178
DROP TABLE IF EXISTS t0_aux;
180
eval CREATE TABLE t0_aux ( $column_list )
183
# Prevent that a change of defaults breaks the tests.
185
SET @@session.sql_mode= '';
187
--echo # End of basic preparations needed for all tests
188
--echo #-----------------------------------------------
192
# README for the partioning tests (t/partition_<feature>_<engine>.test)
193
# ========================================================================
195
# 1. Explanation of the variables to be assigned in the top-level storage engine
197
#------------------------------------------------------------------------------#
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.
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 .
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.
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
226
# Please make a run with $with_partitioning= 0, whenever
227
# - you do not trust the scripts (routines checking server codes/
229
# - fear that there is a new bug affecting partitioned and non
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.
246
# Option, for displaying files:
248
# --> Display the table related directory content via
249
# "ls $MYSQLTEST_VARDIR/mysqld.1/data/test/t1*"
250
# if these informations were collected.
251
# This is probably not portable to some OS.
253
# --> Omit displaying the directory
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
273
# I assume a value of 300 rows should be
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
283
# =1 (default for extended QA test)
286
# Perform PRIMARY KEY specific tests:
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).
300
# MyISAM - AFAIK there is no effect on the tree containing the rows.
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);
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
321
# c) Generate the prerequisites ($variables, @variables, tables) needed
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
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
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
345
# c) Setting of auxiliary variables
346
# d) Creation of auxiliary tables ....
348
# 2.3. script checking a feature
349
# (inc/partition_<feature.inc>.inc)
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
361
# --source inc/partition_check.pre
363
# Switch to other CREATE and ALTER statements and run sequence a)-g) again
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
373
# - TRUNCATE/OPTIMIZE/..
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.
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
390
# We get no error message even if the statement fails.
391
# b) --error ER_DUP_KEY, ER_DUP_ENTRY
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
397
# --enable_abort_on_error
399
# We do not abort in case of unexpected server errors.
402
# --disable_abort_on_error
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.
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/mysqld.1/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
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
431
# "--source inc/partition_<feature>.inc"
432
# with all statements between the last
433
# CREATE TABLE t1 statement (included this)
435
# "# Start usability test (inc/partition_check.inc)"
436
# - add the content of inc/partition_check.inc at the end.
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.
added
removed
mysql-test/suite/parts/inc/partition.pre
