Hi all,
The description of the issue is as follows…
We have recently implemented a firewall. My Application is on one subnet and the data base is on another subnet.
Since then, on the Application it’s not possible to pass the authentication page if the deployed Application hasn’t been used for a while. This is due to the fact that the portal is opening a connection with MySQL(4.1) database only at startup and doesn’t pick up that the firewall dropped the connection if there isn’t any traffic during a certain period.
We are using Spring frame work and Connection pooling for connecting to DataBase.
The Application should be able to notice that the connection has been lost and reestablish the connection.
Is there any way to reestablish the connection with MySql.
TestWhileBorrow and validationQuery property of CustomDataSource has solved this issue upto the certain extends but still connection fails at the first attempt.
So is it possible to connect the MySql on the very first attempt.
Thanks )
I can’t answer your question directly, but can offer a workaround…
One possible approach and one that is used in practice in various messaging buses but does initially sound a bit dirty is to incorporate “keep alive” requests in the system. Basically, a timer task that issues a simple message, or inexpensive query in your case, on a minutely basis or so to ensure that no connections get dropped.
hi …Thanx for your reply but this is not a very good solution what i think so…
As I tried it before by writing a cron job and got success to full fill my requirement.
But cron Jobs are not made for these things like to maintain connection pool…
And If like any application have more than one Database, then we have to write this for all other data bases…and this will definetlly hamper the performence too.
Is there any other way to maintaine the connection.
we are using
mysql-connector-java-3.1.7 and commons-dbcp-1.2.2…
In some aspects it is of course a performance hit since you are adding unnecessary load, but in other respects it is not so. In my experience some firewalls will just close connections that they believe to be stale - thus you are adding the overhead of having to recreate the connection for the next query which is a live query! So for the sake of a non expensive periodic query, you are actually making the next live query open up the tunnel. Thus “heartbeat” messages do have benefit.
I’ve seen them in use in large XML messaging systems doing 400 messages a second at peak load during work hours, but which drop to minimum traffic outside. Some of these routes go through many firewalls on an ESB and thus it has real benefit there. In fact it was an hourly heartbeat if I remember correctly.
Like I said, it’s just a thought but one that might have benefit for you. Also remember that this is not your DB connection pool that is controlling this - it is the Firewall. Without testing the connection the pool has no knowledge the connection has been made stale…
Another alternative is to make the pool min idle = 0 and have the pool time out connections before the firewall. Thus before the firewall has closed it, the pool has already done so - thus handing control back to the application. The downside is of course that the first next query needs to open it all up again.
hi…
Once again thanks for your reply.
I tried with minIdle=0.But still its not working.
I replaced mysql-connector-java-3.1.7 with mysql-connector-java-5.0.7 as I get one property named tcpKeepAlive(default value=true) to make the data transfer through connection.
I think so validationQuery, TestWhileBorrow or any othe propery is to make the connection, and when I checked the connection by “netstat -a” command , i found it’s status as established.
So the connection is there. IS IT SO???
The problem is with data transfer and on the very first request it in not transfering any data. even I am not getting any exception…that is the thing which is making me to tough to solve the problem.
One thing more can you tell me that how to give Pool Connection Time out. Is there any property which decides its value??
Thanks in advance…