The issue appeares not only on Postgresql but also on MySQL and it is not specific just for JMS, REST API fails too.

Hi Ivo,
can you provide the log for MySQL? 
And can you confirm that you are using the taskorm.xml file that is provided by the jbpm-human-task-jpa jar module? -> https://github.com/droolsjbpm/jbpm/blob/master/jbpm-human-task/jbpm-human-task-jpa/src/main/resources/META-INF/Taskorm.xml

Hi Mauricio,

Radek will attach the server log for MySQL.

Everything is running on the BPMS server and the problem happens there so I do not think that I should configure anything else in the client. I just do some REST or JMS requests. If Jbpm engine is not configured correctly to use taskorm.xml in the Business Central then it is a bug and I am not sure if can change there anything.

Ivo, that's ok I just wanted to be sure that you are not running against a modified copy of the taskorm.xml file. 

Can you also please attach the result of running the tests that you mention before -> http://git.app.eng.bos.redhat.com/git/jbossqe/brms.git/tree/test-jbpm-integration/src/test/java/org/jboss/qa/bpms/jbpm/integration/client/task/CGetTaskTest.java#n102 with MySQL and postgreSQL? because the logs that you have attached doesn't seem to be running that specific test.

I'm doing some tests with the internal APIs and there seems to not be any issue at that level.

There seems to be an issue with the DBs in general regarding the IN operator in the queries: http://postgresql.1045698.n5.nabble.com/IN-query-operator-and-NULL-values-td1901954.html

This means that we will need to check for empty lists. 
Can you please confirm that the user has roles (groups) configured? So we make sure that this will work if the user is providing at least a group?

regards

Created attachment 904621 [details]
mysql stacktrace

Mauricio, here is the requested stacktrace for mysql.

This is the output of the test on the client side:

org.kie.services.client.api.command.exception.RemoteApiException: SQLGrammarException thrown with message 'could not extract ResultSet'
	at org.kie.services.client.api.command.AbstractRemoteCommandObject.executeJmsCommand(AbstractRemoteCommandObject.java:358)
	at org.kie.services.client.api.command.AbstractRemoteCommandObject.execute(AbstractRemoteCommandObject.java:126)
	at org.jbpm.services.task.impl.command.CommandBasedTaskService.getTasksAssignedAsPotentialOwner(CommandBasedTaskService.java:185)
	at org.jboss.qa.bpms.jbpm.integration.client.task.CGetTaskTest.testGetGroupTask(CGetTaskTest.java:110)

The log I attached previously is from the server log what is happening there because the client log won't tell much as you can see. Actually it fails when I do:

List<TaskSummary> tasksForTestUser = taskService.getTasksAssignedAsPotentialOwner("testuser", "en-UK");

For sure the user does not have any tasks to do, so it might be the problem as you pointed in the case the list is empty.

If you mean the groups like admin, analyst, manager, ... then yes, I set admin group for the testadmin.

Ivo, can you try setting a different role like for example user to the testuser? To see if you get the exact same error?

Sure, I will try it. We have a meeting now, so after that.

The role admin is filtered to not be used as a group for a user, so it must be avoided for doing tests, let us know if it works with a user with different roles.

The issue is independent on the role I set. The test fails also when I use a testuser with role user.

AFAIK the admin role is allowed and we use it quite often because otherwise we don't have all the permissions we need but the problem is when the user is called admin too, isn't it?

Hi Ivo, guys,
I've pushed some internal checks for empty lists inside the TaskQueryService -> http://github.com/droolsjbpm/jbpm/commit/32fba6dad (6.0.x)

Can you please test again to verify that these fixes fix the issue with PostgreSQL and MySQL?

Hi Mauricio,
fixed (at least) for mysql.

Thanks!

Hi Radovan!
Great news! Let's see if it works for PostgreSQL. Any idea if anyone is testing with Oracle? just to confirm that too?

Mauricio, it is not easy to test the fix against all databases we support right now (Oracle, DB2, MSSql, ...) because we have to configure it all locally, so I would wait for the next build. At least I could try PostgreSQL too if you want to be sure.

Hi Ivo, if you can try with PostgreSQL that will be great

Tomorrow, we will test the fix against all databases and run all integration tests to confirm and be absolutely sure there are no regressions.

I've reconfigured business-central (BPMS 6.0.2.ER3) to run on PostgreSQL 9.3 (installed on localhost) and didn't have any issues using the remote Java client (through REST) to get the task list.

I did the following changes from a clean unzip of the EAP distribution:

in jboss-eap-6.1\standalone\deployments\business-central.war\WEB-INF\classes\META-INF\persistence.xml
      <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />

in jboss-eap-6.1\standalone\configuration\standalone.xml

         <datasources>
            <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
               <connection-url>jdbc:postgresql://localhost/jbpm</connection-url>
               <driver>postgresql</driver>
               <security>
                  <user-name>jbpm</user-name>
                  <password>jbpm</password>
               </security>
            </datasource>
            <drivers>
               <driver name="h2" module="com.h2database.h2">
                  <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
               </driver>
               <driver name="postgresql" module="org.postgresql">
                  <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
               </driver>
            </drivers>
         </datasources>

Afterwards, I ran the following code (where adminuser is a user with only role admin):

RuntimeEngine engine = RemoteRestRuntimeEngineFactory.newBuilder()
			.addUserName("adminuser").addPassword("password1_")
			.addUrl(new URL("http://localhost:8080/business-central")).build().newRuntimeEngine();
		System.out.println(engine.getTaskService().getTasksAssignedAsPotentialOwner("adminuser", "en-UK").size());

I did that once without any tasks, and then once after creating one task through the UI, it returned 0 and 1 respectively (as expected).

All the integration tests passed against db2-97, mssql2012, mysql5.5, postgresql 9.2, and oracle11. There no regressions and the changes fix the problem.

Verified in BPMS 6.0.2.CR1