#At file:///C:/ade/mysql-bzr/b11891417-trunk/ based on revid:georgi.kodinov@stripped
3303 Mattias Jonsson 2011-03-23
Bug#11891417 - MAIN SUITE PARTITIONS TESTS FAILING WITH INNODB ENGINE
Several test depended on MyISAM engine, without explicitly setting it to MyISAM,
resulting in failures/differences when using InnoDB as default engine.
Fixed by hardcode the engine to MyISAM if test is MyISAM specific.
Also replaced InnoDB/MyISAM with <curr_engine> in the result.
Also renamed partition_not_embedded to partition_myisam,
since it now works with embedded and is myisam only.
Also moved the pure MyISAM tests there.
renamed:
mysql-test/r/partition_not_embedded.result => mysql-test/r/partition_myisam.result
mysql-test/t/partition_not_embedded.test => mysql-test/t/partition_myisam.test
modified:
mysql-test/r/partition_error.result
mysql-test/r/partition_symlink.result
mysql-test/t/partition_error.test
mysql-test/t/partition_symlink.test
mysql-test/r/partition_myisam.result
mysql-test/t/partition_myisam.test
=== modified file 'mysql-test/r/partition_error.result'
--- a/mysql-test/r/partition_error.result 2011-03-11 09:35:38 +0000
+++ b/mysql-test/r/partition_error.result 2011-03-23 15:27:39 +0000
@@ -720,95 +720,8 @@ DROP TABLE t1;
CREATE TABLE t1 (c TIMESTAMP)
PARTITION BY HASH (c) PARTITIONS 4;
ERROR HY000: Field 'c' is of a not allowed type for this type of partitioning
+# Moved to partition_myisam, since it was MyISAM specific
# Added test with existing TIMESTAMP partitioning (when it was allowed).
-CREATE TABLE t1 (a TIMESTAMP)
-PARTITION BY HASH (UNIX_TIMESTAMP(a));
-INSERT INTO t1 VALUES ('2000-01-02 03:04:05');
-SELECT * FROM t1;
-a
-2000-01-02 03:04:05
-FLUSH TABLES;
-# replacing t1.frm with TO_DAYS(a) which was allowed earlier.
-# Disable warnings, since the result would differ when running with
-# --ps-protocol (only for the 'SELECT * FROM t1' statement).
-SELECT * FROM t1;
-a
-2000-01-02 03:04:05
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY HASH (TO_DAYS(a)) */
-INSERT INTO t1 VALUES ('2001-02-03 04:05:06');
-SELECT * FROM t1;
-a
-2000-01-02 03:04:05
-2001-02-03 04:05:06
-ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
-Warnings:
-Warning 1486 Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
-ALTER TABLE t1
-PARTITION BY RANGE (TO_DAYS(a))
-(PARTITION p0 VALUES LESS THAN (10000),
-PARTITION p1 VALUES LESS THAN (MAXVALUE));
-ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY HASH (TO_DAYS(a))
-PARTITIONS 3 */
-CREATE TABLE t2 LIKE t1;
-SHOW CREATE TABLE t2;
-Table Create Table
-t2 CREATE TABLE `t2` (
- `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY HASH (TO_DAYS(a))
-PARTITIONS 3 */
-Warnings:
-Warning 1486 Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
-DROP TABLE t2;
-CREATE TABLE t2 SELECT * FROM t1;
-DROP TABLE t2;
-ALTER TABLE t1 PARTITION BY HASH (UNIX_TIMESTAMP(a));
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a)) */
-ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a))
-PARTITIONS 3 */
-SELECT * FROM t1;
-a
-2000-01-02 03:04:05
-2001-02-03 04:05:06
-DROP TABLE t1;
-#
-# Bug#49161: Out of memory; restart server and try again (needed 2 bytes)
-#
-CREATE TABLE t1 (a INT) PARTITION BY HASH (a);
-FLUSH TABLES;
-CHECK TABLE t1;
-Table Op Msg_type Msg_text
-test.t1 check Error Failed to read from the .par file
-test.t1 check Error Incorrect information in file: './test/t1.frm'
-test.t1 check error Corrupt
-SELECT * FROM t1;
-ERROR HY000: Failed to read from the .par file
-# Note that it is currently impossible to drop a partitioned table
-# without the .par file
-DROP TABLE t1;
-ERROR 42S02: Unknown table 'test.t1'
#
# Bug#49477: Assertion `0' failed in ha_partition.cc:5530
# with temporary table and partitions
@@ -841,10 +754,10 @@ Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`purchased` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=<curr_engine> DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(purchased))
SUBPARTITION BY HASH (TO_DAYS(purchased))
-(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = <curr_engine>) */
DROP TABLE t1;
CREATE TABLE t1 (id INT, purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
@@ -862,12 +775,12 @@ Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`purchased` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=<curr_engine> DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(purchased))
SUBPARTITION BY HASH (TO_DAYS(purchased))
(PARTITION p0 VALUES LESS THAN MAXVALUE
- (SUBPARTITION sp0 ENGINE = MyISAM,
- SUBPARTITION sp1 ENGINE = MyISAM)) */
+ (SUBPARTITION sp0 ENGINE = <curr_engine>,
+ SUBPARTITION sp1 ENGINE = <curr_engine>)) */
DROP TABLE t1;
CREATE TABLE t1 (id INT, purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
@@ -882,53 +795,11 @@ Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`purchased` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=<curr_engine> DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(purchased))
-(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = <curr_engine>) */
DROP TABLE t1;
SET @@sql_mode= @org_mode;
-#
-# Bug#50392: insert_id is not reset for partitioned tables
-# auto_increment on duplicate entry
-CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
-SET INSERT_ID= 13;
-INSERT INTO t1 VALUES (NULL);
-SET INSERT_ID= 12;
-INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
-ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `a` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`a`)
-) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1;
-a
-12
-13
-14
-DROP TABLE t1;
-CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) PARTITION BY KEY(a);
-SET INSERT_ID= 13;
-INSERT INTO t1 VALUES (NULL);
-SET INSERT_ID= 12;
-INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
-ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `a` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`a`)
-) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
-/*!50100 PARTITION BY KEY (a) */
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1;
-a
-12
-13
-14
-DROP TABLE t1;
CREATE TABLE t1 (a INTEGER NOT NULL, PRIMARY KEY (a));
INSERT INTO t1 VALUES (1),(1);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
@@ -938,19 +809,6 @@ PARTITION BY KEY (a) PARTITIONS 2;
INSERT INTO t1 VALUES (1),(1);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
DROP TABLE t1;
-CREATE TABLE t1 (a INT)
-PARTITION BY HASH (a)
-( PARTITION p0 ENGINE=MyISAM,
-PARTITION p1);
-ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
-CREATE TABLE t1 (a INT)
-PARTITION BY LIST (a)
-SUBPARTITION BY HASH (a)
-( PARTITION p0 VALUES IN (0)
-( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2),
-PARTITION p1 VALUES IN (1)
-( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM));
-ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
CREATE TABLE t1 (
a int
)
=== renamed file 'mysql-test/r/partition_not_embedded.result' => 'mysql-test/r/partition_myisam.result'
--- a/mysql-test/r/partition_not_embedded.result 2009-07-27 11:20:43 +0000
+++ b/mysql-test/r/partition_myisam.result 2011-03-23 15:27:39 +0000
@@ -1,6 +1,166 @@
DROP TABLE IF EXISTS t1, t2;
+#
+# Bug#50036: Inconsistent errors when using TIMESTAMP
+# columns/expressions
+# Added test with existing TIMESTAMP partitioning (when it was allowed).
+CREATE TABLE t1 (a TIMESTAMP)
+ENGINE = MyISAM
+PARTITION BY HASH (UNIX_TIMESTAMP(a));
+INSERT INTO t1 VALUES ('2000-01-02 03:04:05');
+SELECT * FROM t1;
+a
+2000-01-02 03:04:05
+FLUSH TABLES;
+# replacing t1.frm with TO_DAYS(a) which was allowed earlier.
+# Disable warnings, since the result would differ when running with
+# --ps-protocol (only for the 'SELECT * FROM t1' statement).
+SELECT * FROM t1;
+a
+2000-01-02 03:04:05
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+) ENGINE=<curr_engine> DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (TO_DAYS(a)) */
+INSERT INTO t1 VALUES ('2001-02-03 04:05:06');
+SELECT * FROM t1;
+a
+2000-01-02 03:04:05
+2001-02-03 04:05:06
+ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
+Warnings:
+Warning 1486 Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+ALTER TABLE t1
+PARTITION BY RANGE (TO_DAYS(a))
+(PARTITION p0 VALUES LESS THAN (10000),
+PARTITION p1 VALUES LESS THAN (MAXVALUE));
+ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (TO_DAYS(a))
+PARTITIONS 3 */
+CREATE TABLE t2 LIKE t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (TO_DAYS(a))
+PARTITIONS 3 */
+Warnings:
+Warning 1486 Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
+DROP TABLE t2;
+CREATE TABLE t2 SELECT * FROM t1;
+DROP TABLE t2;
+ALTER TABLE t1 PARTITION BY HASH (UNIX_TIMESTAMP(a));
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a)) */
+ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a))
+PARTITIONS 3 */
+SELECT * FROM t1;
+a
+2000-01-02 03:04:05
+2001-02-03 04:05:06
+DROP TABLE t1;
+#
+# Bug#31931: Mix of handlers error message
+#
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (a)
+( PARTITION p0 ENGINE=MyISAM,
+PARTITION p1);
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
+CREATE TABLE t1 (a INT)
+PARTITION BY LIST (a)
+SUBPARTITION BY HASH (a)
+( PARTITION p0 VALUES IN (0)
+( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2),
+PARTITION p1 VALUES IN (1)
+( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM));
+ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MySQL
+#
+# Bug#49161: Out of memory; restart server and try again (needed 2 bytes)
+#
+CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
+PARTITION BY HASH (a);
+FLUSH TABLES;
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 check Error Failed to read from the .par file
+test.t1 check Error Incorrect information in file: './test/t1.frm'
+test.t1 check error Corrupt
+SELECT * FROM t1;
+ERROR HY000: Failed to read from the .par file
+# Note that it is currently impossible to drop a partitioned table
+# without the .par file
+DROP TABLE t1;
+ERROR 42S02: Unknown table 'test.t1'
+#
+# Bug#50392: insert_id is not reset for partitioned tables
+# auto_increment on duplicate entry
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY)
+ENGINE = MyISAM;
+SET INSERT_ID= 13;
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID= 12;
+# For transactional engines, 12 will not be inserted, since the failing
+# statement is rolled back.
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+# NOTE: 12 exists only in non transactional engines!
+SELECT * FROM t1;
+a
+12
+13
+14
+DROP TABLE t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY)
+ENGINE = MyISAM
+PARTITION BY KEY(a);
+SET INSERT_ID= 13;
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID= 12;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY KEY (a) */
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+a
+12
+13
+14
+DROP TABLE t1;
# Bug#30102 test
CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (6),
PARTITION `p1....................` VALUES LESS THAN (9),
=== modified file 'mysql-test/r/partition_symlink.result'
--- a/mysql-test/r/partition_symlink.result 2010-06-03 21:00:55 +0000
+++ b/mysql-test/r/partition_symlink.result 2011-03-23 15:27:39 +0000
@@ -4,6 +4,7 @@ DROP DATABASE IF EXISTS mysqltest2;
# Test for WL#4445: EXCHANGE PARTITION
#
CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
@@ -13,6 +14,7 @@ DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p2 VALUES IN (2));
CREATE TABLE t2 (a INT)
+ENGINE = MyISAM
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp';
SHOW CREATE TABLE t1;
@@ -43,11 +45,12 @@ DROP TABLE t1, t2;
CREATE USER mysqltest_1@localhost;
CREATE DATABASE mysqltest2;
USE mysqltest2;
-CREATE TABLE t1 (a INT);
+CREATE TABLE t1 (a INT) ENGINE = MyISAM;
INSERT INTO t1 VALUES (0);
# user mysqltest_1:
USE test;
CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
@@ -84,6 +87,7 @@ DROP DATABASE mysqltest2;
CREATE DATABASE mysqltest2;
USE mysqltest2;
CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
@@ -95,6 +99,7 @@ DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
# user mysqltest_1:
USE test;
CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
@@ -105,6 +110,7 @@ DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
);
Got one of the listed errors
CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
@@ -119,6 +125,7 @@ DROP DATABASE mysqltest2;
USE test;
DROP USER mysqltest_1@localhost;
create table t2 (i int )
+ENGINE = MyISAM
partition by range (i)
(
partition p01 values less than (1000)
@@ -131,6 +138,7 @@ select @@sql_mode;
@@sql_mode
NO_DIR_IN_CREATE
create table t1 (i int )
+ENGINE = MyISAM
partition by range (i)
(
partition p01 values less than (1000)
@@ -150,10 +158,12 @@ t2 CREATE TABLE `t2` (
DROP TABLE t1, t2;
set @@sql_mode=@org_mode;
create table t1 (a int)
+ENGINE = MyISAM
partition by key (a)
(partition p0 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data');
Got one of the listed errors
create table t1 (a int)
+ENGINE = MyISAM
partition by key (a)
(partition p0,
partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data');
=== modified file 'mysql-test/t/partition_error.test'
--- a/mysql-test/t/partition_error.test 2011-03-11 09:35:38 +0000
+++ b/mysql-test/t/partition_error.test 2011-03-23 15:27:39 +0000
@@ -752,65 +752,9 @@ DROP TABLE t1;
CREATE TABLE t1 (c TIMESTAMP)
PARTITION BY HASH (c) PARTITIONS 4;
+--echo # Moved to partition_myisam, since it was MyISAM specific
--echo # Added test with existing TIMESTAMP partitioning (when it was allowed).
-CREATE TABLE t1 (a TIMESTAMP)
-PARTITION BY HASH (UNIX_TIMESTAMP(a));
-INSERT INTO t1 VALUES ('2000-01-02 03:04:05');
---sorted_result
-SELECT * FROM t1;
-FLUSH TABLES;
---echo # replacing t1.frm with TO_DAYS(a) which was allowed earlier.
---remove_file $MYSQLD_DATADIR/test/t1.frm
---copy_file std_data/parts/t1TIMESTAMP.frm $MYSQLD_DATADIR/test/t1.frm
---echo # Disable warnings, since the result would differ when running with
---echo # --ps-protocol (only for the 'SELECT * FROM t1' statement).
---disable_warnings
---sorted_result
-SELECT * FROM t1;
---enable_warnings
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('2001-02-03 04:05:06');
---sorted_result
-SELECT * FROM t1;
-ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
---error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
-ALTER TABLE t1
-PARTITION BY RANGE (TO_DAYS(a))
-(PARTITION p0 VALUES LESS THAN (10000),
- PARTITION p1 VALUES LESS THAN (MAXVALUE));
-SHOW CREATE TABLE t1;
-CREATE TABLE t2 LIKE t1;
-SHOW CREATE TABLE t2;
-DROP TABLE t2;
-CREATE TABLE t2 SELECT * FROM t1;
-DROP TABLE t2;
-ALTER TABLE t1 PARTITION BY HASH (UNIX_TIMESTAMP(a));
-SHOW CREATE TABLE t1;
-ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
-SHOW CREATE TABLE t1;
---sorted_result
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # Bug#49161: Out of memory; restart server and try again (needed 2 bytes)
---echo #
-CREATE TABLE t1 (a INT) PARTITION BY HASH (a);
-FLUSH TABLES;
---remove_file $MYSQLD_DATADIR/test/t1.par
---replace_result $MYSQLD_DATADIR ./
-CHECK TABLE t1;
---error ER_FAILED_READ_FROM_PAR_FILE
-SELECT * FROM t1;
---echo # Note that it is currently impossible to drop a partitioned table
---echo # without the .par file
---error ER_BAD_TABLE_ERROR
-DROP TABLE t1;
---remove_file $MYSQLD_DATADIR/test/t1.frm
---remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYI
---remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYD
-
--echo #
--echo # Bug#49477: Assertion `0' failed in ha_partition.cc:5530
--echo # with temporary table and partitions
@@ -831,6 +775,7 @@ SUBPARTITION BY HASH(TO_DAYS(purchased))
(PARTITION p0 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '/tmp/not-existing'
INDEX DIRECTORY = '/tmp/not-existing');
+--replace_result MyISAM <curr_engine> InnoDB <curr_engine>
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (id INT, purchased DATE)
@@ -841,6 +786,7 @@ SUBPARTITION BY HASH(TO_DAYS(purchased))
DATA DIRECTORY = '/tmp/not-existing'
INDEX DIRECTORY = '/tmp/not-existing',
SUBPARTITION sp1));
+--replace_result MyISAM <curr_engine> InnoDB <curr_engine>
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (id INT, purchased DATE)
@@ -848,34 +794,11 @@ PARTITION BY RANGE(YEAR(purchased))
(PARTITION p0 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '/tmp/not-existing'
INDEX DIRECTORY = '/tmp/not-existing');
+--replace_result MyISAM <curr_engine> InnoDB <curr_engine>
SHOW CREATE TABLE t1;
DROP TABLE t1;
SET @@sql_mode= @org_mode;
---echo #
---echo # Bug#50392: insert_id is not reset for partitioned tables
---echo # auto_increment on duplicate entry
-CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
-SET INSERT_ID= 13;
-INSERT INTO t1 VALUES (NULL);
-SET INSERT_ID= 12;
---error ER_DUP_ENTRY
-INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1;
-DROP TABLE t1;
-CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) PARTITION BY KEY(a);
-SET INSERT_ID= 13;
-INSERT INTO t1 VALUES (NULL);
-SET INSERT_ID= 12;
---error ER_DUP_ENTRY
-INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1;
-DROP TABLE t1;
-
#
# Bug#38719: Partitioning returns a different error code for a
# duplicate key error
@@ -890,24 +813,6 @@ INSERT INTO t1 VALUES (1),(1);
DROP TABLE t1;
#
-# Bug#31931: Mix of handlers error message
-#
---error ER_MIX_HANDLER_ERROR
-CREATE TABLE t1 (a INT)
-PARTITION BY HASH (a)
-( PARTITION p0 ENGINE=MyISAM,
- PARTITION p1);
---error ER_MIX_HANDLER_ERROR
-CREATE TABLE t1 (a INT)
-PARTITION BY LIST (a)
-SUBPARTITION BY HASH (a)
-( PARTITION p0 VALUES IN (0)
-( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2),
- PARTITION p1 VALUES IN (1)
-( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM));
-
-
-#
# Bug 29368:
# Incorrect error, 1467, for syntax error when creating partition
--error ER_PARTITION_REQUIRES_VALUES_ERROR
@@ -923,7 +828,7 @@ PARTITION BY RANGE (a)
#
# Partition by key stand-alone error
#
---error 1064
+--error ER_PARSE_ERROR
partition by list (a)
partitions 3
(partition x1 values in (1,2,9,4) tablespace ts1,
@@ -960,7 +865,7 @@ partitions 3
#
# Partition by key, partition function not allowed
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -975,7 +880,7 @@ partitions 3
#
# Partition by key, no partition name
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1023,7 +928,7 @@ select load_file('$MYSQLD_DATADIR/test/t
#
# Partition by hash, invalid field in function
#
---error 1054
+--error ER_BAD_FIELD_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1053,7 +958,7 @@ partitions 3
#
# Partition by key specified 3 partitions but only defined 2 => error
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1066,7 +971,7 @@ partitions 3
#
# Partition by hash, random function
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1079,7 +984,7 @@ partitions 2
#
# Partition by range, random function
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1092,7 +997,7 @@ partitions 2
#
# Partition by list, random function
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1228,7 +1133,7 @@ select load_file('$MYSQLD_DATADIR/test/t
#
# Subpartition by hash, no partitions defined, wrong subpartition function
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1255,7 +1160,7 @@ select load_file('$MYSQLD_DATADIR/test/t
#
# Subpartition by hash, no partitions defined, wrong subpartition function
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1296,7 +1201,7 @@ subpartition by hash (3+4);
#
# Subpartition by hash, no partitions defined, wrong subpartition function
#
---error 1054
+--error ER_BAD_FIELD_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1322,7 +1227,7 @@ select load_file('$MYSQLD_DATADIR/test/t
#
# Partition by range, invalid field in function
#
---error 1054
+--error ER_BAD_FIELD_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1476,7 +1381,7 @@ partitions 2
#
# Subpartition by hash, wrong number of subpartitions
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1496,7 +1401,7 @@ subpartitions 3
#
# Subpartition by hash, wrong number of subpartitions
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1516,7 +1421,7 @@ subpartition by hash (a+b)
#
# Subpartition by list => error
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1535,7 +1440,7 @@ subpartition by list (a+b)
#
# Subpartition by list => error
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1579,7 +1484,7 @@ partitions 2
#
# Partition by list, invalid field in function
#
---error 1054
+--error ER_BAD_FIELD_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1663,7 +1568,7 @@ partitions 2
#
# Partition by list, missing parenthesis
#
---error 1064
+--error ER_PARSE_ERROR
CREATE TABLE t1 (
a int not null,
b int not null,
@@ -1677,7 +1582,7 @@ partitions 2
#
# Bug #13439: Crash when LESS THAN (non-literal)
#
---error 1054
+--error ER_BAD_FIELD_ERROR
CREATE TABLE t1 (a int)
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (x1));
@@ -1704,13 +1609,13 @@ partition by range (ascii(v))
(partition p0 values less than (10));
#drop table t1;
--- error 1064
+-- error ER_PARSE_ERROR
create table t1 (a int)
partition by hash (rand(a));
--- error 1064
+-- error ER_PARSE_ERROR
create table t1 (a int)
partition by hash(CURTIME() + a);
--- error 1064
+-- error ER_PARSE_ERROR
create table t1 (a int)
partition by hash (NOW()+a);
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
=== renamed file 'mysql-test/t/partition_not_embedded.test' => 'mysql-test/t/partition_myisam.test'
--- a/mysql-test/t/partition_not_embedded.test 2009-07-27 11:20:43 +0000
+++ b/mysql-test/t/partition_myisam.test 2011-03-23 15:27:39 +0000
@@ -1,15 +1,135 @@
-- source include/have_partition.inc
--- source include/not_embedded.inc
+
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings
+
+# These tests is only useful when running on MyISAM,
+# due to DATA/INDEX directory, non transactional behavior, tests with MyISAM
+# files etc.
+
let $MYSQLD_DATADIR= `SELECT @@datadir`;
+
+--echo #
+--echo # Bug#50036: Inconsistent errors when using TIMESTAMP
+--echo # columns/expressions
+
+--echo # Added test with existing TIMESTAMP partitioning (when it was allowed).
+CREATE TABLE t1 (a TIMESTAMP)
+ENGINE = MyISAM
+PARTITION BY HASH (UNIX_TIMESTAMP(a));
+INSERT INTO t1 VALUES ('2000-01-02 03:04:05');
+--sorted_result
+SELECT * FROM t1;
+FLUSH TABLES;
+--echo # replacing t1.frm with TO_DAYS(a) which was allowed earlier.
+--remove_file $MYSQLD_DATADIR/test/t1.frm
+--copy_file std_data/parts/t1TIMESTAMP.frm $MYSQLD_DATADIR/test/t1.frm
+--echo # Disable warnings, since the result would differ when running with
+--echo # --ps-protocol (only for the 'SELECT * FROM t1' statement).
+--disable_warnings
+--sorted_result
+SELECT * FROM t1;
+--enable_warnings
+--replace_result MyISAM <curr_engine> InnoDB <curr_engine>
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES ('2001-02-03 04:05:06');
+--sorted_result
+SELECT * FROM t1;
+ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
+--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
+ALTER TABLE t1
+PARTITION BY RANGE (TO_DAYS(a))
+(PARTITION p0 VALUES LESS THAN (10000),
+ PARTITION p1 VALUES LESS THAN (MAXVALUE));
+SHOW CREATE TABLE t1;
+CREATE TABLE t2 LIKE t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+CREATE TABLE t2 SELECT * FROM t1;
+DROP TABLE t2;
+ALTER TABLE t1 PARTITION BY HASH (UNIX_TIMESTAMP(a));
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Bug#31931: Mix of handlers error message
+--echo #
+--error ER_MIX_HANDLER_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY HASH (a)
+( PARTITION p0 ENGINE=MyISAM,
+ PARTITION p1);
+--error ER_MIX_HANDLER_ERROR
+CREATE TABLE t1 (a INT)
+PARTITION BY LIST (a)
+SUBPARTITION BY HASH (a)
+( PARTITION p0 VALUES IN (0)
+( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2),
+ PARTITION p1 VALUES IN (1)
+( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM));
+
+--echo #
+--echo # Bug#49161: Out of memory; restart server and try again (needed 2 bytes)
+--echo #
+CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
+PARTITION BY HASH (a);
+FLUSH TABLES;
+--remove_file $MYSQLD_DATADIR/test/t1.par
+--replace_result $MYSQLD_DATADIR ./
+CHECK TABLE t1;
+--error ER_FAILED_READ_FROM_PAR_FILE
+SELECT * FROM t1;
+--echo # Note that it is currently impossible to drop a partitioned table
+--echo # without the .par file
+--error ER_BAD_TABLE_ERROR
+DROP TABLE t1;
+--remove_file $MYSQLD_DATADIR/test/t1.frm
+--remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYI
+--remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYD
+
+--echo #
+--echo # Bug#50392: insert_id is not reset for partitioned tables
+--echo # auto_increment on duplicate entry
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY)
+ENGINE = MyISAM;
+SET INSERT_ID= 13;
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID= 12;
+--echo # For transactional engines, 12 will not be inserted, since the failing
+--echo # statement is rolled back.
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+--echo # NOTE: 12 exists only in non transactional engines!
+SELECT * FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY)
+ENGINE = MyISAM
+PARTITION BY KEY(a);
+SET INSERT_ID= 13;
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID= 12;
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+DROP TABLE t1;
#
# Bug#30102: rename table does corrupt tables with partition files on failure
#
--echo # Bug#30102 test
CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (6),
PARTITION `p1....................` VALUES LESS THAN (9),
=== modified file 'mysql-test/t/partition_symlink.test'
--- a/mysql-test/t/partition_symlink.test 2010-06-03 21:00:55 +0000
+++ b/mysql-test/t/partition_symlink.test 2011-03-23 15:27:39 +0000
@@ -1,5 +1,6 @@
# Test that must have symlink. eg. using DATA/INDEX DIR
# (DATA/INDEX DIR requires symlinks)
+# This test is only useful for MyISAM, since no other engine supports DATA DIR
-- source include/have_partition.inc
-- source include/have_symlink.inc
# remove the not_windows line after fixing bug#33687
@@ -16,6 +17,7 @@ DROP DATABASE IF EXISTS mysqltest2;
--echo #
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval CREATE TABLE t1 (a INT)
+ENGINE = MyISAM
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (0)
DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp'
@@ -27,6 +29,7 @@ PARTITION BY LIST (a)
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval CREATE TABLE t2 (a INT)
+ENGINE = MyISAM
DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp'
INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp';
@@ -73,13 +76,14 @@ DROP TABLE t1, t2;
CREATE USER mysqltest_1@localhost;
CREATE DATABASE mysqltest2;
USE mysqltest2;
- CREATE TABLE t1 (a INT);
+ CREATE TABLE t1 (a INT) ENGINE = MyISAM;
INSERT INTO t1 VALUES (0);
connect(con1,localhost,mysqltest_1,,);
-- echo # user mysqltest_1:
USE test;
-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval CREATE TABLE t1 (a INT)
+ ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp'
@@ -122,6 +126,7 @@ connection default;
USE mysqltest2;
-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval CREATE TABLE t1 (a INT)
+ ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp'
@@ -136,6 +141,7 @@ connection con1;
-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
-- error 1,1
eval CREATE TABLE t1 (a INT)
+ ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp'
@@ -147,6 +153,7 @@ connection con1;
-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
-- error 1,1
eval CREATE TABLE t1 (a INT)
+ ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp'
@@ -168,6 +175,7 @@ connection default;
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval create table t2 (i int )
+ENGINE = MyISAM
partition by range (i)
(
partition p01 values less than (1000)
@@ -179,6 +187,7 @@ set @org_mode=@@sql_mode;
set @@sql_mode='NO_DIR_IN_CREATE';
select @@sql_mode;
create table t1 (i int )
+ENGINE = MyISAM
partition by range (i)
(
partition p01 values less than (1000)
@@ -197,6 +206,7 @@ set @@sql_mode=@org_mode;
# Added ER_WRONG_TABLE_NAME and reported bug#39045
-- error ER_WRONG_ARGUMENTS, ER_WRONG_TABLE_NAME
create table t1 (a int)
+ENGINE = MyISAM
partition by key (a)
(partition p0 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data');
@@ -207,6 +217,7 @@ partition by key (a)
# Added ER_WRONG_TABLE_NAME and reported bug#39045
--error ER_WRONG_ARGUMENTS, ER_WRONG_TABLE_NAME
create table t1 (a int)
+ENGINE = MyISAM
partition by key (a)
(partition p0,
partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data');
Attachment: [text/bzr-bundle] bzr/mattias.jonsson@oracle.com-20110323152739-97609y3yvavh68g5.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (mattias.jonsson:3303) Bug#11891417 | Mattias Jonsson | 23 Mar |
