[#MDEV-15953] Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir

Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to datadir.

This is also an upstream bug, reported here:

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.

[MDEV-15953] Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir Created: 2018-04-20 Updated: 2020-08-25 Resolved: 2018-06-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Partitioning, Storage Engine - InnoDB
Affects Version/s:	10.0, 10.1, 10.2.14, 10.2, 10.3
Fix Version/s:	10.0.36, 10.1.35, 10.2.17, 10.3.8