diff options
Diffstat (limited to 'mysql-test/suite/parts/inc/partition_check.inc')
| -rw-r--r-- | mysql-test/suite/parts/inc/partition_check.inc | 1198 |
1 files changed, 1198 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/inc/partition_check.inc b/mysql-test/suite/parts/inc/partition_check.inc new file mode 100644 index 00000000000..698e9611af1 --- /dev/null +++ b/mysql-test/suite/parts/inc/partition_check.inc @@ -0,0 +1,1198 @@ +################################################################################ +# include/partition_check.inc # +# # +# Purpose: # +# Do some basic usability checks on table t1. # +# This routine is only useful for the partition_<feature>_<engine> tests. # +# # +# Some Notes: # +# It is intended that in many testcases (statements) more than one partition # +# or subpartition is affected. # +# Without analysis of the partitioning function used during CREATE TABLE # +# we cannot be 100% sure that this goal is reached. # +# But statements affecting many rows give a good probability that this # +# appears. # +# # +# It is expected that the table to be checked contains at the beginning # +# of this script records following the scheme # +# f_int1 f_int2 f_char1 f_char2 f_charbig # +# 1 1 '1' '1' '###1###' # +# 2 2 '2' '1' '###2###' # +# ... ... ... ... ... # +# x x 'x' 'x' '###x###' # +# x = @max_row # +# # +# The table content must be equal to the content of the table t0_template. # +# Attention: Please be careful when modiying the data. # +# Records can be deleted or inserted, but the content of the # +# records after a test/testsequence should follow this scheme. # +# # +# All checks of preceeding statements via Select are so written, # +# that they deliver a # +# # check <n> success: 1 # +# when everything is like expected. # +# - f_charbig is typically used for showing if something was changed. # +# This is useful for cleanups. # +# # +#------------------------------------------------------------------------------# +# Original Author: ML # +# Original Date: 2006-03-05 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + + +--echo # Start usability test (include/partition_check.inc) +# Print the CREATE TABLE STATEMENT and store the current layout of the table +--source suite/parts/inc/partition_layout_check1.inc +# --source include/partition_layout_check1.inc + + +#------------------------------------------------------------------------------- +## 1. Check the prerequisites for the following tests +# (sideeffect some SELECT functionality is also tested) +# Determine if we have PRIMARY KEYs or UNIQUE INDEXes +## 1.1 Check if the content of the records is like expected +# Sideeffect: mass SELECT, all records/partitions/subpartitions have to be +# read, because at least f_charbig is not part of any +# INDEX/PRIMARY KEY +if ($no_debug) +{ +--disable_query_log +} +let $my_stmt= SELECT COUNT(*) <> 0 INTO @aux FROM t1 +WHERE f_int1 <> f_int2 OR f_char1 <> CAST(f_int1 AS CHAR) OR f_char1 <> f_char2 + OR f_charbig <> CONCAT('===',f_char1,'===') + OR f_int1 IS NULL OR f_int2 IS NULL OR f_char1 IS NULL OR f_char2 IS NULL + OR f_charbig IS NULL; +eval $my_stmt; +let $run= `SELECT @aux`; +--enable_query_log +if ($run) +{ + --echo # Prerequisites for following tests not fullfilled. + --echo # The content of the table t1 is unexpected + eval $my_stmt; + SELECT @aux; + --echo # Sorry, have to abort. + exit; +} +# Give a success message like in the other following tests +--echo # check prerequisites-1 success: 1 +#------------------------------------------------------------------------------- +## 1.2 Check if the number of records and the maximum and minimum values are +# like expected +# Sideeffect: Check +# - COUNT(*) +# - MIN/MAX on all columns possibly used in part. function +# The optimizer might decide to run on INDEX only, if available. +# +## 1.2.1 Check COUNT(*) +if ($no_debug) +{ +--disable_query_log +} +let $my_stmt= SELECT COUNT(*) <> @max_row INTO @aux FROM t1; +let $run= `SELECT @aux`; +--enable_query_log +if ($run) +{ + --echo # Prerequisites for following tests not fullfilled. + --echo # The content of the table t1 is unexpected + eval $my_stmt; + SELECT @aux; + --echo # Sorry, have to abort. + exit; +} +# Give a success message like in the other following tests +--echo # check COUNT(*) success: 1 +## 1.2.2 Check MAX(f_int1),MIN(f_int1) +if ($no_debug) +{ +--disable_query_log +} +let $my_stmt= SELECT MIN(f_int1) <> 1 AND MAX(f_int1) <> @max_row INTO @aux +FROM t1; +let $run= `SELECT @aux`; +--enable_query_log +if ($run) +{ + --echo # Prerequisites for following tests not fullfilled. + --echo # The content of the table t1 is unexpected + eval $my_stmt; + SELECT @aux; + --echo # Sorry, have to abort. + exit; +} +# Give a success message like in the other following tests +--echo # check MIN/MAX(f_int1) success: 1 +## 1.2.3 Check MAX(f_int2),MIN(f_int2) +if ($no_debug) +{ +--disable_query_log +} +let $my_stmt= SELECT MIN(f_int2) <> 1 AND MAX(f_int2) <> @max_row INTO @aux +FROM t1; +let $run= `SELECT @aux`; +--enable_query_log +if ($run) +{ + --echo # Prerequisites for following tests not fullfilled. + --echo # The content of the table t1 is unexpected + eval $my_stmt; + SELECT @aux; + --echo # Sorry, have to abort. + exit; +} +# Give a success message like in the other following tests +--echo # check MIN/MAX(f_int2) success: 1 + +#------------------------------------------------------------------------------- +## 1.3 Check, if f_int1 and/or f_char2 and/or (f_char1,f_char2) is UNIQUE +# by testing if any DUPLICATE KEY might appear +# Note: INFORMATION_SCHEMA SELECTs could be also used, but testing the +# table via INSERT and SELECT is better because is stresses the +# partitioning mechanism. +# Sideeffect: Attempt to INSERT one record +# DUPLICATE KEY will appear if we have UNIQUE columns +# 1022: Can't write; duplicate key in table 't1' UIDX/PK(f_int1) +# 1062: Duplicate entry '2' for key 1 UIDX/PK(f_int2) +--disable_abort_on_error +INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) +SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), + CAST(f_int1 AS CHAR), 'delete me' FROM t0_template +WHERE f_int1 IN (2,3); +--enable_abort_on_error +if ($no_debug) +{ +--disable_query_log +} +eval SET @my_errno = $mysql_errno; +let $run_delete= `SELECT @my_errno = 0`; +let $any_unique= `SELECT @my_errno IN (1022,1062)`; +let $unexpected_error= `SELECT @my_errno NOT IN (0,1022,1062)`; +# DEBUG eval SELECT $run_delete AS run_delete, $any_unique AS any_unique, +# $unexpected_error AS unexpected_error; +if ($unexpected_error) +{ + --echo # The last command got an unexepected error response. + --echo # Expected/handled SQL codes are 0,1022,1062 + SELECT '# SQL code we got was: ' AS "", @my_errno AS ""; + --echo # Sorry, have to abort. + exit; + --echo +} +# Give a success message like in the other following tests +--echo # check prerequisites-3 success: 1 +--enable_query_log +# DEBUG eval SELECT $run_delete AS run_delete, $any_unique AS any_unique; +if ($run_delete) +{ + # INSERT was successful -> DELETE this new record + DELETE FROM t1 WHERE f_charbig = 'delete me'; + --echo # INFO: Neither f_int1 nor f_int2 nor (f_int1,f_int2) is UNIQUE +} +if ($any_unique) +{ + --echo # INFO: f_int1 AND/OR f_int2 AND/OR (f_int1,f_int2) is UNIQUE + + ## 1.3.1 Check, if f_int1 is UNIQUE + # Sideeffect: Attempt to INSERT one record + # DUPLICATE KEY will appear if we have UNIQUE columns + # 1022: Can't write; duplicate key in table 't1' UIDX/PK + --disable_abort_on_error + INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) + SELECT f_int1, 2 * @max_row + f_int1, CAST((2 * @max_row + f_int1) AS CHAR), + CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template + WHERE f_int1 IN (2,3); + --enable_abort_on_error + if ($no_debug) + { + --disable_query_log + } + eval SET @my_errno = $mysql_errno; + let $run_delete= `SELECT @my_errno = 0`; + let $f_int1_is_unique= `SELECT @my_errno IN (1022,1062)`; + let $unexpected_error= `SELECT @my_errno NOT IN (0,1022,1062)`; + # DEBUG eval SELECT $run_delete AS run_delete, $f_int1_is_unique AS any_unique, + # $unexpected_error AS unexpected_error; + if ($unexpected_error) + { + --echo # The last command got an unexepected error response. + --echo # Expected/handled SQL codes are 0,1022,1062 + SELECT '# SQL code we got was: ' AS "", @my_errno AS ""; + --echo # Sorry, have to abort. + exit; + --echo + } + --enable_query_log + if ($f_int1_is_unique) + { + --echo # INFO: f_int1 is UNIQUE + } + if ($run_delete) + { + # INSERT was successful -> DELETE this new record + DELETE FROM t1 WHERE f_charbig = 'delete me'; + } + + ## 1.3.2 Check, if f_int2 is UNIQUE + --disable_abort_on_error + INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) + SELECT 2 * @max_row + f_int1, f_int1, CAST((2 * @max_row + f_int1) AS CHAR), + CAST((2 * @max_row + f_int1) AS CHAR), 'delete me' FROM t0_template + WHERE f_int1 IN (2,3); + --enable_abort_on_error + if ($no_debug) + { + --disable_query_log + } + eval SET @my_errno = $mysql_errno; + let $run_delete= `SELECT @my_errno = 0`; + let $f_int1_is_unique= `SELECT @my_errno IN (1022,1062)`; + let $unexpected_error= `SELECT @my_errno NOT IN (0,1022,1062)`; + # DEBUG eval SELECT $run_delete AS run_delete, $f_int1_is_unique AS any_unique, + # $unexpected_error AS unexpected_error; + if ($unexpected_error) + { + --echo # The last command got an unexepected error response. + --echo # Expected/handled SQL codes are 0,1022,1062 + SELECT '# SQL code we got was: ' AS "", @my_errno AS ""; + --echo # Sorry, have to abort. + exit; + --echo + } + if ($f_int2_is_unique) + { + --echo # INFO: f_int2 is UNIQUE + } + --enable_query_log + if ($run_delete) + { + # INSERT was successful -> DELETE this new record + DELETE FROM t1 WHERE f_charbig = 'delete me'; + } +} + + +#------------------------------------------------------------------------------- +## 2. Read the table row by row +# Note: There were crashes in history when reading a partitioned table +# PRIMARY KEY AND/OR UNIQUE INDEXes +## 2.1 Read all existing and some not existing records of table +# per f_int1 used in partitioning function +let $col_to_check= f_int1; +--source suite/parts/inc/partition_check_read.inc +# --source include/partition_check_read.inc +## 2.2 Read all existing and some not existing records of table +# per f_int2 used in partitioning function +let $col_to_check= f_int2; +--source suite/parts/inc/partition_check_read.inc + +if ($fixed_bug18735) +{ + +#------------------------------------------------------------------------------- +# 3 Some operations with multiple records +# 3.1 Select on "full" table +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check multiple-1 success: ' AS "",COUNT(*) = @max_row AS "" FROM t1; +--enable_query_log +# +# 3.2 (mass) DELETE of @max_row_div3 records +DELETE FROM t1 WHERE MOD(f_int1,3) = 0; +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check multiple-2 success: ' AS "",COUNT(*) = @max_row - @max_row_div3 AS "" FROM t1; +--enable_query_log +# +# 3.3 (mass) Insert of @max_row_div3 records +# (Insert the records deleted in 3.2) +INSERT INTO t1 SELECT * FROM t0_template +WHERE MOD(f_int1,3) = 0; +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check multiple-3 success: ' AS "", +(COUNT(*) = @max_row) AND (MIN(f_int1) = 1) AND (MAX(f_int1) = @max_row) AS "" +FROM t1; +--enable_query_log +# DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1; +# +# 3.4 (mass) Update @max_row_div4 * 2 + 1 records +# Bug#18735: Partitions: NDB, UNIQUE INDEX, UPDATE, strange server response +UPDATE t1 SET f_int1 = f_int1 + @max_row +WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + AND @max_row_div2 + @max_row_div4; +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check multiple-4 success: ' AS "",(COUNT(*) = @max_row) AND (MIN(f_int1) = 1) AND +(MAX(f_int1) = @max_row_div2 + @max_row_div4 + @max_row ) AS "" FROM t1; +--enable_query_log +# DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1; +# +# 3.5 (mass) Delete @max_row_div4 * 2 + 1 records +# (Delete the records updated in 3.4) +DELETE FROM t1 +WHERE f_int1 BETWEEN @max_row_div2 - @max_row_div4 + @max_row + AND @max_row_div2 + @max_row_div4 + @max_row; +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check multiple-5 success: ' AS "", +(COUNT(*) = @max_row - @max_row_div4 - @max_row_div4 - 1) +AND (MIN(f_int1) = 1) AND (MAX(f_int1) = @max_row) AS "" FROM t1; +--enable_query_log +# DEBUG SELECT COUNT(*),MIN(f_int1),MAX(f_int1) FROM t1; + +#------------------------------------------------------------------------------- +# Attention: After this section all modification on the table have to be reverted ! +# Current content of t1 follows the rule: +# <value>,<value>,'<value>','<value>',===<value>=== +# <value> contains all INTEGER values +# between 1 and @max_row_div2 - @max_row_div4 - 1 +# and +# between @max_row_div2 + @max_row_div4 + 1 and @max_row +# With other words the values between @max_row_div2 - @max_row_div4 +# and @max_row_div2 + @max_row_div4 are "missing". +#------------------------------------------------------------------------------- +# The following is only needed for tests of UNIQUE CONSTRAINTs. +if ($any_unique) +{ + # Calculate the number of records, where we will try INSERT ..... or REPLACE + SELECT COUNT(*) INTO @try_count FROM t0_template + WHERE MOD(f_int1,3) = 0 + AND f_int1 BETWEEN @max_row_div2 AND @max_row; + # + # Calculate the number of records, where we will get DUPLICATE KEY + # f_int1 is sufficient for calculating this, because 1.1 + # checks, that f_int1 = f_int2 is valid for all rows. + SELECT COUNT(*) INTO @clash_count + FROM t1 INNER JOIN t0_template USING(f_int1) + WHERE MOD(f_int1,3) = 0 + AND f_int1 BETWEEN @max_row_div2 AND @max_row; + if ($debug) + { + SELECT @try_count, @clash_count; + } +} + + +#------------------------------------------------------------------------------- +# 4 Some operations with single records +# 4.1 Insert one record with a value for f_int1 which is lower than in all +# existing records. +SELECT MIN(f_int1) - 1 INTO @cur_value FROM t1; +INSERT INTO t1 +SET f_int1 = @cur_value , f_int2 = @cur_value, + f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), + f_charbig = '#SINGLE#'; +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check single-1 success: ' AS "",COUNT(*) = 1 AS "" FROM t1 +WHERE f_int1 = @cur_value AND f_int2 = @cur_value + AND f_char1 = CAST(@cur_value AS CHAR) + AND f_char2 = CAST(@cur_value AS CHAR) AND f_charbig = '#SINGLE#'; +--enable_query_log +# +# 4.2 Insert one record with a value for f_int1 which is higher than in all +# existing records. +SELECT MAX(f_int1) + 1 INTO @cur_value FROM t1; +INSERT INTO t1 +SET f_int1 = @cur_value , f_int2 = @cur_value, + f_char1 = CAST(@cur_value AS CHAR), f_char2 = CAST(@cur_value AS CHAR), + f_charbig = '#SINGLE#'; +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check single-2 success: ' AS "",COUNT(*) = 1 AS "" FROM t1 +WHERE f_int1 = @cur_value AND f_int2 = @cur_value + AND f_char1 = CAST(@cur_value AS CHAR) + AND f_char2 = CAST(@cur_value AS CHAR) AND f_charbig = '#SINGLE#'; +--enable_query_log +# +# 4.3 Update one record. The value of f_int1 is altered from the lowest to +# the highest value of all existing records. +# If f_int1 is used for the partitioning expression a movement of the +# record to another partition/subpartition might appear. +SELECT MIN(f_int1) INTO @cur_value1 FROM t1; +SELECT MAX(f_int1) + 1 INTO @cur_value2 FROM t1; +# Bug#18735: Partitions: NDB, UNIQUE INDEX, UPDATE, strange server response +UPDATE t1 SET f_int1 = @cur_value2 +WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check single-3 success: ' AS "",COUNT(*) = 1 AS "" FROM t1 +WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; +--enable_query_log +# +# 4.4 Update one record. The value of f_int1 is altered from the highest value +# to a value lower than in all existing records. +# If f_int1 is used for the partitioning expression a movement of the +# record to another partition/subpartition might appear. +# f_int1 gets the delicate value '-1'. +SET @cur_value1= -1; +SELECT MAX(f_int1) INTO @cur_value2 FROM t1; +# Bug#15968: Partitions: crash when INSERT with f_int1 = -1 into PARTITION BY HASH(f_int1) +# Bug#16385: Partitions: crash when updating a range partitioned NDB table +# Bug#18735: Partitions: NDB, UNIQUE INDEX, UPDATE, strange server response +UPDATE t1 SET f_int1 = @cur_value1 +WHERE f_int1 = @cur_value2 AND f_charbig = '#SINGLE#'; +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check single-4 success: ' AS "",COUNT(*) AS "" FROM t1 +WHERE f_int1 = @cur_value1 AND f_charbig = '#SINGLE#'; +--enable_query_log +# +# 4.5 Delete the record with the highest value of f_int1. +SELECT MAX(f_int1) INTO @cur_value FROM t1; +DELETE FROM t1 WHERE f_int1 = @cur_value AND f_charbig = '#SINGLE#'; +# Check of preceeding statements via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check single-5 success: ' AS "",COUNT(*) = 0 AS "" FROM t1 +WHERE f_charbig = '#SINGLE#' AND f_int1 = f_int1 = @cur_value; +--enable_query_log +# +# 4.6 Delete the record with f_int1 = -1 +DELETE FROM t1 WHERE f_int1 = -1 AND f_charbig = '#SINGLE#'; +# Check of preceeding statements via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check single-6 success: ' AS "",COUNT(*) = 0 AS "" FROM t1 +WHERE f_charbig = '#SINGLE#' AND f_int1 IN (-1,@cur_value); +--enable_query_log +# +# 4.7 Insert one record with such a big value for f_int1, so that in case +# - f_int1 is used within the partitioning algorithm +# - we use range partitioning +# we get error ER_NO_PARTITION_FOR_GIVEN_VALUE (1514) +# "Table has no partition for value ...." +--disable_abort_on_error +eval INSERT INTO t1 SET f_int1 = @max_int_4 , f_int2 = @max_int_4, f_charbig = '#$max_int_4##'; +--enable_abort_on_error +if ($no_debug) +{ +--disable_query_log +} +eval SET @my_errno = $mysql_errno; +let $unexpected_error= `SELECT @my_errno NOT IN (0,1505,1514)`; +if ($unexpected_error) +{ + --echo # The last command got an unexepected error response. + --echo # Expected/handled SQL codes are 0,1514 + SELECT '# SQL code we got was: ' AS "", @my_errno AS ""; + --echo # Sorry, have to abort. + exit; + --echo +} +# Check of preceeding statement via Select, if the INSERT was successful +let $run= `SELECT @my_errno = 0`; +if ($run) +{ +# Attention: There are some tests where the column type is changed from +# INTEGER to MEDIUMINT. MEDIUMINT has a smaller range and the +# inserted value is automatically adjusted to the maximum value +# of the data type. +# that's the reason why we cannot use WHERE <column> = @max_int_4 here. +# +eval SELECT '# check single-7 success: ' AS "", +COUNT(*) = 1 AS "" FROM t1 WHERE f_charbig = '#$max_int_4##'; +# Revert this modification +--enable_query_log +eval DELETE FROM t1 WHERE f_charbig = '#$max_int_4##'; +} +--enable_query_log + +} +# End workaround for Bug#18735 + +#------------------------------------------------------------------------------- +# 5 Experiments with NULL +# If the result of the partitioning function IS NULL partitioning treats +# this record as if the the result of the partitioning function is +# MySQL 5.1 < March 2006 : zero +# MySQL 5.1 >= March 2006 : LONGLONG_MIN +# Let's INSERT a record where the result of the partitioning function is +# probably (depends on function currently used) zero and look if there are +# any strange effects during the execution of the next statements. +# Bug#17891: Partitions: NDB, crash on select .. where col is null or col = value +# Bug#18659: Partitions: wrong result on WHERE <col. used in part. function> IS NULL +DELETE FROM t1 WHERE f_int1 IS NULL OR f_int1 = 0; +# Attention: Zero should be tested +INSERT t1 SET f_int1 = 0 , f_int2 = 0, + f_char1 = CAST(0 AS CHAR), f_char2 = CAST(0 AS CHAR), + f_charbig = '#NULL#'; +# 5.1 Insert one record with f_int1 IS NULL. +# f1 "=" NULL is a delicate value which might stress the partitioning +# mechanism if the result of the expression in the partitioning algorithm +# becomes NULL. +# Not: This INSERT will fail, if f_int1 is PRIMARY KEY or UNIQUE INDEX +# 1048: Column 'f_int1' cannot be null +--disable_abort_on_error + +INSERT INTO t1 + SET f_int1 = NULL , f_int2 = -@max_row, + f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR), + f_charbig = '#NULL#'; +# Some other NULL experiments if preceeding INSERT was successfull +--enable_abort_on_error +if ($no_debug) +{ +--disable_query_log +} +eval SET @my_errno = $mysql_errno; +let $run= `SELECT @my_errno = 0`; +let $unexpected_error= `SELECT @my_errno NOT IN (0,1048)`; +if ($unexpected_error) +{ + --echo # The last command got an unexepected error response. + --echo # Expected/handled SQL codes are 0,1048 + SELECT '# SQL code we got was: ' AS "", @my_errno AS ""; + --echo # Sorry, have to abort. + exit; + --echo +} +--enable_query_log +# Give a success message like in the other following tests +--echo # check null success: 1 +# The following checks do not make sense if f_int1 cannot be NULL +if ($run) +{ +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +# Bug#17432: Partitions: wrong result, SELECT ... where <column> is null +SELECT '# check null-1 success: ' AS "",COUNT(*) = 1 AS "" FROM t1 +WHERE f_int1 IS NULL AND f_charbig = '#NULL#'; +--enable_query_log +# +# 5.2 Update of f_int1 from NULL to negative value +# Bug#17432: Partitions: wrong result, SELECT ... where <column> is null +UPDATE t1 SET f_int1 = -@max_row +WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) + AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; +if ($no_debug) +{ +--disable_query_log +} +# Check of preceeding statement via Select +SELECT '# check null-2 success: ' AS "",COUNT(*) = 1 AS "" FROM t1 +WHERE f_int1 = -@max_row AND f_charbig = '#NULL#'; +--enable_query_log +# 5.3 Update of f_int1 from negative value to NULL +UPDATE t1 SET f_int1 = NULL +WHERE f_int1 = -@max_row AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) + AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; +if ($no_debug) +{ +--disable_query_log +} +# Check of preceeding statement via Select +SELECT '# check null-3 success: ' AS "",COUNT(*) = 1 AS "" FROM t1 +WHERE f_int1 IS NULL AND f_charbig = '#NULL#'; +--enable_query_log +# 5.4 DELETE of the record with f_int1 IS NULL +DELETE FROM t1 +WHERE f_int1 IS NULL AND f_int2 = -@max_row AND f_char1 = CAST(-@max_row AS CHAR) + AND f_char2 = CAST(-@max_row AS CHAR) AND f_charbig = '#NULL#'; +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check null-4 success: ' AS "",COUNT(*) = 0 AS "" FROM t1 +WHERE f_int1 IS NULL; +--enable_query_log +} +# Remove the "0" record +DELETE FROM t1 +WHERE f_int1 = 0 AND f_int2 = 0 + AND f_char1 = CAST(0 AS CHAR) AND f_char2 = CAST(0 AS CHAR) + AND f_charbig = '#NULL#'; + + +#------------------------------------------------------------------------------- +## 6. UPDATEs of columns used in the partitioning function and the PRIMARY KEY +# the UNIQUE INDEX without using straight forward UPDATE. +# INSERT .... ON DUPLICATE KEY UPDATE .... --> update existing record +# REPLACE --> delete existing record + insert new record +# Note: +# - This test is skipped for tables without any PRIMARY KEY or +# UNIQUE INDEX. +# - MOD(<column>,n) with n = prime number, n <> 2 is used to cause +# that many records and most probably more than one PARTITION/ +# SUBPARTITION are affected. +# - Under certain circumstanditions a movement of one or more records +# to other PARTITIONs/SUBPARTITIONs might appear. +# - There are some storage engines, which are unable to revert changes +# of a failing statement. This has to be taken into account when +# checking if a DUPLICATE KEY might occur. +# +# What to test ? +# UNIQUE columns +# f_int1 IU f_int1 IU f_int1,f_int2 R +# f_int2 IU f_int2 IU f_int1,f_int2 R +# f_int1,f_int2 IU f_int1,f_int2 R +# +# IU column = INSERT .. ON DUPLICATE KEY UPDATE column +# R = REPLACE .. +# +# Current state of the data +# 1. f_int1 = f_int2, f_char1 = CAST(f_int1 AS CHAR), f_char2 = f_char1, +# f_charbig = CONCAT('===',f_char1,'===); +# 2. f_int1 FROM 1 TO @max_row_div4 +# AND @max_row_div2 + @max_row_div4 TO @max_row +# +# Do not apply the following tests to tables without UNIQUE columns. +if ($any_unique) +{ + let $num= 1; + if ($f_int1_is_unique) + { + ## 6.1 f_int1 is UNIQUE, UPDATE f_int1 when DUPLICATE KEY + # Bug#15236 Partitions: crash, if Insert .. on duplicate key causes update of existing row + INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) + SELECT f_int1, f_int1, '', '', 'was inserted' + FROM t0_template source_tab + WHERE MOD(f_int1,3) = 0 + AND f_int1 BETWEEN @max_row_div2 AND @max_row + ON DUPLICATE KEY + UPDATE f_int1 = 2 * @max_row + source_tab.f_int1, + f_charbig = 'was updated'; + --source suite/parts/inc/partition_20.inc + # --source include/partition_20.inc + } + + if ($f_int2_is_unique) + { + ## 6.2 f_int2 is UNIQUE, UPDATE f_int2 when DUPLICATE KEY + # Bug#15236 Partitions: crash, if Insert .. on duplicate key causes update of existing row + INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) + SELECT f_int1, f_int1, '', '', 'was inserted' + FROM t0_template source_tab + WHERE MOD(f_int1,3) = 0 + AND f_int1 BETWEEN @max_row_div2 AND @max_row + ON DUPLICATE KEY + UPDATE f_int2 = 2 * @max_row + source_tab.f_int1, + f_charbig = 'was updated'; + --source suite/parts/inc/partition_20.inc + # --source include/partition_20.inc + } + + ## 6.3 f_int1, f_int2 is UNIQUE, UPDATE f_int1, f_int2 when DUPLICATE KEY + INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) + SELECT f_int1, f_int1, '', '', 'was inserted' + FROM t0_template source_tab + WHERE MOD(f_int1,3) = 0 + AND f_int1 BETWEEN @max_row_div2 AND @max_row + ON DUPLICATE KEY + UPDATE f_int1 = 2 * @max_row + source_tab.f_int1, + f_int2 = 2 * @max_row + source_tab.f_int1, + f_charbig = 'was updated'; + --source suite/parts/inc/partition_20.inc + # --source include/partition_20.inc + + ## 6.4 REPLACE + # Bug#16782: Partitions: crash, REPLACE .. on table with PK, DUPLICATE KEY + REPLACE INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) + SELECT f_int1, - f_int1, '', '', 'was inserted or replaced' + FROM t0_template source_tab + WHERE MOD(f_int1,3) = 0 AND f_int1 BETWEEN @max_row_div2 AND @max_row; + # DEBUG SELECT * FROM t1 ORDER BY f_int1, f_int2; + # Check of preceeding statement via Select + if ($no_debug) + { + --disable_query_log + } + SELECT '# check replace success: ' AS "", COUNT(*) = @try_count AS "" + FROM t1 WHERE f_charbig = 'was inserted or replaced'; + --enable_query_log + # Revert the modification + DELETE FROM t1 + WHERE f_int1 BETWEEN @max_row_div2 AND @max_row_div2 + @max_row_div4; + # If there is only UNIQUE (f1,f2) we will have pairs f_int1,f_int2 + # <n>, <n> and <n>, <-n> + # where MOD(f_int1,3) = 0 + # and f_int1 between @max_row_div2 + @max_row_div4 and @max_row. + # Delete the <n>, <n> records. + DELETE FROM t1 + WHERE f_int1 = f_int2 AND MOD(f_int1,3) = 0 AND + f_int1 BETWEEN @max_row_div2 + @max_row_div4 AND @max_row; + UPDATE t1 SET f_int2 = f_int1, + f_char1 = CAST(f_int1 AS CHAR), + f_char2 = CAST(f_int1 AS CHAR), + f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'===') + WHERE f_charbig = 'was inserted or replaced' AND f_int1 = - f_int2; + # DEBUG SELECT * FROM t1 ORDER BY f_int1, f_int2; +} + +if ($fixed_bug18735) +{ + # The following tests work cannot like intended, if we had to omit + # tests because of Bug#18735 + +#------------------------------------------------------------------------------- +# 7 Transactions +SET AUTOCOMMIT= 0; +# DEBUG SELECT @max_row_div4 , @max_row_div2 + @max_row_div4; +if ($no_debug) +{ + --disable_query_log +} +SELECT COUNT(f_int1) INTO @start_count FROM t1 +WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +--enable_query_log +let $run= `SELECT @start_count <> 0`; +if ($run) +{ + --echo # Prerequisites for following tests not fullfilled. + --echo # The content of the table t1 is unexpected + --echo # There must be no rows BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; + SELECT COUNT(f_int1) FROM t1 + WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; + --echo # Sorry, have to abort. + exit; +} +# Number of records to be inserted +if ($no_debug) +{ + --disable_query_log +} +SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; +--enable_query_log +# 7.1 Successful INSERT + COMMIT +INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) +SELECT f_int1, f_int1, '', '', 'was inserted' +FROM t0_template source_tab +WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +# The inserted records must be visible (at least for our current session) +if ($no_debug) +{ + --disable_query_log +} +SELECT '# check transactions-1 success: ' AS "", + COUNT(*) = @exp_inserted_rows AS "" +FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +--enable_query_log +# Make the changes persistent for all storage engines +COMMIT WORK; +# The inserted records must be visible (for all open and future sessions) +if ($no_debug) +{ + --disable_query_log +} +SELECT '# check transactions-2 success: ' AS "", + COUNT(*) = @exp_inserted_rows AS "" +FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +--enable_query_log +# Let's assume we have a transactional engine + COMMIT is ill. +# A correct working ROLLBACK might revert the INSERT. +ROLLBACK WORK; +if ($no_debug) +{ + --disable_query_log +} +SELECT '# check transactions-3 success: ' AS "", + COUNT(*) = @exp_inserted_rows AS "" +FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +--enable_query_log +# Revert the changes +DELETE FROM t1 WHERE f_charbig = 'was inserted'; +COMMIT WORK; +ROLLBACK WORK; +if ($no_debug) +{ + --disable_query_log +} +SELECT '# check transactions-4 success: ' AS "", + COUNT(*) = 0 AS "" +FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +--enable_query_log +# +# 7.2 Successful INSERT + ROLLBACK +INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) +SELECT f_int1, f_int1, '', '', 'was inserted' +FROM t0_template source_tab +WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +if ($no_debug) +{ + --disable_query_log +} +SELECT '# check transactions-5 success: ' AS "", + COUNT(*) = @exp_inserted_rows AS "" +FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +--enable_query_log +ROLLBACK WORK; +if ($no_debug) +{ + --disable_query_log +} +SELECT COUNT(*) INTO @my_count +FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +SELECT '# check transactions-6 success: ' AS "", + @my_count IN (0,@exp_inserted_rows) AS ""; +let $run= `SELECT @my_count = 0`; +if ($run) +{ + --echo # INFO: Storage engine used for t1 seems to be transactional. +} +let $run= `SELECT @my_count = @exp_inserted_rows`; +if ($run) +{ + --echo # INFO: Storage engine used for t1 seems to be not transactional. +} +--enable_query_log +# Let's assume we have a transactional engine + ROLLBACK is ill. +# A correct working COMMIT might make the inserted records again visible. +COMMIT; +if ($no_debug) +{ + --disable_query_log +} +SELECT '# check transactions-7 success: ' AS "", + COUNT(*) IN (0,@exp_inserted_rows) AS "" +FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +--enable_query_log +# Revert the changes +DELETE FROM t1 WHERE f_charbig = 'was inserted'; +COMMIT WORK; +# +# 7.3 Failing INSERT (in mid of statement processing) + COMMIT +SET @@session.sql_mode = 'traditional'; +# Number of records where a INSERT has to be tried +SELECT @max_row_div2 + @max_row_div4 - @max_row_div4 + 1 INTO @exp_inserted_rows; +# +--disable_abort_on_error +INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) +SELECT IF(f_int1 = @max_row_div2,f_int1 / 0,f_int1),f_int1, + '', '', 'was inserted' FROM t0_template +WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +--enable_abort_on_error +COMMIT; +# How many new records should be now visible ? +# 1. storage engine unable to revert changes made by the failing statement +# @max_row_div2 - 1 - @max_row_div4 + 1 +# 2. storage engine able to revert changes made by the failing statement +# 0 +if ($no_debug) +{ + --disable_query_log +} +SELECT COUNT(*) INTO @my_count +FROM t1 WHERE f_int1 BETWEEN @max_row_div4 AND @max_row_div2 + @max_row_div4; +SELECT '# check transactions-8 success: ' AS "", + @my_count IN (@max_row_div2 - 1 - @max_row_div4 + 1,0) AS ""; +let $run= `SELECT @my_count = @max_row_div2 - 1 - @max_row_div4 + 1`; +if ($run) +{ + --echo # INFO: Storage engine used for t1 seems to be unable to revert + --echo # changes made by the failing statement. +} +let $run= `SELECT @my_count = 0`; +if ($run) +{ + --echo # INFO: Storage engine used for t1 seems to be able to revert + --echo # changes made by the failing statement. +} +--enable_query_log +SET @@session.sql_mode = ''; +SET AUTOCOMMIT= 1; +# Revert the changes +DELETE FROM t1 WHERE f_charbig = 'was inserted'; +COMMIT WORK; + +if ($debug) +{ + SELECT * FROM t1 ORDER BY f_int1; +} + +} +# End workaround for Bug#18735 + +#------------------------------------------------------------------------------- +# 8 Some special cases +# 8.1 Dramatic increase of the record/partition/subpartition/table sizes +UPDATE t1 SET f_charbig = REPEAT('b', 1000); +# partial check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +eval SELECT '# check special-1 success: ' AS "",1 AS "" FROM t1 +WHERE f_int1 = 1 AND f_charbig = REPEAT('b', 1000); +--enable_query_log +# +# 8.2 Dramatic decrease of the record/partition/subpartition/table sizes +UPDATE t1 SET f_charbig = ''; +# partial check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +eval SELECT '# check special-2 success: ' AS "",1 AS "" FROM t1 +WHERE f_int1 = 1 AND f_charbig = ''; +--enable_query_log +# Revert the changes +UPDATE t1 SET f_charbig = CONCAT('===',CAST(f_int1 AS CHAR),'==='); + +if ($debug) +{ + SELECT * FROM t1 ORDER BY f_int1; +} + +if ($fixed_bug18735) +{ +#------------------------------------------------------------------------------- +# 9 TRIGGERs +let $num= 1; +# 9.1 BEFORE/AFTER INSERT/UPDATE/DELETE TRIGGER on table t0_aux causes that +# column values used in partitioning function of t1 are changed. +let $tab_has_trigg= t0_aux; +let $tab_in_trigg= t1; + +# Insert three records, which will be updated by the trigger +# Bug#18735: Partitions: NDB, UNIQUE INDEX, UPDATE, strange server response +eval INSERT INTO $tab_in_trigg(f_int1,f_int2,f_char1,f_char2,f_charbig) +SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template +WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; + +let $statement= INSERT INTO t0_aux(f_int1,f_int2,f_char1,f_char2,f_charbig) +SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template +WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; +let $event= BEFORE INSERT; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc +let $event= AFTER INSERT; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc + +let $statement= UPDATE t0_aux SET f_int1 = - f_int1, f_int2 = - f_int2 +WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); +let $event= BEFORE UPDATE; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc +let $event= AFTER UPDATE; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc + +let $statement= DELETE FROM t0_aux +WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); +let $event= BEFORE DELETE; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc +let $event= AFTER DELETE; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc + +# Cleanup +eval DELETE FROM $tab_in_trigg +WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; + +# Two currently (February 2006) impossible operations. +# 1442: 'Can't update table 't1' in stored function/trigger because it is +# already used by statement which invoked this stored function/trigger.' +# 1362: 'Updating of OLD row is not allowed in trigger' + +if ($debug) +{ + SELECT * FROM t1 ORDER BY f_int1; +} + +if ($more_trigger_tests) +{ +# 9.2 BEFORE/AFTER INSERT/UPDATE/DELETE TRIGGER on partitioned table t1 causes +# that column values in not partitioned table t0_aux are changed. +let $tab_has_trigg= t1; +let $tab_in_trigg= t0_aux; + +# Insert three records, which will be updated by the trigger +eval INSERT INTO $tab_in_trigg(f_int1,f_int2,f_char1,f_char2,f_charbig) +SELECT f_int1,f_int2,f_char1,f_char2,'just inserted' FROM t0_template +WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; + +let $statement= INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig) +SELECT f_int1,f_int2,f_char1,f_char2,NULL FROM t0_template +WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; +let $event= BEFORE INSERT; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc +let $event= AFTER INSERT; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc + +let $statement= UPDATE t1 SET f_int1 = - f_int1, f_int2 = - f_int2 +WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); +let $event= BEFORE UPDATE; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc +let $event= AFTER UPDATE; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc + +let $statement= DELETE FROM t1 +WHERE f_int1 IN (- (@max_row_div2 - 1),- @max_row_div2,- (@max_row_div2 + 1)); +let $event= BEFORE DELETE; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc +let $event= AFTER DELETE; +--source suite/parts/inc/partition_trigg1.inc +# --source include/partition_trigg1.inc +eval DELETE FROM $tab_in_trigg +WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1; +} + +if ($debug) +{ + SELECT * FROM t1 ORDER BY f_int1; +} + +# 9.3 BEFORE/AFTER UPDATE TRIGGER on partitioned table causes that the value +# of columns in partitioning function is recalculated +if ($more_trigger_tests) +{ +# 9.3.1 The UPDATE itself changes a column which is not used in the partitioning +# function. +# "old" values are used as source within the trigger. +let $statement= UPDATE t1 +SET f_charbig = '####updated per update statement itself####'; +let $source= old; +let $event= BEFORE UPDATE; +--source suite/parts/inc/partition_trigg2.inc +# --source include/partition_trigg2.inc +# FIXME when AFTER TRIGGER can be used +# Currently (2006-02-23) a AFTER TRIGGER is not allowed to modify a row, which +# was just modified: 1362: Updating of NEW row is not allowed in after trigger +} + +# 9.3.2 The UPDATE itself changes a column which is used in the partitioning +# function. +let $statement= UPDATE t1 +SET f_int1 = f_int1 + @max_row, f_int2 = f_int2 - @max_row, +f_charbig = '####updated per update statement itself####'; + +# 9.3.2.1 "old" values are used as source within the trigger. +let $source= old; +let $event= BEFORE UPDATE; +--source suite/parts/inc/partition_trigg2.inc +# --source include/partition_trigg2.inc +# FIXME when AFTER TRIGGER can be used +# Currently (2006-02-23) a AFTER TRIGGER is not allowed to modify a row, which +# was just modified: 1362: Updating of NEW row is not allowed in after trigger +# 9.3.2.2 "new" values are used as source within the trigger. +let $source= new; +let $event= BEFORE UPDATE; +--source suite/parts/inc/partition_trigg2.inc +# --source include/partition_trigg2.inc +# FIXME when AFTER TRIGGER can be used + +if ($debug) +{ + SELECT * FROM t1 ORDER BY f_int1; +} + +# 9.4 BEFORE/AFTER INSERT TRIGGER on partitioned table causes that the value of +# columns in partitioning function is recalculated. +# 9.4.1 INSERT assigns values to the recalculate columns +let $statement= INSERT INTO t1 (f_int1, f_int2, f_char1, f_char2, f_charbig) +SELECT f_int1, f_int1, CAST(f_int1 AS CHAR), + CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template +WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 +ORDER BY f_int1; +let $event= BEFORE INSERT; +let $source= new; +--source suite/parts/inc/partition_trigg3.inc +# --source include/partition_trigg3.inc +# FIXME when AFTER TRIGGER can be used + +# 9.4.2 INSERT assigns no values to the recalculate columns +let $statement= INSERT INTO t1 (f_char1, f_char2, f_charbig) +SELECT CAST(f_int1 AS CHAR), + CAST(f_int1 AS CHAR), 'just inserted' FROM t0_template +WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1 +ORDER BY f_int1; +let $event= BEFORE INSERT; +let $source= new; +--source suite/parts/inc/partition_trigg3.inc +# --source include/partition_trigg3.inc +# FIXME when AFTER TRIGGER can be used + +if ($debug) +{ + SELECT * FROM t1 ORDER BY f_int1; +} + +} +# End workaround for Bug#18735 + +#------------------------------------------------------------------------------- +# 10 ANALYZE/CHECK/CHECKSUM +ANALYZE TABLE t1; +CHECK TABLE t1 EXTENDED; +# Checksum depends on @max_row so we have to unify the value +--replace_column 2 <some_value> +CHECKSUM TABLE t1 EXTENDED; + + +#------------------------------------------------------------------------------- +# 11 Some special statements, which may lead to a rebuild of the trees +# depending on the storage engine and some particular conditions +# 11.1 OPTIMIZE TABLE +# Manual about OPTIMIZE <InnoDB table>: +# ... , it is mapped to ALTER TABLE, which rebuilds the table. +# Rebuilding updates index statistics and frees unused space in the +# clustered index. +# FIXME What will happen with NDB ? +OPTIMIZE TABLE t1; +--source suite/parts/inc/partition_layout_check2.inc +# --source include/partition_layout_check2.inc +# 10.2 REPAIR TABLE +REPAIR TABLE t1 EXTENDED; +--source suite/parts/inc/partition_layout_check2.inc +# --source include/partition_layout_check2.inc +# +# 11.3 Truncate +# Manual about TRUNCATE on tables ( != InnoDB table with FOREIGN KEY ): +# Truncate operations drop and re-create the table .... +TRUNCATE t1; +# Check of preceeding statement via Select +if ($no_debug) +{ +--disable_query_log +} +SELECT '# check TRUNCATE success: ' AS "",COUNT(*) = 0 AS "" FROM t1; +--enable_query_log +--source suite/parts/inc/partition_layout_check2.inc +# --source include/partition_layout_check2.inc +--echo # End usability test (include/partition_check.inc) + |