Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.14, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Description
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
This needs a bit more work. I would like to test the following types of ALTER TABLE on both partitioned and non-partitioned tables:
- ALTER TABLE t DATA DIRECTORY='something'
- Removing the DATA DIRECTORY attribute. (How? DATA DIRECTORY=''? DEFAULT? NULL?)
Adding, changing or removing the attribute should cause the table to be rebuilt.
Specifying no change for the DATA DIRECTORY should be an instantaneous operation.
The usual restrictions for rebuilding should apply. (If there are multiple FULLTEXT indexes, ALGORITHM=INPLACE will be refused. If FULLTEXT or SPATIAL indexes exist, LOCK=NONE will be refused.)
This one doesn't work: ALTER TABLE t DATA DIRECTORY='something' https://github.com/MariaDB/server/blob/364a20fe0b072fb1d2a9b54a8c4e47a5012f3e97/sql/sql_alter.cc#L293
It looks like ALTER TABLE doesn't support DATA DIRECTORY at all https://mariadb.com/kb/en/library/alter-table/
CREATE TABLE documentation says: "DATA DIRECTORY and INDEX DIRECTORY were only supported for MyISAM and Aria, before MariaDB 5.5. Since 5.5, DATA DIRECTORY has also been supported by InnoDB if the innodb_file_per_table server system variable is enabled, but only in CREATE TABLE, not in ALTER TABLE."
Is there a documented way of removing the DATA DIRECTORY attribute? Do Aria and MyISAM behave in the same way as InnoDB (with the exception that InnoDB creates a subdirectory)?
Nope, there is a documented difference: InnoDB doesn't allow to change DATA DIRECTORY while MyISAM and Aria does. https://mariadb.com/kb/en/library/create-table/#data-directoryindex-directory
Right now changing DATA DIRECTORY of a InnoDB partition silently ignores a new path. I've added a test to PR.
A more future-proof version of the test case would replace the ADD COLUMN with FORCE. In MariaDB Server 10.3, ADD COLUMN would by default be an instantaneous operation, which will not rebuild the table.
The problem appears to be that the DATA DIRECTORY attribute is not being preserved when the table is rebuilt in ALGORITHM=INPLACE. Does the same occur with ALGORITHM=COPY?