MySQL won't listen - MySQL

[msch-prv@bluewin.ch]

I set up a small LAN with 2 machines (acer.loc IP:192.168.1.33 and
hppav.loc IP: 192.168.1.34) for test purposes. Each client can access
its own MySQL db w/o problems either via the cmd prompt or MyODBC:
acer: mysql -h192.168.1.33 -u<usr> -p<pwd>
or
hppav: mysql -h192.168.1.34 -u<usr> -p<pwd>

The problem arises when trying to access the MySQL db from the other
client. For example, if I want to connect to the hppav db from
'acer.loc' via:
mysqL -h192.168.1.34 -u<usr> -p<pwd>
I get the following error message:
'ERROR 1130: Host 'acer23555' is not allowed to connect to this MySQL
server'.

The listening port is default 3306.

Running 'netstat -a' reveals that MySQL is not listed as a listener.

Question: How can I force MySQL to listen to access calls originating
from clients on the network?

Thanks for any hints.

Mark

[Bill Karwin]

[email]msch-prvbluewin.ch[/email] wrote:

> mysqL -h192.168.1.34 -u<usr> -p<pwd>
> I get the following error message:
> 'ERROR 1130: Host 'acer23555' is not allowed to connect to this MySQL
> server'.

Well, error messages 1000-1999 are server-generated error messages. So
you _are_ connecting to the MySQL server, but this is its response.

This may be due to a "bind-address=127.0.0.1" setting in the my.cnf (or
my.ini) file on the server. This option is typically enabled during
installation. It means that only clients from 127.0.0.1 (localhost) can
connect. Edit your my.cnf and comment out this statement, if it is there.

Also make sure you've use GRANT to enable user <usr> to connect from the
remote client host.
See [url]http://dev.mysql.com/doc/refman/5.0/en/adding-users.html[/url]

Regards,
Bill K.

[Jerry Stuckle]

[email]msch-prvbluewin.ch[/email] wrote:

> I set up a small LAN with 2 machines (acer.loc IP:192.168.1.33 and
> hppav.loc IP: 192.168.1.34) for test purposes. Each client can access
> its own MySQL db w/o problems either via the cmd prompt or MyODBC:
> acer: mysql -h192.168.1.33 -u<usr> -p<pwd>
> or
> hppav: mysql -h192.168.1.34 -u<usr> -p<pwd>
>
> The problem arises when trying to access the MySQL db from the other
> client. For example, if I want to connect to the hppav db from
> 'acer.loc' via:
> mysqL -h192.168.1.34 -u<usr> -p<pwd>
> I get the following error message:
> 'ERROR 1130: Host 'acer23555' is not allowed to connect to this MySQL
> server'.
>
> The listening port is default 3306.
>
> Running 'netstat -a' reveals that MySQL is not listed as a listener.
>
> Question: How can I force MySQL to listen to access calls originating
> from clients on the network?
>
> Thanks for any hints.
>
> Mark
>

Mark,

Can you telnet to port 3306 on the other machine? If so, you'll get garbage
back but at least know MySQL is listening. In this case, you need to check your
authentication - chances are your id is only allowed to connect from
'localhost'. I suspect this is the problem.

If you can't telnet to port 3306, you have a configuration problem. You didn't
say which version of MySQL you're using, but a couple of things to check in the
my.cnf file:

port=3306 (both in [client] and [safe-mysql] sections - 2 entries
skip-networking ([safe-mysqld] section is NOT set.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklexattglobal.net[/email]
==================

[Bill Karwin]

Bill Karwin wrote:

> This may be due to a "bind-address=127.0.0.1" setting in the my.cnf
> Edit your my.cnf and comment out this statement, if it is there.

I should also mention that you need to restart the MySQL service for
this change to take effect.

Regards,
Bill K.

[msch-prv@bluewin.ch]

Mark

Many thanks for your comments.

First off, the MySQL specs are:
acer client: 5.0.0-alpha-nt
hppav client: 5.0.18-nt

I have been experimenting with the different suggestions with no
results so far:
1. Commented out bind address 127.0.0.1' in 'my.ini'
2. Ran telnet on port 3306 ('o hppav3310.loc 3306'). Returns a 'Cannot
open connexion' msg.
3. Suppressed the password for user mark to simplify testing
4. Granted all permissions to user 'mark' in hppav.loc's user table
with the following entries (all other entries set to yes):
Host User Password ...
localhost root <none> ...
acer2355.loc mark <none> ...
hppav3310.loc mark <none> ...

5. Amended 'my.ini' and added Jerry's suggestions:
------------
#This File was made using the WinMySQLAdmin 1.4 Tool
...
[mysqld]
basedir=C:/Program Files/MySQL
#bind-address=127.0.0.1
datadir=C:/Program Files/MySQL/data
#language=C:/Program Files/MySQL/share/your language directory
#slow query log#=
#tmpdir#=
port=3306
#set-variable=key_buffer=16M

[safe-mysql]
port=3306
skip-networking=OFF

[WinMySQLadmin]
Server=C:/Program Files/MySQL/bin/mysqld-nt.exe
user=mark
password=mark
--------------

6. End Result. Entering on acer3350.loc the command:
mysql -h192.168.1.34 -umark
returns now the following error message:
'ERROR 2003: Can't connect to MySQL server on '192.168.1.34' (10060).

I am puzzled. Thanks for any further insights,

Mark

[Bill Karwin]

[email]msch-prvbluewin.ch[/email] wrote:

> 1. Commented out bind address 127.0.0.1' in 'my.ini'

Did you restart the MySQL service after making this change?

> 2. Ran telnet on port 3306 ('o hppav3310.loc 3306'). Returns a 'Cannot
> open connexion' msg.

Do you have a firewall or antivirus software that is blocking the
connection from the client to the server on port 3306?

Regards,
Bill K.

[msch-prv@bluewin.ch]

Bill,

I did restart the MySQL service after every change. The problem may be
related to the firewall (there is a firewall on both machines). Earlier
on, I disabled it on both clients but noticed no change when I
rebooted. I'll give it another try.

What is puzzling me is the fact that I can access files on the remote
Apache server. For instance, the command
'http://hppav3310.loc/apps/index.php' works on remote hppav from client
acer. So, there must be a configuration issue somewhere.

Thanks,

Mark

[Jerry Stuckle]

[email]msch-prvbluewin.ch[/email] wrote:

> Bill,
>
> I did restart the MySQL service after every change. The problem may be
> related to the firewall (there is a firewall on both machines). Earlier
> on, I disabled it on both clients but noticed no change when I
> rebooted. I'll give it another try.
>
> What is puzzling me is the fact that I can access files on the remote
> Apache server. For instance, the command
> 'http://hppav3310.loc/apps/index.php' works on remote hppav from client
> acer. So, there must be a configuration issue somewhere.
>
> Thanks,
>
> Mark
>

Mark,

OK, so you can access port 80 on the Apache server. But a firewall can still be
blocking port 3306.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklexattglobal.net[/email]
==================

[msch-prv@bluewin.ch]

After many hours of troubleshooting, I finally was able to solve the
problem. It turned out that a series of unrelated factors were clouding
the issue.

1. Connection hick-ups. Clients are linked to a wireless gateway out of
line of sight. By accident, I disconvered that erratic perturbations on
the 'acer.loc' server side caused the network to temporarily freeze. By
moving clients within range, I was able to improve channel link quality
and issue commands when the network was 'on'.

2. Firewall settings. I wrongly assumed that updating WAMP would also
update firewall definitions. Manually adding MySQL to the firewall's
exception list cured the problem.

3. Third-party tool. For testing purposes, I relied on MySQLFront v
2.4. Version 2.4 does not recognize the user passwords set up via
phpMyAdmin, causing connection attempts to fail. Checking the
connection in the command window got rid of this problem.

Morality: When troubleshooting, 1. Be wary of erratic connection
glitches. 2. Double check all firewall settings. 3. Stick to trusted
tools: make sure your tools are compatible with the db version at hand.

Thanks to all for your insightful comments.

Mark