Database operations fail in Plesk for Linux: “Can’t open file” or “Out of resources when opening file”

Symptoms

• When backing up/restoring/exporting/importing a MySQL database in Plesk, the following error message is shown:

  mysqldump: Got error: 1016: "Can't open file: './example_db/table.frm' (errno: 24)" when using LOCK TABLES

  ---

  Not all the data was backed up into [...]/databasename_1 successfully. mysqldump: Got error: 23: Out of resources when opening file '[...]' (Errcode: 24 - Too many open files) when using LOCK TABLES

• The value of max_connections changes to 214 automatically after some time, although the value itself is defined in my.cnf:

  # grep -i 'max_connections' /etc/my.cnf
  max_connections=2048

  # plesk db "SHOW GLOBAL VARIABLES LIKE 'max_connections' G"
  *************************** 1. row ***************************
  Variable_name: max_connections
  Value: 214

Cause

Default open_files_limit for MySQL has been reached.

Resolution

Increase open_files_limit for the MySQL service as below.

Note: The solution below is applicable to systemd-based systems:
    - RHEL/CentOS/CloudLunix 7 and above
    - Ubuntu 16.04 and above
    - Debian 8 and above

1. Connect to the server via SSH.

2. Find the MySQL service in use:

   # systemctl list-unit-files | grep -E 'mysql|mariadb'
   mariadb.service enabled

3. Open the service configuration file from the step above in an editor:

   # systemctl edit mariadb.service

4. Add the following content:

   [Service]
   LimitNOFILE=4096

5. Save the changes and close the file.

6. Restart the MySQL/MariaDB server to apply the changes:

   # systemctl restart mysql mysqld mariadb 2>/dev/null

   If the same error appears after these changes, increase the value.