Resolved Complete server Failure when auto-backuping database

Discussion in 'Plesk 12.x for Linux' started by Jose Pontvianne, Dec 30, 2016.

  1. Jose Pontvianne

    Jose Pontvianne New Pleskian

    2
    20%
    Joined:
    Dec 30, 2016
    Messages:
    4
    Likes Received:
    0
    Location:
    Spain
    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.

     
  2. Peter Debik

    Peter Debik Golden Pleskian Plesk Guru

    34
    30%
    Joined:
    Oct 15, 2015
    Messages:
    1,425
    Likes Received:
    233
    Location:
    Berlin, Germany
    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.)
     
  3. Peter Debik

    Peter Debik Golden Pleskian Plesk Guru

    34
    30%
    Joined:
    Oct 15, 2015
    Messages:
    1,425
    Likes Received:
    233
    Location:
    Berlin, Germany
    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]
     
  4. Jose Pontvianne

    Jose Pontvianne New Pleskian

    2
    20%
    Joined:
    Dec 30, 2016
    Messages:
    4
    Likes Received:
    0
    Location:
    Spain
    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?
     
  5. Marcio Nunes

    Marcio Nunes Basic Pleskian

    7
    20%
    Joined:
    Jan 12, 2016
    Messages:
    31
    Likes Received:
    0
    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)
     
  6. IgorG

    IgorG Forums Analyst Plesk Team

    43
    84%
    Joined:
    Oct 27, 2009
    Messages:
    23,144
    Likes Received:
    805
    Location:
    Novosibirsk, Russia
    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.
     
  7. IgorG

    IgorG Forums Analyst Plesk Team

    43
    84%
    Joined:
    Oct 27, 2009
    Messages:
    23,144
    Likes Received:
    805
    Location:
    Novosibirsk, Russia
    BTW, you can increase this limit directly in my.cnf with parameter like

    open_files_limit=32768
     
  8. Marcio Nunes

    Marcio Nunes Basic Pleskian

    7
    20%
    Joined:
    Jan 12, 2016
    Messages:
    31
    Likes Received:
    0
    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
     
  9. Marcio Nunes

    Marcio Nunes Basic Pleskian

    7
    20%
    Joined:
    Jan 12, 2016
    Messages:
    31
    Likes Received:
    0
  10. IgorG

    IgorG Forums Analyst Plesk Team

    43
    84%
    Joined:
    Oct 27, 2009
    Messages:
    23,144
    Likes Received:
    805
    Location:
    Novosibirsk, Russia
    Try to edit /usr/lib/systemd/system/mysqld.service file with line

    LimitNOFILE=infinity

    and then restart mysql service.
     
  11. Marcio Nunes

    Marcio Nunes Basic Pleskian

    7
    20%
    Joined:
    Jan 12, 2016
    Messages:
    31
    Likes Received:
    0
    This file dont exist.
    Try create and save, but cannot.

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


    upload_2017-4-19_2-14-36.png
     
  12. IgorG

    IgorG Forums Analyst Plesk Team

    43
    84%
    Joined:
    Oct 27, 2009
    Messages:
    23,144
    Likes Received:
    805
    Location:
    Novosibirsk, Russia
    Ohh, Ubuntu...
    What about /lib/systemd/system/mysql.service ?
     
  13. Marcio Nunes

    Marcio Nunes Basic Pleskian

    7
    20%
    Joined:
    Jan 12, 2016
    Messages:
    31
    Likes Received:
    0
    Coincidentally the file was already with this line.
    PS.: I've trying to solve it for 2 weeks

    upload_2017-4-19_2-19-47.png
     
  14. Peter Debik

    Peter Debik Golden Pleskian Plesk Guru

    34
    30%
    Joined:
    Oct 15, 2015
    Messages:
    1,425
    Likes Received:
    233
    Location:
    Berlin, Germany
    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.
     
  15. Marcio Nunes

    Marcio Nunes Basic Pleskian

    7
    20%
    Joined:
    Jan 12, 2016
    Messages:
    31
    Likes Received:
    0
    Yes. Only rebooting stop this error.
    Rebooting MySQL service, in minutes repeat error and ALL websites dont work.
     
  16. Peter Debik

    Peter Debik Golden Pleskian Plesk Guru

    34
    30%
    Joined:
    Oct 15, 2015
    Messages:
    1,425
    Likes Received:
    233
    Location:
    Berlin, Germany
    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?
     
  17. Marcio Nunes

    Marcio Nunes Basic Pleskian

    7
    20%
    Joined:
    Jan 12, 2016
    Messages:
    31
    Likes Received:
    0
    Rebooting my VPS, have the feeling that it will take time to occur again. Maybe I'm wrong.
     
  18. UFHH01

    UFHH01 Plesk addicted! Plesk Guru

    43
    64%
    Joined:
    Jun 11, 2013
    Messages:
    5,380
    Likes Received:
    1,163
    Location:
    Hamburg / Germany
    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". :)





     
  19. Marcio Nunes

    Marcio Nunes Basic Pleskian

    7
    20%
    Joined:
    Jan 12, 2016
    Messages:
    31
    Likes Received:
    0
    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/
    
     
  20. Marcio Nunes

    Marcio Nunes Basic Pleskian

    7
    20%
    Joined:
    Jan 12, 2016
    Messages:
    31
    Likes Received:
    0
    Best Answer
    Last edited: Apr 19, 2017
Loading...