Fun with partitions and MyISAM, part #2

First attempt: insert data into a partitioned MyISAM table with 1000 partitions:

  mysql> create table t1 (id int primary key) 
           engine=myisam partition by hash(id) 
           partitions 1000;
  Query OK, 0 rows affected (0.31 sec)

  mysql> insert into t1 (id) values (1);
  ERROR 1016 (HY000): Can't open file: './test/t1.frm' (errno: 24)

So it really tries to open all 2000 .MYD + .MYI partition files for this table even though it should be pretty clear which partition the inserted data will be going to.

So lets try with less partitions. 300 should give us enough room to work with (600 .MY* files) and fill it with some random data:

  mysql> create table t1 (id int primary key auto_increment) 
           engine=myisam partition by hash(id) 
           partitions 300;
  Query OK, 0 rows affected (0.20 sec)

  mysql> insert into t1 (id) values (1);
  Query OK, 1 row affected (0.03 sec)

  mysql> insert into t1 select NULL, RAND()*1000 from t1; 
  Query OK, 1 row affected (0.03 sec) 
  Records: 1  Duplicates: 0  Warnings: 0

  [...]

  mysql> insert into t1 select NULL, RAND()*1000 from t1;
  Query OK, 65536 rows affected (0.66 sec)  
  Records: 65536  Duplicates: 0  Warnings: 0

Funny enough the INSERT...SELECT failed with "Too many open files" when i tested this yesterday but works fine today with the very same mysqld binary. No idea yet what the difference between yesterday and todays tests was ...

But lets continue for now by creating a copy of the table:

  mysql> create table t2 like t1;
  Query OK, 0 rows affected (0.17 sec)

  mysql> insert into t2 select * from t1;
  ERROR 1016 (HY000): Can't open file: './test/t2.frm' (errno: 24)

So now we have 600 .MY* files in table t1 and another 600 in t2, and together these 1200 MyISAM files exceed the 1024 open-files limit once again. So far so 'good', this is what i expected. But there is more 'fun' to it:

  mysql> show create table t2;
  ERROR 1016 (HY000): Can't open file: './test/t2.frm' (errno: 24)

What is the problem this time? Well, it's the table_cache ... table t1 is still held open via its table cache entry. So a FLUSH TABLES fixes the situation:

  mysql> flush tables;
  Query OK, 0 rows affected (0.01 sec)

  mysql> show create table t2\G
  *************************** 1. row ***************************
         Table: t2
  Create Table: CREATE TABLE `t2` (
  [...]

So not only does any access to a partitioned MyISAM table open all the underlying MYD/MYI files, they will also stay open as long as the table is still kept open via the table cache, and so not only the local session trying to operate on the multi-partition table is affected by "too many open files" errors, but the situation can become 'sticky' and can affect all other current and future connection sessions, too, until the table cache entry for the multi-partition table(s) is cleared.

Maybe running an implicit FLUSH TABLES should be part of the error handling for error 24 "too many open files"?