MySQL Bugs: #19134: mysqld runs out of file descriptors (error: 1017, errno: 9)

Bug #19134	mysqld runs out of file descriptors (error: 1017, errno: 9)
Submitted:	17 Apr 2006 0:50	Modified:	12 Jun 2006 18:28
Reporter:	P-O Yliniemi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18, 5.0.19, 5.0.20	OS:	Other (OpenBSD 3.9)
Assigned to:		CPU Architecture:	Any

Description:
Running a mysqldump gives (after a very short time):
Got error: 1017: Can't find file: './wordpress_wonttelldbname/wp_options.frm' (errno: 9) when using LOCK TABLES

This because of running out of file descriptors
# fstat |grep mysql |wc -l
     128

tells me that 128 files are opened by the user running the mysqld process.

Trying to connect to the db from the localhost gives me:
mysql> show databases;
ERROR 1018 (HY000): Can't read dir of '.' (errno: 9)
mysql>

The mysql error log says:
[ERROR] /usr/local/libexec/mysqld: Can't find file: './wordpress_wonttelldbname/wp_options.frm' (errno: 9)

How to repeat:
Install on an out-of-the-box OpenBSD 3.8 / 3.9:
mysql 5.0.18 from ports, or mysql 5.0.19 from source
Use the default configuration my-huge.cnf (any will crash sooner or later)

Run a couple of database backups sequentially. Backing up databases with a lot of tables makes the problem appear more quickly.

On my server it appears when running the backup for the sixth database (test, information_schema and four WordPress blogs which sums up in only 54 opened tables)

This problem will also apperar when accessing mysql through PHP, when too many files (tables) has been opened and not released for reuse.

Suggested fix:
Temporary fix:
add the option
open-files-limit = 8192

to my.cnf

Looks similar to http://bugs.mysql.com/bug.php?id=17089 
so maybe a problem common to BSD based systems?

Thank you for a problem report. Please, send your my.cnf file content and

ulimit -a

results from your system (from a session of a user that runs your mysqld process).

/etc/my.cnf = my-huge.cnf with the MySQL 5.0.19 distribution

# su - _mysql
$ ulimit -a
time(cpu-seconds)    unlimited
file(blocks)         unlimited
coredump(blocks)     unlimited
data(kbytes)         1048576
stack(kbytes)        8192
lockedmem(kbytes)    unlimited
memory(kbytes)       unlimited
nofiles(descriptors) 8192
processes            4087

bug related my.cnf (just the same as my-huge.cnf with another datadir)

Attachment: my.cnf (application/octet-stream, text), 5.03 KiB.

Send the results of 

show variables like 'open%';

from "stock" installation of 5.0.19 (or, beeter, 5.0.20) on your platfrom, without the workaround applied.

mysql> show variables like "open%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1134  |
+------------------+-------+
1 row in set (0.00 sec)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.20-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "open%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1134  |
+------------------+-------+
1 row in set (0.00 sec)

show variables liks "open%" with the workaround in my.cnf (open-files-limit = 8192):

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 11010 |
+------------------+-------+
1 row in set (0.00 sec)

(mysql 5.0.20)

Please, check http://jeremy.zawodny.com/blog/archives/000697.html. Try to build from sources (5.0.21) with -DHAVE_BROKEN_REALPATH as suggested, and check if it solves your problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".