[#MDEV-27065] Partitioning tables with custom data directories moves data back to default directory

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

[MDEV-27065] Partitioning tables with custom data directories moves data back to default directory Created: 2021-11-16 Updated: 2022-10-31 Resolved: 2022-04-08
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Definition - Alter Table, Partitioning
Affects Version/s:	10.4.22, 10.5.13, 10.6.5, 10.2, 10.3, 10.7, 10.8
Fix Version/s:	10.2.44, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3

[MDEV-27065] Partitioning tables with custom data directories moves data back to default directory Created: 2021-11-16 Updated: 2022-10-31 Resolved: 2022-04-08