Bug #35953 Partitions: index directory preserved

• Submitted: 10 Apr 2008 1:41
• Modified: 22 Sep 2008 7:18
• Reporter: Peter Gulutzan
• Status: No Feedback
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.1.25-rc-debug
• OS: Linux (SUSE 10 | 32-bit)
• Assigned to: Assigned Account
• CPU Architecture: Any

[10 Apr 2008 1:41] Peter Gulutzan

Description:
I'm using mysql-6.0-backup.

I create a MyISAM table with INDEX DIRECTORY clause.
I alter the table to InnoDB.
The INDEX DIRECTORY clause disappears.

I create a partitioned MyISAM table with INDEX DIRECTORY clauses.
I alter the table to InnoDB.
The INDEX DIRECTORY clauses do not disappear.

I believe that's not intentional behaviour.

How to repeat:
mysql> create table t1 (s1 int) index directory '/usr/local/mysql/var';
Query OK, 0 rows affected (0.11 sec)

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `s1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 INDEX DIRECTORY='/usr/local/mysql/var/' |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t1 engine=innodb;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `s1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t2 (s1 int) partition by list (s1) (partition p1 values in (1) index directory '/usr/local/mysql/var');
Query OK, 0 rows affected (0.14 sec)

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `s1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (s1) (PARTITION p1 VALUES IN (1) INDEX DIRECTORY = '/usr/local/mysql/var' ENGINE = MyISAM) */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> alter table t2 engine=innodb;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `s1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (s1) (PARTITION p1 VALUES IN (1) INDEX DIRECTORY = '/usr/local/mysql/var' ENGINE = InnoDB) */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[10 Apr 2008 1:41] Peter Gulutzan

I can think of only one situation where this behaviour
causes a problem: using online backup in version-6.0.

create database e;
use e
create table te (s1 int) partition by list (s1) (partition px1 values in (1) index directory '/usr/local/mysql/var', partition px2 values in (2) index directory '/usr/local/mysql/var');
insert into te values (1);
alter table te engine=falcon;
backup database e to 'te#P#px2.MYI';
alter table te engine=myisam;
backup database e to '17';
drop database e;

The DROP DATABASE fails.

[10 Apr 2008 11:20] MySQL Verification Team

Thank you for the bug report.

[17 Apr 2008 23:26] Mikael Ronström

The bug is that non-partitioned tables drops the INDEX DIRECTORY clause

[22 Aug 2008 7:18] Mattias Jonsson

Tried the test in latest mysql-6.0-bugteam without failure.

I would suggest that the correct behavior should be to preserve the INDEX/DATA DIRECTORY also on table level. If not, should it always be cleared when changing engines, or only to engines that does not support I/D DIR? (and how can one know if a handler supports it or not?)

[22 Sep 2008 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".