Dropping Remote Connections Too Often!

[buzzt]

I have MySQL running on a Win 2K server. The problem is that I have a server with a remote connection to one of the databases whose connection keeps getting dropped or refused. I end up having to do a flush-hosts on a regular basis (every day or so).

Does anyone have any idea what could be causing this, and how I might remedy the problem?

 

[sleipnir214]

How have you defined that server's username's allowed host?

Perhaps if you're doing it by domain name, there's some kind of name resolution problem on your MySQL server machine.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[buzzt]

The user is identified by static IP address.

 

[sleipnir214]

Then you have stumped the chump. That was my one faint glimmer of an idea.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[buzzt]

D'oh! Anyone else?

 

[vanekl]

Do you have long periods of inactivity on the connections?
You may want to check your wait_timeout variables.
In my my.ini file, I define:

wait_timeout=60
interactive_timeout=14000

What this does is drop the interactive connections
(e.g. mysql) after about 3 hours of inactivity, and
connections from my web page program after 60 seconds
of inactivity (which is fine since I create a new connection
for every page served).

You may want to experiment with these values. For example,

wait_timeout=172000
interactive_timeout=172000

would keep the connections up for almost 2 days of no
activity. You can't use these settings for a database
that is being hit frequently (like a web page server) where
tons of connections are created, but in your
case you might not be setting up a lot of connections to
your MySQL database, so these values might work for you.

 

[buzzt]

I have it installed on a Win 2K server. My my.ini file does not even seem to have these values. Should I add them? Here's what it looks like:


[mysqld]
basedir=C:/mysql
#bind-address=the ip address
datadir=D:/data
#language=C:/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-nt.exe
user=user
password=password

 

[vanekl]

By default the two variables are not defined in your configuration files.
(There also might be a my.cnf configuration file.)

You can see (most) of the MySQL variables when you do a,
show variables;
within mysql.
One problem is that 'wait_timeout' is really
'interactive_timeout' when you do a 'show variables' within
mysql.
I believe wait_timeout and interactive_timeout are both, by
default, set to 8 hours (28,800 seconds). Normally 8 hours is
too long for most programs, but in your case it might make
sense to increase the values. It's worth a try at least.
You should read the MySQL manual about what it says about these
variables, and then decide whether you want to make the
additions to the my.ini file (within the [mysqld] block) and restart the mysql daemon.
But, yes, if you don't have a huge number of connections
being created all the time you could try this for a couple
of days and see if it solves your problem.
If you always get database activity within 8 hours, however,
then resetting these variables will not help you (they might
even hurt by gobbling up more RAM and threads). If there is
a span of more than 8 hours (e.g. overnight) when the DB
connection has no activity, then you should either increase
these variables or rewrite your application to handle
dropped connections gracefully.

 

[buzzt]

So where on the list (in the my.ini file) would these values go? Does it matter if it is below [mysqld] or below [WinMySQLAdmin]? I just have to drop them in and save the file?

 

[vanekl]

It goes anywhere between the words [mysqld] and [WinMySQLAdmin].
Make sure you don't precede your statements with '#' or
else you will have commented the line out.

 

[buzzt]

Thanks. I'll give it a try today.

 

[PCSAARON]

What client is accessing the W2K server that is having the problems? It wouldn't happen to be a windows 98 machine would it? Or is the server itself actually connecting to another W2K server? What are the exact error messages?

I have noticed problems with Windows 98 not releasing the connections fast enough and then it will give errors to the fact that connections have been lost to the mysql server when there are actually too many connections made... Instead of actually saying "too many connections...", it throws a different message making you beleive it has actually lost connection...

How often does it connect? - Also, I fixed this problem with Windows 98 by downloading the NEWEST mdac from microsofts website.

http://www.microsoft.com/data

(I beleive)
It is version 2.8...

This may have nothing to do with your problem, but it is a good idea to keep Microshaft updated...

Aaron

 

[PCSAARON]

Sorry, the mdac link is:

http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860

 

[PCSAARON]

Also, you can check out your variables by using th "show variables;" SQL statement.

My wait_timeout variable says it is 28800. Which is the default. The interactive timeout is also 28800. Lowering these may cause problems as well if connections are not close properly first...

Aaron

 

[buzzt]

The connection that is being dropped is not a Win 98. It is however, using Microsoft SQL 2000 to connect to my MySQL database. Does that ring any bells?

BTW, thanks for the mdac link. I'll check it out.