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

  Written by The Jahia Team
 
Sysadmins
   Estimated reading time:
7.1.2.0 7.2

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 DXM 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 DX install under digital-factory-data/db/sql/schema/mysql/jbpm-schema-triggers-index.sql
    • Stop the DXM 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 DXM 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 ;

 

Related links