Bug #64498 Running out of file handles when ALTERing partitioned MyISAM table
Submitted: 29 Feb 2012 23:55 Modified: 9 Feb 2015 19:04
Reporter: Hartmut Holzgraefe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[29 Feb 2012 23:55] Hartmut Holzgraefe
Description:
Trying to ALTER a partitioned MyISAM table with more than ~250 partitions fails as the default open_files_limit setting of 1024 open files is exceeded.

How to repeat:
create table t1 (id int primary key) 
  engine=myisam
  partition by hash(id) 
  partitions 1000;

alter table t1 add column i int;

-> error 24 "Too many open files"

(actually fails with ~250 partitions already as this sums up to 4 * 250 MYD/MYI files for the original and the temporary altered table)

Suggested fix:
Do not keep all the partition tables open during the ALTER operation. 

As an ALTER TABLE on an InnoDB table succeeds even with 1000 partitions and innodb_file_per_table being active this looks like a MyISAM specific problem and not a general one ...
[29 Feb 2012 23:56] Hartmut Holzgraefe
Forget about that InnoDB comment, i forgot the totally different way that InnoDB uses for file handling ... so it may be a server level, not engine level, problem after all
[1 Mar 2012 5:08] Valeriy Kravchuk
Thank you for the bug report. Indeed, table can be created and dropped, but not altered:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> create table t1 (id int primary key) 
    ->   engine=myisam
    ->   partition by hash(id) 
    ->   partitions 1000;
Query OK, 0 rows affected (1.83 sec)

mysql> alter table t1 add column i int;
ERROR 23 (HY000): Out of resources when opening file './test/t1#P#p463.MYD' (Errcode: 24)

This is a bug.
[6 Mar 2012 11:15] Mattias Jonsson
This is a design limitation, MyISAM have one file handle open for the .MYI file shared by all instances of one table, and one file handle open for each table instance for the .MYD file. And the ha_partition generic partitioning engine always open all partitions when opening a table/handler.

Say we want to alter a HASH partitioned MyISAM table from 100 to 101 partitions, then the data will be redistributed from 100 partition to 101 partitions, which needs to have all 201 partitions open.

What we could do is to add an optimization for ALTER TABLE (ha_partition::copy_partition function) to only have one READ partition open at a time. That seems to be a viable solution to this bug.

Also the 'all partitions are opened problem' is related to WL#3513.
[6 Feb 2015 12:32] Jon Stephens
Fixed wrong category.

BUG#75753 is a duplicate of this bug.
[9 Feb 2015 19:04] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.
[19 Oct 2016 7:21] Umesh Shastry
Bug #83434 marked as duplicate of this