JDBC commit failed Exception after trying to send notification mails to group of users (more than 100) in a custom workflow serviceTask

[mtsiak]

The service task of our workflow is like:

var mail = actions.create("mail");
mail.parameters.to_many = receivers; // array of more than 100 users

  ...

mail.executeAsynchronously(bpm_package);

The exeption we get every 10 minutes:

2016-12-01 18:13:29,318  ERROR [hibernate.transaction.JDBCTransaction] [mailAsyncAction8] JDBC commit failed

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during commit(). Transaction resolution unknown.

        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

        at java.lang.reflect.Constructor.newInstance(Constructor.java:408)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)

        at com.mysql.jdbc.Util.getInstance(Util.java:383)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1023)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)

        at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1748)

        at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334)

        at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334)

        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:211)

        at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:139)

        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:115)

        at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:657)

        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755)

        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724)

        at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475)

        at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:482)

        at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:479)

        at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:326)

        at org.alfresco.repo.action.AsynchronousActionExecutionQueueImpl$ActionExecutionWrapper$1.doWork(AsynchronousActionExecutionQueueImpl.java:437)

        at org.alfresco.repo.tenant.TenantUtil.runAsWork(TenantUtil.java:119)

        at org.alfresco.repo.tenant.TenantUtil.runAsTenant(TenantUtil.java:88)

        at org.alfresco.repo.tenant.TenantUtil$1.doWork(TenantUtil.java:62)

        at org.alfresco.repo.security.authentication.AuthenticationUtil.runAs(AuthenticationUtil.java:548)

        at org.alfresco.repo.tenant.TenantUtil.runAsUserTenant(TenantUtil.java:58)

        at org.alfresco.repo.action.AsynchronousActionExecutionQueueImpl$ActionExecutionWrapper.run(AsynchronousActionExecutionQueueImpl.java:440)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

        at java.lang.Thread.run(Thread.java:745)

Any ideas;

Thanks in advance.

[afaust]

It is not guaranteed that the error is directly caused by the action. Is there a kind of timeout on the JDBC connection e.g. configured on the database server? If that is the case and sending the email(s) takes a long time the connection may be closed due to inactivity (I have not seen such behaviour in practice).

Have you tried creating multiple action instances for (reasonable) subsets of the users to see if the behaviour changes?

[mtsiak]

when the recipient is one user, the message is sent. When the recipient is a group of 60 people, the error occurs.

is there some kind of logging that we could enable ?

[mtsiak]

Previously, when adding a group of 70 people to 'to_many' param (executeAsynchronous), we start getting mails after 5 minutes, and after 10 minutes we where getting JDBC commit failed exception. As the service task did not end with success, it repeats itself, and starts again to sent emails and again the exception.

Now we iterate on each member, get its email property, set the 'to' param, and executeAsynchronous, one by one, and it works: the email are sent in 2-3 minutes, and no exeption. 

Any ideas?

[mtsiak]

We enabled the debug mode for some class, and located the transaction id that is related with the exception:

2016-12-06 14:04:33,747  DEBUG [util.transaction.TransactionSupportUtil] [mailAsyncAction1] Bound txn synch: TransactionSychronizationImpl[ txnId=738574ec-e248-475b-be26-d6f8945c5ad8]
2016-12-06 14:12:49,121  DEBUG [util.transaction.TransactionSupportUtil] [mailAsyncAction1] Before commit TransactionSychronizationImpl[ txnId=738574ec-e248-475b-be26-d6f8945c5ad8]
2016-12-06 14:12:49,121  DEBUG [util.transaction.TransactionSupportUtil] [mailAsyncAction1] Before Prepare priorities:[4]
2016-12-06 14:12:49,121  DEBUG [util.transaction.TransactionSupportUtil] [mailAsyncAction1] Prepared
2016-12-06 14:12:49,121  DEBUG [util.transaction.TransactionSupportUtil] [mailAsyncAction1] Before completion: TransactionSychronizationImpl[ txnId=738574ec-e248-475b-be26-d6f8945c5ad8]
 
2016-12-06 14:12:49,139  ERROR [hibernate.transaction.JDBCTransaction] [mailAsyncAction1] JDBC commit failed
 com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during commit(). Transaction resolution unknown.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    ... 
    
2016-12-06 14:12:49,747  DEBUG [util.transaction.TransactionSupportUtil] [mailAsyncAction1] After completion (unknown): TransactionSychronizationImpl[ txnId=738574ec-e248-475b-be26-d6f8945c5ad8]     
2016-12-06 14:12:49,748  DEBUG [util.transaction.TransactionSupportUtil] [mailAsyncAction1] Unbound txn synch:TransactionSychronizationImpl[ txnId=738574ec-e248-475b-be26-d6f8945c5ad8]

Any ideas? What could we more set to debug mode? Or where should we look into? Thanks in advance.