javax.persistence.NonUniqueResultException: result returns more than one elements

Cause level : 0 (level 0 is the most precise exception)
javax.persistence.NonUniqueResultException: result returns more than one elements
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:297)
at org.jbpm.process.audit.JPAAuditLogService.findProcessInstance(JPAAuditLogService.java:146)
at org.jahia.services.workflow.jbpm.BaseCommand.convertToWorkflow(BaseCommand.java:310)
at org.jahia.services.workflow.jbpm.command.GetWorkflowCommand.execute(GetWorkflowCommand.java:67)
at org.jahia.services.workflow.jbpm.command.GetWorkflowCommand.execute(GetWorkflowCommand.java:55)
at org.jahia.services.workflow.jbpm.BaseCommand.execute(BaseCommand.java:216)

Cause

This is an issue that was already mentioned when using MySQL with an InnoDB engine.

Solution

Here are steps to solve this issue:

To run this SQL command to temporary solve the issue:

DELETE FROM jbpm_process_instance_log where process_instance_id
IN (SELECT * FROM (SELECT process_instance_id FROM jbpm_process_instance_log
GROUP BY process_instance_id having count(*) > 1) AS duplicates) AND status > 1;

As the issue is solved in DX 7.1.2, run:

SHOW TRIGGERS;

It will display the list of triggers and you should have a trigger named TRIGGER trigger_autoinc_jbpm_process_instance_info"

If this trigger does not exist for some reason, it needs to be recreated. The script should be present in your Jahia install under digital-factory-data/db/sql/schema/mysql/jbpm-schema-triggers-index.sql

Stop the Jahia instance

Execute the following query:

DELETE FROM jbpm_process_instance_log where process_instance_id
IN (SELECT * FROM (SELECT process_instance_id FROM jbpm_process_instance_log
GROUP BY process_instance_id having count(*) > 1) AS duplicates) AND status > 1;

Execute the content of the script jbpm-schema-triggers-index.sql

Start the Jahia instance

If the issue persists after running the above procedure, you can empty all jbpm tables except 2 (jbpm_session_infos and jbpm_organizational_entity) and run again the script jbpm-schema-triggers-index.sql