Faulty connections to databases, mainframes and inside application servers are the main cause of Java 2 Platform Enterprise Edition (J2EE) application-related downtime, research claims. Wily Technologies ran a survey that showed only 42 per cent of J2EE-based applications deliver against the performance targets set at the point of deployment, but the problem wasn't Java.

Only 13 per cent of respondents cited code-related issues as the cause of downtime for web services and consumer websites, the remaining 86 per cent blamed faulty connections to and from the Java application.

Application code bugs were seen as the likely cause by 13.7 per cent, with configuration and tuning problems coming second with 11.9 per cent.

Read Faulty connections slow Java deployments

Obviously all of the servers need integrated wireless capabilities to avoid any network outages. Hm.. JDBC over bluetooth? Speedy!

Seriously though.. it talks about lost connections, but I wonder where the fault actually lies. For example.. is the database just being reset for maintenance and the database pool not clearing the dead connections? I reminds me of having to put the autoReconnect flag on MySQL connections.

A lost connection could just as easily be the J2EE server's fault at not managing the connections better as it could be the database server's fault. The fact that server configuration/tuning was picked up as a problem could point to this.

The good thing is that the code is aparently pretty good.. but the whole "faulty connections" group seems sort of broad.

"Connectivity issues concerning J2EE applications are causing always-on systems to lose around a day's productivity every week, according to research sponsored by Java software management vendor Wily Technologies."

Not surprisingly this article paints a pretty bleak picture as it is sponsored my Wiley technologies who sell monotoring tools. It is also obviously aimed more at management types who sign Purchase orders then at people who are in the trenches. For a start how do you define a day's productivity?

Wily actually has a good product and constant monitoring of you external interfaces, MBeans and production logs are essential but surveys like this IMHO does not do their cause any good.

Tell me about it. I got bitten by this just last week...

As the developer of the MySQL JDBC driver, I plead with you _not_ to use autoReconnect!

It is a crutch, left over from the days when connection pools could not handle this themselves. I have actually been considering deprecating, and eventually removing the feature in a future version, because it so often inaaproriately used :(

The JDBC specification doesn't state that JDBC connections survive network errors, server restarts, etc. for good reason...The only component that has 'enough' information to determine the correct case of action (attempt retry of transaction, fail this transaction, etc) when a connection fails for some reason is the application itself! This is also why SQLExceptions have SQLStates, so that applications can recover from such an event in a 'standardized' way.

Please use the features in your connection pools that scavenge idle connections, test idle connections or test connections on reserve or release. All modern connection pools have this functionality.

To be even safer, code defensively! React to SQLExceptions, rather than just logging them and tossing them back up the stack. There are cases where you can retry the transaction (deadlock timeouts, network connection is down when you start the transaction, etc.). You can see an example of how you might do this in the Connector/J documentation at http://www.mysql.com/documentation/connector-j/index.html#id2804194

> Please use the features in your connection pools that scavenge idle connections, test idle connections or test connections on reserve or release. All modern connection pools have this functionality.
>
This shouls be replaced with SHOULD have, try using WebSphere!

What about the "Aged Timeout" and "Purge Policy"? I just started using Websphere, so I have no practicle experience, but sound too me that this features are supported.

Remy

Sorry, but I haven't yet seen a connection pool (either commercial or open source) that have all the options I would like regarding performance and robustness, like efficient connection checking, maximum connection live time, maximum connection busy time, automatic eviction of connections that raised an exception, efficient prepared statement caching, fine tuning of the pool size, automatic grow/shrink according to load, dynamic reconfiguration, protection against 'burst allocation' etc...

Mileta

Not sure what burst allocation means, most of the rest is covered by WebLogic.

Regards,

Slava Imeshev

Interesting.. first off, thanks for developing that driver! I use it every day, and have had very few problems with it.

I do agree with the idea that its probably not the drivers responsibility to maintain connections for long-term sessions, attempt to do session management, and so forth.. but its also a fact that many pool implementations do not provide adaquate support for checking and updating connections. (As a side questions.. which do this effectivly? What pools can handle connection's that have failed gracefully? C3P0? DBCP? others?)

However.. from a simplicity perspective.. its a really nice option to have in there, if only as a catch for situations where your pool isn't handling things as gracefully as you would like.

Finally, I don't frequently touch the database connection much anymore. Using things like Hibernate keep me from too much direct connection handling. My guess is that people will be mucking around with the database connections less and less, and will just hope that their object mapping framework is handling the job effectively.

Hibernate relays on pluggable connection provider, so responsiblity for connection management is just shifted to it, and the problem persist.

Ironically, when I first tried to read the article, the vnunet site returned an error page full of OLE-DB error messages in Spanish... I should have taken a screenshot.

<excerpt>
With only 13 per cent of respondents citing code-related issues as the cause of downtime for web services and consumer websites...
</excerpt>

Yup. Sounds like those surveyed are definitely programmers.

This can be very easily solved by good connection pool implementation. We have home-wirten connection pool with efficient and proven connection checking with a variety of options to tune the pool that can help improve robustness as well as performance. Here are some connection pool techniques that can help avoid faulty connections:

- Connection checking. Prior to returning connection to the client, the connection is pinged by running a ping query (like SELECT * FROM DUAL on Oracle or SELECT 1 on MS SQL/Sybase etc.). In order to avoid an extra RPC before every connection request, connection is pinged only if specified amount od time (like 60 secs) is elapsed since last sucessful return to the pool. This is more efficient then having a background thread that pings connections in specified intervals (I've seen this in some connection pool implementations).
Connection checking prevents connections from network problems.

- Maximum connection live time. Connections can have maximum live time (an hour, for example). This means that connection will be closed and removed from the pool after maximum live time has been elapsed since the connection is created (allocated). This prevents us from problems that could be caused by 'long-living' connections (bugs in database and/or dirvers, etc.), and it does not affect performance if connections are allocated again every few hours.

- Maximum connection busy time. Connections can have maximum busy time too. This means that connection will be closed and removed from the pool after maximum busy time has been elapsed since connection is borrowed and connection is still not returned to the pool. This prevents from programming errors like forgetting to return connection to the pool (GC can help against this, but you can not relay on it).

Mileta