Solving the MySQL "can't create/write to file" error message

Two MySQL error messages

Earlier this week I ran into a problem with a website that I tracked down to a MySQL error message that looked like this:

can't create/write to file /var/lib/mysql/
Errcode: 23

(The actual file referenced in that error message doesn't matter, as it will be different on most systems, but the "can't create/write to file" is important.) After digging through my log files even more, I also found this error message related to MySQL:

Got error 12 from select
Error in accept: Too many open files in system

Both of these error messages ("can't create/write to file", "too many open files in system") are related to the same problem that I was able to solve: MySQL is trying to open more files on my Linux system than it was configured to allow.

Other related error messages

Before I move on to the solution, it's also worth noting that you may see different MySQL error messages based on this same root cause. In fact, the MySQL documentation shows that you can run into several different error messages that all have this same root cause.

Here are several of the error messages they show, which they display with the perror utility:

shell> perror 23
OS error code  23:  File table overflow
shell> perror 24
OS error code  24:  Too many open files
shell> perror 11
OS error code  11:  Resource temporarily unavailable

Solving the root cause of the problem

Reading through the MySQL documentation, there appear to be a number of ways to solve this problem, but they all relate to the limit on the number of files a user can have open at one time.

In my case I solved this problem by changing the MySQL variable open-files-limit to a value that is much higher than its default value. I did this by editing the MySQL /etc/my.cnf configuration file on my CentOS Linux system, and setting the value like this:


One word of caution here: So far I don't know how to determine the number of open files my application and MySQL actually need, so I'm starting with 2,048, and I'll increase that number as needed.

MySQL documentation

The MySQL website has several documents that describe these error messages, and the potential steps to solve these problems. Here are the two most helpful links I found on their site:

(As I final note, let me apologize here that this article is a little rushed. I'll come back here and clean it up when I have a little more time.)