Connection timed out.

[Dheeraj Joshi]

My colleague is facing an issue with MySQL. I am asking question on behalf of him.

He has created an application and they use MySQL as database. The application is in Java and they use NHibernate.

When the application run continuously database connection is getting closed after 21 Hours.

Error in the log file is

java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Software caused connection abort: recv failed

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Software caused connection abort: recv failed

STACKTRACE:

java.net.SocketException: Software caused connection abort: recv failed

And i suppose if we don't query the database, database connection will get closed after 8 hours...
Where to change this behavior.?

Is it really a MySQL problem.?

Regards
Dheeraj Joshi

[dgreenhouse]

Your friend will have to put a "Is Connection Live?" check in the code and reconnect if the connection dies.

[Dheeraj Joshi]

Ok.... So basically i am trying to find out, why the connections gets timed out.?

Regards
Dheeraj Joshi

[dgreenhouse]

I have no idea, but a 21 hour run is pretty long.

Why is it open so long? I have had connections open that long, but is it really necessary in this case? I guess I have to assume it is.

Is it always roughly about 21 hours?

There are a number of MySQL settings, but they may or may not have access to them in their environment.

It might be that the app bogs down after it has been running awhile and doesn't have any activity with the database withing its set timeout period - ergo - the timeout.

[Dheeraj Joshi]

They are doing testing of the application by keeping it running.
They are inserting the data, updating etc etc..

Yeah it's roughly around 21 hours , once it was also up and running for 36 hours.
But mostly 21 hours....

Regards
Dheeraj Joshi

[dgreenhouse]

I have no idea, but a 21 hour run is a long time.

MySQL has a number of run time options some of which may or may not be available in their environment.

It could be the app is bogging down at the 20+ hours mark and isn't doing any database interaction and going over the MySQL's connection timeout setting.

If it's on a hosted account, maybe they have some stuff running that's disconnecting.

Network failures, etc., etc., etc.

Google:
http://www.google.com/search?q=mysql...t+setting+java

[dgreenhouse]

As stated, they should place some "guard" code in the application and reconnect on failure.

~ It's not IF it's WHEN ~

[Dheeraj Joshi]

I don't know about the runtime configurations of MySQL.

I will see, what they are doing and post.

Regards
Dheeraj Joshi

[Dheeraj Joshi]

Here is a link which speaks about same issue...

Link

Regards
Dheeraj Joshi

[dgreenhouse]

@dheerajjoshim
Bill Karwin's response in that thread is correct.

Any robust application has to constantly check for failure conditions and act accordingly.

You HAVE to ALWAYS expect that your application is going to get network failures, machine crashes, access permissions denials, etc., and react appropriately. Many times the application's only alternative is to just log a failure and die. ~ It's not IF it's WHEN ~

But as far as DB timeouts are concerned, you have to expect them at every potential point of failure, and reconnect if possible after detection (Guard Conditions).

You have to love writing error detection and mitigation code as that's the only way your applications will run properly and mark it as professional.

It's a big subject and there's a lot of information available to help in developing good strategies.

[mwasif]

What are interactive_timeout and wait_timeout values in MySQL configuration file (my.cnf/my.ini)? You can check the values on mysql prompt with the following queries

SHOW VARIABLES LIKE 'interactive_timeout'

SHOW VARIABLES LIKE 'wait_timeout'