If you had a database outage or failure while JBoss was up, you’re likely in a situation where you would have to restart JBoss because the database connections didn’t get reconnected for you. You’re likely also faced with a massive set of log files due to the data source complaining about it not being able to connect to the database.
If you want JBoss to automatically reconnect the data source pool(s) for you, here’s some settings to use (only valid if using a “local-tx-datasource”):
1) <autoReconnect>true</autoReconnect>
2) <check-valid-connection-sql>an efficient select statement</check-valid-connection-sql>
3) <failOverReadOnly>false</failOverReadOnly>
4) <maxReconnects>50</maxReconnects>
5) <initialTimeout>15</initialTimeout>
In the above settings, if the data source connectivity is lost (database down or network failure) – then JBoss will automatically attempt to reconnect 50 times every 15 seconds. You can obviously set those numbers to values suitable to your environment. It’s also important to set the failOverReadOnly flag to false as it ensures that recovered connections are writable.
Additionally, some people prefer to include a valid and efficient SQL statement in the “check-valid-connection”, it’s completely optional but a good practice in environments where stale connections are common. Optionally, there’s also “new-connection-sql” which will check on newly created connections.
Thanks for posting this topic but is there one for WebSphere environment?
WebSphere will handle this automatically for you without any additional changes.
Hi!
Where will you put these configuration? Is it in the *-ds.xml?
And will this work if I did not put the
tag?
Thanks!
Walter, yes – these settings go in the data source configuration xml files. Not sure I understand your second question.
Hi, do you mind sharing the version of JBoss this works with? I’ve only seen references to the autoReconnect feature in combination with MySQL (and then only as an option). Thanks!
We know this works with JBoss 4.0.1, 4.2.1, and 5.1
Thanks! Bonus question :) Do you know if it work with Oracle or only MySQL?
Hi Andrew, when I tested this with JBoss 4.0.3, I was hitting an Oracle 10g database, and it worked just fine.
Worked like a charm… THANKS!
hi
Do you know how to use “autoReconnect” element in “xa-datasource” tag.
could you tell me where i can find the jboss org authority’s doc to use “autoReconnect” element ?
thank a lot
Hi,
How would jbossas 5.0.0 behave during the database outage for 2hrs without autoreconnect param and with only valid-connection-checker-class-name *-ds.xml file
i am not able to autoconnect in jboss 7…as this configuration does not work in jboss 7 ..please tell me how to reconnect in jboss7
Pingback: Resolving java.sql.SQLRecoverableException with the Oracle 11 driver | Adobe LiveCycle Blog