[MDEV-14611] ALTER TABLE EXCHANGE PARTITION does not work properly when used with DATA DIRECTORY Created: 2017-12-08  Updated: 2020-08-25  Resolved: 2018-02-10

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Partitioning
Affects Version/s: 10.1, 10.2.11, 10.2
Fix Version/s: 10.1.32

Type: Bug Priority: Critical
Reporter: Geoff Montee (Inactive) Assignee: Alexey Botchkov
Resolution: Fixed Votes: 1
Labels: alter, data_directory, partitioning

Issue Links:
Relates
relates to MDEV-13157 Specifying DATA DIRECTORY in tables l... Closed
relates to MDEV-14618 "reorganize partition" ignores "data ... Open
Sprint: 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)



 Comments   
Comment by Elena Stepanova [ 2017-12-11 ]

Such EXCHANGE isn't allowed in MariaDB 10.0 and MySQL, but in MariaDB 10.1+ the limitation was lifted (see MDEV-13157).

Comment by Alexey Botchkov [ 2018-02-06 ]

Patch proposed.
http://lists.askmonty.org/pipermail/commits/2018-February/011961.html

Comment by Alexey Botchkov [ 2018-02-06 ]

http://lists.askmonty.org/pipermail/commits/2018-February/011961.html

Comment by Marko Mäkelä [ 2018-02-06 ]

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.

Comment by Alexey Botchkov [ 2018-02-07 ]

Updated patch.
http://lists.askmonty.org/pipermail/commits/2018-February/011970.html

Comment by Marko Mäkelä [ 2018-02-08 ]

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.

Comment by Alexey Botchkov [ 2018-02-10 ]

http://lists.askmonty.org/pipermail/commits/2018-February/011981.html

Generated at Thu Feb 08 08:14:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.