MDEV-14611 ALTER TABLE EXCHANGE PARTITION does not work properly when used with DATA DIRECTORY.

        When table is renamed, the InnoDB's dictionary cache didn't
        change the ib_table->data_dir_path accordingly.
        Now it's set to NULL.

2 files changed, 334 insertions, 2 deletions

diff --git a/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result b/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result
index d7a77e5e54a..405511543bf 100644
--- a/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result
+++ b/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result
@@ -127,5 +127,198 @@ t1#P#p0.ibd
 t1#P#p1.ibd
 DROP TABLE t1;
 #
+# MDEV-14611 ALTER TABLE EXCHANGE PARTITION does not work
+#                 properly when used with DATA DIRECTORY
+#
+SET GLOBAL innodb_file_per_table = ON;
+CREATE TABLE t1
+(
+myid INT(11) NOT NULL,
+myval VARCHAR(10),
+PRIMARY KEY (myid)
+) ENGINE=INNODB PARTITION BY KEY (myid)
+(
+PARTITION p0001 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
+PARTITION p0002 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
+PARTITION p0003 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
+PARTITION p0004 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB
+);
+CREATE TABLE t2
+(
+myid INT(11) NOT NULL,
+myval VARCHAR(10),
+PRIMARY KEY (myid)
+) ENGINE=INNODB DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir';
+ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `myid` int(11) NOT NULL,
+  `myval` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`myid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY KEY (myid)
+(PARTITION p0001 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
+ PARTITION p0002 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
+ PARTITION p0003 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
+ PARTITION p0004 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB) */
+DROP TABLE t1, t2;
+CREATE TABLE t1
+(
+myid INT(11) NOT NULL,
+myval VARCHAR(10),
+PRIMARY KEY (myid)
+) ENGINE=INNODB PARTITION BY RANGE (myid)
+(
+PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
+PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
+PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
+PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB
+);
+CREATE TABLE t2
+(
+myid INT(11) NOT NULL,
+myval VARCHAR(10),
+PRIMARY KEY (myid)
+) ENGINE=INNODB DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir';
+insert into t1 values (1, 'one');
+insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
+select * from t1;
+myid	myval
+1	one
+ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `myid` int(11) NOT NULL,
+  `myval` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`myid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (myid)
+(PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = InnoDB,
+ PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
+ PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
+ PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB) */
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `myid` int(11) NOT NULL,
+  `myval` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`myid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQLTEST_VARDIR/mysql-test-data-dir/'
+select * from t1;
+myid	myval
+2	two
+3	threee
+4	four
+select * from t2;
+myid	myval
+1	one
+DROP TABLE t1, t2;
+CREATE TABLE t1
+(
+myid INT(11) NOT NULL,
+myval VARCHAR(10),
+PRIMARY KEY (myid)
+) ENGINE=INNODB PARTITION BY RANGE (myid)
+(
+PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
+PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
+PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
+PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB
+);
+CREATE TABLE t2
+(
+myid INT(11) NOT NULL,
+myval VARCHAR(10),
+PRIMARY KEY (myid)
+) ENGINE=INNODB;
+insert into t1 values (1, 'one');
+insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
+select * from t1;
+myid	myval
+1	one
+ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `myid` int(11) NOT NULL,
+  `myval` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`myid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (myid)
+(PARTITION p0001 VALUES LESS THAN (50) ENGINE = InnoDB,
+ PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
+ PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
+ PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB) */
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `myid` int(11) NOT NULL,
+  `myval` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`myid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQLTEST_VARDIR/mysql-test-data-dir/'
+select * from t1;
+myid	myval
+2	two
+3	threee
+4	four
+select * from t2;
+myid	myval
+1	one
+DROP TABLE t1, t2;
+CREATE TABLE t1
+(
+myid INT(11) NOT NULL,
+myval VARCHAR(10),
+PRIMARY KEY (myid)
+) ENGINE=INNODB PARTITION BY RANGE (myid)
+(
+PARTITION p0001 VALUES LESS THAN (50) ENGINE = INNODB,
+PARTITION p0002 VALUES LESS THAN (150) ENGINE = INNODB,
+PARTITION p0003 VALUES LESS THAN (1050) ENGINE = INNODB,
+PARTITION p0004 VALUES LESS THAN (10050) ENGINE = INNODB
+);
+CREATE TABLE t2
+(
+myid INT(11) NOT NULL,
+myval VARCHAR(10),
+PRIMARY KEY (myid)
+) ENGINE=INNODB DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir';
+insert into t1 values (1, 'one');
+insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
+select * from t1;
+myid	myval
+1	one
+ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `myid` int(11) NOT NULL,
+  `myval` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`myid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (myid)
+(PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = InnoDB,
+ PARTITION p0002 VALUES LESS THAN (150) ENGINE = InnoDB,
+ PARTITION p0003 VALUES LESS THAN (1050) ENGINE = InnoDB,
+ PARTITION p0004 VALUES LESS THAN (10050) ENGINE = InnoDB) */
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `myid` int(11) NOT NULL,
+  `myval` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`myid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+select * from t1;
+myid	myval
+2	two
+3	threee
+4	four
+select * from t2;
+myid	myval
+1	one
+DROP TABLE t1, t2;
+#
 # Cleanup
 #
diff --git a/mysql-test/suite/parts/t/partition_basic_symlink_innodb.test b/mysql-test/suite/parts/t/partition_basic_symlink_innodb.test
index 35dc2d5e004..31448c7a9fe 100644
--- a/mysql-test/suite/parts/t/partition_basic_symlink_innodb.test
+++ b/mysql-test/suite/parts/t/partition_basic_symlink_innodb.test
@@ -148,6 +148,147 @@ SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-14611 ALTER TABLE EXCHANGE PARTITION does not work
+--echo #                 properly when used with DATA DIRECTORY
+--echo #
+let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir;
+let $alt_data_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir;
+SET GLOBAL innodb_file_per_table = ON;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval CREATE TABLE t1
+(
+    myid INT(11) NOT NULL,
+    myval VARCHAR(10),
+    PRIMARY KEY (myid)
+) ENGINE=INNODB PARTITION BY KEY (myid)
+  (
+   PARTITION p0001 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
+   PARTITION p0002 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
+   PARTITION p0003 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
+   PARTITION p0004 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB
+  );
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval CREATE TABLE t2
+(
+   myid INT(11) NOT NULL,
+   myval VARCHAR(10),
+   PRIMARY KEY (myid)
+) ENGINE=INNODB DATA DIRECTORY = '$data_dir_path';
+  
+ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+SHOW CREATE TABLE t1;
+DROP TABLE t1, t2;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval CREATE TABLE t1
+(
+    myid INT(11) NOT NULL,
+    myval VARCHAR(10),
+    PRIMARY KEY (myid)
+) ENGINE=INNODB PARTITION BY RANGE (myid)
+  (
+   PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
+   PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
+   PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
+   PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB
+  );
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval CREATE TABLE t2
+(
+   myid INT(11) NOT NULL,
+   myval VARCHAR(10),
+   PRIMARY KEY (myid)
+) ENGINE=INNODB DATA DIRECTORY = '$alt_data_dir_path';
+
+insert into t1 values (1, 'one');
+insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
+  
+select * from t1;
+ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+SHOW CREATE TABLE t1;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+SHOW CREATE TABLE t2;
+select * from t1;
+select * from t2;
+DROP TABLE t1, t2;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval CREATE TABLE t1
+(
+    myid INT(11) NOT NULL,
+    myval VARCHAR(10),
+    PRIMARY KEY (myid)
+) ENGINE=INNODB PARTITION BY RANGE (myid)
+  (
+   PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
+   PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
+   PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
+   PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB
+  );
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval CREATE TABLE t2
+(
+   myid INT(11) NOT NULL,
+   myval VARCHAR(10),
+   PRIMARY KEY (myid)
+) ENGINE=INNODB;
+
+insert into t1 values (1, 'one');
+insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
+  
+select * from t1;
+ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+SHOW CREATE TABLE t1;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+SHOW CREATE TABLE t2;
+select * from t1;
+select * from t2;
+DROP TABLE t1, t2;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval CREATE TABLE t1
+(
+    myid INT(11) NOT NULL,
+    myval VARCHAR(10),
+    PRIMARY KEY (myid)
+) ENGINE=INNODB PARTITION BY RANGE (myid)
+  (
+   PARTITION p0001 VALUES LESS THAN (50) ENGINE = INNODB,
+   PARTITION p0002 VALUES LESS THAN (150) ENGINE = INNODB,
+   PARTITION p0003 VALUES LESS THAN (1050) ENGINE = INNODB,
+   PARTITION p0004 VALUES LESS THAN (10050) ENGINE = INNODB
+  );
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval CREATE TABLE t2
+(
+   myid INT(11) NOT NULL,
+   myval VARCHAR(10),
+   PRIMARY KEY (myid)
+) ENGINE=INNODB DATA DIRECTORY = '$alt_data_dir_path';
+
+insert into t1 values (1, 'one');
+insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
+  
+select * from t1;
+ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+SHOW CREATE TABLE t1;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+SHOW CREATE TABLE t2;
+select * from t1;
+select * from t2;
+DROP TABLE t1, t2;
+--echo #
 --echo # Cleanup
 --echo #
 
@@ -160,5 +301,3 @@ EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
 EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig;
 --enable_query_log
 
-
-