MaxOpenPreparedStatements limit reached exception while migrating to MySql

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

pradeepsimha

Hi,

I am trying to change the database of Alfresco to MySql and I followed the steps mentioned here: http://docs.alfresco.com/4.1/index.jsp?topic=%2Fcom.alfresco.enterprise.doc%2Ftasks%2Fmysql-config.html but when I try to start the service I am getting below error:

### Cause: java.sql.SQLException: MaxOpenPreparedStatements limit reached
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; MaxOpenPreparedStatements limit reached; nested exception is java.sql.SQLException: MaxOpenPreparedStatements limit reached
.
.
.
.
.
Caused by: java.sql.SQLException: MaxOpenPreparedStatements limit reached
   at org.apache.commons.dbcp.PoolingConnection.prepareStatement(PoolingConnection.java:109)
   at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
.
.
.
Caused by: java.util.NoSuchElementException: Pool exhausted
   at org.apache.commons.pool.impl.GenericKeyedObjectPool.borrowObject(GenericKeyedObjectPool.java:1130)
   at org.apache.commons.dbcp.PoolingConnection.prepareStatement(PoolingConnection.java:107)
   … 101 more
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Service starts from Windows, but if I see the tomcat logs, it is just stuck here:

INFO: Deploying web application archive C:\Alfresco\tomcat\webapps\alfresco.war
Mar 27, 2014 12:28:01 PM org.apache.catalina.startup.TaglibUriRule body
INFO: TLD skipped. URI: http://java.sun.com/jsp/jstl/core is already defined
Mar 27, 2014 12:28:02 PM org.apache.catalina.core.ApplicationContext log
INFO: Initializing Spring root WebApplicationContext
‍‍‍‍‍‍‍

If I try to hit url in browser, it keeps on saying "Waiting for localhost.." nothing gonna come.

This is my content related to alfresco-global.properties

db.driver=org.gjt.mm.mysql.Driver
db.url=jdbc:mysql://${db.host}:${db.port}/${db.name}?useUnicode=yes&characterEncoding=UTF-8 

db.name=alfresco
db.username=alfresco
db.password=alfresco
db.host=localhost
db.port=3306
db.pool.max=400
‍‍‍‍‍‍‍‍‍‍‍

Can someone kindly help me? What maybe the cause of this exception. All these were working fine when I try to use default database, but suddenly got this exceptions when I try to migrate to MySql.

sanket

In your code, are the resources closed properly in finally block ?
If not, you need to do it first.


Also, can you try putting this property in alfresco-global.properties and check ?

db.pool.statements.enable = false