[SOLVED!] Client unable to connect to MySQL (1045)

Discussion in 'Installation/Configuration' started by niemand-glaumy, Dec 25, 2017.

  1. niemand-glaumy

    niemand-glaumy New Member

    Hello and happy Hanuka/Christmas/Xmas/Treeocaust (whatever you chose to believe in.)!

    I am still at setting this up and my test client found out he is unable to connect to his DB. He created it and a user for it, tried to connect (localhost:3306) and ends up getting a MySQL-error 1045 (can not login [email protected] Password: YES). The password is correct, I made him check twice.
    phpmyadmin works, though (I had another thread of getting it to run, you might remember).

    After a bit of trouble shooting, I found a suggestion and I will have him try 127.0.0.1 instead of localhost tomorrow. But since I don't want to waste time, I ask YOU for your advise. :)
    Code:
    netstat -nlp | grep 3306
    tcp6       0      0 :::3306                 :::*                    LISTEN      1464/mysqld
    I saw there, that it is tcp6 and that there is no ip listed (should be 127.0.0.1 or 0.0.0.0?)

    And:
    Code:
    netstat -nlp | grep mysql
    tcp6       0      0 :::3306                 :::*                    LISTEN      1464/mysqld
    unix  2      [ ACC ]     STREAM     LISTENING     19328    1464/mysqld         /var/run/mysqld/mysqld.sock
    Any advise/suggestion?

    Quite appreciated! <3
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    The netstat lines are fine. let him try the IP address for the connection.
     
    niemand-glaumy likes this.
  3. niemand-glaumy

    niemand-glaumy New Member

    Happy new year!

    The 127.0.0.1 won't work because of 1045.

    We also tried the proper server-ip, which says:
    Code:
    Connecting to MySQL server '<ip>' failed
    SQLSTATE[HY000] [1130] Host '<censored>' is not allowed to connect to this MariaDB server
     
  4. cocovina

    cocovina New Member

    I have a similar problem on new installed server. I have installed the server using this tutorial: (https://www.howtoforge.com/tutorial...-9-stretch-apache-bind-dovecot-ispconfig-3-1/).
    Everything looks good and I can work in ISCPconfig panel or migrate users from my old server by migration tool.
    But, duruing the clients functionality checks I can see that there is no possible loging into the server by FTP and postfix does not works too.
    Reason is, that ispconfig installation created configurations with host = 127.0.0.1. But in Mariadb privileges the ispconfig user have localhost only.
    I am confused a little bit because on the old server is the same situation, but works. Just root has all provileges for localhost and for 127.0.0.1 too.
    Please see...
    OLD server:
    #mysql -h 127.0.0.1 -u ispconfig -p********* dbispconfig
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 327210
    Server version: 5.1.73-1+deb6u1-log (Debian)

    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> \q
    Bye

    NEW server:
    #mysql -h 127.0.0.1 -u ispconfig -p********* dbispconfig
    ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MariaDB server

    Did I make a some mistake during install or forget something do?
    The old server is Debian6 and the new is Debian9. I know, there is lots of diferencies. But...
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    The configuration is is fine like that and it works here when I test it on Debian 9 with the setup that is described in the tutorial you used. Maybe you have wrong settings in your /etc/hosts file so that localhost is not IP 127.0.0.1 on your system.
     
    cocovina likes this.
  6. cocovina

    cocovina New Member

    THX for your reply. Everything points to the host table. It was one of first I checked (during googling).
    I rechecked it again now and I see that I have this line only:
    # grep localhost /etc/hosts
    127.0.0.1 localhost
    ::1 localhost ip6-localhost ip6-loopback

    So I changed it to
    127.0.0.1 localhost.localdomain localhost
    ... an its working OK now !!! :)
    THX again for your hint (I overlooked this in tutorial before)!
     
  7. cocovina

    cocovina New Member

    Sorry! Actually the problem causes this directive:
    skip-name-resolve=1
    Comment out this, really solved the problem.
     
  8. niemand-glaumy

    niemand-glaumy New Member

    I have to correct myself: Logging in via phpmyadmin is not working, either, also a 1045:
    Code:
     #1045 - Access denied for user 'c2niemandtest'@'localhost' (using password: YES)
    Username is correct (I doublechecked) and pwd is copied over directly from ISPC-pwd-generation.

    Okay, if you're still with me, let's go through both answers with proper examples - I feel really stupid being confused by the hosts file right now, tbh.:
    My /etc/hostnames shows:
    Code:
    web.fubar.tld
    My /etc/hosts shows:
    Code:
    127.0.0.1       localhost
    12.345.67.89  fubar.tld web.fubar.tld
    
    # The following lines are desirable for IPv6 capable hosts
    ::1     localhost ip6-localhost ip6-loopback
    ff02::1 ip6-allnodes
    ff02::2 ip6-allrouters
    Could you please tell me where that file is located?
    the /etc/mysql/my.cnf has no other content than:
    Code:
    # The MariaDB configuration file
    #
    # The MariaDB/MySQL tools read configuration files in the following order:
    # 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
    # 2. "/etc/mysql/conf.d/*.cnf" to set global options.
    # 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
    # 4. "~/.my.cnf" to set user-specific options.
    #
    # If the same option is defined multiple times, the last one will apply.
    #
    # 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.
    
    #
    # This group is read both both by the client and the server
    # use it for options that affect everything
    #
    [client-server]
    
    # Import all .cnf files from configuration directory
    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mariadb.conf.d/
     
  9. cocovina

    cocovina New Member

    I added it into /etc/mysql/mariadb.conf.d/50-server.cnf
    But try grep all files in ...
    # 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
    # 2. "/etc/mysql/conf.d/*.cnf" to set global options.
    # 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
    # 4. "~/.my.cnf" to set user-specific options.
     
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    Did you check that the mysql uers c2niemandtest exists in the mysql user table? Are you able to login to that user with mysql command?
     
  11. niemand-glaumy

    niemand-glaumy New Member

    I'll check. What if it doesn't?

    Edit: No it is not in there. The User for c1 is in there, though.

    When trying to login, I was also unable to stop or restart the mysql service, it was still running. A restart changed nothing. I had to seriously kill it, which worked.
     
    Last edited: Jan 12, 2018
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    Then probably the mysql root password has been changed after ISPConfig installation and you'll have to set the new password in /usr/local/ispconfig/server/lib/mysql_clientdb.conf file.
     
    niemand-glaumy likes this.
  13. niemand-glaumy

    niemand-glaumy New Member

    [Edit: Post before deleted because obsolete. Turning Triplepost into doublepost.]

    I changed the root pwd in the mysql.user table and in the mysql_clientdb.conf file, as you advised. I rebooted the whole server afterwards (doesn't take long and there is nothing on it except this one user/client who helps me figure everything out.). But it did not change anything, it still does not add a new user to the table when I try to create one (also not for c1, which means you were right and I did change the PWD).

    Also, the /phpmyadmin page shows
    Code:
    Connection for controluser as defined in your configuration failed.
    The username and pwd in the mysql_client.db are correct and the host shows 'localhost'.


    EDIT - The Problem right now:
     
    Last edited: Jan 22, 2018
  14. niemand-glaumy

    niemand-glaumy New Member

    After merging all my posts above, here should be my last post in this topic:

    The last three posts of this were my solution/explanation:
    https://www.howtoforge.com/community/threads/ispconfig-3-mysql-user-problem.62282/

    In a nutshell:
    Step 1) Create a DB-User in ISPC (it will not be created in MySQL!)
    Step 2) Create a DB, owned by the DB-User in ISPC (Both will be created in MySQL).

    If I understood this wrong, please correct me.
     
  15. till

    till Super Moderator Staff Member ISPConfig Developer

    Correct. Creating a DB user without DB makes no sense and that's why ISPConfig works like this as this user would not be able to do anything and even worse, in a multiserver setup you won't even know where to create it without knowing where the DB shall be created.
     
    niemand-glaumy likes this.

Share This Page