Resolved Complete server Failure when auto-backuping database

[Jose Pontvianne]

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.

 

[Bitpalast]

Increase the number of allowed open files on your system.

Edit /etc/security/limits.conf and edit or add:

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.)

 

[Bitpalast]

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]

 

[Jose Pontvianne]

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?

 

[Marcio Nunes]

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)

 

[IgorG]

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.

 

[IgorG]

BTW, you can increase this limit directly in my.cnf with parameter like

open_files_limit=32768

 

[Marcio Nunes]

Limit is 1024. Imposible change this value!
Found many user with the same problem. Please, we need KB for fix this problem.

 

[Marcio Nunes]

 

[IgorG]

Try to edit /usr/lib/systemd/system/mysqld.service file with line

LimitNOFILE=infinity

and then restart mysql service.

 

[Marcio Nunes]

This file dont exist.
Try create and save, but cannot.

PS.: I use Ubuntu in all VPSs and update to MySQL 5.6

 

[IgorG]

Ohh, Ubuntu...
What about /lib/systemd/system/mysql.service ?

 

[Marcio Nunes]

Coincidentally the file was already with this line.
PS.: I've trying to solve it for 2 weeks

 

[Bitpalast]

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.

 

[Marcio Nunes]

Yes. Only rebooting stop this error.
Rebooting MySQL service, in minutes repeat error and ALL websites dont work.

 

[Bitpalast]

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?

 

[Marcio Nunes]

Rebooting my VPS, have the feeling that it will take time to occur again. Maybe I'm wrong.

 

[UFHH01]

Hi Marcio Nunes,

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

Example:

# /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:

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".

​

 

[Marcio Nunes]

Hi!

Not found others my.cnf:


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

# 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/

 

[Marcio Nunes]

Hi!

Thank you for quick Support. This KB fixed my problem:
Open file limit and max_connections values are not updated / applied

Big thanks!!!