partition_02myisam.result

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

)' at line 7
# 3.3.4 (negative) number of partitions < number of named partitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ;
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2
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)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPAR' at line 5
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)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2000)
(SUBPARTITION subpart21              ' at line 5
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)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPAR' at line 5

#------------------------------------------------------------------------
#  4. Checks of logical partition/subpartition name
#     file name clashes during CREATE TABLE
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
#  4.1 (negative) A partition name used more than once
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) (PARTITION part1, PARTITION part1);
ERROR HY000: Duplicate partition name part1
# FIXME Implement testcases with filename problems
#       existing file of other table --- partition/subpartition file name
#       partition/subpartition file name  --- file of the same table

#------------------------------------------------------------------------
#  5. Alter table experiments
#------------------------------------------------------------------------
DROP TABLE IF EXISTS t1;
# 5.1 alter table add partition
# 5.1.1 (negative) add partition to non partitioned table
CREATE TABLE t1 ( f1 INTEGER, f2 char(20));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
Got one of the listed errors
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
# 5.1.2 Add one partition to a table with one partition
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) 
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
ALTER TABLE t1 ADD PARTITION (PARTITION part1);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION p0  ENGINE = MyISAM, PARTITION part1  ENGINE = MyISAM)
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 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);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM)
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
ALTER TABLE t1 ADD PARTITION (PARTITION part0);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM, PARTITION part0  ENGINE = MyISAM)
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DELETE FROM t1;
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
ALTER TABLE t1 ADD PARTITION (PARTITION part2);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM, PARTITION part0  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DELETE FROM t1;
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM, PARTITION part0  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 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);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM)
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 100 - 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part3  ENGINE = MyISAM)
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 100 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
# 5.1.5 (negative) Add partitions to a table with some partitions
#       clash on new and already existing partition names
CREATE TA