MySQLNonTransientConnectionException while doing FULL Re-indexing.

[faizaan]

Hi,

We are getting "com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException" while performing full re-indexing of repository.

Full Error Logs :

16:16:47,698  INFO  [node.index.FullIndexRecoveryComponent] Index recovery started: 156,296 transactions.
16:18:15,900  INFO  [node.index.FullIndexRecoveryComponent]    10 % complete.
16:19:02,933  INFO  [node.index.FullIndexRecoveryComponent]    20 % complete.
16:21:07,481  INFO  [node.index.FullIndexRecoveryComponent]    30 % complete.
16:26:26,857  WARN  [jdbc.support.SQLErrorCodesFactory] Error while extracting database product name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:


** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: Communications link failure

Last packet sent to the server was 1 ms ago.

STACKTRACE:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 1 ms ago.
   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
   at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
   at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
   at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
   at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009)
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
   at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1021)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:229)
   at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
   at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
   at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
   at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)
   at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
   at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
   at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:295)
   at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:1)
   at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)
   at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:293)
   at org.alfresco.repo.domain.node.ibatis.NodeDAOImpl.selectTxnChanges(NodeDAOImpl.java:1263)
   at org.alfresco.repo.domain.node.AbstractNodeDAOImpl.getTxnChangesForStore(AbstractNodeDAOImpl.java:3282)
   at org.alfresco.repo.domain.node.AbstractNodeDAOImpl.getTxnChanges(AbstractNodeDAOImpl.java:3276)
   at org.alfresco.repo.node.index.AbstractReindexComponent.reindexTransaction(AbstractReindexComponent.java:662)
   at org.alfresco.repo.node.index.AbstractReindexComponent$ReindexWorkerRunnable$1.execute(AbstractReindexComponent.java:827)
   at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:381)
   at org.alfresco.repo.node.index.AbstractReindexComponent$ReindexWorkerRunnable.run(AbstractReindexComponent.java:849)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
   at java.lang.Thread.run(Thread.java:662)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
   at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2455)
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2906)
   … 31 more

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Following are some configuration related details :

#repository.properties :
db.pool.max=40
db.pool.wait.max=-1
db.pool.idle=-1

db.schema.stopAfterSchemaBootstrap=false
db.schema.update=true
db.schema.update.lockRetryCount=24
db.schema.update.lockRetryWaitSeconds=5
db.driver=org.gjt.mm.mysql.Driver
db.name=alfresco
db.pool.initial=10
db.txn.isolation=-1
db.pool.statements.enable=true
db.pool.statements.max=40
db.pool.min=0
db.pool.validate.query=
db.pool.evict.interval=-1
db.pool.evict.idle.min=1800000
db.pool.validate.borrow=true
db.pool.validate.return=false
db.pool.evict.validate=false

#We have added below properties to alfresco-global.properties :

lucene.query.maxClauses=1000000
lucene.indexer.maxPathCacheSize=1000000
lucene.indexer.maxFieldLength=1000000
lucene.maxAtomicTransformationTime=200
lucene.indexer.maxDocsForInMemoryMerge=40000
lucene.indexer.maxDocsForInMemoryIndex=160000
lucene.indexer.mergerMaxMergeDocs=4000000
lucene.indexer.writerMaxMergeDocs=16000000
lucene.indexer.batchSize=1500
lucene.indexer.mergerTargetIndexCount=8
index.tracking.maxTransactionsPerLuceneCommit=6
index.tracking.maxRecordSetSize=50
lucene.indexer.maxRamInMbForInMemoryMerge=2048
lucene.indexer.maxRamInMbForInMemoryIndex=2048
lucene.indexer.writerRamBufferSizeMb=2048
lucene.indexer.mergerRamBufferSizeMb=2048
index.recovery.maximumPoolSize=8
lucene.indexer.mergeFactor=100

#MYSql variables :
wait_timeout=7500
max_connections=50

We are using alfresco 3.4.3 coupled with MYSql and tomcat 6.x.

Any suggestions/pointers to fix above issue is highly appreciated.

Thanks,
Faizaan

[mitpatoliya]

It seems like mysql db server not responding after some time.
I suggest you one thing.
Just change the db connection properties which are required to be changed don't override all the properties.
Same with the lucene unless you are not sure about the property better go with default value.

[faizaan]

Hi Mits,

Thanks for the quick reply !

We have not changed any properties related to db in repository.properties, I have added those just for information they are all having there default values that are set in OOTB alfresco 3.4.3.

Yes, we have overriden some lucene related properties by adding it to alfresco-global.properties that are mentioned in previous post.

Thanks !
Faizaan