SUJIT RAJAN
Reputation: 49
Connection to database (MS-SQL) is getting closed randomly after migrating from Jboss 4 to Wildfly 8
I got stuck in one issue due to which we can't make the application live on production. This issue never comes on dev environment. It comes in the QA environment of our project client.
It is a random issue comes once or twice a week. Most of the times it comes on weekend or idle time when no users access the application in QA. But, in our application there is a separate thread which keeps on running 24 X 7 and it keeps on interacting with database and most of the times during that interaction to database we got exception that the connection is closed.
As this interaction is in a loop and thread runs 24 x 7, so exception keeps on coming and log file keeps on increasing. Log file size generated in GBs. Ultimately wildfly server stops responding. After that we need to restart the widfly server.
We are migrating from Java 6 to Java 8 & Jboss 4.2@ to wildfly 8.0.0, also migrating from MSSQL server 2005 to 2008. When I look into the connection counts in wildfly admin console, it is showing like for ex. Available count 20, active count 5 & max used 5. So, I think or it seems that connections are available in pool.
Below is the Java code, persistence.xml, datasource config in wildfly and exception stacktrace.
1) Java code:-
//@Service bean is the custom annotation for looking EJB stateless session bean
@ServiceBean(Lookup="CloseOutSchedulerService")
private CloseOutSchedulerRemote dao;
public void run() {
ServiceInjector.inject(this);
while(this.executionStatus){
try {
List<ApplicationBranch> appBranchesForAutoCloseOut =new ArrayList<ApplicationBranch>();
ApplicationEventTracker event=null;
int columnCount=TaskBoard.getRowsCount(boardName);
inner: for(int i=0;i<columnCount; i++){
ApplicationBranch branchId=(ApplicationBranch) TaskBoard.getCell(boardName, "Branch", i);
branchId=dao.getApplicationBranch(branchId.getBranchId());
2)persistence.xml
<persistence version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="EJBModule3PU" >
<jta-data-source>java:/EMSDB</jta-data-source>
<properties>
<!-- Properties for Hibernate -->
<property name="hibernate.show_sql" value="false" />
<property name="hibernate.id.new_generator_mappings" value="false"/>
</properties>
</persistence-unit>
</persistence>
3)datasource configuration in wildfly:-
<datasource jndi-name="java:/EMSDB" pool-name="EMSDB" enabled="true" use-java-context="true">
<connection-url>jdbc:sqlserver://emsqafnb:1433;databaseName=wealth_ems;</connection-url>
<driver>sqlserver</driver>
<pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
</pool>
<security>
<user-name>emsmaster1</user-name>
<password>ems@master</password>
</security>
<timeout>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>
4)exception stacktrace:-
2015-06-25 00:16:58,326 INFO [org.hibernate.event.internal.DefaultLoadEventListener] (Thread-115) HHH000327: Error performing load command : org.hibernate.exception.GenericJDBCException: could not prepare statement
2015-06-25 00:16:58,326 ERROR [org.jboss.as.ejb3.invocation] (Thread-115) JBAS014134: EJB Invocation failed on component CloseOutSchedulerService for method public abstract com.fnb.ems.models.ApplicationBranch com.fnb.ems.services.CloseOutSchedulerRemote.getApplicationBranch(java.lang.Integer): javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleExceptionInOurTx(CMTTxInterceptor.java:190) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:275) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:340) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:239) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.remote.EJBRemoteTransactionPropagatingInterceptor.processInvocation(EJBRemoteTransactionPropagatingInterceptor.java:79) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:43) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:95) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:59) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:55) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:64)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:326)
at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:448)
at org.jboss.invocation.AccessCheckingInterceptor.processInvocation(AccessCheckingInterceptor.java:61)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:326)
at org.jboss.invocation.PrivilegedWithCombinerInterceptor.processInvocation(PrivilegedWithCombinerInterceptor.java:80)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:185)
at org.jboss.as.ejb3.remote.LocalEjbReceiver.processInvocation(LocalEjbReceiver.java:243) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:181) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBObjectInterceptor.handleInvocation(EJBObjectInterceptor.java:58) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:183) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBHomeInterceptor.handleInvocation(EJBHomeInterceptor.java:83) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:183) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.TransactionInterceptor.handleInvocation(TransactionInterceptor.java:42) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:183) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.ReceiverInterceptor.handleInvocation(ReceiverInterceptor.java:125) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:183) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBInvocationHandler.sendRequestWithPossibleRetries(EJBInvocationHandler.java:253) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBInvocationHandler.doInvoke(EJBInvocationHandler.java:198) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBInvocationHandler.doInvoke(EJBInvocationHandler.java:181) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at org.jboss.ejb.client.EJBInvocationHandler.invoke(EJBInvocationHandler.java:144) [jboss-ejb-client-2.0.0.Final.jar:2.0.0.Final]
at com.sun.proxy.$Proxy96.getApplicationBranch(Unknown Source)
at com.fnb.ems.common.CloseoutScheduleService$closeoutExecutor.run(CloseoutScheduleService.java:183)
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_40]
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763) [hibernate-entitymanager-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1694) [hibernate-entitymanager-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:1141) [hibernate-entitymanager-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:1068) [hibernate-entitymanager-4.3.1.Final.jar:4.3.1.Final]
at org.jboss.as.jpa.container.AbstractEntityManager.find(AbstractEntityManager.java:199) [wildfly-jpa-8.0.0.Final.jar:8.0.0.Final]
at com.fnb.ems.servicebeans.CloseOutSchedulerBean.getApplicationBranch(CloseOutSchedulerBean.java:129) [wealth_fnt_ems_ejb.jar:]
at sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source) [:1.8.0_40]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_40]
at java.lang.reflect.Method.invoke(Method.java:497) [rt.jar:1.8.0_40]
at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53)
at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:407)
at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:82) [wildfly-weld-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:93) [wildfly-weld-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53)
at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47) [wildfly-jpa-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:407)
at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:55) [weld-core-impl-2.1.2.Final.jar:2014-01-09 09:23]
at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83) [wildfly-weld-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45) [wildfly-ee-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53)
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.component.interceptors.NonPooledEJBComponentInstanceAssociatingInterceptor.processInvocation(NonPooledEJBComponentInstanceAssociatingInterceptor.java:59) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:273) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
... 47 more
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.prepareQueryStatement(AbstractLoadPlanBasedLoader.java:257) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:201) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:137) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:102) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:186) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4120) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:502) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:467) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:212) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:274) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:150) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1066) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.internal.SessionImpl.access$2000(SessionImpl.java:176) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2540) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.internal.SessionImpl.get(SessionImpl.java:951) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:1110) [hibernate-entitymanager-4.3.1.Final.jar:4.3.1.Final]
... 84 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:389)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2175)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:745)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:731)
at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:405)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
... 101 more
Upvotes: 2
Views: 3428
Answers (1)
mikaelovi
Reputation: 131
I have answered this question in anther SO thread, but I'll post same answer here all the same.
This Link explains the problem and proffers a good solution.
Summarily, your database or application server (e.g JBoss) connection pool manager has invalidated some connections due to idle timeout settings either set by default or configured explicitly, meanwhile, hibernate's internal database connection pool manager has those connections as valid connections. Hence, when your application tries to reuse them, you get the connection closed error which is not cleared until you restart your application server.
To resolve, simply delegate hibernate connection pool management to a helper library called hibernate-c3p0 and follow an example configuration such as
<property name="hibernate.c3p0.min_size" value="5" />
<property name="hibernate.c3p0.max_size" value="20" />
<property name="hibernate.c3p0.timeout" value="300" />
These properties are well explained in the link above and also here
Upvotes: 0
Related Questions
- Datasource pool connection not closed in Wildfly 20
- Cannot connect to SQL server database from Wildfly
- Wildfly + SQL Server connections closing randomly and not recovering
- JPA/Wildfly/MsSql: GenericJDBCException: Unable to acquire JDBC Connection
- Java.sql Connection closed Exception when on a particular server
- jboss wildfly datasource connection failing - No managed connections available within configured blocking timeout
- Sql Server JDBC not Working on wildfly AS
- On Wildfly 11 the Microsoft SQL Server driver incorrectly trys to connect to localhost
- Jboss Wildfly not closing connections when not in use
- Getting OracleConnection on JBoss Wildfly 8.1