Issue

Applications running on an Oracle database fail to start when the task queue subsystem generates invalid SQL. The task queue retrieves queued tasks using a query that combines a FOR UPDATE clause with an IN subquery that contains an ORDER BY clause, which Oracle rejects. Startup fails with the following error:

Caused by: com.mendix.basis.connectionbus.ConnectionBusException: Exception occurred while retrieving data. (SQL State: 42000, Error Code: 907) Detail Message: Error : 907, Position : 833, SQL = SELECT  "SYSTEM$QUEUEDTASK"."ID", "SYSTEM$QUEUEDTASK"."SEQUENCE", "SYSTEM$QUEUEDTASK"."STATUS", "SYSTEM$QUEUEDTASK"."QUEUEID", "SYSTEM$QUEUEDTASK"."QUEUENAME", "SYSTEM$QUEUEDTASK"."CONTEXTTYPE", "SYSTEM$QUEUEDTASK"."CONTEXTDATA", "SYSTEM$QUEUEDTASK"."MICROFLOWNAME", "SYSTEM$QUEUEDTASK"."USERACTIONNAME", "SYSTEM$QUEUEDTASK"."ARGUMENTS", "SYSTEM$QUEUEDTASK"."XASID", "SYSTEM$QUEUEDTASK"."THREADID", "SYSTEM$QUEUEDTASK"."CREATED", "SYSTEM$QUEUEDTASK"."STARTAT", "SYSTEM$QUEUEDTASK"."STARTED", "SYSTEM$QUEUEDTASK"."RETRIED", "SYSTEM$QUEUEDTASK"."RETRY", "SYSTEM$QUEUEDTASK"."SCHEDULEDEVENTNAME", "SYSTEM$QUEUEDTASK"."SYSTEM$OWNER"  FROM "SYSTEM$QUEUEDTASK" WHERE "SYSTEM$QUEUEDTASK"."ID" IN (SELECT "SYSTEM$QUEUEDTASK"."ID" FROM "SYSTEM$QUEUEDTASK" WHERE "SYSTEM$QUEUEDTASK"."USERACTIONNAME" = :1 AND "SYSTEM$QUEUEDTASK"."STATUS" = :2 ORDER BY "SYSTEM$QUEUEDTASK"."STARTAT" ASC, "SYSTEM$QUEUEDTASK"."ID" ASC) FOR UPDATE, Error Message = ORA-00907: missing right parenthesis

Environment

• Studio Pro v10.24.17
• Studio Pro v11.6.5
• Studio Pro v11.9

Cause

In Oracle, the FOR UPDATE clause cannot be used in a query when the WHERE clause contains an IN subquery that includes an ORDER BY clause. The task queue subsystem builds this exact combination when retrieving records from the System$QueuedTask table, so Oracle returns ORA-00907: missing right parenthesis and the application fails to start.

Solution / Workaround

Upgrade to a Mendix version that contains the fix:

• 10.24.18
• 11.6.6
• 11.10.0

After upgrading, the task queue generates valid SQL and the application starts on Oracle.

Additional information

Not Applicable