| blob | 7c3b0d9d1ef6b8e7560f79a2322b6438b964fd18 |
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 ################################################################################
25 # Set the session storage engine
26 eval SET @@session.storage_engine = $engine;
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;
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 }
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
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
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`;
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;
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);
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 }
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===';
161 dec $num;
162 }
163 --enable_query_log
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;
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;
183 # Prevent that a change of defaults breaks the tests.
184 SET AUTOCOMMIT= 1;
185 SET @@session.sql_mode= '';
187 --echo # End of basic preparations needed for all tests
188 --echo #-----------------------------------------------
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/mysqld.1/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/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
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 }