Compiere provides three types of Migration
- Version Migration - Moving your system from one version/release to another
- Component Migration - Installing and upgrading your Component/Customization
- Data Migration - Moving/updating selections of data from one system to another
In contrast to other ERP/CRM systems, Compiere was designed to be extended/customized and with the principle that there is only one maintained version - the latest. In the beginning people were a bit uncomfortable with the idea that there is no support for historical versions. When they experienced the benefits of the Version Migration (available and stable since 2001), this concern was no longer an issue. To be able to migrate to the current version new concepts were introduced to enable this. The main element is the clear distinction who owns what via the Entity Type - and that in the migration does not touch customizations nor customer data.
The Version Migration is basically a "diff" program which compares the current state of the target system with the to be achieved state of the source system. The migration is now in it's third generation and also maintains database index, constraints and views. The runtime is more dependent on the number of differences between the versions rather than the size of the database.
The main advantage of the migration is that it uncovers potential issues (it does NOT create them). The most useful output is the error log. In most cases, you can start/continue production, even if you have a significant number of issues. Let's look into causes and options:
The primary cause for "migration issues" I encountered are consequences of people updating the database directly with SQL rather using the Compiere tools. The second cause are non-unique user keys (details below). Well, there are also some bugs, but those are handled mostly automatically.
The Component and Data Migration requires unique user keys (unique identifier), so we start enforcing this uniqueness. Let's use a simplified example: Payment Terms are identified by their name. So when I want to move Payment Terms from one system to another, the name is used as criteria for comparison, not the internal ID as in the version migration. If there were no unique index on the payment term name, it would allow the user to create multiple payment terms with the same name. If there were no (tenant) unique index on the name, we would create it now to ensure the functioning of the component and data migration. In this case we actually had the unique index for quite some time (simplified example).
In general, the issues uncovered by the version migration usually do not go away. If you do not fix them now, you will have to fix them in the future. Small issues have the tendency to get bigger.
Here are some examples of typical error messages and your options:
--------------
ORA-01440: column to be modified must be empty to decrease precision or scale
- ALTER TABLE S_ResourceType MODIFY ChargeableQty NUMBER(10) DEFAULT NULL - java.sql.SQLException: ORA-01440: column to be modified must be empty to decrease precision or scale
Cause: Initially the column was created as NUMBER(0) and it has some
Options: Ignore (suggested) or copy the data:
- ALTER TABLE S_ResourceType ADD temp NUMBER(10);
- UPDATE S_ResourceType SET temp=ChargeableQty;
- ALTER TABLE S_ResourceType DROP COLUMN ChargeableQty;
- ALTER TABLE S_ResourceType RENAME COLUMN temp TO ChargeanleQty;
--------------
ORA-01441: cannot decrease column length because some value is too big
- ALTER TABLE M_Product MODIFY Name NVARCHAR2(60) DEFAULT NULL - java.sql.SQLException: ORA-01441: cannot decrease column length because some value is too big
Cause: You increased the length
Options: Ignore or copy (see above)
--------------
ORA-02298: cannot validate (COMPIERE.FK335_5355) - parent keys not found
- ALTER TABLE C_Payment ADD CONSTRAINT FK335_5355 FOREIGN KEY (C_Invoice_ID) REFERENCES C_Invoice (C_Invoice_ID) - java.sql.SQLIntegrityConstraintViolationException: ORA-02298: cannot validate (COMPIERE.FK335_5355) - parent keys not found
Cause: Someone played with sql ... SERIOUS!!
Options: You must fix the data - you may want to ask Compiere consulting to help you:
- Identify the cause:
SELECT * FROM C_InvoiceLine il
WHERE NOT EXISTS (SELECT * FROM C_Invoive i WHERE i.C_Invoice_ID=il.C_Invoice_ID)
- You cannot just delete the recods found (the easiest option), you need to check if they are posted, etc. One option is to re-create the header manually and re-post it.
--------------
ORA-02296: cannot enable (COMPIERE.) - null values found
- ALTER TABLE C_AcctSchema MODIFY M_CostType_ID NOT NULL - java.sql.SQLIntegrityConstraintViolationException: ORA-02296: cannot enable (COMPIERE.) - null values found
Cause: New mandatory column.
Options: Go into the different Tenants and select a value (don't forget GardenWorld) and then enable the constraint
--------------
ORA-00001: unique constraint (COMPIERE.AD_ELEMENT_COLUMNNAME) violated
- INSERT INTO AD_Element ..... - java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (COMPIERE.AD_ELEMENT_COLUMNNAME) violated
Cause: You have duplicate Elements
Options: You should find and fix these type of errors. One popular error is that you have the same column name spelled with a different case (test, Test, TEST).
--------------
ORA-00001: unique constraint (COMPIERE.C_REGION_NAME) violated
- INSERT INTO C_Region ... - java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (COMPIERE.C_REGION_NAME) violated
Cause: Duplicate region names, if you have an older version, this might happen with Austration or Japanese states.
Options: Ignore or fix.
--------------
ORA-00001: unique constraint (COMPIERE.AS_PRINTFORMATITEM_KEY) violated
- INSERT INTO AD_PrintFormatItem ...- java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (COMPIERE.AS_PRINTFORMATITEM_KEY) violated
Cause: Someone played with sql .... (this is a custom table and it was not developed properly)
Options: Fix it!! You may want to ask Compiere consulting to help you
--------------
ORA-02291: integrity constraint (COMPIERE.FK116_233) violated - parent key not found
- INSERT INTO AD_Menu ... - java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (COMPIERE.FK116_233) violated - parent key not found
Cause: Someone played with sql direct ....
Options: Fix it!! You may want to ask Compiere consulting to help you
--------------
ORA-00955: name is already used by an existing object
- CREATE INDEX M_Product_ProductCategory ON M_Product (M_Product_Category_ID) - java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object
Cause: Sequence issue
Options: Usually automatically fixed if you run the migration again - or go into window Table & Column, find the column and press "Synchronize Database".
--------------
NOTE: I will update this and improve the suggestions ... let me know if there are more examples I should cover
