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/foo.bar.baz
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:

[mysqld]
open-files-limit=2048

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.)

MySQL Errcode 17 follow-up

As a follow-up, I just ran into a similar problem this morning, with a "MySQL Errcode: 17" error in the log files. The actual error message looked like this:

Can't create/write to file '#sql_5db2_0.MYD' (Errcode: 17)

I don't have time to write a complete blog post on this today, but in short, this appears to be due to MySQL trying to re-use a temporary filename, and for some reason that old filename was already on the system. So, when it tries to write this file as a new file, it fails to overwrite the old file.
As I researched the problem as it relates to Drupal, the suggestions I saw were (a) give MySQL its own temporary directory (tmpdir), and (b) run the Drupal cron.php script more often. Again, I'm short on time today, and I don't know if these suggestions will help, but that's what I've

test result

i just delete the temp files...everything Ok. the problems is caused by overwrite.

Errcode 17

Did you get any solution on this? I am facing this error with mysql :
Can't create/write to file 'C:\Program Files\MySQL\MySQL Server 5.0\Data\#sql_fe0_0.MYI' (Errcode: 17)

Please let me know if u have got any pointers.

MySQL Errcode 17

I'm sorry, I haven't run into this problem in a long time now, and the solutions shown in the comments above are all I know about it.

Post new comment

The content of this field is kept private and will not be shown publicly.