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

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 user@localhost 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

 

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

 

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

 

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

 

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

 

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/

 

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.

 

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.

 

[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:

 

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.