partition_1.inc

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

--source include/partition_10.inc
DROP TABLE t1;
eval $part0 $part1_Y $part2_N $part3_N ;
--source include/partition_10.inc
DROP TABLE t1;
eval $part0 $part1_Y $part2_N $part3_Y ;
--source include/partition_10.inc
DROP TABLE t1;
eval $part0 $part1_Y $part2_Y $part3_N ;
--source include/partition_10.inc
DROP TABLE t1;
}
eval $part0 $part1_Y $part2_Y $part3_Y ;
--source include/partition_10.inc
DROP TABLE t1;

--echo # 3.2 partition/subpartition numbers good and bad values and notations
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
--echo # 3.2.1 partition/subpartition numbers INTEGER notation
# ML: "positive/negative" is my private judgement. It need no to correspond
#     with the server response.
# (positive) number = 2
let $part_number= 2;
--source include/partition_11.inc
# (positive) special case number = 1
let $part_number= 1;
--source include/partition_11.inc
# (negative) 0 is non sense
let $part_number= 0;
--source include/partition_11.inc
# (negative) -1 is non sense
let $part_number= -1;
--source include/partition_11.inc
# (negative) 1000000 is too huge
let $part_number= 1000000;
--source include/partition_11.inc

if ($fixed_bug15890)
{
--echo # 3.2.2 partition/subpartition numbers DECIMAL notation
# (positive) number = 2.0
let $part_number= 2.0;
--source include/partition_11.inc
# (negative) -2.0 is non sense
let $part_number= -2.0;
--source include/partition_11.inc
# (negative) case number = 0.0 is non sense
let $part_number= 0.0;
--source include/partition_11.inc
# Bug#15890 Partitions: Strange interpretation of partition number
# (negative) number = 1.5 is non sense
let $part_number= 1.5;
--source include/partition_11.inc
# (negative) number is too huge
let $part_number= 999999999999999999999999999999.999999999999999999999999999999;
--source include/partition_11.inc
# (negative) number is nearly zero
let $part_number= 0.000000000000000000000000000001;
--source include/partition_11.inc

--echo # 3.2.3 partition/subpartition numbers FLOAT notation
##### FLOAT notation
# (positive) number = 2.0E+0
let $part_number= 2.0E+0;
--source include/partition_11.inc
# Bug#15890 Partitions: Strange interpretation of partition number
# (positive) number = 0.2E+1
let $part_number= 0.2E+1;
--source include/partition_11.inc
# (negative) -2.0E+0 is non sense
let $part_number= -2.0E+0;
--source include/partition_11.inc
# (negative) 0.15E+1 is non sense
let $part_number= 0.15E+1;
--source include/partition_11.inc
# (negative) 0.0E+300 is zero
let $part_number= 0.0E+300;
--source include/partition_11.inc
# Bug#15890 Partitions: Strange interpretation of partition number
# (negative) 1E+300 is too huge
let $part_number= 1E+300;
--source include/partition_11.inc
# (negative) 1E-300 is nearly zero
let $part_number= 1E-300;
--source include/partition_11.inc
}

--echo # 3.2.4 partition/subpartition numbers STRING notation
##### STRING notation
# (negative?) case number = '2'
let $part_number= '2';
--source include/partition_11.inc
# (negative?) case number = '2.0'
let $part_number= '2.0';
--source include/partition_11.inc
# (negative?) case number = '0.2E+1'
let $part_number= '0.2E+1';
--source include/partition_11.inc
# (negative) Strings starts with digit, but 'A' follows
let $part_number= '2A';
--source include/partition_11.inc
# (negative) Strings starts with 'A', but digit follows
let $part_number= 'A2';
--source include/partition_11.inc
# (negative) empty string
let $part_number= '';
--source include/partition_11.inc
# (negative) string without any digits
let $part_number= 'GARBAGE';
--source include/partition_11.inc

--echo # 3.2.5 partition/subpartition numbers other notations
# (negative) Strings starts with digit, but 'A' follows
let $part_number= 2A;
--source include/partition_11.inc
# (negative) Strings starts with 'A', but digit follows
let $part_number= A2;
--source include/partition_11.inc
# (negative) string without any digits
let $part_number= GARBAGE;
--source include/partition_11.inc

# (negative?) double quotes
let $part_number= "2";
--source include/partition_11.inc
# (negative) Strings starts with digit, but 'A' follows
let $part_number= "2A";
--source include/partition_11.inc
# (negative) Strings starts with 'A', but digit follows
let $part_number= "A2";
--source include/partition_11.inc
# (negative) string without any digits
let $part_number= "GARBAGE";
--source include/partition_11.inc

--echo # 3.3 Mixups of assigned partition/subpartition numbers and names
--echo # 3.3.1 (positive) number of partition/subpartition
--echo #                         = number of named partition/subpartition
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) PARTITIONS 2
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
    (SUBPARTITION subpart11, SUBPARTITION subpart12),
  PARTITION part2 VALUES LESS THAN (2147483647)
    (SUBPARTITION subpart21, SUBPARTITION subpart22)
);
--source include/partition_layout.inc
DROP TABLE t1;
--echo # 3.3.2 (positive) number of partition/subpartition ,
--echo #                  0 (= no) named partition/subpartition
--echo #                  already checked above
--echo # 3.3.3 (negative) number of partitions/subpartitions
--echo #                         > number of named partitions/subpartitions
--error 1064
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1 ) ;
# Wrong number of named subpartitions in first partition
--error 1064
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
    (SUBPARTITION subpart11 ),
  PARTITION part2 VALUES LESS THAN (2147483647)
    (SUBPARTITION subpart21, SUBPARTITION subpart22)
);
# Wrong number of named subpartitions in non first/non last partition
--error 1064
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
    (SUBPARTITION subpart11, SUBPARTITION subpart12),
  PARTITION part2 VALUES LESS THAN (2000)
    (SUBPARTITION subpart21 ),
  PARTITION part3 VALUES LESS THAN (2147483647)
    (SUBPARTITION subpart31, SUBPARTITION subpart32)
);
# Wrong number of named subpartitions in last partition
--error 1064
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) PARTITIONS 2
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
    (SUBPARTITION subpart11, SUBPARTITION subpart12),
  PARTITION part2 VALUES LESS THAN (2147483647)
    (SUBPARTITION subpart21                        )
);
--echo # 3.3.4 (negative) number of partitions < number of named partitions
--error 1064
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
# Wrong number of named subpartitions in first partition
--error 1064
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
( PARTITION part1 VALUES LESS THAN (1000)
    (SUBPARTITION subpart11, SUBPARTITION subpart12),
  PARTITION part2 VALUES LESS THAN (2147483647)
    (SUBPARTITION subpart21, SUBPARTITION subpart22)
);
# Wrong number of named subpartitions in non first/non last partition
--error 1064
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
( PARTITION part1 VALUES LESS THAN (1000)
    (SUBPARTITION subpart11, SUBPARTITION subpart12),
  PARTITION part2 VALUES LESS THAN (2000)
    (SUBPARTITION subpart21                        ),
  PARTITION part3 VALUES LESS THAN (2147483647)
    (SUBPARTITION subpart31, SUBPARTITION subpart32)
);
# Wrong number of named subpartitions in last partition
--error 1064
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 1
( PARTITION part1 VALUES LESS THAN (1000)
    (SUBPARTITION subpart11, SUBPARTITION subpart12),
  PARTITION part2 VALUES LESS THAN (2147483647)
    (SUBPARTITION subpart21, SUBPARTITION subpart22)
);


--echo
--echo #------------------------------------------------------------------------
--echo #  4. Checks of logical partition/subpartition name
--echo #     file name clashes during CREATE TABLE
--echo #------------------------------------------------------------------------
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

--echo #  4.1 (negative) A partition name used more than once
--error ER_SAME_NAME_PARTITION
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part1);
#
if ($fixed_bug15408)
{
# Bug#15408 Partitions: subpartition names are not unique
--error ER_SAME_NAME_PARTITION
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1)
( PARTITION part1 VALUES LESS THAN (1000)
    (SUBPARTITION subpart11, SUBPARTITION subpart11)
);
}
--echo # FIXME Implement testcases with filename problems
--echo #       existing file of other table --- partition/subpartition file name
--echo #       partition/subpartition file name  --- file of the same table

--echo
--echo #------------------------------------------------------------------------
--echo #  5. Alter table experiments
--echo #------------------------------------------------------------------------
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
--echo # 5.1 alter table add partition
--echo # 5.1.1 (negative) add partition to non partitioned table
CREATE TABLE t1 ( f1 INTEGER, f2 char(20));
--source include/partition_layout.inc
# MyISAM gets ER_PARTITION_MGMT_ON_NONPARTITIONED and NDB 1005
# The error code of NDB differs, because all NDB tables are partitioned even
# if the CREATE TABLE does not contain a partitioning clause.
--error ER_PARTITION_MGMT_ON_NONPARTITIONED,1005
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
--source include/partition_layout.inc
DROP TABLE t1;

--echo # 5.1.2 Add one partition to a table with one partition
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1);
--source include/partition_layout.inc
eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
--disable_query_log
eval SELECT $engine = 'NDB' INTO @aux;
let $my_exit= `SELECT @aux`;
if ($my_exit)
{
   exit;
}
--enable_query_log
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
--source include/partition_12.inc
DROP TABLE t1;

--echo # 5.1.3 Several times add one partition to a table with some partitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
--source include/partition_layout.inc
eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
# Partition name before first existing partition name
ALTER TABLE t1 ADD PARTITION (PARTITION part0);
--source include/partition_12.inc
DELETE FROM t1;
eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
# Partition name between existing partition names
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
--source include/partition_12.inc
DELETE FROM t1;
eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
if ($fixed_bug13447)
{
# Partition name after all existing partition names
# Bug#13447 Partitions: crash with alter table
ALTER TABLE t1 ADD PARTITION (PARTITION part4);
}
--source include/partition_12.inc
DROP TABLE t1;

--echo # 5.1.4 Add several partitions to a table with some partitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part3);
--source include/partition_layout.inc
eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
if ($fixed_bug13447)
{
# Bug#13447 Partitions: crash with alter table
ALTER TABLE t1 ADD PARTITION (PARTITION part0, PARTITION part2, PARTITION part4);
}
--source include/partition_12.inc
DROP TABLE t1;

--echo # 5.1.5 (negative) Add partitions to a table with some partitions
--echo #       clash on new and already existing partition names
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part2, PARTITION part3);
# Clash on first/non last partition name
--error ER_SAME_NAME_PARTITION
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
# Clash on non first/non last partition name
--error ER_SAME_NAME_PARTITION
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
# Clash on non first/last partition name
--error ER_SAME_NAME_PARTITION
ALTER TABLE t1 ADD PARTITION (PARTITION part3);
# Clash on all partition names
--error ER_SAME_NAME_PARTITION
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part2, PARTITION part3);
DROP TABLE t1;

# FIXME Is there any way to add a subpartition to an already existing partition

--echo # 5.2 alter table add subpartition
--echo # 5.2.1 Add one subpartition to a table with subpartitioning rule and
--echo #       no explicit defined subpartitions
eval CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1)
(PARTITION part1 VALUES LESS THAN ($max_row_div2));
if ($fixed_bug16370)
{
--source include/partition_layout.inc
}
eval INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND $max_row_div2 - 1;
eval ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN ($max_int_4)
    (SUBPARTITION subpart21));
if ($fixed_bug16370)
{
--source include/partition_12.inc
}
DROP TABLE t1;
DROP TABLE if exists t0_template;