The same counts for ALTER TABLE statements which recreate the whole tablespace:
ip-10-36-36-72 mysql>
CREATE TABLE partitionedtable
(
ID int unsigned not null auto_increment,
col varchar(32) null,
timer datetime not null,
PRIMARY KEY(ID,timer)
)
ENGINE=InnoDB
PARTITION BY RANGE(YEAR(timer)) (
PARTITION p2014 VALUES LESS THAN (2015) DATA DIRECTORY = '/tmp/partdb/2014',
PARTITION p2015 VALUES LESS THAN (2016) DATA DIRECTORY = '/tmp/partdb/2015',
PARTITION p2020 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/tmp/partdb/rest'
);
Query OK, 0 rows affected (0.07 sec)
ip-10-36-36-72 mysql> show create table partitionedtable\G
*************************** 1. row ***************************
Table: partitionedtable
Create Table: CREATE TABLE `partitionedtable` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col` varchar(32) DEFAULT NULL,
`timer` datetime NOT NULL,
PRIMARY KEY (`ID`,`timer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(timer))
(PARTITION p2014 VALUES LESS THAN (2015) DATA DIRECTORY = '/tmp/partdb/2014' ENGINE = InnoDB,
PARTITION p2015 VALUES LESS THAN (2016) DATA DIRECTORY = '/tmp/partdb/2015' ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/tmp/partdb/rest' ENGINE = InnoDB) */
1 row in set (0.00 sec)
[root@ip-10-36-36-72 partdb]# ls -alhs /var/lib/mysql/test/
total 316K
4.0K drwx------. 2 mysql mysql 4.0K Jan 16 13:21 .
4.0K drwxr-xr-x. 6 mysql mysql 4.0K Jan 16 10:28 ..
4.0K -rw-rw----. 1 mysql mysql 65 Jan 16 10:05 db.opt
96K -rw-rw----. 1 mysql mysql 96K Jan 16 13:21 partitionedtable#P#p2014.ibd
96K -rw-rw----. 1 mysql mysql 96K Jan 16 13:21 partitionedtable#P#p2015.ibd
96K -rw-rw----. 1 mysql mysql 96K Jan 16 13:21 partitionedtable#P#p2020.ibd
12K -rw-rw----. 1 mysql mysql 8.5K Jan 16 13:21 partitionedtable.frm
4.0K -rw-rw----. 1 mysql mysql 40 Jan 16 13:21 partitionedtable.par
The same counts for ALTER TABLE statements which recreate the whole tablespace:
ip-10-36-36-72 mysql>
DATA DIRECTORY = '/tmp/partdb/2014',
DATA DIRECTORY = '/tmp/partdb/2015',
DATA DIRECTORY = '/tmp/partdb/rest'
CREATE TABLE partitionedtable
(
ID int unsigned not null auto_increment,
col varchar(32) null,
timer datetime not null,
PRIMARY KEY(ID,timer)
)
ENGINE=InnoDB
PARTITION BY RANGE(YEAR(timer)) (
PARTITION p2014 VALUES LESS THAN (2015)
PARTITION p2015 VALUES LESS THAN (2016)
PARTITION p2020 VALUES LESS THAN MAXVALUE
);
Query OK, 0 rows affected (0.07 sec)
ip-10-36-36-72 mysql> show create table partitionedtable\G ******* ******* ****** 1. row ******* ******* ******* ******
*******
Table: partitionedtable
Create Table: CREATE TABLE `partitionedtable` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col` varchar(32) DEFAULT NULL,
`timer` datetime NOT NULL,
PRIMARY KEY (`ID`,`timer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(timer))
(PARTITION p2014 VALUES LESS THAN (2015) DATA DIRECTORY = '/tmp/partdb/2014' ENGINE = InnoDB,
PARTITION p2015 VALUES LESS THAN (2016) DATA DIRECTORY = '/tmp/partdb/2015' ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/tmp/partdb/rest' ENGINE = InnoDB) */
1 row in set (0.00 sec)
[root@ip- 10-36-36- 72 partdb]# find /tmp/partdb/ 2014/test 2014/test/ partitionedtabl e#P#p2014. ibd 2015/test 2015/test/ partitionedtabl e#P#p2015. ibd rest/test rest/test/ partitionedtabl e#P#p2020. ibd
/tmp/partdb/
/tmp/partdb/2014
/tmp/partdb/
/tmp/partdb/
/tmp/partdb/2015
/tmp/partdb/
/tmp/partdb/
/tmp/partdb/rest
/tmp/partdb/
/tmp/partdb/
ip-10-36-36-72 mysql> alter table partitionedtable add extracol varchar(32) null;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
[root@ip- 10-36-36- 72 partdb]# find /tmp/partdb/ 2014/test 2015/test rest/test
/tmp/partdb/
/tmp/partdb/2014
/tmp/partdb/
/tmp/partdb/2015
/tmp/partdb/
/tmp/partdb/rest
/tmp/partdb/
[root@ip- 10-36-36- 72 partdb]# ls -alhs /var/lib/ mysql/test/ e#P#p2014. ibd e#P#p2015. ibd e#P#p2020. ibd e.frm e.par
total 316K
4.0K drwx------. 2 mysql mysql 4.0K Jan 16 13:21 .
4.0K drwxr-xr-x. 6 mysql mysql 4.0K Jan 16 10:28 ..
4.0K -rw-rw----. 1 mysql mysql 65 Jan 16 10:05 db.opt
96K -rw-rw----. 1 mysql mysql 96K Jan 16 13:21 partitionedtabl
96K -rw-rw----. 1 mysql mysql 96K Jan 16 13:21 partitionedtabl
96K -rw-rw----. 1 mysql mysql 96K Jan 16 13:21 partitionedtabl
12K -rw-rw----. 1 mysql mysql 8.5K Jan 16 13:21 partitionedtabl
4.0K -rw-rw----. 1 mysql mysql 40 Jan 16 13:21 partitionedtabl