Cannot connect to mysql when setting bind-address=localhost

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
hypersonic
New user
New user
Posts: 3
Joined: 2019-11-09 19:51

Cannot connect to mysql when setting bind-address=localhost

Post by hypersonic » 2019-11-09 20:03

Hi there,
I have a Windows 2012 server with the current hmailserver version and MariaDB 10.1 up and running.
For optimizing purposes I tried to set
bind-address=localhost
in the my.ini

After restarting MariaDB hmailserver no longer sends mail (although the hmailserver service is still running) and I get the following error when starting hmailserver admin application:
hMailServer Administrator
---------------------------
The connection to the database is not available.

Can't connect to MySQL server on 'localhost' (10061)

In the log files I get:
"Severity: 1 (Critical), Code: HM4354, Source: Application::OpenDatabase, Description: hMailServer failed to connect to the database server. Error message: Can't connect to MySQL server on 'localhost' (10061)"

Maybe hmailserver tries to connect to the db on 127.0.0.1 which is no longer possible when bind-address=localhost is active?
If I comment this line in my.ini and restart MariaDB then hmailserver can connect again and works fine.

The Database section in hmailserver.ini looks like this:
Type=MYSQL
Username=hmailserver
Password=*removed*
PasswordEncryption=1
Port=3306
Server=localhost
Database=hmailserver
Internal=0

The user hmailserver has all privileges in MariaDB and set up to connect from localhost.

BTW: I have two other Windows machines with the same setup and there I can use the bind-address=localhost w/o any problems.

Any ideas anyone?

Thanks a lot
Hyper

palinka
Senior user
Senior user
Posts: 2010
Joined: 2017-09-12 17:57

Re: Cannot connect to mysql when setting bind-address=localhost

Post by palinka » 2019-11-09 23:43

https://mariadb.com/kb/en/library/confi ... nt-access/
bind-address requires a little bit of background information. A given server usually has at least two networking interfaces (although this is not required) and can easily have more. The two most common are a Loopback network device and a physical Network Interface Card (NIC) which allows you to communicate with the network. MariaDB is bound to the loopback interface by default because it makes it impossible to connect to the TCP port on the server from a remote host (the bind-address must refer to a local IP address, or you will receive a fatal error and MariaDB will not start). This of course is not desirable if you want to use the TCP port from a remote host, so you must remove this bind-address directive (MariaDB only supports one bind-address, but binds to 0.0.0.0, or :: (every IP) if the bind-address directive is left out).
I think its just like the bind in hmailserver - unless you have a really good reason to use it, just leave it blank (hmailserver) or commented out in my.ini.

Do you have more than one NIC?

User avatar
mattg
Moderator
Moderator
Posts: 20961
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Cannot connect to mysql when setting bind-address=localhost

Post by mattg » 2019-11-10 02:03

hypersonic wrote:
2019-11-09 20:03
If I comment this line in my.ini ...
Which line?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

palinka
Senior user
Senior user
Posts: 2010
Joined: 2017-09-12 17:57

Re: Cannot connect to mysql when setting bind-address=localhost

Post by palinka » 2019-11-10 03:07

mattg wrote:
2019-11-10 02:03
hypersonic wrote:
2019-11-09 20:03
If I comment this line in my.ini ...
Which line?

Code: Select all

# Change here for bind listening
# bind-address="127.0.0.1" 
# bind-address = ::1          # for ipv6
I believe its commented out by default.

By the documentation, it works exactly like the bind option in hmailserver. Binding to localhost should not allow any connection except directly from localhost. Commenting out the bind-address line allows it to bind to any connection. I use my firewall to block external connections and leave this line commented out (actually, today was the first time I've ever seen it :mrgreen: ).

User avatar
mattg
Moderator
Moderator
Posts: 20961
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Cannot connect to mysql when setting bind-address=localhost

Post by mattg » 2019-11-10 04:13

palinka wrote:
2019-11-10 03:07
, today was the first time I've ever seen it :mrgreen:
I've not ever seen it either
Is that in the hmailserver.ini ?

(hmailserver.ini doesn't get overwritten when you upgrade)

Can someone please post the default 'hmailserver.ini' as it is currently created in a clean install
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

palinka
Senior user
Senior user
Posts: 2010
Joined: 2017-09-12 17:57

Re: Cannot connect to mysql when setting bind-address=localhost

Post by palinka » 2019-11-10 04:19

mattg wrote:
2019-11-10 04:13
palinka wrote:
2019-11-10 03:07
, today was the first time I've ever seen it :mrgreen:
I've not ever seen it either
Is that in the hmailserver.ini ?

(hmailserver.ini doesn't get overwritten when you upgrade)

Can someone please post the default 'hmailserver.ini' as it is currently created in a clean install
It's in mysql my.ini, not hMailServer.ini.

User avatar
ras07
Normal user
Normal user
Posts: 228
Joined: 2010-03-11 08:51

Re: Cannot connect to mysql when setting bind-address=localhost

Post by ras07 » 2019-11-10 07:16

hypersonic wrote:
2019-11-09 20:03
I have a Windows 2012 server with the current hmailserver version and MariaDB 10.1 up and running.
For optimizing purposes I tried to set
bind-address=localhost
in the my.ini

After restarting MariaDB hmailserver no longer sends mail (although the hmailserver service is still running) and I get the following error when starting hmailserver admin application:
hMailServer Administrator
---------------------------
The connection to the database is not available.

Can't connect to MySQL server on 'localhost' (10061)
Did you restart the hMailServer service in addition to the MariaDB service? This is sometimes required when restarting the database (hMS needs to re-establish the connection and re-authenticate).

Some other things to try - see if setting the bin-address to 127.0.0.1 explicitly helps. You also might try setting it to ::1 (the IPv6 loopback address). I've seen Windows do some weird stuff with IPv6, and it's possible that hMS is looking for a IPv6 and MariaDB is binding to IPv4, or vice versa. Explicitly telling MariaDB either 127.0.0.1 or ::1 might solve it, depending on which way it is.

With any change, restart both MariaDB and hMS.

User avatar
Dravion
Senior user
Senior user
Posts: 1688
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Cannot connect to mysql when setting bind-address=localhost

Post by Dravion » 2019-11-10 08:16

10061 is a Windows Winsock2 Network TCP/IP Error message and means "Connection refused". This only occurs if a TCP/IP Client process (in this case hMailServer)
cannot get in contact (after a timeout period of 300 milliseconds) with the expected TCP/IP Server Socket (in this case MariaDB) and thats it.

1) Check if you MariaDB Server Windows Server is started
2) Check your MariaDB Server logfiles for problems.
3) Try to ltest ogin to MariaDB with HeidiSQL

Report back.

hypersonic
New user
New user
Posts: 3
Joined: 2019-11-09 19:51

Re: Cannot connect to mysql when setting bind-address=localhost

Post by hypersonic » 2019-11-11 09:14

Hi,
thanks for all your replies. I'll try to answer your questions and to give you more information:
This server is mainly used as a webserver running PHP.
The option bind-address=localhost brought a better performance for some PHP applications on my other servers so I thought it to be a good idea to do this here as well.
As fas as I understood the setting prevents DNS lookups when connections are established from PHP in some cases and so speeds up db queries.

hmailserver service is automatically re-started when MariaDB is re-started cause its defined as dependend service.

There is only one NIC in the machine.

I can login to the db with HeidSQL using the given credentials of hmailserver.ini. The MariaDB user is restricted to port "localhost". But the problem remains also if I set the user's host to %.

I've tried bind-address=127.0.0.1 before, but it slows down PHP mysql connections (don't know why as it should be the same).
I didn't try ::1 yet cause I'm not sure if all services and apps on the server can use IPv6.

Maybe something in the DNS settings of the server is broken.

Currently, I'm a giving a try to
bind-address=0.0.0.0
that seems to work with both PHP/MySQL and hmailserver and also is faster than 127.0.0.1.

User avatar
ras07
Normal user
Normal user
Posts: 228
Joined: 2010-03-11 08:51

Re: Cannot connect to mysql when setting bind-address=localhost

Post by ras07 » 2019-11-11 18:59

hypersonic wrote:
2019-11-11 09:14
The option bind-address=localhost brought a better performance for some PHP applications on my other servers so I thought it to be a good idea to do this here as well.
That shouldn't change performance - could be an indication that there's some weird routing going on.
As fas as I understood the setting prevents DNS lookups when connections are established from PHP in some cases and so speeds up db queries.
Not exactly. localhost just tells the server not to respond to requests made to the external IP(s).

If your machine's name is myserver.example.com and the IP is 1.2.3.4, then if you bind to all IPs and set hMS (or any client app) to use the db at myserver.example.com, then yes, it has to resolve the name. Even so that should still happen locally with no measurable effect on performance; but if the server is misconfigured, it could potentially result in a DNS lookup (and I suppose potentially TCP traffic being routed outside the server and back again (which would affect performance), depending on exactly what your network is like).

In the above example, if you bind the db to localhost, you're telling it to only respond to requests to 127.0.0.1 (well, 127.0.0.x, but that's not important.) (Well, also ::1, on the IPv6 stack.) Since 127.0.0.x never gets routed anywhere, the only way that can happen is if the request is coming from the same machine.
There is only one NIC in the machine.
Any chance that there is more than one IP associated with the one NIC?
I've tried bind-address=127.0.0.1 before, but it slows down PHP mysql connections (don't know why as it should be the same).
That should NOT happen - very strange. I don't have a good explanation for that, but there is definitely something weird going on.
I didn't try ::1 yet cause I'm not sure if all services and apps on the server can use IPv6.
It's really a function of the TCP stack on the machine rather than the individual services and apps, but yeah, if you are relying on the same db for multiple apps, I wouldn't go around messing with that. In any case localhost should, in a well-behaved app, tell it to bind to both stacks (127.0.0.1 for the IPv4 stack and ::1 for the IPv6 stack).
Maybe something in the DNS settings of the server is broken.
Do you have anything nonstandard in %windir%\system32\drivers\etc\hosts or %windir%\system32\drivers\etc\lmhosts ?
Currently, I'm a giving a try to
bind-address=0.0.0.0
that seems to work with both PHP/MySQL and hmailserver and also is faster than 127.0.0.1.
Again, that should NOT affect performance. If it does there is something very strange going on.

hypersonic
New user
New user
Posts: 3
Joined: 2019-11-09 19:51

Re: Cannot connect to mysql when setting bind-address=localhost

Post by hypersonic » 2019-11-11 20:40

ras07 wrote:
2019-11-11 18:59
Any chance that there is more than one IP associated with the one NIC?
no, there is only one static public ip
Do you have anything nonstandard in %windir%\system32\drivers\etc\hosts or %windir%\system32\drivers\etc\lmhosts ?
no, both files only contain lines commented with #
Currently, I'm a giving a try to
bind-address=0.0.0.0
that seems to work with both PHP/MySQL and hmailserver and also is faster than 127.0.0.1.
Again, that should NOT affect performance. If it does there is something very strange going on.
yep, I absolutely agree with you. Anyway, thanks a lot for your comments.

Post Reply