[MNT-7445] Migration of alfresco database mysql to postgresql Created: 25-Mar-11  Updated: 19-Mar-13  Resolved: 25-Mar-11

Status: Closed
Project: Service Packs and Hot Fixes
Component/s: Repository
Affects Version/s: 3.1.1
Fix Version/s: 3.4

Type: Information
Reporter: Jose Portillo (Inactive) Assignee: Closed Bugs
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

RHEL, JBOSS, PostgreSQL


Issue Links:
Related
is related to by MNT-7465 Customer cannot upgrade 3.1.2 to 3.4 Closed
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



 Comments   
Comment by Derek Hulley [X] (Inactive) [ 25-Mar-11 ]

It is quite common during data migrations for the sequences to be forgotten: Until V3.4, there was a shared sequence called 'hibernate_sequence' that was used for generating IDs for all tables. Of course MySQL didn't and doesn't need it, but PostgreSQL does. It is therefore necessary to pre-populate the 'hibernate_sequence' with a value that is greater than any ID throughout the database. This is improved in 3.4, where each table gets its own sequence with the exception of the JBPM tables, which still use Hibernate and therefore still use the 'hibernate_sequence' - this is done automatically by the upgrade.

The good news is that, apart from annoyance and time wasting, the data will just not insert if the sequence is wrong and will continue retrying the transactions until the sequence gets to a value that doesn't conflict. I believe that this explains your behaviour.

Generated at Tue Aug 11 23:47:27 BST 2020 using Jira 7.13.15#713015-sha1:7c5ddd2c3e1709974ae9c48c17df8edd3919fe2c.