Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0015862Openbravo ERPY. DBSourceManagerpublic2011-02-03 16:452011-05-24 10:56
Reportergorkaion 
Assigned Tomarvintm 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS20OS VersionGentoo
Product Versionpi 
Target Version3.0RC4Fixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0015862: Foreign keys to self table with delete on cascade constraint deletes valid records on update.database
DescriptionThe update.database validates that parent records on foreign keys with delete on cascade constraint exist. And in case that the foreign key refers to a record that doesn't exist it is deleted.

If the foreign key refers to the same table this validation is not properly done and all records with the foreign key filled are deleted.
Steps To ReproduceThis case exists on the obkmo_widget_instance table where the copied_from column is a foreign key to the same table.

1- Check the number of records on this table where the copied_from column is not null.
2- Execute the update.database task.
3- Before login in to the application again check that only records with copied_from null remain.
Proposed SolutionThe update.database generates the following delete statement

DELETE FROM OBKMO_WIDGET_INSTANCE WHERE NOT EXISTS (SELECT COPIED_FROM FROM OBKMO_WIDGET_INSTANCE WHERE OBKMO_WIDGET_INSTANCE.COPIED_FROM=OBKMO_WIDGET_INSTANCE.OBKMO_WIDGET_INSTANCE_ID) AND OBKMO_WIDGET_INSTANCE.COPIED_FROM IS NOT NULL

This statement should use an alias on one of the tables.
Additional Information
TagsclosingMay2011
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2011-02-03 16:45gorkaionNew Issue
2011-02-03 16:45gorkaionAssigned To => marvintm
2011-02-03 16:45gorkaionModules => Core
2011-02-03 19:36marvintmNote Added: 0034046
2011-02-03 19:36hgbotCheckin
2011-02-03 19:36hgbotNote Added: 0034047
2011-02-03 19:36hgbotStatusnew => resolved
2011-02-03 19:36hgbotResolutionopen => fixed
2011-02-03 19:36hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/dbsm-main/rev/04cfdf3a3bd15922feadfa8570c4f1e0b3a59b6f [^]
2011-02-03 19:38hgbotCheckin
2011-02-03 19:38hgbotNote Added: 0034048
2011-02-03 19:38hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/04cfdf3a3bd15922feadfa8570c4f1e0b3a59b6f [^] => http://code.openbravo.com/erp/devel/pi/rev/0ad082cba10156f9e881e96048caaf715945adab [^]
2011-02-04 14:57gorkaionNote Added: 0034059
2011-05-24 10:08dalsasuaTag Attached: closingMay2011
2011-05-24 10:56dalsasuaStatusresolved => closed

Notes
(0034046)
marvintm   
2011-02-03 19:36   
This problem only happens on Oracle, because dbsourcemanager uses different syntax for both databases.
(0034047)
hgbot   
2011-02-03 19:36   
Repository: erp/devel/dbsm-main
Changeset: 04cfdf3a3bd15922feadfa8570c4f1e0b3a59b6f
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Thu Feb 03 19:36:30 2011 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/04cfdf3a3bd15922feadfa8570c4f1e0b3a59b6f [^]

Fixed issue 15862. Use an alias when removing invalid rows

---
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/openbravo/ddlutils/task/ExportDatabase.java
---
(0034048)
hgbot   
2011-02-03 19:38   
Repository: erp/devel/pi
Changeset: 0ad082cba10156f9e881e96048caaf715945adab
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Thu Feb 03 19:37:54 2011 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/0ad082cba10156f9e881e96048caaf715945adab [^]

Fixed issue 15862. Use an alias when removing invalid rows

---
M src-db/database/lib/dbsourcemanager.jar
---
(0034059)
gorkaion   
2011-02-04 14:57   
retested on oracle. working fine.