1. Please take a little time for this simple survey! Thank you for participating!
    Dismiss Notice
  2. Dear Pleskians, please read this carefully! New attachments and other rules Thank you!
    Dismiss Notice
  3. Dear Pleskians, I really hope that you will share your opinion in this Special topic for chatter about Plesk in the Clouds. Thank you!
    Dismiss Notice

Resolved (Errcode: 24 - Too many open files) [23] - Plesk Onyx 17.5.3 and Ubuntu 16.04.2 LTS‬

Discussion in 'Plesk Onyx for Linux' started by ruben_0129, Apr 24, 2017.

  1. ruben_0129

    ruben_0129 New Pleskian

    0
    70%
    Joined:
    Apr 24, 2017
    Messages:
    5
    Likes Received:
    0
    Location:
    Madrid
    Good morning

    I have a problem with the configuration of my.cnf.

    I am shown the error:

    I have read the Plesk help articles, but the information they provide does not help me correct the problem.

    Plesk or website are not working: Unable to connect to database MySQL server has gone away: Full server backup is created with warning: errno: 24 - Too many open files

    I modify etc/mysql/my.cnf but it does not change any value.

    In what file and directory should I change the value open_files_limit?

    Thanks in advance
    .


    My my.cnf -> etc/mysql/my.cnf

    Code:
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    #
    # * 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/
    !includedir /etc/mysql/mysql.conf.d/
    [mysqld]
    open_files_limit = 1024000
    sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    bind-address = ::ffff:127.0.0.1
    local-infile=0
    
    [mysqld_safe]
    open_files_limit = 102400

    My mysql.cnf-> etc/mysql/mysql.cnf

    Code:
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    #
    # * 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/
    !includedir /etc/mysql/mysql.conf.d/
    [mysqld]
    local-infile=0
    

    My mysql.cnf -> etc/mysql/conf.d/mysql.cnf

    Code:
    [mysql]
    

    My mysqld.cnf -> etc/mysql/mysql.cnf.d/mysqld.cnf

    Code:
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    # escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    [mysqld_safe]
    socket        = /var/run/mysqld/mysqld.sock
    nice        = 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    user        = mysql
    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
    skip-external-locking
    
    #
    # 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
    #
    # * Fine Tuning
    #
    key_buffer_size        = 16M
    max_allowed_packet    = 16M
    thread_stack        = 192K
    thread_cache_size       = 8
    open-files-limit               = 65535
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover-options  = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit    = 1M
    query_cache_size        = 16M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Here you can see queries with especially long duration
    #log_slow_queries    = /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id        = 1
    #log_bin            = /var/log/mysql/mysql-bin.log
    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
    
    
     
    Last edited: Apr 24, 2017
  2. Peter Debik

    Peter Debik Golden Pleskian Plesk Guru

    37
    80%
    Joined:
    Oct 15, 2015
    Messages:
    1,926
    Likes Received:
    394
    Location:
    Berlin, Germany
    Please check this:
    #2
     
  3. ruben_0129

    ruben_0129 New Pleskian

    0
    70%
    Joined:
    Apr 24, 2017
    Messages:
    5
    Likes Received:
    0
    Location:
    Madrid
    Good afternoon,

    I have done what is stated in the publication, but it does not solve my problem.

    I have modified my file etc/mysql/my.cnf

    Code:
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    #
    # * 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/
    !includedir /etc/mysql/mysql.conf.d/
    [mysqld]
    open_files_limit = 1048576
    sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    bind-address = ::ffff:127.0.0.1
    local-infile=0
    
    [mysqld_safe]
    open_files_limit = 1048576

    And my file /etc/security/limits.conf

    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
    mysql            soft    nofile          1048576
    mysql            hard    nofile          1048576
    
    
    # End of file
    If I execute the command via ssh:

    ulimit -a | grep open

    the answer is:

    open files (-n) 1024

    and;

    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqladmin -uadmin variables | grep "open_files_limit"

    the answer is:

    | open_files_limit | 1024 |

    Should I change the values elsewhere?

    My /etc/pam.d/common-session ::

    Code:
    #
    # /etc/pam.d/common-session - session-related modules common to all services
    #
    # This file is included from other service-specific PAM config files,
    # and should contain a list of modules that define tasks to be performed
    # at the start and end of sessions of *any* kind (both interactive and
    # non-interactive).
    #
    # As of pam 1.0.1-6, this file is managed by pam-auth-update by default.
    # To take advantage of this, it is recommended that you configure any
    # local modules either before or after the default block, and use
    # pam-auth-update to manage selection of other modules.  See
    # pam-auth-update(8) for details.
    
    # here are the per-package modules (the "Primary" block)
    session    [default=1]            pam_permit.so
    # here's the fallback if no module succeeds
    session    requisite            pam_deny.so
    # prime the stack with a positive return value if there isn't one already;
    # this avoids us returning an error just because nothing sets a success code
    # since the modules above will each just jump around
    session    required            pam_permit.so
    # The pam_umask module will set the umask according to the system default in
    # /etc/login.defs and user settings, solving the problem of different
    # umask settings with different shells, display managers, remote sessions etc.
    # See "man pam_umask".
    session optional            pam_umask.so
    # and here are more per-package modules (the "Additional" block)
    session    required    pam_unix.so
    session    optional    pam_systemd.so
    session       required      pam_limits.so
    # end of pam-auth-update config
    My /etc/pam.d/common-session-noninteractive

    Tranks!
     
    Last edited: Apr 25, 2017
  4. ruben_0129

    ruben_0129 New Pleskian

    0
    70%
    Joined:
    Apr 24, 2017
    Messages:
    5
    Likes Received:
    0
    Location:
    Madrid
    Solution one part:

    Put in /lib/systemd/system/mysql.service

    Code:
    LimitNOFILE=1024000
    Test:

    # cat /proc/$(pgrep mysql)/limits | grep files
    Max open files 65536 65536 files



    #ulimit -a | grep open
    open files (-n) 1024

    Why does not the value change at all?
     
  5. mr-wolf

    mr-wolf Regular Pleskian Plesk Guru

    24
    73%
    Joined:
    Oct 26, 2016
    Messages:
    499
    Likes Received:
    97
    Location:
    Netherlands
    Last edited: Apr 26, 2017
Loading...