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
Tags: innodb, partition, Tablespace
Triage: Triaged: D5 (Feature request) / R5 (Severe) / E5 (Major)

[29 Mar 2010 3:41] Philip Tellis
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
[29 Mar 2010 5:08] Sveta Smirnova
Thank you for the report.

Verified as described.  innodb_file_per_table is not required to repeat the problem.
[29 Mar 2010 20:19] Philip Tellis
typo in my description.  The second partition should be p1 and not p0.
[25 Aug 2010 13:01] Mattias Jonsson
needs to implement ha_partition::discard_or_import_tablespace().
[14 Sep 2010 18:26] Konstantin Osipov
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.
[25 Feb 2011 14:40] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=60251 marked as duplicate of this one.
[28 Feb 2012 14:19] Andrew Hefford
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.
[13 Feb 2013 15:48] Mattias Jonsson
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.
[30 Aug 2013 9:08] Valerii Kravchuk
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.
[14 Mar 2014 9:41] Mattias Jonsson
Fixed in 5.7.4, see http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html