|
Inconsistency with slashes when specifying DATA DIRECTORY in tables with and without partitions leads to failing EXCHANGE PARTITION
1. Create directory to be used (with correct permissions), in this example, C:/mariadb
2. Test case:
use test;
|
|
CREATE TABLE t1 (
|
id1 INT(11) NOT NULL,
|
id2 INT(11) NOT NULL
|
) ENGINE=INNODB DEFAULT CHARSET=latin1
|
/*!50100 PARTITION BY RANGE (id2)
|
(PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
|
PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
|
PARTITION p2 VALUES LESS THAN (16) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
|
PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB) */;
|
|
CREATE TABLE t2 (
|
id1 INT(11) NOT NULL,
|
id2 INT(11) NOT NULL
|
) ENGINE=INNODB DEFAULT CHARSET=latin1 DATA DIRECTORY='C:/mariadb';
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
|
ERROR 1731 (HY000): Non matching attribute 'DATA DIRECTORY' between partition and table
|
If we examine the SHOW CREATE TABLE outputs, we see that in t2, DATA DIRECTORY appended an extra slash at the end, whereas that of t1 did not:
mysql> show create table t1\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id1` int(11) NOT NULL,
|
`id2` int(11) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
/*!50100 PARTITION BY RANGE (id2)
|
(PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
|
PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
|
PARTITION p2 VALUES LESS THAN (16) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
|
PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB) */
|
1 row in set (0.01 sec)
|
|
mysql> show create table t2\G
|
*************************** 1. row ***************************
|
Table: t2
|
Create Table: CREATE TABLE `t2` (
|
`id1` int(11) NOT NULL,
|
`id2` int(11) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='C:/mariadb/'
|
1 row in set (0.00 sec)
|
In the above, note how t2 appended a slash to the end of the value for DATA DIRECTORY, but t1 did not. Both were created without the extra slash.
If you do not specify DATA DIRECTORY, this all works fine:
drop schema test;
|
create schema test;
|
use test;
|
|
CREATE TABLE t1 (
|
id1 INT(11) NOT NULL,
|
id2 INT(11) NOT NULL
|
) ENGINE=INNODB DEFAULT CHARSET=latin1
|
/*!50100 PARTITION BY RANGE (id2)
|
(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
|
PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,
|
PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,
|
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
|
|
CREATE TABLE t2 (
|
id1 INT(11) NOT NULL,
|
id2 INT(11) NOT NULL
|
) ENGINE=INNODB DEFAULT CHARSET=latin1;
|
|
mysql> ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
|
Query OK, 0 rows affected (0.04 sec)
|
You cannot ALTER t2 to remove the extra slash:
mysql> ALTER TABLE t2 DATA DIRECTORY='C:/mariadb';
|
Query OK, 0 rows affected, 1 warning (0.01 sec)
|
Records: 0 Duplicates: 0 Warnings: 1
|
|
mysql> show warnings;
|
+---------+------+---------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------+
|
| Warning | 1618 | <DATA DIRECTORY> option ignored |
|
+---------+------+---------------------------------+
|
1 row in set (0.00 sec)
|
Similarly, you cannot ALTER t1 to add the extra slashes:
CREATE TABLE t1 (
|
id1 INT(11) NOT NULL,
|
id2 INT(11) NOT NULL
|
) ENGINE=INNODB DEFAULT CHARSET=latin1
|
/*!50100 PARTITION BY RANGE (id2)
|
(PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = 'C:/mariadb/' ENGINE = InnoDB,
|
PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = 'C:/mariadb/' ENGINE = InnoDB,
|
PARTITION p2 VALUES LESS THAN (16) DATA DIRECTORY = 'C:/mariadb/' ENGINE = InnoDB,
|
PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'C:/mariadb/' ENGINE = InnoDB) */;
|
|
mysql> show create table t1\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id1` int(11) NOT NULL,
|
`id2` int(11) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
/*!50100 PARTITION BY RANGE (id2)
|
(PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
|
PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
|
PARTITION p2 VALUES LESS THAN (16) DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB,
|
PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'C:/mariadb' ENGINE = InnoDB) */
|
1 row in set (0.01 sec)
|
Note it ignores/removes them.
|