Bug #17646 open_files_limit variable cannot be set
Submitted: 22 Feb 2006 13:20 Modified: 27 Sep 2008 18:42
Reporter: d di (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.18 OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[22 Feb 2006 13:20] d di
Description:
I'm getting various errors (eg. error 24) from two MySQL systems after upgrading them to 5.0.18.
After that, these MySQL systems seems to be completely unable to access the affected tables.
A restart of the MySQL server helps for about 2 minutes.

The cause seems to be that there are lots of users on those systems, and that causes MySQL to run out of some sort of file descriptors (?).  The affected servers are (has been for years) configured with open_files_limit to set this limit high enough for the servers to cope, but:

It seems that 5.0.18 completely ignores open_files_limit when given on the command line or in my.ini.

Any chance of a fix or an explanation?

How to repeat:
1.) First, install mysql server 5.0.18 on a Windows system.

2.) A quick test is to open cmd.exe, CD to mysql's bin directory and then type:
mysqld --open_files_limit=12345 --help --verbose | find "open_files"

The open_files_limit number should show 12345, but it doesn't.

3.) Start the mysql server with "--open_files_limit=12345" or enter open_files_limit=12345 under [mysqld] in %windir%\my.ini.

4.) Log in and execute SHOW GLOBAL VARIABLES LIKE 'open%'.

The open_files_limit number should show 12345, but it doesn't.
[22 Feb 2006 13:34] MySQL Verification Team
Yes the file descriptors are limited to 2048 on Windows.
What I suggest you is for to reduce at minimum possible the
value of table_cache variable (every table uses 2 files descriptor).
[4 Apr 2008 21:04] Abraham Chaffin
I'm getting the same error on a Gentoo linux machine - trying to do a mysqldump

# mysqldump -u root -p --opt databasename > datafile.sql
Enter password:
mysqldump: Got error: 29: File './databasename/tablename.MYD' not found (Errcode: 24) when using LOCK TABLES

Is there a work around for this on linux - I'd prefer not to delete tables to be able to dump the database:

mysql  Ver 14.12 Distrib 5.0.44, for pc-linux-gnu (x86_64)
[27 Sep 2008 18:42] Vladislav Vaintroub
Fixed with  Bug#24509 (in 6.0)