I've ran into some problems when connecting to a mysql server over a
long-distance connection (i.e.: the internet). The first time a client
reconnects from a idle connection this reconnect fails, only the second time
it succeeds.
The main problem is that i've written a win32 app that connects to this
server. Normally the client and server will be on a LAN but i test the
program on a their database from my place. I use libmysql.dll for this. (no
odbc!)
Normally i relied on libmysql.dll to reconnect when a connection was lost,
on a lan this never gave any problem but now it does, giving my app no
result set on a particulair query after been idle for a while, and thus
introducing bugs.
I am able to reproduce this with the standard mysql client. Both the win32
and the linux client behave the same.
Tech info:
server running mysql 3.23.32 win32 binary distribution
Client app is behind a firewall, MySQL server is directly connected to the
internet. Telnet or other sessions normally remain alive through my
firewall.
ping trip-around time: about 200ms, however sometimes loads of packet loss
(possible cause?)
Just after a connection everything is fine:
mysql> show processlist;
+-----+-------+------------------------+------+---------+------+-------+----
--------------+
| Id | User | Host | db | Command | Time | State |
Info |
+-----+-------+------------------------+------+---------+------+-------+----
--------------+
| 429 | admin | cxxxxx.upc-c.chello.nl | NULL | Query | 0 | NULL |
show processlist |
+-----+-------+------------------------+------+---------+------+-------+----
--------------+
1 row in set (0.21 sec)
Then if a leave the client for a while (say: ten minutes) the connection is
gone:
mysql> show processlist;
ERROR 2013: Lost connection to MySQL server during query
mysql> show processlist;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 430
Current database: *** NONE ***
+-----+-------+------------------------+------+---------+------+-------+----
--------------+
| Id | User | Host | db | Command | Time | State |
Info |
+-----+-------+------------------------+------+---------+------+-------+----
--------------+
| 429 | admin | cxxxxx.upc-c.chello.nl | NULL | Sleep | 2732 | |
NULL |
| 430 | admin | cxxxxx.upc-c.chello.nl | NULL | Query | 0 | NULL |
show processlist |
+-----+-------+------------------------+------+---------+------+-------+----
--------------+
2 rows in set (5.99 sec)
So, the server still thinks i've got an active connection. this list keeps
on growing.
For as far as I can see, my win32 app behaves the same as the mysql client.
It'll try to reconnect but it fails, only second time it succesfully
reconnects.
I ported my app from interbase to mysql. with interbase i did not have this
connection problem, but it's so terribly slow on many queries/ larger result
sets (interbase fetches a result field by field, generating numerous network
packets...)
I also have some PHP scripts that connect to this server, running in win32
cgi mode. They do behave normally, i guess because they have to connect
every time (i.e.: no persistant connections for win32 w/o isapi) they are
called.
When not idle (i.e. active querying) everything behaves normal and i notice
no errors at all, not even on heavy load.
Any ideas / solution would be really appreciated
thanx in advance,
rene
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
