How to fix MySQL error"Can't open file './*.frm' (errno 24)" !

Some times when you build a MySQL server without going thrum the small details and you just wanna put it online as fast as possible the long run is affected as you will end up doing unwanted emergent maintenance or your MySQL instance will become unavailable. Because of the reasons stated in the previous paragraph the "Can't open file: './*.frm' (errno: 24)" error will appear.

  • Why do i get this error ?
  • Well this a common error when you install everything using the default parameters. The "Can't open file: './*.frm' (errno: 24)" error comes up when your operational system(host) reached the max open files value available for the user who is running the MySQL service.
  • How to find the max open files value for my MySQL user ?
  • You need to log in as the MySQL user and issue the following command:
    [mysql@dch008tmp]# ulimit -n
    1024
  • How can we alter the value of the max open files value for the MySQL user ?
  • For this you need to log in as the root user and edit the "/etc/security/limits.conf" file. Here is how i do it for my MySQL in order for him to be able to open 25.000 files.
    mysql            hard    nofile          25000
  • Where :
  • nofile - max number of open files hard - for enforcing hard limits
  • In order for the MySQL instance to be able to apply the new parameter value you need to restart your MySQL instance.
  • After restart check you open_files_limit value using the syntax bellow. Also i good to know that your MySQL instance will apply the host parameter values if you don't specify it on your my.cnf MySQL parameter file.
    mysql> show variables like 'open_files_limit';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | open_files_limit | 25000 |
    +------------------+-------+
    1 row in set (0.00 sec)
  • If you want to make this parameter available to your my.cnf file just add the values bellow to your my.cnf file.
  • Before doing this you should check the value on the host to make sure that your MySQL parameter won't pass the host one.
    open-files-limit               = 25000
    I hope this was helpfull.