If you have ever encountered a “23 error” you are most likely running MySQL on a windows machine.
The error looks something like this:
ERROR 23 (HY000): Out of resources when opening file ‘./somedb/sometable.MYD’ (Errcode: 24)
What this error typically means is that MySQL has exceeded the limit for the number of files you can open at once. This is easy to fix in Linux by upping that number.
edit /etc/security/limits.conf
and add:
mysql soft nofile 4096
mysql hard nofile 4096
This effectively doubles the number of files MySQL can open (default is half this).
On windows you can’t really fix this. It is an OS limitation. See: http://dev.mysql.com/doc/refman/5.0/en/limits-windows.html
Alternatively, the error may come up if you are using partitioned tables, as was the case for me. It is a confusing error in this situation. I learned more about the MyISAM engine when this happened. It turns out that each table (or (sub)partition) requires up to 3 files to be opened when accessed. So for a table with a large number of partitions, like my weather data table a large number of files are opened. This is because MyISAM stores the table format, table data and table indexes in separate files.
In my situation doubling the number of files MySQL could open didn’t have any effect… so I searched on.
What I found was that MySQL keeps some files open for previously cached queries. So, once I executed a FLUSH TABLES; I no longer had any problems.
What FLUSH TABLES; does is clear the internal cache. After this I had no problem working with my 204 subpartitioned table anymore.
Hope this is helpful for someone else!
-Chris