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