Description:
Hello folks!
When trying to add a partition with a different DATA DIRECTORY (X) than the other partitions (which already had a non-default DATA DIRECTORY (Z)), the existing partitions from Z are moved to the default datadir.
Needless to explain this can wreck havoc as it can fill up the default datadir, and also the move operation can take very long times, blocking the table... etc.
How to repeat:
This one is super simple to reproduce; I tested on 8.0.32:
dbdeployer deploy 8.0.32;
mkdir ~/sanboxes/msb_8_0_32/{extradir,moardir};
echo "innodb_directories='${HOME}/sanboxes/msb_8_0_32/extradir;${HOME}/sanboxes/msb_8_0_32/extradir'" >> ${HOME}/sanboxes/msb_8_0_32/my.sandbox.cnf
~/sanboxes/msb_8_0_32/use test;
Then create a table that uses data directory 'extradir'
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/home/marcos.albe/sandboxes/msb_8_0_32/extradir' PARTITION BY HASH ( c1 ) PARTITIONS 10;
And you can see the files are where we expect them to be:
mysql> SYSTEM ls extradir/test
t1#p#p0.ibd t1#p#p1.ibd t1#p#p2.ibd t1#p#p3.ibd t1#p#p4.ibd t1#p#p5.ibd t1#p#p6.ibd t1#p#p7.ibd t1#p#p8.ibd t1#p#p9.ibd
And then add a partition to the second directory 'moardir':
mysql> ALTER TABLE test.t1 ADD PARTITION (PARTITION p11 DATA DIRECTORY = '/home/marcos.albe/sandboxes/msb_8_0_32/moardir' );
Now if we check 'extradir' is empty and 'moardir' has it's own single partition:
mysql> system ls extradir/test
mysql> system ls moardir/test
t1#p#p11.ibd
While the rest of the partitions were migrated to default datadir:
mysql> system ls data/test/t1*
data/test/t1#p#p0.ibd data/test/t1#p#p2.ibd data/test/t1#p#p4.ibd data/test/t1#p#p6.ibd data/test/t1#p#p8.ibd
data/test/t1#p#p1.ibd data/test/t1#p#p3.ibd data/test/t1#p#p5.ibd data/test/t1#p#p7.ibd data/test/t1#p#p9.ibd
I can't find anything in the docs hinting me this is expected behavior... and for most humans I assume it's not the expected behavior either :)
Suggested fix:
Don't move anything, just create the new partition where it was requested and leave everything else in place.
Description: Hello folks! When trying to add a partition with a different DATA DIRECTORY (X) than the other partitions (which already had a non-default DATA DIRECTORY (Z)), the existing partitions from Z are moved to the default datadir. Needless to explain this can wreck havoc as it can fill up the default datadir, and also the move operation can take very long times, blocking the table... etc. How to repeat: This one is super simple to reproduce; I tested on 8.0.32: dbdeployer deploy 8.0.32; mkdir ~/sanboxes/msb_8_0_32/{extradir,moardir}; echo "innodb_directories='${HOME}/sanboxes/msb_8_0_32/extradir;${HOME}/sanboxes/msb_8_0_32/extradir'" >> ${HOME}/sanboxes/msb_8_0_32/my.sandbox.cnf ~/sanboxes/msb_8_0_32/use test; Then create a table that uses data directory 'extradir' mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/home/marcos.albe/sandboxes/msb_8_0_32/extradir' PARTITION BY HASH ( c1 ) PARTITIONS 10; And you can see the files are where we expect them to be: mysql> SYSTEM ls extradir/test t1#p#p0.ibd t1#p#p1.ibd t1#p#p2.ibd t1#p#p3.ibd t1#p#p4.ibd t1#p#p5.ibd t1#p#p6.ibd t1#p#p7.ibd t1#p#p8.ibd t1#p#p9.ibd And then add a partition to the second directory 'moardir': mysql> ALTER TABLE test.t1 ADD PARTITION (PARTITION p11 DATA DIRECTORY = '/home/marcos.albe/sandboxes/msb_8_0_32/moardir' ); Now if we check 'extradir' is empty and 'moardir' has it's own single partition: mysql> system ls extradir/test mysql> system ls moardir/test t1#p#p11.ibd While the rest of the partitions were migrated to default datadir: mysql> system ls data/test/t1* data/test/t1#p#p0.ibd data/test/t1#p#p2.ibd data/test/t1#p#p4.ibd data/test/t1#p#p6.ibd data/test/t1#p#p8.ibd data/test/t1#p#p1.ibd data/test/t1#p#p3.ibd data/test/t1#p#p5.ibd data/test/t1#p#p7.ibd data/test/t1#p#p9.ibd I can't find anything in the docs hinting me this is expected behavior... and for most humans I assume it's not the expected behavior either :) Suggested fix: Don't move anything, just create the new partition where it was requested and leave everything else in place.