Version Migration allows to move your installation to the current distribution version to take advantage of new features, improved performance and stability (refer to other postings for Component/Application migration and Data migration).
The version migration is stable in production since 2003 but has the disadvantage that it is the barer of bad news. The migration does not create issues - it discovers and identifies issues in your current system. In this blog, I will discuss typical problems and your options of solving them.
One important principal: Errors will not go away - they will compound. So fix them ASAP. Procastination will ensure that you'll spend significantly more time fixing them later and might even destabilize the system.
When evaluating the situation, view all of the errors displayed. You very often find that ther are just a few causes creating potentially a long list of errors. Example: if some record fails to be inserted due to a naining constraint, you may also get many other insert errors failing due to parent contstraints.
Column Name Conflicts
Cause: you added a column in a Dictionary table and get something like the following error:
ORA-00001: unique constraint (COMPIEREPRO.AD_COLUMN_NAME) violated
INSERT INTO AD_Column (AD_CLIENT_ID....)
VALUES (0,15851,550,0,19,333,236,'C_Campaign_ID','R',TO_DATE('2006-07-07 17:19:08','YYYY-MM-DD HH24:MI:SS'),0,'Marketing Campaign','D',10,..)
In this case an entity with the same name was created by the user. So, identify that entity and in general either rename it - or delete it in the application. Here, a custom column already exists and you probably do not want to loose the existing data. So here, the solution would be to delete the column in the dictionary - this does not delete the column and information in the database. The migration process will then add the column again, but not change the database as the column already exists.
Note that Compiere column names are case sensitive whereas the column name in the database may not be case sensitive (default in Oracle, other databases have case sensitive column names by default). In this case, you get a slightly different error - adding the duplicate column in Compiere succeeds, but creating it in the database fails (ORA-01430: column being added already exists in table). The sulution is the same.
Foreign Key Conflicts
You may get the error that a Foreign Key cannot be created, because one exists already:
ORA-02275: such a referential constraint already exists in the table - ALTER TABLE C_UOMGroup ADD CONSTRAINT FK1000027_1000440 FOREIGN KEY (C_UOM_ID) REFERENCES C_UOM (C_UOM_ID) -java.sql.SQLSyntaxErrorException: ORA-02275: such a referential constraint already exists in the table
Here the situation is usually resolved automatically by running the migration again. Nevertheless, there is a known bug where you get this error for constraints of components. There is no harm to manually drop the constraint and running migration again.
ORA-02261: such unique or primary key already exists in the table
- ALTER TABLE C_DocBaseType ADD CONSTRAINT C_DocBaseType_Name UNIQUE (DocBaseType) - java.sql.SQLSyntaxErrorException: ORA-02261: such unique or primary key already exists in the table
The situation here ia also usually resolved by running the migration again. Also, you often can drop the constarint or index and re-run the migration to re-create it correctly.
ORA-02429: cannot drop index used for enforcement of unique/primary key - DROP INDEX C_DOCBASETYPE_TYPE - java.sql.SQLException: ORA-02429: cannot drop index used for enforcement of unique/primary key
In rare cases -where it concerns a primary key - you need to drop and re-create the primary key. Create a support request or send me an email, if you need help with this.
Another situation is that the creation of the FK failes, because invalid values:
ORA-02298: cannot validate (COMPIERE.FK101_114) - parent keys not found - ALTER TABLE AD_Column ADD CONSTRAINT FK101_114 FOREIGN KEY (AD_Table_ID) REFERENCES AD_Table (AD_Table_ID) ON DELETE CASCADE - java.sql.SQLIntegrityConstraintViolationException: ORA-02298: cannot validate (COMPIERE.FK101_114) - parent keys not found
Here you need to identift the offending records:
Step 1: Get the detail Details
In this case the constraint and the offending columns are listed. In case you don't know, query the view CP_FK, which gives details on Foreign keys:
SELECT TableName, ColumnName FROM CP_FK WHERE ConstraintName='FK101_114'
This results in: AD_Column, AD_Table_ID - i.e. there are columns with invalid table references. This is obviously serious and we need to fix the data issue and enable the constraint, so that no further damage can be done.
Step 2) Identify offending records
SELECT AD_Column_ID, AD_Table_ID, ColumnName
FROM AD_Column
WHERE AD_Table_ID NOT IN (SELECT AD_Table_ID FROM AD_Table)
The next steps depend on what records were returned here. Here, it is actually OK to delete them (as they are re-created by the migration) - usually you would search for the correct Parent record, maybe even create it and update the parentless records.
You may get a similar error message when migration tries to insert a record:
ORA-02291: integrity constraint (COMPIERE.FK285_7729) violated - parent key not found - INSERT INTO AD_Process_Para (AD_CLIENT_ID,AD_ELEMENT_ID,... UPDATEDBY) VALUES (0,3443,... 100) - java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (COMPIERE.FK285_7729) violated - parent key not found
In most cases, there is no need to fix this, as this error usually is a consequence of a previous error (i.e. fail to insert the parent record). In the case that it is not dependent and after migration again re-appears, the steps to fix the situation is similar:
Step 1: Get the detail Details
SELECT TableName, ColumnName FROM CP_FK WHERE ConstraintName='FK285_7729'
Step 2) Identify and research the missing record
Well, after reading this, you might be a bit sceptical about the migration - but no need. If you fix issues immediately and monitor your system, you rarely need to spend time with this.
The major causes of migration challenges are naming conflicts with customizations. The fix is relative easy - and prevention is easy too - using the entity type prefix.
