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

Question

The end of the publication process fails with this exception:

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:

1. 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; 

2. 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"
3. 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 
4. 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

Note that if you get an error when executing the script jbpm-schema-triggers-index.sql then you should change the delimiter for the trigger creation according to https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html. So here could be a way to execute the script:

drop trigger if exists trigger_autoinc_jbpm_process_instance_info;

delimiter //

CREATE TRIGGER trigger_autoinc_jbpm_process_instance_info 
BEFORE INSERT ON jbpm_process_instance_info FOR EACH ROW 
BEGIN 
    DECLARE auto_incr1 BIGINT;
    DECLARE auto_incr2 BIGINT;
    SELECT AUTO_INCREMENT INTO auto_incr1 
        FROM information_schema.TABLES WHERE table_schema=DATABASE() 
        AND table_name='jbpm_process_instance_info';
    SELECT AUTO_INCREMENT INTO auto_incr2 
        FROM information_schema.TABLES 
        WHERE table_schema=DATABASE() 
        AND table_name='jbpm_process_instance_log';  
    IF (auto_incr2 > auto_incr1 and NEW.instance_id<auto_incr2) 
        THEN SET NEW.instance_id = auto_incr2; 
    END IF;
END; ; ; //

delimiter ;