Debian Codegraph @

mysql-test/suite/parts/inc/partition.pre

raw
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.default_storage_engine = $engine;
27
28 --echo
29 --echo #------------------------------------------------------------------------
30 --echo # 0. Setting of auxiliary variables + Creation of an auxiliary tables
31 --echo # needed in many testcases
32 --echo #------------------------------------------------------------------------
33 # Set the variable $no_debug depending on the current value of $debug;
34 --disable_query_log
35 eval SET @aux = $debug;
36 let $no_debug= `SELECT @aux = 0`;
37 --enable_query_log
38 if ($debug)
39 {
40 --echo # Attention: Script debugging is swiched on.
41 --echo # - all statements will be protocolled
42 --echo # - some additional will be executed
43 --echo # It is to be expected, that we get huge differences.
44 }
45
46 let $ER_DUP_KEY= 1022;
47 let $ER_GET_ERRNO= 1030;
48 let $ER_BAD_NULL_ERROR= 1048;
49 let $ER_DUP_ENTRY= 1062;
50 let $ER_PARSE_ERROR= 1064;
51 let $ER_TOO_MANY_PARTITIONS_ERROR= 1499;
52 let $ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF= 1503;
53 let $ER_NO_PARTS_ERROR= 1504;
54 let $ER_PARTITION_DOES_NOT_EXIST= 1507;
55 let $ER_SAME_NAME_PARTITION= 1517;
56 let $ER_NO_PARTITION_FOR_GIVEN_VALUE= 1526;
57
58 # Set the variable $engine_other to a storage engine <> $engine
59 --disable_query_log
60 --disable_cursor_protocol
61 eval SELECT UPPER($engine) = 'MEMORY' INTO @aux;
62 --enable_cursor_protocol
63 let $aux= `SELECT @aux`;
64 if ($aux)
65 {
66 let $engine_other= 'MyISAM';
67 }
68 if (!$aux)
69 {
70 let $engine_other= 'MEMORY';
71 }
72 --enable_query_log
73
74 --disable_cursor_protocol
75 # Numbers used for
76 # - partitioning Example: ... PARTITION part1 VALUES LESS THAN ($max_row_div2)
77 # - INSERT/SELECT/UPDATE/DELETE Example: ... WHERE f_int1 > @max_row_div3
78 let $max_row= `SELECT @max_row`;
79 SELECT @max_row DIV 2 INTO @max_row_div2;
80 let $max_row_div2= `SELECT @max_row_div2`;
81 SELECT @max_row DIV 3 INTO @max_row_div3;
82 let $max_row_div3= `SELECT @max_row_div3`;
83 SELECT @max_row DIV 4 INTO @max_row_div4;
84 let $max_row_div4= `SELECT @max_row_div4`;
85 SET @max_int_4 = 2147483647;
86 let $max_int_4= `SELECT @max_int_4`;
87
88 # Three insert statements used in many testcases.
89 let $insert_first_half= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
90 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
91 WHERE f_int1 BETWEEN 1 AND @max_row_div2 - 1;
92 let $insert_second_half= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
93 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
94 WHERE f_int1 BETWEEN @max_row_div2 AND @max_row;
95 #
96 let $insert_first_third= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
97 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
98 WHERE f_int1 BETWEEN 1 AND @max_row_div3 - 1;
99 let $insert_second_third= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
100 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
101 WHERE f_int1 BETWEEN @max_row_div3 AND 2 * @max_row_div3 - 1;
102 let $insert_third_third= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
103 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template
104 WHERE f_int1 BETWEEN 2 * @max_row_div3 AND @max_row;
105 #
106 let $insert_all= 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 --enable_cursor_protocol
109
110 # Column list with definition for all tables to be checked
111 let $column_list= f_int1 INTEGER DEFAULT 0,
112 f_int2 INTEGER DEFAULT 0,
113 f_char1 CHAR(20),
114 f_char2 CHAR(20),
115 f_charbig VARCHAR(1000);
116
117 let $sub_part_no= 3;
118
119 # Auxiliary table used for many experiments (INSERT INTO t1 ... SELECT ...)
120 # on the tables to be checked
121 --disable_warnings
122 DROP TABLE IF EXISTS t0_template;
123 --enable_warnings
124 eval CREATE TABLE t0_template (
125 $column_list ,
126 PRIMARY KEY(f_int1))
127 ENGINE = MEMORY;
128 --echo # Logging of <max_row> INSERTs into t0_template suppressed
129 --disable_query_log
130 let $num= `SELECT @max_row`;
131 begin;
132 while ($num)
133 {
134 eval INSERT INTO t0_template
135 SET f_int1 = $num, f_int2 = $num, f_char1 = '$num', f_char2 = '$num',
136 f_charbig = '===$num===';
137
138 dec $num;
139 }
140 commit;
141 --enable_query_log
142
143 # Auxiliary table used for comparisons of table definitions and file lists
144 --disable_warnings
145 DROP TABLE IF EXISTS t0_definition;
146 --enable_warnings
147 CREATE TABLE t0_definition (
148 state CHAR(3),
149 create_command VARBINARY(5000),
150 file_list VARBINARY(10000),
151 PRIMARY KEY (state)
152 ) ENGINE = MEMORY;
153
154 # Auxiliary table used for trigger experiments
155 --disable_warnings
156 DROP TABLE IF EXISTS t0_aux;
157 --enable_warnings
158 eval CREATE TABLE t0_aux ( $column_list )
159 ENGINE = MEMORY;
160
161 # Prevent that a change of defaults breaks the tests.
162 SET AUTOCOMMIT= 1;
163 SET @@session.sql_mode= '';
164
165 --echo # End of basic preparations needed for all tests
166 --echo #-----------------------------------------------
167
168 if (0)
169 {
170 # README for the partioning tests (t/partition_<feature>_<engine>.test)
171 # ========================================================================
172 #
173 # 1. Explanation of the variables to be assigned in the top-level storage engine
174 # specific scripts
175 #------------------------------------------------------------------------------#
176 #
177 # Options, for mostly test(script+logic+result) debugging support:
178 # $debug= 0 (default)
179 # --> The protocolling of auxiliary stuff is suppressed.
180 # The file with expected results fits to this setting.
181 # $debug= 1
182 # --> All executed statements will be printed into the protocol.
183 # That means statements which
184 # - are most time of low interest and do auxiliary stuff
185 # like generating the next SQL statement to be executed
186 # - additional statements giving information about table
187 # contents or the value of some variables
188 # You will get huge differences, because the file with the
189 # expected results was created with $debug = 0 .
190 #
191 # $with_partitioning= 1 (default)
192 # --> Do the test with really partitioned tables.
193 # $with_partitioning= 0
194 # --> Do not use partitioned tables. This means omit the
195 # "PARTITION BY ... SUBPARTITION BY ..." part of the CREATE TABLE
196 # statement. This setting has only an effect on tests where
197 # partition_methods1.inc and/or partition_methods2.inc are sourced.
198 #
199 # You will get differences when the CREATE TABLE statements
200 # and table related files are printed or testcases check
201 # partition borders, but most server responses and result
202 # sets should be usable as reference for the test with the
203 # partioned tables.
204 # Please make a run with $with_partitioning= 0, whenever
205 # - you do not trust the scripts (routines checking server codes/
206 # result sets)
207 # - fear that there is a new bug affecting partitioned and non
208 # partitioned tables
209 #
210 #
211 # Execute the test of "table" files
212 # $do_file_tests= 1 (default for
213 # - all storage engines within the extended QA test
214 # - only MyISAM within the main regression tests)
215 # --> Collect the file list and compare the file list before and after
216 # OPTIMIZE/REPAIR/TRUNCATE
217 # $do_file_tests= 0 (default for non MyISAM storage engines within the
218 # main regression tests)
219 # --> Do not collect the file list.
220 # Only MyISAM has files per PARTITION/SUBPARTITION, PRIMARY KEY, INDEX, ..
221 # There is a low probability that this tests detects bugs when used in
222 # connection with other storage engines.
223 #
224 # Option, for displaying files:
225 # $ls= 1 (default)
226 # --> Display the table related directory content via
227 # "ls $MYSQLTEST_VARDIR/mysqld.1/data/test/t1*"
228 # if these information were collected.
229 # This is probably not portable to some OS.
230 # $ls= 0
231 # --> Omit displaying the directory
232 #
233 #
234 # Number of rows for the INSERT/UPDATE/DELETE/SELECT experiments
235 # on partitioned tables:
236 # @max_row is the number of rows which will be filled into the auxiliary
237 # MEMORY table t0_template. This table is used for INSERT ... SELECT
238 # experiments. The typical test table t1 contains most of the time
239 # about @max_row DIV 2 rows.
240 # Too small values of @max_row should be avoided, because some statements
241 # should affect several rows and partitions.
242 # Too big values of @max_row should be avoided, because of runtime issues.
243 # @max_row= 20 (default for the main regression tests)
244 # The file with expected results fits to this amount of rows.
245 # @max_row= 300 (default for extended QA test)
246 # --> Use <number rows>.
247 # There should be only a few systematic differences to the file
248 # with expected results, because most SQL statements use @max_row and
249 # variables like max_row_div2 instead of a constant with the actual
250 # number of rows.
251 # I assume a value of 300 rows should be
252 #
253 #
254 # Perform the variant with extended tests:
255 # $more_trigger_tests, $more_pk_ui_tests(PK=PRIMARY KEY,UI=UNIQUE INDEX),
256 # =0 (default for the main regression tests)
257 # - There is a very low probability, that the omitted tests reveal a
258 # bug which cannot be detected with the other tests.
259 # - Limiting the partitioning tests solves issues with runtime and
260 # protocol size.
261 # =1 (default for extended QA test)
262 #
263 #
264 # Perform PRIMARY KEY specific tests:
265 # $do_pk_tests= 0;
266 # --> Do not execute the PRIMARY KEY related tests.
267 # $do_pk_tests= 1 (default for extended QA test)
268 # --> Execute the PRIMARY KEY related tests.
269 # The default setting for the main regression tests depends on the
270 # storage engine. The PRIMARY KEY tests must be executed for every storage
271 # engine, where the existence of a PRIMARY KEY affects the kind how the
272 # table rows are stored.
273 # Examples for the main rgression tests:
274 # InnoDB - The PRIMARY KEY is a clustered index where the data for the
275 # rows are stored. $do_pk_tests= 1
276 # MyISAM - AFAIK there is no effect on the tree containing the rows.
277 # $do_pk_tests= 0
278 #
279 # Assign a big number smaller than the maximum value for partitions
280 # and smaller than the maximum value of SIGNED INTEGER
281 #
282 #
283 # 2. Typical architecture of a test:
284 #------------------------------------------------------------------------------#
285 # 2.1. storage engine specific script on top level
286 # (t/partition_<feature>_<engine>.test)
287 # a) General not engine specific settings and requirements
288 # $debug, $ls, @max_row, $more_trigger_tests, .....
289 # --source inc/have_partition.inc
290 # b) Engine specific settings and requirements
291 # $do_pk_tests, $MAX_VALUE, $engine
292 # SET SESSION default_storage_engine
293 # $engine_other
294 # c) Generate the prerequisites ($variables, @variables, tables) needed
295 # via
296 # --source inc/partition.pre
297 # d) Set "fixed_bug<number>" variables to 1 if there are open engine
298 # specific bugs which need worarounds.
299 # e) Execute the feature specific testscript via
300 # --source inc/partition_<feature>.inc
301 # f) Perform a cleanup by removing all objects created within the tests
302 # --source inc/partition_cleanup.inc
303 #
304 # 2.2. script generating the prerequisites needed in all tests
305 # (inc/partition.pre)
306 # a) Message about open bugs causing that
307 # - some testcases are disabled
308 # - it cannot be avoided that the file with expected results suffers
309 # from open bugs
310 # This should not occur often !
311 # Example: There is extreme often an auxiliary testscript sourced,
312 # but the the conditions vary. We get under a certain combination
313 # of conditions a wrong result set or server response.
314 # b) Set "fixed_bug<number>" variables to 0 if there are open engine
315 # specific bugs. They are later set to 1 within the toplevel script.
316 # Set "fixed_bug<number>" variables to 1 if there are open NOT engine
317 # specific bugs.
318 # c) Setting of auxiliary variables
319 # d) Creation of auxiliary tables ....
320 #
321 # 2.3. script checking a feature
322 # (inc/partition_<feature.inc>.inc)
323 # Example:
324 # a) "set/compute" a CREATE TABLE t1 .. and an ALTER TABLE ... statement
325 # b) CREATE TABLE t1 ...
326 # c) INSERT INTO t1 (.....) SELECT .... FROM t0_template WHERE ...
327 # The first 50 % of all t0_template rows will be inserted into t1.
328 # d) ALTER TABLE t1 (Example: ADD/DROP UNIQUE INDEX)
329 # e) INSERT INTO t1 (.....) SELECT .... FROM t0_template WHERE ...
330 # The second 50 % of all t0_template rows will be inserted into t1.
331 # Now t1 and t0_template should have the same content.
332 # f) Check the "usability" of the current table t1
333 # via
334 # --source inc/partition_check.pre
335 # g) DROP TABLE t1
336 # Switch to other CREATE and ALTER statements and run sequence a)-g) again
337 # ...
338 #
339 # 2.4. script checking if a certain table shows the expected behaviour
340 # ("usability" check): inc/partition_check.inc
341 # - SELECT/INSERT/UPDATE/DELETE affecting single and multiple records
342 # - check of values of special interest like NULL etc.
343 # - INSERT/UPDATE with BEFORE/AFTER triggers
344 # - violations of UNIQUE constraints, if there are any defined
345 # - transactions ...
346 # - TRUNCATE/OPTIMIZE/..
347 # - ...
348 #
349 #
350 # 2.5. There are some auxiliary scripts with sub tests where we cannot predict
351 # if we get an error and if we get one, which one.
352 # Example: INSERT a record where the value for a certain column equals
353 # some existing record.
354 # Depending on existing/not existing PRIMARY KEYs, UNIQUE INDEXes
355 # the response might be "no error", ER_DUP_KEY, ER_DUP_ENTRY.
356 # Our requirements:
357 # 1. We cannot abort whenever get an error message from the server.
358 # 2. We want the exact server message into the protocol.
359 # 3. We want abort testing if we know that a certain error must not happen.
360 # Common but unusable Solutions:
361 # a) --error 0, ER_DUP_KEY, ER_DUP_ENTRY
362 # <statment>
363 # We get no error message even if the statement fails.
364 # b) --error ER_DUP_KEY, ER_DUP_ENTRY
365 # <statment>
366 # We might get "got one of the expected errors".
367 # There are situations where the statement must be successful.
368 # c) --disable_abort_on_error
369 # <statment>
370 # --enable_abort_on_error
371 # And nothing extra
372 # We do not abort in case of unexpected server errors.
373 #
374 # Final solution:
375 # --disable_abort_on_error
376 # <statment>
377 # --enable_abort_on_error
378 # Check via error number if the error is not totally unexpected.
379 # The sub tests use $ER_DUP_KEY, $ER_DUP_ENTRY, etc.
380 # Assignment of values happen in this file.
381 #
382 #
383 # 3. How to analyze a partitioning bug revealed with these tests/ How to build
384 # a small replay script from the monstrous protocols ?
385 #------------------------------------------------------------------------------#
386 # a) crash -- use the file var/mysqld.1/data/mysql/general_log.CSV
387 # b) no crash, but unexpected server response (there is no "reject file)
388 # -- use the file r/<testcase>.log
389 # Please be aware that the option $debug= 0 suppresses the
390 # protocolling of some queries.
391 # c) no crash, but unexpected result set
392 # -- use the file r/<testcase>.reject
393 # Please be aware that the option $debug= 0 suppresses the
394 # protocolling of some queries.
395 # In most cases you will find that the r/<testcase>.<log/reject> contains at
396 # least a line "# # check <something>: 0".
397 # That means that a check within inc/partition_check did not got the
398 # expected result.
399 # A good start for a replay script would be
400 # 1. Copy t/partition_<feature>_<engine>.test to t/my_test.test
401 # 2. Edit t/my_test.test
402 # - set $debug to 1
403 # - replace the line
404 # "--source inc/partition_<feature>.inc"
405 # with all statements between the last
406 # CREATE TABLE t1 statement (included this)
407 # and the line
408 # "# Start usability test (inc/partition_check.inc)"
409 # - add the content of inc/partition_check.inc at the end.
410 #
411 # Please excuse that the partitioning tests generate such huge protocols which
412 # and are not very handy when it comes to bug analysis. I tried to squeez out
413 # as much test coverage as possible by writing some hopefully smart routines
414 # and reusing them in various combinations.
415 #
416 # Matthias
417 #
418 }