Uploaded image for project: 'Service Packs and Hot Fixes'
  1. Service Packs and Hot Fixes
  2. MNT-7445

Migration of alfresco database mysql to postgresql

    Details

    • Type: Information
    • Status: Closed (View Workflow)
    • Resolution: Fixed
    • Affects Version/s: 3.1.1
    • Fix Version/s: 3.4
    • Component/s: Repository
    • Labels:
      None
    • Environment:
      RHEL, JBOSS, PostgreSQL
    • Bug Priority:
      Category 2
    • ACT Numbers:

      15024-28288

      Description

      The customer recently needed to migrate their alfresco database from MySQL to PostgreSQL, they used the tool EnterpriseDB - MySQL to PostgreSQL Migration Wizard to accomplish their goal, but ran into a series of database errors when trying to start Alfresco after the migration was done. The error was

      Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into JBPM_NODE (NAME_, DESCRIPTION_, PROCESSDEFINITION_, ISASYNC_, ISASYNCEXCL_, ACTION_, SUPERSTATE_, CLASS_, ID_) values (Document Submitted, NULL, 1437, 0, 0, NULL, NULL, 'R', 1438) was aborted. Call getNextException to see the cause.
      at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2545)
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1344)

      They have solved their issue and below is their solution:

      "We have success! The process we used:

      1. Use the EnterpriseDB migration tool to initially get data into PostgreSQL.
      2. Convert all tables, columns, constraints, indexes in this database to lowercase.
      3. Have alfresco create a new default database in PostgreSQL.
      4. Remove any entries from the new database.
      5. Make a dump of the migrated database and import (just the data) into the new database.
      6. Restart several times to get passed the JDBC errors. (not sure why this works, but had to restart like 30-40 times)

      I would like to add a few more notes on number 6. The JDBC errors I was getting were these:

      Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into JBPM_NODE (NAME_, DESCRIPTION_, PROCESSDEFINITION_, ISASYNC_, ISASYNCEXCL_, ACTION_, SUPERSTATE_, CLASS_, ID_) values (Document Submitted, NULL, 1437, 0, 0, NULL, NULL, 'R', 1438) was aborted. Call getNextException to see the cause.
      at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2545)
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1344)

      Id like to pay particular attention to the ID_ column. Upon each restart, the ID_ would increase by about 25-80. It did this until it was getting close to the highest ID_ number we have in the database (1643). I assume that it stopped after it reached an ID_ greater than this. I tried doing a full reindex, but the error still persisted. Any thoughts as to why this error occurred?"

      Any information on this would be helpful.

      thank you

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                closedbugs Closed Bugs
                Reporter:
                j.portillo Jose Portillo
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: