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:
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.
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.)
There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.
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
i just delete the temp files...everything Ok. the problems is caused by overwrite.
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.
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.