MySQL Bugs: #46611: Innodb needs partitioning support

Bug #46611	Innodb needs partitioning support
Submitted:	7 Aug 2009 19:04	Modified:	2 Dec 2009 13:46
Reporter:	bhushan uparkar	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.1	OS:	Linux (Debian Linux 2.6.24-etchnhalf.1-amd64)
Assigned to:		CPU Architecture:	Any
Tags:	partitioning innodb corruption

Description:
Partitioning feature is handled mostly by mysql , and not by storage engines. I am talking about storage engine innodb as we use it exclusively in dataware house applications which spans few TB's. 
If you have partitioned tables, and one of the partition gets corrupted you end up losing entire table. Innodb engine marks entire table as corrupted. No table recovery or optimize helps either.

Also we use file per table tablespace, in which case the corruption is restricted to that particular partitions only. If innodb sees the corrupted partition then it should mark that partition only corrupted as well as should not return any data to queries which refers to this partition. The support for partition is missing from innodb side. 
For us this is serious issue as h/w failures over long term is fact of life and recovering tables should be safe, fast and painless.

I ended up losing few 100GB+ tables because of this. Here is bug # http://bugs.mysql.com/?id=46592 .

How to repeat:
I dont know an easy way to repeat the partition corruption issue. Once can modify an partiton ibd file using hex editior or just rename it. See how innodb handles this .

Suggested fix:
Ability to list partitions status would be ideal , and should be part of information_schema. That way one knows which are good and bad without bringing down the database.

Earlier for the same issue mysql server used to crash, but now this does not happen anymore. Thanks for doing this.

Many thanks for writing a reasonable feature request.

Future support for FK will be added to all engines, including partitioned engines.