MySQL Timeout over crossconnect

[Ian Cunningham]

I've got 2 Win2003 servers, one IIS/PHP and another a dedicated MySQL machine crossconnected.

For some reason, every day or so, my main site will timeout. The other static/php sites that don't use the external MySQL server still work fine on the IIS server. IISReset doesn't do anything to fix it. For some reason the MySQL connection over crossconnect just times out.

The only way to fix this is to restart the IIS/PHP server. Strange eh? Nothing shows up in Event Viewer and both servers appear to be fine, except that the MySQL connection keeps timing out.

Does anyone have any ideas, as I have been battling with this for weeks. The Planet support are looking into it, but its fair to say that its hard for them to fix it without being able to see the problem in action.

Does anyone have any ideas or suggestions on how I can tackle this problem?

[lammert]

Maybe this is caused by the Max Conn Errors setting in the Networking configuration of the MySQL server. This is the number of interrupted connections from a host before MySQL decides to block the host. It could be that MySQL is blocking your IIS/PHP server after a number of connection errors.

[Ian Cunningham]

Thats a very good point! Is there any way to remove this feature?

[lammert]

I am using MySQL Administrator on Windows 2000. The setting is under "Startup Variables" and tab "Networking" somewhere near the bottom of the page. If you enable the option, it will write a line max_connect_errors=XX in the my.ini file in your MySQL system directory. Removing the line from my.ini should disable the host block feature.

[Ian Cunningham]

Thanks! Just checking though, would this be fixed by restarting the IIS server, and not the MySQL server? As that is how I do a temporary workaround.

[Ian Cunningham]

I've just checked and its not in the my.ini file anway, so doesn't look like it could be that.

The site went down again today, and its driving me crazy :(

[Romeo]

Hi Ian,

What type of database connections are used by the applications -- persistent connects? non-persistent?
Opening too many persistent connects but never closing them may be a problem but should give you some 'too many sessions' error messages.
Is there a firewall in between the 2 servers, which may cut long running sessions?

On Linux systems I would check the logs first, but don't know how the log situation is in Windows -- is there an IIS log you could look into? Does the PHP indeed say 'timeout' only? What is in the MySQL log? Try to gather as much log information you can get, on both ends.

Regards,
R.

[Ian Cunningham]

It doesn't say timeout as such, but it looks for the DB connection for a while and then gives a generic no MySQL connection error : 0

It is using persistant connections, do you think it would help if that was changed? No firewall between the two servers either.

I'll have a look at the logs now and see what I can find.

[Ian Cunningham]

My appologies, it isn't using persistent connections. PHP.ini is set up to allow them, but the script is set to disallow it.

[Romeo]

... OK, then I am run out of ideas for now.
Hopefully you will dig up some interesting stuff from your logs.

Regards,
R.