Under MySQL, got sql errors when query task/user/group

Engine version: 7.6.0

When I call will always got sql error.

  • identityService.createUserQuery().list()
  • identityService.createGroupQuery().list()
  • identityService.createTaskQuery().list()
  • … (maybe include other similar case, i didn’t dig much more)

It seems the reason of duplicate “order by” generated in sql statement.

The following text is snippet of error log for identityService.createTaskQuery().list()

### 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.selectTaskByQueryCriteria-Inline
### The error occurred while setting parameters
### SQL: select distinct RES.REV_, RES.ID_, RES.NAME_, RES.PARENT_TASK_ID_, RES.DESCRIPTION_, RES.PRIORITY_, RES.CREATE_TIME_, RES.OWNER_, RES.ASSIGNEE_, RES.DELEGATION_, RES.EXECUTION_ID_, RES.PROC_INST_ID_, RES.PROC_DEF_ID_, RES.CASE_EXECUTION_ID_, RES.CASE_INST_ID_, RES.CASE_DEF_ID_, RES.TASK_DEF_KEY_, RES.DUE_DATE_, RES.FOLLOW_UP_DATE_, RES.SUSPENSION_STATE_, RES.TENANT_ID_ from ACT_RU_TASK RES order by order by RES.ID_ asc LIMIT ? OFFSET ?

Hi Muyv,

sorry but I can’t reproduce your problem. Besides I think you mean taskService.createTaskQuery().list() right?

Please provide an unit test, which enables us to reproduce your problem.
For Information how to create an appropriate unit test, see this template project.

Greets,
Chris

Sorry, the “identityService” just a variable naming mistake for the test op, please don’t mind, it’s not the point.
I’v found the reason, cause there are both ${orderBy} and <include refid="selectXxxByQueryCriteriaSql" /> exists in the Group.xml/User.xml/Task.xml(maybe there are many more same problem) under org/camunda/bpm/engine/impl/mapping/entity/ of “camunda-engine-7.6.0.jar”.
Hope to fix as soon as possible in the next release version.

Much thanks for your reply.

Puzzling thing, I’m trying to create another application in SpringBoot way, it has not such issue.
Maybe I should check my MyBatis config or other config.

Very sorry, it doesn’t seems to be an issue of Canumda, I’ll figure it out.

I found it! I’v tried every MyBatis released versions from 3.2.8 to 3.4.4, the versions between 3.3.0 to 3.4.1 will got the error. I’m pretty sure that is a MyBatis’s bug.
And unfortunately, in my intergrated application, I’m using a version which has the bug to override Camunda managed one.

In short thank you!

Hi Muyv,

I’m glad that you found the root cause and a solution to solve this.
Thanks for your feedback.

Greets,
Chris