Tasklist filters with 'like' not working on postgres

Camunda 7 Topics Discussion & Questions
,
1

I am trying to upgrade an old 7.4 installation of camunda to 7.12 on red hat linux.
If I extract the tar file and run on the h2 database I can add filters in tasklist with criteria like:

Process Definition
Name Like Invoice

and they work fine

However as soon as I try and run this against an empty postgres database I get errors saying:

Failure: Process engine persistence exception

In the browser console I can see this has called the endpoint below and failed with an http 500 error
http://192.168.101.3:8080/camunda/api/engine/engine/default/filter/a12dc1d1-6859-11ea-a721-000c2911b3d3/list?firstResult=0&maxResults=15

and if I run it manually I get
{“type”:“ProcessEngineException”,“message”:“Process engine persistence exception”}

I know I am connecting to the empty database okay because everything else seems to work and I can see the tables have been created in pgAdmin.

Looking in the Tomcat logs I can see an exception with lots of SQL

the key bits I think are

Error querying database. Cause: org.postgresql.util.PSQLException: Unterminated string literal started at position 1,443 in SQL select distinct…

from ACT_RU_TASK RES…
WHERE (
1 = 1 and
D.NAME_ like ? ESCAPE ‘’
and RES.SUSPENSION_STATE_ = 1
)

I think the problem may be that the ESCAPE ‘’
should be ESCAPE ‘’’

Is this a known issue with camunda on postgres and is there any way I can fix the problem?

2 
> Perhaps this:
>         D.NAME_ like ? ESCAPE '\'
> should be:
>       D.NAME_ like ? ESCAPE '\''
3

Hi @GaryRich,

I had a look into this and the ESCAPE part of sql statement seems to be added somewhere in between 7.4 and 7.12.

It could be a mixup with the database setting, that the engine assumes working with a H2 database instead of postgresQL.

If you want more details: I’ve searched for escapeChar in the engine code on my local harddrive.

Hope this helps, Ingo

4

Thanks very much for your response Ingo,

I have also seen some similar errors arising from my use of the java api.
Specifically a HistoricProcessInstanceQuery which uses processDefinitionKeyNotIn(List)

This also seems to result in

# Error querying database. Cause: org.postgresql.util.PSQLException: Unterminated string literal started at position 428 in SQL select distinct RES.

from (

SELECT SELF.*, DEF.NAME_, DEF.VERSION_
FROM ACT_HI_PROCINST SELF

LEFT JOIN ACT_RE_PROCDEF DEF
ON SELF.PROC_DEF_ID_ = DEF.ID_

 WHERE  SELF.START_TIME_ <= ?
    and SELF.START_TIME_ >= ?
  

      and DEF.KEY_ not like ? ESCAPE '\'
    
      and DEF.KEY_ not like ? ESCAPE '\'   ...

Fortunately I can work around this one by not using “processDefinitionKeyNotIn()” and removing the instances with certain keys manually after the query.

I am using the full camunda distibution - is there any way I can reconfigure the escape character?
Or would I need to rebuild some of the camunda code?

5

Running this in pgadmin:

     select * from act_id_user where first_ like '%e%' ESCAPE '\'

results in this error:

ERROR:  unterminated quoted string at or near "'\'"
LINE 1: select * from act_id_user where first_ like '%e%' ESCAPE '\'
                                                             ^
SQL state: 42601
Character: 58

But either of these variations works fine:

select * from act_id_user where first_ like '%e%' ESCAPE '\\'

select * from act_id_user where first_ like '%e%' ESCAPE '\''

returning users “Demo Demo” & “Peter Meter”

so am fairly sure Camunda is generating incorrect SQL for postgres at least