• If you are still using CentOS 7.9, it's time to convert to Alma 8 with the free centos2alma tool by Plesk or Plesk Migrator. Please let us know your experiences or concerns in this thread:
    CentOS2Alma discussion

Resolved Complete server Failure when auto-backuping database

Jose Pontvianne

New Pleskian
Hi!,

(Plesk 12.5 hosted in Arsys, SPAIN + Debian 8)

14 domains configured under Plesk working fine.
On a Scheduled Backup in Backupmanager we experienced a complete failure in our server, all services: mail, http, plesk panel, etc were down until a soft restart is done, and disable the backup.

Error log says:
Warning: mysql "qwb523"
Not all the data was backed up into /var/lib/psa/dumps/domains/opencartcfo.com/databases/qwb523_1 successfully. mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_6d65_2.MAD' (Errcode: 24 "Too many open files")' when trying to dump tablespaces mysqldump: Got error: 23: "Out of resources when opening file './qwb523/oc_module.MYD' (Errcode: 24 "Too many open files")" when using LOCK TABLES
Warning: mysql "qwb523"
Unable to execute SQL: Out of resources when opening file '/tmp/#sql_6d65_0.MAD' (Errcode: 24 "Too many open files"). SQL query: SHOW FULL COLUMNS IN `oc_information`

and... so on with all tables...

Some ideas:
- That db stores some fragments of php code, it's a doc&samples site of our OpenCart extension.
- May be some special chars are been injected during the backup process.
- We don't understand how is done that process into the main thread, so the failure of that thread causes a general failure in the server.

We could provide a copy of the database.
Best regards.
 
Increase the number of allowed open files on your system.

Edit /etc/security/limits.conf and edit or add:
Code:
mysql            soft    nofile          [max_number_of_open_files]
mysql            hard    nofile          [max_number_of_open_files]
Instead of [max_number_of_open_files] enter a high number. A good orientation is to execute
# lsof | wc -l
and add an extra margin, e.g. 50%. That's the value you want to have as max_number_of_open_files.

Then reload system daemon:
# systemctl --system daemon-reload
# sysctl -p
(Not perfectly sure, it this restarts the system daemon on Debian, I am not a Debian expert. But restart it, so that the new open files values are read and applied.)
 
Sorry, I forgot to mention that you should also increase the overall number of allowed open file handles:

# vi /etc/sysctl.conf

Add/modify:
fs.file-max = [max_number_of_open_files]
 
Thanks!

We will try it.

The point is how can a minor issue cause a critical failure in the whole server?¿
And seems to be a minor issue, just increasing overall number of allowed open file handles?

Plesk should run backup thread in a safe way, doesn't it?
 
I try fix reading all KBs and all threads related with this error without sucess!!
Any other solution?

ERROR: Plesk\Exception\Database: DB query failed: SQLSTATE[HY000]: General error: 23 Out of resources when opening file '/tmp/#sql_611_0.MYI' (Errcode: 24 - Too many open files), query was: DESCRIBE `sessions`

Additionally, an exception has occurred while trying to report this error: Zend_Exception
No entry is registered for key 'translate' (Mysql.php:53)
 
Out of resources when opening file '/tmp/#sql_611_0.MYI'
Check current number of open files limit with

mysql> show global variables like 'open%';

Increase this limit as it is described above and check again that changes were applied.
 
BTW, you can increase this limit directly in my.cnf with parameter like

open_files_limit=32768
 
Limit is 1024. Imposible change this value!
Found many user with the same problem. Please, we need KB for fix this problem.
upload_2017-4-19_2-0-49.png

upload_2017-4-19_2-1-44.png

upload_2017-4-19_2-2-14.png

upload_2017-4-19_2-3-11.png
 
Try to edit /usr/lib/systemd/system/mysqld.service file with line

LimitNOFILE=infinity

and then restart mysql service.
 
Ohh, Ubuntu...
What about /lib/systemd/system/mysql.service ?
 
During your solution attempts, did you try to reboot your server or restart the system daemon? Otherwise the /etc/security/limits.conf may not have been loaded, so that the increased values of /etc/security/limits.conf might not have been applied.
 
You are saying that
- Rebooting the server fixes the issue.
- Restarting MySQL service afterwards recreates the error.
I wonder how that can be, because rebooting the server also restarts MySQL. So why will MySQL use different configuration values when restarted by the root account from console opposed to restart in a reboot sequence?
 
Hi Marcio Nunes,

on Debian/Ubuntu - based systems, you have the choice to set individual open - files limits at: => /etc/security/limits.conf

Example:
Code:
# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#<domain>        <type>  <item>  <value>
#
#Where:
#<domain> can be:
#        - a user name
#        - a group name, with @group syntax
#        - the wildcard *, for default entry
#        - the wildcard %, can be also used with %group syntax,
#                 for maxlogin limit
#        - NOTE: group and wildcard limits are not applied to root.
#          To apply a limit to the root user, <domain> must be
#          the literal username root.
#
#<type> can have the two values:
#        - "soft" for enforcing the soft limits
#        - "hard" for enforcing hard limits
#
#<item> can be one of the following:
#        - core - limits the core file size (KB)
#        - data - max data size (KB)
#        - fsize - maximum filesize (KB)
#        - memlock - max locked-in-memory address space (KB)
#        - nofile - max number of open files
#        - rss - max resident set size (KB)
#        - stack - max stack size (KB)
#        - cpu - max CPU time (MIN)
#        - nproc - max number of processes
#        - as - address space limit (KB)
#        - maxlogins - max number of logins for this user
#        - maxsyslogins - max number of logins on the system
#        - priority - the priority to run user process with
#        - locks - max number of file locks the user can hold
#        - sigpending - max number of pending signals
#        - msgqueue - max memory used by POSIX message queues (bytes)
#        - nice - max nice priority allowed to raise to values: [-20, 19]
#        - rtprio - max realtime priority
#        - chroot - change root to directory (Debian-specific)
#
#<domain>      <type>  <item>         <value>
#

#*               soft    core            0
#root            hard    core            100000
#*               hard    rss             10000
#@student        hard    nproc           20
#@faculty        soft    nproc           20
#@faculty        hard    nproc           50
#ftp             hard    nproc           0
#ftp             -       chroot          /ftp
#@student        -       maxlogins       4

*               hard    nofile          64000
*               hard    nofile          64000
root            soft    nofile          1048576
root            hard    nofile          1048576
psaadm          soft    nofile          1048576
psaadm          hard    nofile          1048576
nginx           soft    nofile          1048576
nginx           hard    nofile          1048576
www-data        soft    nofile          1048576
www-data        hard    nofile          1048576
mysql           soft    nofile          1048576
mysql           hard    nofile          1048576

# End of file
As you can see, I changed not only the limits for the mysql - system-user, but as well for several others. ;) A reboot is required after you changed something here.​


In addition, pls. investigate possible ( unnecessary ) additional "my.cnf" - files, which are sometimes located in your "/root" - directory, while your correct MySQL - configuration has then no effect at "/etc/mysql/my.cnf". Some hosting providers offer server installation templates with such an additional "my.cnf" - file, where as well a password for the "root" - user has been defined, according to your choosen password over the Control Panel of your hosting provider. Since Plesk doesn't use the system - user "root" at the mysql - database ( Plesk uses the system - users "admin" ! ), you should even consider to delete or backup this file ( naming it to something like *.bak ), because it is unwanted when you use Plesk on your server. ;)

Another hint is to use a "FIND" - command, to search for additional, unwanted "my.cnf" - files on your server, which might interfere with your correct mysql - configuration file. Pls. use for example:
Code:
find / -name 'my.cnf'
and ignore the results "/var/lib/dpkg/alternatives/my.cnf" and "/etc/alternatives/my.cnf" and certainly your correct MySQL - configuration at "/etc/mysql/my.cnf". :)




 
Hi!

Not found others my.cnf:
upload_2017-4-19_11-53-38.png

I have copy your code and into /etc/security/limits.conf (replace all lines) and reboot the VPS.

max_open_files dont change, maybe, my my.cnf help in found the problem

Code:
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
open_files_limit = 8196000
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
open_files_limit = 8196000
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1

log-error       = /var/log/mysql/error.log

# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0

expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

# MEMORY variables
max_heap_table_size            =128M

# InnoDB variables
#innodb_strict_mode             = ON
#innodb_file_format_check       = 1
#innodb_buffer_pool_size        = 128M
#innodb_buffer_pool_instances   = 8

innodb_file_per_table          = 1
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size         = 8M
#innodb_log_file_size           = 256M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
 
Back
Top