Partitioning tables ignores specified data directory and moves tables back to default data directory. This has serious consequences when there is a larger alternate mount bigger partitioned tables and not enough space to hold that data in the default data directory.
When tables are created with an individually specified data directory different from the default, this definition is lost during partitioning, and the engine re-creates the table back in the default data directory. Alter table operations allow the specification of a data directory with partitioning commands, but ignore the specified directory, showing a warning.
Other alter table operations, such as adding a column, do not have this problem.
create table partest(
|
id int(10) unsigned not null auto_increment,
|
val int(10) unsigned not null default 0,
|
primary key (id, val)
|
) engine=InnoDB
|
default charset=utf8mb4
|
data directory='/tmp/maria-partest/';
|
|
insert into partest(val) values(1);
|
insert into partest(val) values(1001);
|
|
~# ls -alh /tmp/maria-partest/test/
|
total 128K
|
drwxrwx--- 2 mysql mysql 25 Nov 16 23:01 .
|
drwxrwx--- 3 mysql mysql 18 Nov 16 22:55 ..
|
-rw-rw---- 1 mysql mysql 128K Nov 16 23:01 partest.ibd
|
|
~# ls -alh /var/lib/mysql/test/
|
total 12K
|
drwx------ 2 mysql mysql 58 Nov 16 23:22 .
|
drwxr-xr-x 5 mysql mysql 289 Nov 16 22:49 ..
|
-rw-rw---- 1 mysql mysql 65 Nov 16 21:39 db.opt
|
-rw-rw---- 1 mysql mysql 947 Nov 16 23:22 partest.frm
|
-rw-rw---- 1 mysql mysql 35 Nov 16 23:22 partest.isl
|
|
alter table partest partition by range(val)(
|
partition p0 values less than (1000),
|
partition p1 values less than (2000),
|
partition pFFFF values less than maxvalue
|
);
|
|
~# ls -alh /tmp/maria-partest/test/
|
total 0
|
drwxrwx--- 2 mysql mysql 6 Nov 16 23:09 .
|
drwxrwx--- 3 mysql mysql 18 Nov 16 22:55 ..
|
|
~# ls -alh /var/lib/mysql/test/
|
total 300K
|
drwx------ 2 mysql mysql 133 Nov 16 23:11 .
|
drwxr-xr-x 5 mysql mysql 289 Nov 16 22:49 ..
|
-rw-rw---- 1 mysql mysql 65 Nov 16 21:39 db.opt
|
-rw-rw---- 1 mysql mysql 1.2K Nov 16 23:11 partest.frm
|
-rw-rw---- 1 mysql mysql 44 Nov 16 23:11 partest.par
|
-rw-rw---- 1 mysql mysql 96K Nov 16 23:09 partest#P#p0.ibd
|
-rw-rw---- 1 mysql mysql 96K Nov 16 23:09 partest#P#p1.ibd
|
-rw-rw---- 1 mysql mysql 96K Nov 16 23:09 partest#P#pFFFF.ibd
|
Bug
Critical
MDEV-28108 add partition should not reset data directory back to default
