Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to datadir.
This is also an upstream bug, reported here:
https://bugs.mysql.com/bug.php?id=78164
There is a test case there. Here is also a test case provided to us:
To recreate (replace <path_outside_datadir> with a path outside the datadir):
CREATE TABLE my_part_table (
|
my_id BIGINT(20) NOT NULL,
|
my_value SMALLINT(6) NOT NULL,
|
insert_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
|
PRIMARY KEY (my_id)
|
) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
|
PARTITION BY HASH (my_id)
|
(PARTITION p0001 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
|
PARTITION p0002 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
|
PARTITION p0003 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
|
PARTITION p0004 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
|
PARTITION p0005 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB
|
);
|
Now check the table (before the ALTER) and you see it shows the correct paths:
SHOW CREATE TABLE my_part_table;
|
|
CREATE TABLE my_part_table (
|
my_id BIGINT(20) NOT NULL,
|
my_value SMALLINT(6) NOT NULL,
|
insert_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
|
PRIMARY KEY (my_id)
|
) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
|
PARTITION BY HASH (my_id)
|
(PARTITION p0001 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
|
PARTITION p0002 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
|
PARTITION p0003 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
|
PARTITION p0004 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB,
|
PARTITION p0005 DATA DIRECTORY = '<path_outside_datadir>' ENGINE = INNODB)
|
Now, issue an ALTER:
ALTER TABLE my_part_table
|
ADD COLUMN update_date DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
|
AFTER insert_date;
|
Re-run SHOW CREATE:
SHOW CREATE TABLE my_part_table;
|
|
CREATE TABLE my_part_table (
|
my_id BIGINT(20) NOT NULL,
|
my_value SMALLINT(6) NOT NULL,
|
insert_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
|
update_date DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
|
PRIMARY KEY (my_id)
|
) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
|
PARTITION BY HASH (my_id)
|
(PARTITION p0001 ENGINE = INNODB,
|
PARTITION p0002 ENGINE = INNODB,
|
PARTITION p0003 ENGINE = INNODB,
|
PARTITION p0004 ENGINE = INNODB,
|
PARTITION p0005 ENGINE = INNODB)
|
Note the non-standard paths are gone and all is in the datadir.
Bug
Major
