Bug #78164 alter table command affect partitioned table data directory
Submitted: 21 Aug 2015 10:11 Modified: 21 Aug 2015 10:29
Reporter: ki young yun Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.6.26 , 5.6.27, 5.7.9 OS:Ubuntu (14.04.2 64bit)
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, innodb, partitioning

[21 Aug 2015 10:11] ki young yun
Description:
I created a partitioned table. I set data directory option. 
After that I added column using ALTER TABLE and the .ibd files of the table location was changed to default datadir.

The queries are below

mysql> CREATE TABLE test1 (c1 INT)
PARTITION BY RANGE (c1) (    
   PARTITION lessthou VALUES less than (1000) DATA DIRECTORY = '/testmysql',    
   PARTITION highthou VALUES less than MAXVALUE DATA DIRECTORY = '/testmysql'
);

mysql> alter table test1 add stdname varchar(100);

Before last query, in /testmysql 

drwxrwx--- 2 mysql mysql 4.0K  8월 21 19:08 .
drwxrwxr-x 3 mysql mysql 4.0K  8월 21 10:16 ..
-rw-rw---- 1 mysql mysql  96K  8월 21 19:08 test1#P#highthou.ibd
-rw-rw---- 1 mysql mysql  96K  8월 21 19:08 test1#P#lessthou.ibd

After last query, in /testmysql

drwxrwx--- 2 mysql mysql 4.0K  8월 21 19:08 .
drwxrwxr-x 3 mysql mysql 4.0K  8월 21 10:16 ..

How to repeat:

mysql> CREATE TABLE test1 (c1 INT)
PARTITION BY RANGE (c1) (    
   PARTITION lessthou VALUES less than (1000) DATA DIRECTORY = '/testmysql',    
   PARTITION highthou VALUES less than MAXVALUE DATA DIRECTORY = '/testmysql'
);

mysql> alter table test1 add stdname varchar(100);
[21 Aug 2015 10:29] Umesh Shastry
Hello ki young yun,

Thank you for the report.
Verified as described on 5.6.26, 5.6.27 and 5.7.9.

Thanks,
Umesh
[21 Aug 2015 10:29] Umesh Shastry
// 5.6.26

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.26: bin/mysql -uroot -S run/master.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.26-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test

mysql> CREATE TABLE test1 (c1 INT)
    -> PARTITION BY RANGE (c1) (
    ->    PARTITION lessthou VALUES less than (1000) DATA DIRECTORY = '/tmp',
    ->    PARTITION highthou VALUES less than MAXVALUE DATA DIRECTORY = '/tmp'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> \! ls -l /tmp/
total 14540
drwx------ 3 umshastr common       18 Jul 29 13:22 boost_1_58_0
drwxrwxrwx 2 umshastr common       16 Aug  6 19:32 mysql-unique-ids
drwxrwx--- 2 umshastr common       60 Aug 21 12:18 test
-rw-r--r-- 1 umshastr common 14888896 Aug 13 12:23 test.csv
mysql> \! ls -l /tmp/test
total 256
-rw-rw---- 1 umshastr common 98304 Aug 21 12:18 test1#P#highthou.ibd
-rw-rw---- 1 umshastr common 98304 Aug 21 12:18 test1#P#lessthou.ibd
mysql> alter table test1 add stdname varchar(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! ls -l /tmp/test
total 0
mysql> \q
[21 Aug 2015 10:29] Umesh Shastry
// 5.6.27

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: bin/mysql -uroot -S /tmp/mysql_ushastry.sock  test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE test1 (c1 INT)
    -> PARTITION BY RANGE (c1) (
    ->    PARTITION lessthou VALUES less than (1000) DATA DIRECTORY = '/tmp',
    ->    PARTITION highthou VALUES less than MAXVALUE DATA DIRECTORY = '/tmp'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> \! ls -l /tmp/test
total 192
-rw-rw---- 1 umshastr common 98304 Aug 21 12:24 test1#P#highthou.ibd
-rw-rw---- 1 umshastr common 98304 Aug 21 12:24 test1#P#lessthou.ibd
mysql>
mysql> alter table test1 add stdname varchar(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! ls -l /tmp/test
total 0
mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `c1` int(11) DEFAULT NULL,
  `stdname` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (c1)
(PARTITION lessthou VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION highthou VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql>
mysql> \! cat docs/INFO_SRC
commit: 9ef7ef98bb7fec19f7f2503529a2cb085170f364
date: 2015-08-13 10:40:17 +0530
build-date: 2015-08-13 07:25:05 +0200
short: 9ef7ef9
branch: mysql-5.6

MySQL source 5.6.27
[21 Aug 2015 10:30] Umesh Shastry
// 5.7.9

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.9-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE test1 (c1 INT)
    -> PARTITION BY RANGE (c1) (
    ->    PARTITION lessthou VALUES less than (1000) DATA DIRECTORY = '/tmp',
    ->    PARTITION highthou VALUES less than MAXVALUE DATA DIRECTORY = '/tmp'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> \! ls -l /tmp/test
total 192
-rw-r----- 1 umshastr common 98304 Aug 21 12:27 test1#P#highthou.ibd
-rw-r----- 1 umshastr common 98304 Aug 21 12:27 test1#P#lessthou.ibd
mysql>
mysql> alter table test1 add stdname varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! ls -l /tmp/test
total 0
mysql>
mysql>  \! cat docs/INFO_SRC
commit: 5d3f588d2743519d4e87a69a7f7567fdc9568296
date: 2015-08-17 08:10:47 +0530
build-date: 2015-08-17 04:51:43 +0200
short: 5d3f588
branch: mysql-5.7

MySQL source 5.7.9
mysql>