Orbeon Forms 2018.1.1 CE & MySQL: 'Error performing search' received on summary page of any created form

1
votes

I have installed Orbeon Forms 2018.1.1 CE using Mysql as a relational database (Mysql version: 8.0.13).

Although I am able to create, save and publish any form, when I try to access the summary of a form, I get an 'Error performing search' error. The same error is also displayed every time I try to search for a form on the summary page using the 'Search Forms' control on the upper section of the page.

In addition to that, the summary page is always empty, although the relevant table in the database contains the appropriate records. The application is running on the Apache Tomcat (8.5.35) and the database was created using the mysql-2017_2.sql script.

I have also checked the orbeon.log file and I've found the following entry:

An Error has Occurred

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near row_number FROM orbeon_i_current c, (select @rownum := 0) r at line 12

Application Call Stack

oxf:/apps/fr/page-flow.xml

reading page view data output 46

element=<service path="/fr/service/(oracle|mysql|postgresql|db2|sqlserver)/search/([^/^.]+)/([^/^.]+)" view="persisten view =persistence/relational/search.xpl

oxf:/apps/fr/persistence/relational/search.xpl

reading processor output 22

element=<p:output name="data" ref="data"/>

name =data

ref =data

I am wondering if there is any other required configuration for the summary page to work properly.

Using the P6Spy application, I've realized that the following query seems to be responsible for the error message:

SELECT count(*)
  FROM (
           SELECT c.data_id,
           c.document_id,
           c.draft,
           c.created,
           c.last_modified_time,
           c.last_modified_by,
           c.username,
           c.groupname,
           c.organization_id,
           @rownum := @rownum + 1 row_number
      FROM orbeon_i_current c
           , (select @rownum := 0) r
     WHERE c.app     = 'DemoApp'         AND
           c.form    = 'DemoForm'
       AND c.draft = 'N'
) a

Actually, the problem is probably in the line: @rownum := @rownum + 1 row_number

MySQL introduced the window function ROW_NUMBER() since version 8.0 and in this line the literal 'row_number' is translated by the sql engine as a function.

1
mysqlorbeon
This might be a bug. Could you setup P6Spy as described here, and reproduce the problem to find the specific SQL query that is causing the issue? Then it would be useful if you could post it in a comment, or update your question to mention the source of that query. You'll let us know what you find. - avernet
I have updated my question with the findings of using the P6Spy. It seems to be a bug. - George Papageorghiou
Thank you for the investigation. And indeed, I think you put your finger on the issue. So for now you won't be able to use MySQL 8. But I've created issue 3864, and we'll shoot to fix this for 2018.2.1. - avernet
Just a quick note to mention that this issue has been fixed, and the fix will be included in Orbeon Forms PE 2018.2.1. (I'm not sure at this point if a point release will be made for Orbeon Forms CE, as point releases are often only made for Orbeon Forms PE.) - avernet

1 Answers

0
votes

This is a bug, and it is fixed since Orbeon Forms 2018.2.1 PE.