Alfresco losing unable to open hiberbate session for transaction

[hsturner]

Alfresco runs fun for a couple of weeks to months with no issues then everyone once in a while Alfresco loses the ability to open a connection to its database on the SQL server we have set up for it.  Using alfresco community 2016.04 GA, MySQL 5.5, MySQL connector MySQL-connector-java-5.1.32-bin.jar, both alfresco and MySQL have 275 for mx db connections. 307 active, but not simultaneous users. Any help with this issue is appreciated.  I have already increased the max connections once from 250 to 275.

We get the following error showing up in the log, and the issue does not clear unless the alfresco service or server is restarted.

2016-12-06 11:39:58,072 ERROR [org.activiti.engine.impl.jobexecutor.AcquireJobsRunnableImpl] [Thread-63] exception during job acquisition: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:598)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:372)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
        at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
        at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
        at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
        at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnableImpl.run(AcquireJobsRunnableImpl.java:54)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
        at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
        at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:119)
        at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
        at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:556)
        ... 8 more
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
        at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:83)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
        ... 13 more
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
        at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1174)
        at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
        ... 16 more

[kaynezhang]

You can try to  update MySQL  max connections setting .

please refer to MySQL :: MySQL 5.5 Reference Manual :: B.5.2.7 Too many connections 

[hsturner]

Hi Kayne,

I've already tired that and it still have the issue. The errror actually isn't too many it's it cant open a hibernated database session. So I'm guessing the database connection is going to sleep and alfresco can't wake it up. I'm also looking at it from the MySQL server and network side, trying to eliminate possible causes. So far it is pointing to alfresco or something in alfresco that is causing the issue.

[kaynezhang]

Have you tried to change your MySQL connector  java to newer version?

[hsturner]

I just updated to the MySQL-connector-java-5.1.32-bin.jar which is the one recommended of community 5.1 but will try the mysql-connector-java-5.1.40-bin.jar connector which I think is the most recent.  We are scheduling time to update the MySQL server to 5.6 as well. Will let you know if that fixes the problem.