MySQL Bugs: #52422: Cannot do ALTER TABLE DISCARD TABLESPACE if table has partitions

Bug #52422	Cannot do ALTER TABLE DISCARD TABLESPACE if table has partitions
Submitted:	29 Mar 2010 3:41	Modified:	14 Mar 2014 9:41
Reporter:	Philip Tellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.41, 5.1, 5.6.99 bzr	OS:	Linux (RHEL4/5)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	innodb, partition, Tablespace

Description:
If you use innodb_file_per_table and have an InnoDB table that uses partitions, then doing an ALTER TABLE DISCARD TABLESPACE gives the following error:

ERROR 1031 (HY000): Table storage engine for 'foo' doesn't have this option

See how to repeat for details.

I can only guess about the problem since I haven't looked at the code, but this is what I think:

When you discard tablespace, innodb looks for table_name.ibd and deletes it along with a bunch of other things internally.  When you use partitioning, there is no table_name.ibd.  Instead, there is table_name#P#partition_name.ibd and there may be multiple such files.  Secondly, when not using partitioning, the single .ibd file has just one tablespace id.  When you use partitioning, each partition gets its own tablespace id.

I blogged about this here: http://tech.bluesmoon.info/2010/03/innodbs-tablespace-ids-and-partitions.html  It's probably the same information.  Hope it's enough to help you fix it.

How to repeat:
1. Configure MySQL to use innodb_file_per_table.

2. create an innodb table with partitions:

CREATE TABLE foo (
   i1 INTEGER
) ENGINE=InnoDB
  PARTITION BY RANGE (i1) (
   PARTITION p0 VALUES LESS THAN(1),
   PARTITION p0 VALUES LESS THAN(2)
);

3. Try to discard the tablespace:

ALTER TABLE foo DISCARD TABLESPACE;

Expected:

foo#P#*.ibd should get deleted

Actual:

ERROR 1031 (HY000): Table storage engine for 'foo' doesn't have this option

Thank you for the report.

Verified as described.  innodb_file_per_table is not required to repeat the problem.

typo in my description.  The second partition should be p1 and not p0.

needs to implement ha_partition::discard_or_import_tablespace().

We find the syntax to discard and import an individual tablespace to be not appropriate for a set of .ibd files associated with a partitioned table, and the risk of a user mistake to provide a different file when importing a tablespace too high to allow for such functionality.
It's a valid feature request, however, to make it work, but we will not consider doing it in any upcoming version. 
Instead, we plan to extend the backup solutions that MySQL provides to be able to backup and restore an individual table.

Thank you for your interest in MySQL.

http://bugs.mysql.com/bug.php?id=60251 marked as duplicate of this one.

We have had this problem (with 5.1.41) a couple of times now following crashes during daily repartitions and occasionally when in the final stages a #table.par file is left behind the partition that was in the process of being dropped is left behind.  Deleting these files the leaves error messages in the logs each time the server is restarted, with no way to resolve them.  We are left with no practical way to resolve this issue.

120228 13:53:53  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './database/table#parXX.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

In 5.6 one can use EXCHANGE PARTITION:
http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html
to be able to export a single partition. It also makes it possible to IMPORT a .ibd file as a table, and then EXCHANGE it to a partition.

EXCHANGE will not help to restore access to the table if one of .ibd files for partition is missing. See http://bugs.mysql.com/bug.php?id=70196 also.

Fixed in 5.7.4, see http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html