0036990: Upgrading from 2.50 to pi some unique constraints are violated

Description

When upgrading from 2.50 to pi, some unique constraints are violating in the new database. See the log of the job[1] that raises the reported problem.

This occurs because an old window (2.5 window: Cost Center) has changes in its ID and a unique constraint depends on this change (unique constraint of ad_window: client_id, name, module_id). When alter the data in update.database task having this scenario, the order the data are loaded into the database are:
- insert: Cost Center - 79FC23AB84F04384B4B7CCCADCDD2942 -> ERROR BECAUSE (client, name, module) is in the database from 2.5
- update: Cost Center - 800030 to Manufacturing Cost Center - 800030 -> Is not executed for the error on insert

Before the changeset that introduces the regression, all the data changes were handled individually and under some lucky circumstances, all the data was updated properly.

[1].- https://ci.openbravo.com/job/up-2.50topi-pgsql/206/consoleText [^]

Steps To Reproduce

Following the steps in the job[1]:
1.- hg clone a fresh pi
2.- hg up 2.50MP24
3.- ant install.source
4.- Execute the following query into the db: DELETE FROM C_BP_CUSTOMER_ACCT WHERE C_BP_CUSTOMER_ACCT_id IN ('1000020', '1000019', '1000024', '1000022', '1000021');
5.- hg update tip
6.- ant smartbuild -Dlocal=no

The following ERROR is raised:
[java] 75270 INFO - Updating Application Dictionary data...
     [java] org.apache.ddlutils.DatabaseOperationException: Error while inserting into the database: ERROR: duplicate key value violates unique constraint "ad_window_name"
     [java] Detail: Key (ad_client_id, name, ad_module_id)=(0, Cost Center, 0) already exists.
     [java] at org.apache.ddlutils.platform.PlatformImplBase.insert(PlatformImplBase.java:1724)
     [java] at org.apache.ddlutils.platform.PlatformImplBase.upsert(PlatformImplBase.java:1618)
     [java] at org.apache.ddlutils.platform.PlatformImplBase.alterData(PlatformImplBase.java:851)
     [java] at org.openbravo.ddlutils.task.AlterDatabaseDataAll.doExecute(AlterDatabaseDataAll.java:228)
     [java] at org.openbravo.ddlutils.task.BaseDatabaseTask.execute(BaseDatabaseTask.java:86)
     [java] at org.openbravo.ddlutils.task.AlterDatabaseJava.main(AlterDatabaseJava.java:57)

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

blocks

defect

0036984

closed

alostale

Upgrading from 2.50 to pi some unique constraints are violated

Notes
(0099711) hgbot (developer) 2017-10-03 08:57	Repository: erp/backports/3.0PR17Q4 Changeset: 007ccf9e4c3dc5e95094658a818e794be17a9faf Author: Asier Lostalé <asier.lostale <at> openbravo.com> Date: Tue Oct 03 08:34:41 2017 +0200 URL: http://code.openbravo.com/erp/backports/3.0PR17Q4/rev/007ccf9e4c3dc5e95094658a818e794be17a9faf [^] fixed bug 36990: 2.50->PR17Q4 upgrade fails If failed becuse of the order source data rows are inserted/deleted/updated. Before 35653 was fixed, rows were sorted by their ID and insertions deltions and updates were performed in that order. Afterwars, rows were sorted in the same way, but insertions and deletions were performed before updates. This casues that, in some weird cases, constraints to be broken while updating making the process to fail. Now it has been fixed by restoring previous order, so that updates are not postponed anymore. --- M src-db/database/lib/dbsourcemanager.jar ---

(0099764) AugustoMauch (administrator) 2017-10-04 15:03	Code reviewed and verified

Issue History
Date Modified	Username	Field	Change
2017-10-03 07:50	alostale	Type	defect => backport
2017-10-03 08:57	hgbot	Checkin
2017-10-03 08:57	hgbot	Note Added: 0099711
2017-10-03 08:57	hgbot	Status	scheduled => resolved
2017-10-03 08:57	hgbot	Resolution	open => fixed
2017-10-03 08:57	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/backports/3.0PR17Q4/rev/007ccf9e4c3dc5e95094658a818e794be17a9faf [^]
2017-10-03 08:57	alostale	Review Assigned To	=> AugustoMauch
2017-10-04 15:02	AugustoMauch	Status	resolved => closed
2017-10-04 15:02	AugustoMauch	Fixed in Version	=> 3.0PR17Q4
2017-10-04 15:03	AugustoMauch	Note Added: 0099764