Error: 'Out of resources when opening file '/tmp/#sql_390d_2.MYD' (Errcode: 24 - Too many open files)' when trying to dump tablespaces


Enter Your Query:
Use '%' for wildcards and quotes for "exact phrases"


Top Level » MySQL / MariaDB

Error: 'Out of resources when opening file '/tmp/#sql_390d_2.MYD' (Errcode: 24 - Too many open files)' when trying to dump tablespacesLast Modified: Jul 9, 2015, 1:20 am
If you get the following error while creating a backup:

Error while backing up database USER_NAME
mysqldump error output: mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_390d_2.MYD' (Errcode: 24 - Too many open files)' when trying to dump tablespaces
mysqldump: Couldn't execute 'show fields from `NAME_accesslog`': Out of resources when opening file '/tmp/#sql_390d_0.MYD' (Errcode: 24 - Too many open files) (23)

150707  0:15:49 [ERROR] Error in accept: Too many open files

check this forum thread for solutions:
http://forum.directadmin.com/showthread.php?t=47098



Some solutions:

1) Edit /etc/init.d/mysqld and add this to the top, after #!/bin/sh

ulimit -HSn 1024
ulimit -HSn 32768
ulimit -HSn 1024000

which should force it to try and set it as high as it can go.  If one of the value is too high, delete that line, or lower the value.

2)  Check /etc/my.cnf.  Under the [mysqld] section, if you have a open_files_limit variable, increase it, eg:

open_files_limit=32768

then restart mysqld.

3) Confirm in mysql itself by checking the open_files_limit variable to ensure it's increased, by running the SQL query:

show global variables like 'open%';




© 2018 JBMC Software, Suite 173  3-11 Bellerose Drive, St Albert, AB  T8N 1P7  Canada.  Mon-Fri 9AM-5PM MST