Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.11, 10.1(EOL), 10.2(EOL)
-
10.2.13
Description
ALTER TABLE EXCHANGE PARTITION does not work properly if the tables and partitions involved were created with the DATA DIRECTORY option specified.
To reproduce, first create a data directory:
sudo mkdir -p /mysql/
|
sudo chown mysql:mysql /mysql/
|
sudo chmod 0750 /mysql/
|
Then create a partitioned table that uses the new data directory:
CREATE TABLE test_swap_part_data_dir
|
(
|
myid INT(11) NOT NULL,
|
myval VARCHAR(10),
|
PRIMARY KEY (myid)
|
) ENGINE=INNODB PARTITION BY KEY (myid)
|
(
|
PARTITION p0001 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0002 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0003 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0004 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0005 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0006 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0007 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0008 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0009 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0010 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0011 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0012 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0013 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0014 DATA DIRECTORY = '/mysql/' ENGINE = INNODB,
|
PARTITION p0015 DATA DIRECTORY = '/mysql/' ENGINE = INNODB
|
);
|
And then create an identical non-partitioned table that uses the data directory:
CREATE TABLE test_swap_part_data_dir_p0001
|
(
|
myid INT(11) NOT NULL,
|
myval VARCHAR(10),
|
PRIMARY KEY (myid)
|
) ENGINE=INNODB DATA DIRECTORY = '/mysql/';
|
And then swap the tablespace of this table with one of the partitions:
ALTER TABLE test_swap_part_data_dir EXCHANGE PARTITION p0001 WITH TABLE test_swap_part_data_dir_p0001;
|
And then if you look at the definition of test_swap_part_data_dir, you can see that the DATA DIRECTORY value for partition p0001 is no longer correct:
MariaDB [db1]> SHOW CREATE TABLE test_swap_part_data_dir\G
|
*************************** 1. row ***************************
|
Table: test_swap_part_data_dir
|
Create Table: CREATE TABLE `test_swap_part_data_dir` (
|
`myid` int(11) NOT NULL,
|
`myval` varchar(10) DEFAULT NULL,
|
PRIMARY KEY (`myid`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
PARTITION BY KEY (`myid`)
|
(PARTITION `p0001` DATA DIRECTORY = '/mysql/test_swap_part_data_dir_p0001' ENGINE = InnoDB,
|
PARTITION `p0002` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0003` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0004` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0005` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0006` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0007` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0008` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0009` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0010` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0011` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0012` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0013` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0014` DATA DIRECTORY = '/mysql' ENGINE = InnoDB,
|
PARTITION `p0015` DATA DIRECTORY = '/mysql' ENGINE = InnoDB)
|
1 row in set (0.00 sec)
|
Of course, this data directory doesn't even exist:
$ sudo ls -l /mysql/test_swap_part_data_dir_p0001
|
ls: cannot access /mysql/test_swap_part_data_dir_p0001: No such file or directory
|
But surprisingly, the table can still be queried:
MariaDB [db1]> SELECT * FROM test_swap_part_data_dir;
|
Empty set (0.00 sec)
|
|
|
MariaDB [db1]> CHECK TABLE test_swap_part_data_dir;
|
+-----------------------------+-------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------------------+-------+----------+----------+
|
| db1.test_swap_part_data_dir | check | status | OK |
|
+-----------------------------+-------+----------+----------+
|
1 row in set (0.00 sec)
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Open
-
- links to
Activity
Please push a fix to a bb-10.1- branch for validation, both InnoDB and XtraDB.
Also test EXCHANGE operations where the DATA DIRECTORY attribute is changing, either to another directory, or to being present or absent.
Note that InnoDB source code uses TAB for indentation, instead of spaces.
The code change looks OK, but the test is missing some replace_regexp:
PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = '/home/hf/wgit/mdev-14611/mysql-test/var/mysql-test-data-dir' ENGINE = INNODB,
|
I would also like to see SHOW CREATE TABLE t2, and I would like to have 2 more tests: exchange a partition that carries a DATA DIRECTORY clause with a table that is missing the clause, and back. (So, the SHOW CREATE TABLE for t1 and t2 should display the absence of a DATA DIRECTORY attribute when expected.
If the enhanced test performs OK, this is OK to push without further review.
Such EXCHANGE isn't allowed in MariaDB 10.0 and MySQL, but in MariaDB 10.1+ the limitation was lifted (see
MDEV-13157).