Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.11
-
None
-
None
-
centos7
Description
HI. I try to move (rotate) old partiotions to another local directory (another hard drive). Unfortunately, "data directory" is ignored by "alter table...reorganize partition" statement without any error/warning. For example:
1. "create table" with "data directory" is OK
MariaDB [(none)]> select @@version; |
+-----------------+ |
| @@version |
|
+-----------------+ |
| 10.2.11-MariaDB |
|
+-----------------+ |
1 row in set (0.00 sec) |
MariaDB [(none)]> use test |
Database changed |
MariaDB [test]> CREATE TABLE `part_test` ( |
-> `id` int(10) unsigned NOT NULL, |
-> PRIMARY KEY (`id`) |
-> ) ENGINE=InnoDB
|
-> PARTITION BY RANGE (`id`) |
-> (
|
-> PARTITION p1 VALUES LESS THAN (100), |
-> PARTITION p2 VALUES LESS THAN (200) DATA DIRECTORY = '/var/lib/mysql_parition_moving/' |
-> );
|
Query OK, 0 rows affected (0.12 sec) |
MariaDB [test]> show create table part_test \G |
*************************** 1. row ***************************
|
Table: part_test |
Create Table: CREATE TABLE `part_test` ( |
`id` int(10) unsigned NOT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
PARTITION BY RANGE (`id`) |
(PARTITION `p1` VALUES LESS THAN (100) ENGINE = InnoDB, |
PARTITION `p2` VALUES LESS THAN (200) DATA DIRECTORY = '/var/lib/mysql_parition_moving' ENGINE = InnoDB) |
1 row in set (0.00 sec) |
"p2" parition file is in the right directory:
[snen]# ls -lh /var/lib/mysql_parition_moving/test/
|
total 96K
|
-rw-rw----. 1 mysql mysql 96K дек 11 15:29 part_test#P#p2.ibd
|
2. try to move "p1" to "p2" directory also
MariaDB [test]> alter table part_test REORGANIZE PARTITION p1 into (PARTITION p1 VALUES LESS THAN(100) DATA DIRECTORY '/var/lib/mysql_parition_moving/'); |
Query OK, 0 rows affected (0.31 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table part_test \G |
*************************** 1. row ***************************
|
Table: part_test |
Create Table: CREATE TABLE `part_test` ( |
`id` int(10) unsigned NOT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
PARTITION BY RANGE (`id`) |
(PARTITION `p1` VALUES LESS THAN (100) ENGINE = InnoDB, |
PARTITION `p2` VALUES LESS THAN (200) DATA DIRECTORY = '/var/lib/mysql_parition_moving' ENGINE = InnoDB) |
1 row in set (0.00 sec) |
"p1" parition file is NOT in the right directory:
[snen]# ls -lh /var/lib/mysql_parition_moving/test/
|
total 96K
|
-rw-rw----. 1 mysql mysql 96K дек 11 15:29 part_test#P#p2.ibd
|
PS
10.1.26-MariaDB has the same behavior
mysql 5.7.20 works fine (p1 moved)
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
Any work-around or progress on this?
E.g. could I use symbolic links to physically move the InnoDB partitioned data files to other volumes? (I guess for that it requires a full server restart, opposed to the ALTER TABLE.)
Any work-around or progress on this?
Sym links is not good for some reasons. Look workaround here - It also usable for moving partition within one table (to another DATA DIRECTORY), contact me if you need simple Perl script for it.
I ran into the same problem. I first thought I did something wrong:
partition maintenance and data directories
Searching for the problem I found this open issue here.
So, my question, will this be fixed?
We ran into the same problem and creating symbolic links as an alternative corrupting the database.
Is this fixed in latest versions ? We are currently using 10.3. What is the workaround for this to move the partition to a different data directory .
I am afraid it is a documented behavior (https://mariadb.com/kb/en/create-table/#data-directoryindex-directory),
the bug was that the option was silently ignored, which is fixed since 10.2.28 (MDEV-16594), currently such
ALTER TABLE .. REORGANIZE PARTITION ends up with a warning "<DATA DIRECTORY> option ignored for InnoDB partition"
reproduced the problem on 10.2.12-MariaDB-debug, 10.3.3