MySQL Bugs: #64986: MySQL 5.1 on Windows does not throw an error if open_file

Bug #64986	MySQL 5.1 on Windows does not throw an error if open_file_limit is set too high
Submitted:	15 Apr 2012 15:42	Modified:	16 Apr 2012 15:45
Reporter:	Matt Carlson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.1.x	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	open_file_limit windows

Description:
From my understanding, the 5.1.x branch of MySQL on Windows is limited to 2048 open files (open_file_limit).  If you set my.cnf to a number higher than this, MySQL starts up with no error messages, however, once you actually come to the hard limit of 2048 open files, MySQL continues to try to open more, but random errors occur, including MySQL believing the table is corrupted/crashed.  If attempting to alter a table, it complains it can't write to the temp file.

How to repeat:
Alter open_file_limits on a Windows box with MySQL 5.1.x, to something above 2048 tables.  Run queries to open more than 2048 files.  No error message about hitting the open_file_limit occurs, but you should start seeing table handler errors.

Suggested fix:
Block startup of MySQL on a windows server if open_file_limit is > 2048, OR alter MySQL to know that it's Windows, and shouldn't continue to try to open new files > 2048.

See bug: http://bugs.mysql.com/bug.php?id=24509.

there is actually a warning in the error log:

H:\ade\mysql\5.1\5.1.62\mysql-5.1.62-win32\bin>mysqld --no-defaults
--open-files-limit=10000 --log-warnings=2 --console --port=3333
120416 15:20:01 [Warning] option 'open_files_limit': unsigned value 10000 adjusted to
2048

@Shane: Understood, but since this does not block startup, and from the testing I did, it still tried to open above the 2048 limit, should this not be a default warning/error message?

Can you confirm you also get table handler issues if you have it set > 2048 and use the DB?

Depending on what storage engine is used, I'd expect various different errors when hitting open_files_limit. (e.g. bug #57895 , Bug #59385 )

The problem is likely your table_open_cache is simply too high.  mysqld cannot guess what a proper value for this is because 'it depends'.  Some tables require 1 file descriptor.  Some might require 1000 descriptors (large partitioned table, merge tables, etc). Thus no one formula can say for sure what a safe value is.
I'd personally never set table_open_cache to higher than ~650 on 5.1 on windows.
Even less if partitions are involved.

Ok, it sounds like it's me not taking care of things properly then.  I assumed it was an issue with open file limit, since it no longer had ANY issues once I set it to 2048.  I'll close this issue as my mistake.