[SOLVED] How to Fix on Ubuntu/Debian linux - MySQL/MariaDB: Out of resources when opening file (Errcode: 24)

This is an issue which occurs due to the maximum number of open files allowed for a process (mysql in this case) being very less that what is required - something that happens because you have lots of large tables / a large DB. To fix this, you just have to increase the limits in a few places.

Here is how you do it:

Step 1: Identify the issue
as limit on the max number of open files (1024) for a single process (mysql) by doing these as root user:
ulimit -a | grep open

    root@server:/home/user# ulimit -a | grep open
    open files                      (-n) 1024

and
SHOW VARIABLES LIKE 'open%'; done in MySQL commandline, logged in as MySQL root user.

  root@server:/home/user# mysql -u root -p
  MariaDB [(none)]> SHOW VARIABLES LIKE 'open%';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | open_files_limit | 1024  |
  +------------------+-------+
  1 row in set (0.00 sec)

Step 2: Do the Fix

1. To the file /etc/security/limits.conf, add the following lines:
   

         mysql             soft    nofile           24000
         mysql             hard    nofile           32000
   

2. To the file /etc/pam.d/common-session, towards the end of the file, add the line:
   session required pam_limits.so
3. To the file /etc/pam.d/common-session-noninteractive, towards the end of the file, add the line:
   session required pam_limits.so
4. To the file /etc/my.cnf, under the section '[mysqld]', add the line:
   open_files_limit = 24000
5. Restart the server.

Step 3: Verify the fix with:
root@server:/home/user# ulimit -a | grep open
and
MariaDB [(none)]> SHOW VARIABLES LIKE 'open%';

--

References:

1. http://stackoverflow.com/questions/502545/too-many-open-files-error-on-u...
2. http://secure.hens-teeth.net/orders/knowledgebase/109/MYSQL-Out-of-resou...
3. https://forums.cpanel.net/f354/upgrade-mysql-5-5-sqlstate-hy000-general-...
4. http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit

--

Did this help you out? Please let me know using the Comments box below.

Cheers!