unable to connect to database

Discussion in 'ISPConfig 3 Priority Support' started by budgierless, Jul 20, 2019.

  1. budgierless

    budgierless Member

    hi,
    my mail error.logs show it cant connect to the database, i also noted this issue when trying to install a script, the prolem is localhost dose not work in some cases but 127.0.0.1 will work,
    but i dont dont know where to make the changes to solve these issues:
    Code:
    server1 dovecot: auth-worker(30554): Error: mysql(localhost): Connect failed to database (dbispconfig): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) - waiting for 1 seconds before retry
    Code:
    server1 amavis[2319]: (02319-01) (!!)TROUBLE in process_request: connect_to_sql: unable to connect to any dataset at (eval 110) line 253.
    please advise
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    If you want to change from socket to IP connection, then you'll have to alter the database details in /etc/dovecot/dovecot-sql.conf and in the amavis 50-user configuration file.
     
  3. budgierless

    budgierless Member

    any idea how i can fix the socket so i can avold changing to IP? the socket should use 127.0.0.1 by default right?

    UPDATE: Sorry forgot to add this error from the log:
    Code:
    server1 postfix/proxymap[2303]: warning: connect to mysql server 127.0.0.1: Access denied for user 'ispconfig'@'127.0.0.1' (using password: YES)
     
    Last edited: Jul 20, 2019
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    The socket and IP are two different things. the socket is /var/run/mysqld/mysqld.sock and the localhost IP is 127.0.0.1.

    I don't know why you have this problem, probably you changed the mysql configuration. E.g. you might have disabled networking in MySQL. If you did that, revert back to your original config.
     
  5. budgierless

    budgierless Member

    I dont know why im having this problem too, the only changes i have made is to optimise performance, see my mariadb 50-server.cnf below.
    Code:
    #
    # These groups are read by MariaDB server.
    # Use it for options that only the server (but not clients) should see
    #
    # See the examples of server my.cnf files in /usr/share/mysql/
    #
    
    # this is read by the standalone daemon and embedded servers
    [server]
    
    # this is only for the mysqld standalone daemon
    [mysqld]
    performance_schema=ON
    performance-schema-instrument='stage/%=ON'
    performance-schema-consumer-events-stages-current=ON
    performance-schema-consumer-events-stages-history=ON
    performance-schema-consumer-events-stages-history-long=ON
    
    # Skip reverse DNS lookup of clients
    #skip-name-resolve=1
    
    #
    # * 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        = 15524M
    max_allowed_packet    = 1024M
    net_buffer_length    = 100k
    thread_stack        = 192K
    thread_cache_size       = 8
    table_open_cache = 3734
    table_definition_cache = 3000
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam_recover_options  = BACKUP
    max_connections        = 500
    #table_cache            = 64
    #thread_concurrency     = 10
    
    join_buffer_size = 3M
    sort_buffer_size = 4M
    read_buffer_size = 4M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 4M
    tmp_table_size = 2116M
    max_heap_table_size = 2116M
    max_seeks_for_key = 2000
    open_files_limit = 10824
    flush_time = 3600
    low_priority_updates=1
    #
    # * Query Cache Configuration
    #
    query_cache_type    = 0
    query_cache_limit    = 512k
    query_cache_size        = 0M
    
    #
    # * 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
    #
    # Enable the slow query log to see queries with especially long duration
    slow_query_log=1
    slow_query_log_file= /var/log/mysql/mariadb-slow.log
    #log_slow_queries    = /var/log/mysql/log-slow.log
    long_query_time = 2
    #log_slow_rate_limit    = 1000
    #log_slow_verbosity    = query_plan
    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    = exclude_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!
    #innodb_additional_mem_pool_size = 160M
    innodb_buffer_pool_size = 1912M
    innodb_buffer_pool_instances = 1
    #innodb_file_per_table = 1
    innodb_log_file_size = 239M
    #innodb_log_buffer_size = 2M
    #innodb_flush_log_at_trx_commit = 1
    #innodb_lock_wait_timeout = 30
    #innodb_file_format = barracuda
    #innodb_thread_concurrency = 4
    #innodb_force_recovery = 1
    #innodb_use_sys_malloc = 0
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates you can use for example the GUI tool "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    #
    # Accept only connections using the latest and most secure TLS protocol version.
    # ..when MariaDB is compiled with OpenSSL:
    # ssl-cipher=TLSv1.2
    # ..when MariaDB is compiled with YaSSL (default in Debian):
    # ssl=on
    
    #
    # * Character sets
    #
    # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
    # utf8 4-byte character set. See also client.cnf
    #
    character-set-server  = utf8mb4
    collation-server      = utf8mb4_general_ci
    
    #
    # * Unix socket authentication plugin is built-in since 10.0.22-6
    #
    # Needed so the root database user can authenticate without a password but
    # only when running as the unix root user.
    #
    # Also available for other users if required.
    # See https://mariadb.com/kb/en/unix_socket-authentication-plugin/
    
    # this is only for embedded server
    [embedded]
    
    # This group is only read by MariaDB servers, not by MySQL.
    # If you use the same .cnf file for MySQL and MariaDB,
    # you can put MariaDB-only options here
    [mariadb]
    
    # This group is only read by MariaDB-10.1 servers.
    # If you use the same .cnf file for MariaDB of different versions,
    # use this group for options that older servers don't understand
    [mariadb-10.1]
    
     
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, that's a possible reason. Did you make a backup so you can go back to the original config? If you don't make a backup then it might be that you can find one in an ISPConfig backup in /var/backup which is made when you update ISPConfig. the backup contains a complete copy of the /etc directory.
     
  7. budgierless

    budgierless Member

    I restored backup of 50-server.cnf file, but this has know effect, the mail error log still shows:
    Code:
    server1 dovecot: auth-worker(4598): Error: mysql(localhost): Connect failed to database (dbispconfig): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) - waiting for 5 seconds before retry
     
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    Did you restart mariadb? Is the socket /var/run/mysqld/mysqld.sock there?
     
  9. budgierless

    budgierless Member

    yes and yes to answer your questions, this is a strange problem, is their an output that could show what is going on?
     
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    Did you try to reboot the server?
     
  11. budgierless

    budgierless Member

    yes i did that too
     
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    any errors in the mysql or mariadb logs?
     
  13. budgierless

    budgierless Member

    no, just notices and warnings, but no errors
     
  14. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Have you checked if you can login from the command line using the mysql details provided inside the dovecot/amavis config?
     
  15. budgierless

    budgierless Member

    HEy Mr Croydon haha,
    no i have not dont that, not sure how but will will give it a try.
     
  16. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    mysql -u <userfromconfig> -D dbispconfig -p
    -> enter password from config
     
  17. budgierless

    budgierless Member

    thanks, yes i was able to login via command line.

    A bit strange, i just noticed the in the dovecot directory, most files are owned by root:root and 3 files root:dovecote, is this right, should they all not be the same?
     
  18. budgierless

    budgierless Member

  19. till

    till Super Moderator Staff Member ISPConfig Developer

    That's not related to your problem. Your problem is that mysql does not allow connections trough its socket. Which tutorial did you use to setup the server?
     

Share This Page