How to fix MySQL/MariaDB error “Can’t open file ‘./*.frm’ (errno 24)” !

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.

Read Also:

Server Environment

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 the below command -a option will show all the limits including hard, soft, and open file:

ulimit -a

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 limit run the below command in this -H represents to hard limit and -a represent all limits:

ulimit -Ha

Above you can see them all hard limits in the image.

Same as to check all soft limit values run following command:

ulimit -Sa

You will get all the soft limit values as below image:

Now, Just to check only open file limit.

ulimit -n

1024

Above will be output for the ulimit -n.

Changing Value For  ‘open files’ limit

• At first update the file /etc/security/limits.conf by below command:

vim /etc/security/limits.conf

• 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

vim /etc/my.cnf

• 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:

vim /usr/lib/systemd/system/mariadb.service

Now find the section [Service] in the file and add the following line just below [Service].

LimitNOFILE=102400

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:

Conclusion

Let us know if you are still having issues, leave the comment below if you have any doubts.