Getting Communications link failure on Listing task

I have huge number of tasks more than 100k, Most of the time i get this exception, not sure why.

Caused by: org.apache.ibatis.exceptions.PersistenceException:
Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 366,693 milliseconds ago. The last packet sent successfully to the server was 366,692 milliseconds ago.
The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Task.xml
The error may involve org.camunda.bpm.engine.impl.persistence.entity.TaskEntity.selectTaskCountByQueryCriteria-Inline
The error occurred while setting parameters
SQL:

    SELECT Count(DISTINCT RES.id_) 
    FROM   act_ru_task RES 
           LEFT JOIN act_re_procdef PROCDEF 
                  ON RES.proc_def_id_ = PROCDEF.id_ 
           LEFT JOIN (SELECT A.* 
                      FROM   act_ru_authorization A 
                      WHERE  A.type_ < 2 
                             AND ( A.user_id_ IN ( ?, '*' ) 
                                    OR A.group_id_ IN ( ? ) ) 
                             AND (( A.resource_type_ = ? 
                                    AND A.perms_ & ? = ? 
                                     OR A.resource_type_ = ? 
                                        AND A.perms_ & ? = ? ))) AUTH 
                  ON ( AUTH.resource_id_ IN ( RES.id_, PROCDEF.key_, '*' ) ) 
    WHERE  ( 1 = 1 
             AND RES.description_ = ? 
             AND RES.create_time_ < ? 
             AND RES.suspension_state_ = 1 ) 
           AND ( ( RES.case_execution_id_ IS NOT NULL ) 
                  OR ( AUTH.resource_id_ IS NOT NULL ) ) 

Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 366,693 milliseconds ago. The last packet sent successfully to the server was 366,692 milliseconds ago.

Do anyone have faces similar issues ?

Hi Jena,

Do you use tomcat?
Have a look at this thread:

Best regards,
Yana

Thanks yana. I just found something interesting. After analysing this above script, i tried to find out why this query is taking longer time and found that some of the columns are not being indexed in mysql. I added index to these and it helps me a lot and drastically improved my task list loading time. Hopefully someone get help with this.

Table : ACT_RU_AUTHORIZATION
Columns:
TYPE_
USER_ID_
RESOURCE_TYPE_
PERMS_

Table : ACT_RE_PROCDEF
Column: KEY_

Table: ACT_RU_TASK
Column: SUSPENSE_STATE_

1 Like

Hi Jena,

Great, thanks for sharing this.

Best regards,
Yana

HI @Jena_Sthitaprajna we have a similar issue just the only difference is we have around 500k running instances which is causing an indefinite lag for the above query. Apart from the indexes you have added is there any other opitimizations you did for the above query.

Hi @Jena_Sthitaprajna ,

Did you add composite keys / Multi-column indexes to the ACT_RU_AUTHORIZATION table or are they separate?