# #Bug #33398681 Innodb ibd tablespace file is moved back from innodb_directories to datadir after ALTER TABLE # # Set different paths for --datadir # Create innodb_directories folder # Create external datadir folder # Create external datadir2 folder # Restart the engine to make the external directory known. # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2 # Create table CREATE DATABASE dbtest; USE dbtest; # Normal tables CREATE TABLE `t1a` (`c1` INT); CREATE TABLE `t1b` (`c1` INT); CREATE TABLE `t1c` (`c1` INT); # Normal tables with data directory clause CREATE TABLE `t2a` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir'; CREATE TABLE `t2b` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir'; CREATE TABLE `t2c` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir'; # Partition tables CREATE TABLE `t3a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20)); CREATE TABLE `t3b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20)); CREATE TABLE `t3c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20)); # Partition tables with data directory clause CREATE TABLE `t4a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir'); CREATE TABLE `t4b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir'); CREATE TABLE `t4c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir'); # Normal tables with data directory clause 2 CREATE TABLE `t5a` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2'; CREATE TABLE `t5b` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2'; CREATE TABLE `t5c` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2'; # Partition tables with data directory clause CREATE TABLE `t6a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2'); CREATE TABLE `t6b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2'); CREATE TABLE `t6c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2'); # Shutdown server # 1. ibd files for t1a, t1b, t1c, t3a, t3b, t3c # Check that the ibd files for each table exist in the working (dbtest) folder t1a.ibd t1b.ibd t1c.ibd t3a#p#p0.ibd t3a#p#p1.ibd t3b#p#p0.ibd t3b#p#p1.ibd t3c#p#p0.ibd t3c#p#p1.ibd # Move ibd files from default directory to new innodb directory # Check that the files exist in the innodb_directories folder before alter after moving t1a.ibd t1b.ibd t1c.ibd t3a#p#p0.ibd t3a#p#p1.ibd t3b#p#p0.ibd t3b#p#p1.ibd t3c#p#p0.ibd t3c#p#p1.ibd # 2. ibd files for t2a, t2b, t2c, t4a, t4b, t4c # Check that the ibd files for each table exist in the external directory where the tables are created t2a.ibd t2b.ibd t2c.ibd t4a#p#p0.ibd t4a#p#p1.ibd t4b#p#p0.ibd t4b#p#p1.ibd t4c#p#p0.ibd t4c#p#p1.ibd # 3. ibd files for t5a, t5b, t5c, t6a, t6b, t6c # Check that the ibd files for each table exist in the external directory2 where the tables are created t5a.ibd t5b.ibd t5c.ibd t6a#p#p0.ibd t6a#p#p1.ibd t6b#p#p0.ibd t6b#p#p1.ibd t6c#p#p0.ibd t6c#p#p1.ibd # Move ibd files for t5a, t5b, t5c, t6a, t6b, t6c from external directory2 to default directory # Check that the ibd files for t5a, t5b, t5c, t6a, t6b, t6c now exist in working (dbtest) folder t5a.ibd t5b.ibd t5c.ibd t6a#p#p0.ibd t6a#p#p1.ibd t6b#p#p0.ibd t6b#p#p1.ibd t6c#p#p0.ibd t6c#p#p1.ibd # Restart the server # restart: --innodb-directories=MYSQLTEST_VARDIR/innodb_directories;MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2 # Alter table ALTER TABLE dbtest.t1a FORCE; ALTER TABLE dbtest.t1b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t1c FORCE, ALGORITHM=COPY; ALTER TABLE dbtest.t2a FORCE; ALTER TABLE dbtest.t2b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t2c FORCE, ALGORITHM=COPY; ALTER TABLE dbtest.t3a FORCE; ALTER TABLE dbtest.t3b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t3c FORCE, ALGORITHM=COPY; ALTER TABLE dbtest.t4a FORCE; ALTER TABLE dbtest.t4b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t4c FORCE, ALGORITHM=COPY; ALTER TABLE dbtest.t5a FORCE; ALTER TABLE dbtest.t5b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t5c FORCE, ALGORITHM=COPY; ALTER TABLE dbtest.t6a FORCE; ALTER TABLE dbtest.t6b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t6c FORCE, ALGORITHM=COPY; # Check that the ibd files for t1a, t1b, t1c, t3a, t3b, t3c still exist in the innodb_directories folder t1a.ibd t1b.ibd t1c.ibd t3a#p#p0.ibd t3a#p#p1.ibd t3b#p#p0.ibd t3b#p#p1.ibd t3c#p#p0.ibd t3c#p#p1.ibd # Check that the ibd files for t1a, t1b, t1c, t3a, t3b, t3c do not exist and ibd files for t5a, t5b, t5c, t6a, t6b, t6c exist in the data directory folder t5a.ibd t5b.ibd t5c.ibd t6a#p#p0.ibd t6a#p#p1.ibd t6b#p#p0.ibd t6b#p#p1.ibd t6c#p#p0.ibd t6c#p#p1.ibd # Check that the ibd files for t5a, t5b, t5c, t6a, t6b, t6c do not exist in external directory2 where the tables are created # Check that the ibd files for t2a, t2b, t2c, t4a, t4b, t4c still exist in the external directory where the tables are created t2a.ibd t2b.ibd t2c.ibd t4a#p#p0.ibd t4a#p#p1.ibd t4b#p#p0.ibd t4b#p#p1.ibd t4c#p#p0.ibd t4c#p#p1.ibd # Shutdown server # Now move ibd files for t1a, t1b, t1c, t3a, t3b, t3c from innodb_directories folder to default directory # Restart the server # restart: --innodb-directories=MYSQLTEST_VARDIR/innodb_directories;MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2 # Alter table ALTER TABLE dbtest.t1a FORCE; ALTER TABLE dbtest.t1b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t1c FORCE, ALGORITHM=COPY; ALTER TABLE dbtest.t3a FORCE; ALTER TABLE dbtest.t3b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t3c FORCE, ALGORITHM=COPY; # Check ibd files for t1a, t1b, t1c, t3a, t3b, t3c exist in default directory t1a.ibd t1b.ibd t1c.ibd t3a#p#p0.ibd t3a#p#p1.ibd t3b#p#p0.ibd t3b#p#p1.ibd t3c#p#p0.ibd t3c#p#p1.ibd t5a.ibd t5b.ibd t5c.ibd t6a#p#p0.ibd t6a#p#p1.ibd t6b#p#p0.ibd t6b#p#p1.ibd t6c#p#p0.ibd t6c#p#p1.ibd # Check ibd files for t1a, t1b, t1c, t3a, t3b, t3c do not exist in innodb_directories # Clean Up # Drop tables DROP TABLE dbtest.t1a; DROP TABLE dbtest.t1b; DROP TABLE dbtest.t1c; DROP TABLE dbtest.t2a; DROP TABLE dbtest.t2b; DROP TABLE dbtest.t2c; DROP TABLE dbtest.t3a; DROP TABLE dbtest.t3b; DROP TABLE dbtest.t3c; DROP TABLE dbtest.t4a; DROP TABLE dbtest.t4b; DROP TABLE dbtest.t4c; DROP TABLE dbtest.t5a; DROP TABLE dbtest.t5b; DROP TABLE dbtest.t5c; DROP TABLE dbtest.t6a; DROP TABLE dbtest.t6b; DROP TABLE dbtest.t6c; # Clean up for other tests # Additional test for tables with data directory clause # Move non-partitioned table with data dir clause at EXTERNAL_DIR1 to EXTERNAL_DIR2 CREATE TABLE `t7a` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir'; CREATE TABLE `t7b` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir'; CREATE TABLE `t7c` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir'; # Shutdown server # Check that the ibd files for t7a, t7b, t7c exist in EXTERNAL_DIR t7a.ibd t7b.ibd t7c.ibd # Move files from EXTERNAL_DIR to EXTERNAL_DIR2 # Check that the ibd files now exist in EXTERNAL_DIR2 t7a.ibd t7b.ibd t7c.ibd # Restart the server # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2 # Alter table ALTER TABLE dbtest.t7a FORCE; ALTER TABLE dbtest.t7b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t7c FORCE, ALGORITHM=COPY; # Check that the ibd files for t7a, t7b, t7c still exist in EXTERNAL_DIR2 t7a.ibd t7b.ibd t7c.ibd # Check that the ibd files for t7a, t7b, t7c do not exist in EXTERNAL_DIR # Clean up # Drop tables DROP TABLE dbtest.t7a; DROP TABLE dbtest.t7b; DROP TABLE dbtest.t7c; # Clean up for other tests # Additional tests for partitioned tables # 1. Creating partitioned table with data directory clause and both partitions in different directories # Partition tables with data directory clause CREATE TABLE `t8a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2'); CREATE TABLE `t8b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2'); CREATE TABLE `t8c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2'); # Partition table with one partition in external directory and one in default directory CREATE TABLE `t8d` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) ); # Shutdown server # Check that for partition p0 for t8a, t8b, t8c and t8d exist in EXTERNAL_DIR and partition p1 for for t8a, t8b, t8c exist in EXTERNAL_DIR2 # Listing EXTERNAL_DIR t8a#p#p0.ibd t8b#p#p0.ibd t8c#p#p0.ibd t8d#p#p0.ibd # Listing EXTERNAL_DIR2 t8a#p#p1.ibd t8b#p#p1.ibd t8c#p#p1.ibd # List default directory. t8d's p1 partition should be present t8d#p#p1.ibd # Restart the server # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2 # Alter table ALTER TABLE dbtest.t8a FORCE; ALTER TABLE dbtest.t8b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t8c FORCE, ALGORITHM=COPY; ALTER TABLE dbtest.t8d FORCE, ALGORITHM=COPY; # Check that for partition p0 for t8a, t8b, t8c still exist in EXTERNAL_DIR and partition p1 for for t8a, t8b, t8c still exist in EXTERNAL_DIR2 # Listing EXTERNAL_DIR t8a#p#p0.ibd t8b#p#p0.ibd t8c#p#p0.ibd t8d#p#p0.ibd # Listing EXTERNAL_DIR2 t8a#p#p1.ibd t8b#p#p1.ibd t8c#p#p1.ibd # List default directory. t8d's p1 partition should be present t8d#p#p1.ibd # Shutdown server # Now lets move all partitioned tables p0 of t8a, t8b, t8c, t8d from EXTERNAL_DIR and partitioned tables p1 of t8a, t8b, t8c from EXTERNAL_DIR2 to default directory # Restart the server # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2 # Alter table ALTER TABLE dbtest.t8a FORCE; ALTER TABLE dbtest.t8b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t8c FORCE, ALGORITHM=COPY; # Check that all the partitioned tables for t8a, t8b, t8c and t8d now exist in default directory t8a#p#p0.ibd t8a#p#p1.ibd t8b#p#p0.ibd t8b#p#p1.ibd t8c#p#p0.ibd t8c#p#p1.ibd t8d#p#p0.ibd t8d#p#p1.ibd # Check that EXTERNAL_DIR and EXTERNAL_DIR2 are empty # Listing EXTERNAL_DIR # Listing EXTERNAL_DIR2 ALTER TABLE dbtest.t8d FORCE, ALGORITHM=COPY; # Check that all the partitioned tables for t8a, t8b, t8c and t8d now exist in default directory t8a#p#p0.ibd t8a#p#p1.ibd t8b#p#p0.ibd t8b#p#p1.ibd t8c#p#p0.ibd t8c#p#p1.ibd t8d#p#p0.ibd t8d#p#p1.ibd # Check that EXTERNAL_DIR and EXTERNAL_DIR2 are empty # Listing EXTERNAL_DIR # Listing EXTERNAL_DIR2 # Drop tables DROP TABLE dbtest.t8a; DROP TABLE dbtest.t8b; DROP TABLE dbtest.t8c; DROP TABLE dbtest.t8d; # Clean up for other tests # 2. Creating partitioned table without data directory clause but moving each partition to different external directories # Partition tables CREATE TABLE `t9a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20)); CREATE TABLE `t9b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20)); CREATE TABLE `t9c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20)); # Shutdown server # Check that the ibd files for all p0 and p1 partitions of t9a, t9b, t9c table exist in the working (dbtest) folder t9a#p#p0.ibd t9a#p#p1.ibd t9b#p#p0.ibd t9b#p#p1.ibd t9c#p#p0.ibd t9c#p#p1.ibd # Now move only p1 partitioned tables of t9a, t9b, t9c to EXTERNAL_DIR # Check that the ibd files for all p0 of t9a, t9b, t9c table exist in the working (dbtest) folder t9a#p#p0.ibd t9b#p#p0.ibd t9c#p#p0.ibd # Check that the ibd files for all p1 of t9a, t9b, t9c table exist in EXTERNAL_DIR t9a#p#p1.ibd t9b#p#p1.ibd t9c#p#p1.ibd # Restart the server # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir # Alter table ALTER TABLE dbtest.t9a FORCE; ALTER TABLE dbtest.t9b FORCE, ALGORITHM=INPLACE; ALTER TABLE dbtest.t9c FORCE, ALGORITHM=COPY; # Check that the ibd files for all p0 of t9a, t9b, t9c table still exist in the working (dbtest) folder t9a#p#p0.ibd t9b#p#p0.ibd t9c#p#p0.ibd # Check that the ibd files for all p1 of t9a, t9b, t9c table still exist in EXTERNAL_DIR t9a#p#p1.ibd t9b#p#p1.ibd t9c#p#p1.ibd # Drop tables and database DROP TABLE dbtest.t9a; DROP TABLE dbtest.t9b; DROP TABLE dbtest.t9c; DROP DATABASE dbtest; # restart: # End Test