MySQL Bugs: #64498: Running out of file handles when ALTERing partitioned MyISAM table

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

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 ...

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

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.

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.

Fixed wrong category.

BUG#75753 is a duplicate of this bug.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Bug #83434 marked as duplicate of this