Jboss data recovery after database recovery

Question

Closed connections are still in the connection pool - why?

servlet -

public class Index extends HttpServlet { TimeZoneService timeZoneService; public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException { WebApplicationContext ctx = WebApplicationContextUtils.getRequiredWebApplicationContext(getServletContext()); timeZoneService = (TimeZoneService) ctx.getBean("timeZoneService"); timeZoneService.loadAllTimeZones(); System.out.println("Done"); } } public interface TimeZoneService { void loadAllTimeZones(); } public class TimeZoneServiceImpl implements TimeZoneService { private TimeZoneDao tzDao; private Map<Long, String> tzOid2JavaName = new HashMap<Long, String>(); public void loadAllTimeZones() { List<TimeZone> timeZones = tzDao.findAllTimeZones(); for (TimeZone tz : timeZones) { tzOid2JavaName.put(tz.getOid(), tz.getJavaName()); } } public void setTzDao(TimeZoneDao tzDao) { this.tzDao = tzDao; } } public interface TimeZoneDao { List<TimeZone> findAllTimeZones() throws DataAccessException; } public class TimeZoneDaoImpl extends JdbcDaoSupport implements TimeZoneDao { public List<TimeZone> findAllTimeZones() throws DataAccessException { StringBuffer sql = new StringBuffer(); sql.append("SELECT TZ.OID, TZ.JAVA_NAME FROM TIME_ZONE TZ"); List<TimeZone> timeZones = getJdbcTemplate().query(sql.toString(), new RowMapper() { public Object mapRow(ResultSet rs, int i) throws SQLException { TimeZone tz = new TimeZone(); tz.setOid(rs.getLong("OID")); tz.setJavaName(rs.getString("JAVA_NAME")); return tz; } }); return timeZones; } } public class TimeZone { private Long oid; private String javaName; public Long getOid() { return this.oid; } public void setOid(Long oid) { this.oid = oid; } public String getJavaName() { return this.javaName; } public void setJavaName(String javaName) { this.javaName = javaName; } } 

spring -config.xml

 <beans> <jee:jndi-lookup id="dataSource" jndi-name="java:/OracleDS"/> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean> <bean id="timeZoneDao" class="dao.impl.TimeZoneDaoImpl"> <property name="dataSource" ref="dataSource"/> </bean> <bean id="timeZoneService" class="logic.impl.TimeZoneServiceImpl"> <property name="tzDao" ref="timeZoneDao"/> </bean> </beans> 

web.xml

 <web-app> <display-name>Spring</display-name> <context-param> <param-name>contextConfigLocation</param-name> <param-value> WEB-INF/spring-config.xml,classpath*:/META-INF/spring-config.xml</param-value> </context-param> <listener> <listener-class> org.springframework.web.context.ContextLoaderListener </listener-class> </listener> <servlet> <servlet-name>index</servlet-name> <display-name>Index page</display-name> <description>Landing page</description> <servlet-class>servlet.Index</servlet-class> </servlet> <servlet-mapping> <servlet-name>index</servlet-name> <url-pattern>/index</url-pattern> </servlet-mapping> <!-- Session Timeout (in minutes) --> <session-config> <session-timeout>60</session-timeout> </session-config> </web-app> 

MySQL-ds.xml

 <datasources> <local-tx-datasource> <jndi-name>OracleDS</jndi-name> <connection-url>jdbc:mysql://localhost:3306/spring</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <user-name>spring_test</user-name> <password>spring_test13</password> <min-pool-size>1</min-pool-size> <max-pool-size>5</max-pool-size> <idle-timeout-minutes>2</idle-timeout-minutes> </local-tx-datasource> </datasources> 

Answer 1

Ok Hope someone will benefit from the following :-)

There is a data source configuration setting - exception-sorter-class-name

According to Jboss, this is used for a class that looks at vendor specific messages to determine whether sql errors are fatal and thus the connection should be destroyed. If none specified, no errors will be treated as fatal. a class that looks at vendor specific messages to determine whether sql errors are fatal and thus the connection should be destroyed. If none specified, no errors will be treated as fatal.

If using an Oracle database , this configuration is set to org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter . This class has all error codes that should be considered fatal, and therefore the connection must be destroyed.

In Jboss 4, error codes 17002 (connection reset) && & && 17008 (connection closed) are not included. They are added to Jboss 5. So, if you are using Jboss 4 and wondering why connections are not being restored, try adding the missing codes.

Answer 2

This is a common problem with connection pooling. When an application borrows a connection from the pool, should the pool itself “check” the connection to make sure it is still valid, or should it leave it until the application?

If the pool checks the connection, it is inevitably connected with sending something down the connection to the database server (usually this is some basic SELECT). On systems with high traffic levels, this is extremely wasteful and can cause significant damage to the database server.

However, on low-traffic sites where your database can handle the extra load, you can set up your data source so that JBoss checks the connection before transferring it to your application. If the connection is dead, JBoss will remove it from the pool and get a new one so that it stops working with a database reboot.

Anyone add this to your mysql-ds.xml file:

 <check-valid-connection-sql>select 1 from mytable</check-valid-connection-sql> 

You must choose the request yourself, make sure that it is not expensive, because it will work a lot.

See the JBoss documentation for documentation on how to modify these data source files.

Answer 3

Here is an example of configuring a validaton connection to an Oracle window for jboss 7.1 + :

 <validation> <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.vendor.OracleValidConnectionChecker"/> <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql> <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.vendor.OracleStaleConnectionChecker"/> <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/> </validation> 

Now Jboss will check your every connection.