Sometimes we face this kind of error when we setup the MySQL server and keep their setting default then we might get these kinds of errors while running any query or taking mysqldump and due to increased visitors on the application. So with this article, we have seen how to increase the ulimits either hard or soft. this article will help to fix Mysql/Mariadb error “Can’t open file ‘./*.frm’ (errno 24)”
Sometimes we face these kinds of errors when we setup the MySQL server and keep their setting default then we might get these kinds of errors while running any query or taking mysqldump and due to increased visitors on the application. These kinds of errors impact your application visitors and you have to take the server in emergency maintenance. Due to some of the default configurations in MySQL/MariaDB, we can face this
Can't open file: './*.frm' (errno: 24) error. We will see how to get rid of this error.
Why do i get this error and how to fix Mysql/Mariadb error
This error is mostly related to the open file limit so when the open file limit reached the maximum for the user who is operating MySQL service it throws the error Can’t open file: ‘./*.frm’ (errno: 24). you might face the issue while taking
mysqldump as well. so let see how to troubleshoot the error.
This is was came when taking mysqldump.
OS :- Centos 7
Panel :- Virtualmin
DB Server :- Mariadb
This is the server environment where I faced the issue and I have posted fixing steps in this article below.
This will help you to fix in Most case with CentOS 6/7 Either it’s with any panel or without panel.
Check current limits
- At first we need to check the limits, So to do that run below command
-aoption will show all the limits including hard, soft, open file:
You will get output something like below:
core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 31202 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 31202 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
- Just to see the current value for
all hard limitrun the below command in this
-Hrepresents to hard limit and
-arepresent all limits:
Above you can see them all hard limits in the image.
Same as to check
all soft limit values run following command:
You will get all the soft limit values as below image:
Now, Just to check only
open file limit.
Above will be output for the
Changing Value For ‘open files’ limit
- At first update the file
/etc/security/limits.confby below command:
- We need to add these below lines at the bottom of the file. These limits will be increased for all the users here
*represents all the users, so add these lines and save the file:
* soft nofile 102400 * hard nofile 102400 * soft nproc 10240 * hard nproc 10240
- Now edit another file /etc/security/limits.d/20-nproc.conf
- Same as above lines as mentioned below and save the file:
* soft nofile 1024000 * hard nofile 1024000 * soft nproc 10240 * hard nproc 10240 root soft nproc unlimited
Set open_files_limit in my.cnf (MySQL)
Now, we need to make changes where we need to resolve the error we are getting. that is related to MySQL service. so to do that just follow the steps as below:
- Edit MySQL conf file
- Put the below line under
[mysqld]section and save the file.
open_files_limit = 102400
As this is not enough sometimes to resolve this issue. I had to make changes to MariaDB service file. so to find the service file run the below command:
systemctl status mariadb | grep loaded
You will get output something like this :
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
You can see service file is
/usr/lib/systemd/system/mariadb.service so you need to open this file now, to open use the below command:
Now find the section
[Service] in the file and add the following line just below
After edit file should look like this:
- Now we need to reload the services to update these changes, to do that run below commands:
systemctl daemon-reload systemctl restart mariadb
Now you are ready to check the updated value, so to do that run the below command in your MySQL command prompt or phpmyadmin:
mysql -uuser -ppass
Then run the below command:
SHOW VARIABLES LIKE 'open_files_limit';
With the above command in MySQL, you will get something like this:
+------------------+--------+ | Variable_name | Value | +------------------+--------+ | open_files_limit | 102400 | +------------------+--------+ 1 row in set (0.00 sec)
If you are not able to see the changes, Please
Reboot the server and run the above query again in MySQL CLI:
Let us know if you are still having issues, leave the comment below if you have any doubts.