Description:
When an ALTER TABLE ... ENGINE=<new engine> statement is performed, the partition info is dropped from the table definition
How to repeat:
mysql> CREATE TABLE part2
-> ( column1 date default NULL,
-> column2 int(11) default NULL
-> )
-> DEFAULT CHARSET=latin1
-> PARTITION BY RANGE (year(column1)) (PARTITION p0 VALUES LESS THAN (1995),
-> PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
-> PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
-> PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
-> PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
-> PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010) );
Query OK, 0 rows affected (0.02 sec)
mysql> show create table part2\G
*************************** 1. row ***************************
Table: part2
Create Table: CREATE TABLE `part2` (
`column1` date default NULL,
`column2` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(column1)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE =
MyISAM, PARTITION p1 VALUES LESS THAN (1996) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (1997) ENGINE = MyISAM, PAR
TITION p3 VALUES LESS THAN (1998) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (1999) ENGINE = MyISAM, PARTITION p5 VA
LUES LESS THAN (2000) ENGINE = MyISAM, PARTITION p6 VALUES LESS THAN (2001) ENGINE = MyISAM, PARTITION p7 VALUES LESS TH
AN (2002) ENGINE = MyISAM, PARTITION p8 VALUES LESS THAN (2003) ENGINE = MyISAM, PARTITION p9 VALUES LESS THAN (2004) EN
GINE = MyISAM, PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM)
1 row in set (0.00 sec)
mysql> alter table part2 engine=innodb;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table part2\G
*************************** 1. row ***************************
Table: part2
Create Table: CREATE TABLE `part2` (
`column1` date default NULL,
`column2` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Description: When an ALTER TABLE ... ENGINE=<new engine> statement is performed, the partition info is dropped from the table definition How to repeat: mysql> CREATE TABLE part2 -> ( column1 date default NULL, -> column2 int(11) default NULL -> ) -> DEFAULT CHARSET=latin1 -> PARTITION BY RANGE (year(column1)) (PARTITION p0 VALUES LESS THAN (1995), -> PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , -> PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , -> PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , -> PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , -> PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010) ); Query OK, 0 rows affected (0.02 sec) mysql> show create table part2\G *************************** 1. row *************************** Table: part2 Create Table: CREATE TABLE `part2` ( `column1` date default NULL, `column2` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(column1)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1996) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (1997) ENGINE = MyISAM, PAR TITION p3 VALUES LESS THAN (1998) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (1999) ENGINE = MyISAM, PARTITION p5 VA LUES LESS THAN (2000) ENGINE = MyISAM, PARTITION p6 VALUES LESS THAN (2001) ENGINE = MyISAM, PARTITION p7 VALUES LESS TH AN (2002) ENGINE = MyISAM, PARTITION p8 VALUES LESS THAN (2003) ENGINE = MyISAM, PARTITION p9 VALUES LESS THAN (2004) EN GINE = MyISAM, PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM) 1 row in set (0.00 sec) mysql> alter table part2 engine=innodb; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table part2\G *************************** 1. row *************************** Table: part2 Create Table: CREATE TABLE `part2` ( `column1` date default NULL, `column2` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)