Uploaded image for project: 'Red Hat Process Automation Manager'
  1. Red Hat Process Automation Manager
  2. RHPAM-308

Amend AUTO_INCREMENT on MySQL/MariaDB restart

    Details

    • Type: Enhancement
    • Status: Verified (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 6.x.x
    • Fix Version/s: 7.0.0.GA
    • Component/s: jBPM Core
    • Labels:
    • Environment:

      MySQL 5.x or MariaDB 10.1

    • Target Release:
    • Fix Build:
      ER4

      Description

      By default, jBPM generate ID using "AUTO_INCREMENT" in MySQL/MariaDB. But it has a problem that AUTO_INCREMENT value could be reset when MySQL/MariaDB is restarted. The issue is described in https://access.redhat.com/solutions/3222791 and https://issues.jboss.org/browse/JBPM-5226

      I filed RHBPMS-5038 for ORM based configuration solution.

      I raised this JIRA for another approach — Amend AUTO_INCREMENT using procedure.

      See attached jbpm-amend-auto-increment-procedure.sql.

      DELIMITER //
      CREATE PROCEDURE JbpmAmendAutoIncrement()
      BEGIN
            SET @max1 = (SELECT MAX(processInstanceId) + 1 FROM ProcessInstanceLog); 
            set @alter_statement1 = concat('ALTER TABLE ProcessInstanceInfo AUTO_INCREMENT = ', @max1);
            PREPARE stmt1 FROM @alter_statement1;
            EXECUTE stmt1;
            DEALLOCATE PREPARE stmt1;
      
            SET @max2 = (SELECT MAX(workItemId) + 1 FROM NodeInstanceLog); 
            set @alter_statement2 = concat('ALTER TABLE WorkItemInfo AUTO_INCREMENT = ', @max2);
            PREPARE stmt2 FROM @alter_statement2;
            EXECUTE stmt2;
            DEALLOCATE PREPARE stmt2;
      
            SET @max3 = (SELECT MAX(processSessionId) + 1 FROM AuditTaskImpl); 
            set @alter_statement3 = concat('ALTER TABLE SessionInfo AUTO_INCREMENT = ', @max3);
            PREPARE stmt3 FROM @alter_statement3;
            EXECUTE stmt3;
            DEALLOCATE PREPARE stmt3;
      
            SET @max4 = (SELECT MAX(taskId) + 1 FROM AuditTaskImpl); 
            set @alter_statement4 = concat('ALTER TABLE Task AUTO_INCREMENT = ', @max4);
            PREPARE stmt4 FROM @alter_statement4;
            EXECUTE stmt4;
            DEALLOCATE PREPARE stmt4;
      END
      //
      DELIMITER ;
      

      It creates a procedure "JbpmAmendAutoIncrement" which sets AUTO_INCREMENT values greater than IDs which already exist in history log tables.

      If you call the procedure on MySQL/MariaDB restart, those values would be amended.

      for example)

      /etc/my.cnf

      [mysqld]
      ...
      init-file=/path/to/jbpm-amend-auto-increment-call.sql
      ...
      

      jbpm-amend-auto-increment-call.sql

      call bpms640db.JbpmAmendAutoIncrement;
      

      I'm not yet sure if it should be included in products or just documented.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  swiderski.maciej Maciej Swiderski
                  Reporter:
                  tkobayashi Toshiya Kobayashi
                  Tester:
                  Marian Macik
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: