Description:
MySQL 5.6 had made a good progress with accessing/managing individual partitions in various SQL statements and with tablespace maintenance for InnoDB tables created with innodb_file_per_table=1. One can now import .ibd file originating almost from anywhere (see http://dev.mysql.com/doc/refman/5.6/en/alter-table.html etc) and that's great.
Problem is: when it comes to partitioned tables where each partition gets individual tablespace, ALTER does NOT allow to DISCARD/IMPORT individual partition (even though you can add, drop, truncate, optimize it etc), and for the entire table we just get error:
mysql> alter table members discard tablespace;
ERROR 1031 (HY000): Table storage engine for 'members' doesn't have this option
So, if I do not miss anything, it seems impossible to import entire partitioned InnoDB table or individual partition of it, and this looks like incomplete implementation of partitioning and tablespace maintenance for InnoDB.
How to repeat:
Let's assume you had lost .ibd file for one of partitions on master (long story how that could happen, failed ALTER TABLE ... TRUNCATE PARTITION ... led to that at least once, other bug report may appear if I create a repeatable test case), but have a good backup or just up to date slave where ALTER worked without error. For testing you can just stop server and remove .ibd file (making a copy of it somewhere). Now, after starting back any access to the table will produce:
mysql> select * from members;
ERROR 1146 (42S02): Table 'test.members' doesn't exist
While it is there:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| catalog |
| counters |
| link_person_wo |
| members |
| t |
| t1 |
| time_sheet |
| tt |
+----------------+
8 rows in set (0.00 sec)
and just one .ibd file for partition is missing (see error log):
...
2013-08-29 20:53:21 6820 [ERROR] InnoDB: Could not find a valid tablespace file for 'test/members#p#p0'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-08-29 20:53:21 6820 [ERROR] InnoDB: Tablespace open failed for '"test"."members" /* Partition "p0" */', ignored.
...
2013-08-29 20:53:37 6820 [ERROR] InnoDB: Failed to find tablespace for table '"test"."members" /* Partition "p0" */' in the cache. Attempting to load the tablespace with space id 79.
2013-08-29 20:53:37 18f4 InnoDB: Operating system error number 2 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2013-08-29 20:53:37 6820 [ERROR] InnoDB: Could not find a valid tablespace file for 'test/members#p#p0'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-08-29 20:53:37 18f4 InnoDB: cannot calculate statistics for table "test"."members" /* Partition "p0" */ because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2013-08-29 20:54:28 18f4 InnoDB: cannot calculate statistics for table "test"."members" /* Partition "p0" */ because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
Now, how to fix this if you have a backup of all table files somewhere (without mysqldump and restore, imagine huge table with only one, empty partition file missing)?
Suggested fix:
Do I miss any workaround or this is just a missing feature?
