Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0035653
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] Y. DBSourceManagermajorsometimes2017-03-30 14:022017-09-26 15:25
Reportereduardo_ArgalView Statuspublic 
Assigned Toalostale 
PriorityhighResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revisiond0d4150b7e96
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Merge Request Status
Review Assigned ToAugustoMauch
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket47951
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035653: update.database can fail updating src tables with a check constraint where more than one column participates

DescriptionHaving DB check constraint for a datasource table that is computed based on more than one column, update.database can fail when adding changes to that table.

This can occur if for a record more than one column participating in that contraint gets modified and even the whole changes fulfills the constraint, individual ones don't.

In the case described in steps to reproduce section, checking executed DB statements we can see:

UPDATE OBCQL_QUERY_COLUMN SET UPDATED = $1, UPDATEDBY = $2, HAS_LINK = $3 WHERE OBCQL_QUERY_COLUMN_ID 
= $4
parameters: $1 = '2017-09-12 12:38:30.269373', $2 = '0', $3 = 'Y', $4 = '28F920CD36E14AFBB2025802363E8B02'


UPDATE OBCQL_QUERY_COLUMN SET UPDATED = $1, UPDATEDBY = $2, AD_TAB_ID = $3 WHERE OBCQL_QUERY_COLUMN_ID 
= $4
parameters: $1 = '2017-09-12 12:38:30.280567', $2 = '0', $3 = '186', $4 = '28F920CD36E14AFBB2025802363E8B02'


UPDATE OBCQL_QUERY_COLUMN SET UPDATED = $1, UPDATEDBY = $2, CAN_BE_FILTERED = $3 WHERE OBCQL_QUERY_COLUMN_ID 
= $4
parameters: $1 = '2017-09-12 12:38:30.291775', $2 = '0', $3 = 'Y', $4 = '28F920CD36E14AFBB2025802363E8B02'


Note that changes in individual columns are applied independently, so it's possible to leave row in a temporary inconsistent state.
Steps To Reproduce1. In a clean pi apply attached reproduce-issue-35653.diff patch
2. execute ant update.database
  -> ERROR:
     ...
     [java] 27738 INFO - Updating Application Dictionary data...
     [java] org.postgresql.util.PSQLException: ERROR: new row for relation "obcql_query_column" violates check constraint "obcql_column_whereclause_chk"
     [java] Detail: Failing row contains (28F920CD36E14AFBB2025802363E8B02, 0, 0, Y, 2015-08-03 12:05:45.870978, 0, 2017-09-12 12:50:33.690805, 0, CFDF8EE593F04CFE9709F5AD19A3A573, qty, Qty, null, 10, null, 20, 29, null, W, Y, 186, Y, null, null).
Proposed SolutionAll changes affecting a single row should be applied at once. This would:

1. Solve described issue where database cannot be updated because the record is tried to be set to a temporary incorrect state.
2. Improve performance for some updates by reducing the number of statements required to update.
TagsNo tags attached.
Attached Fileslog file icon WidgetCompilationError.log [^] (18,455 bytes) 2017-03-30 14:02
diff file icon reproduce-issue-35653.diff [^] (1,277 bytes) 2017-09-12 12:43 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0036937 closedalostale update.database source data queries current time to DB for each change 
related to defect 0036938 closedalostale dbsm test: src changes not applied 
causes defect 0036984 closedalostale Upgrading from 2.50 to pi some unique constraints are violated 

-  Notes
(0095711)
eduardo_Argal (viewer)
2017-03-30 14:15

 You can use:
https://bitbucket.org/gpscode/but_com.openbravo.retail.reports-customwidgets [^]

Install in rev 1 or 2 (depending on date format)
update to rev 3
(0095887)
alostale (viewer)
2017-04-07 09:02

 I don't have access to that repo. Can you provide access to it or attach a simplified version with the failing code?
(0097169)
eduardo_Argal (viewer)
2017-06-06 16:01

 Access has been granted. You should be able to access now. Sorry for the inconvenience
(0099526)
hgbot (developer)
2017-09-25 11:23

 Repository: erp/devel/dbsm-main
Changeset: ee395fa9372021a33722a4808c9945e108f818e0
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Mon Sep 25 10:46:14 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/ee395fa9372021a33722a4808c9945e108f818e0 [^]

related to bug 35653: update.database can fail in src tables with checks

  Added test case that covers current failing scenario: a src table with a check
  contraint including 2 columns and src updates affecting those 2 columns.

  Current implementation fails in this case because each column is tried to be
  updated individually which leads to an invalid state for the check.

---
M src-test/src/org/openbravo/dbsm/test/DBSMTestSuite.java
A src-test/data/datachanges/v1/TEST.xml
A src-test/data/datachanges/v2/TEST.xml
A src-test/model/constraints/TWO_COLS_CHECK.xml
A src-test/src/org/openbravo/dbsm/test/sourcedata/SourceDataSuite.java
A src-test/src/org/openbravo/dbsm/test/sourcedata/SourcedataUpdates.java
---
(0099528)
hgbot (developer)
2017-09-25 11:23

 Repository: erp/devel/pi
Changeset: 5778a1eb46718a99ff7479e483a108f24a529198
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Mon Sep 25 11:04:14 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/5778a1eb46718a99ff7479e483a108f24a529198 [^]

fixed bug 36937, fixed bug 35653: dbsm fixing those 2 issues

---
M src-db/database/lib/dbsourcemanager.jar
---
(0099529)
hgbot (developer)
2017-09-25 11:23

 Repository: erp/devel/dbsm-main
Changeset: d0d4150b7e96d10312b126671ea0f7e693c61e1b
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Mon Sep 25 10:48:54 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/d0d4150b7e96d10312b126671ea0f7e693c61e1b [^]

fixed bug 35653: update.database can fail in src tables with check contraints

  When updating database with changes in src tables that have check contraints,
  process could fail if those contraints included more than one column of that
  table.

  The problem was caused because changes in each column for the same row were
  applied as individual SQL statements. This caused to be possible to reach an
  inconsistent state.

  Now all data changes for a single row are applied altogether.

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
---
(0099563)
AugustoMauch (administrator)
2017-09-25 15:58

 Code reviewed and verified
(0099589)
hudsonbot (viewer)
2017-09-26 15:25

 A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/8fa643b4437e [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2017-03-30 14:02 eduardo_Argal New Issue
2017-03-30 14:02 eduardo_Argal Assigned To => platform
2017-03-30 14:02 eduardo_Argal File Added: WidgetCompilationError.log
2017-03-30 14:02 eduardo_Argal OBNetwork customer => No
2017-03-30 14:02 eduardo_Argal Modules => Core
2017-03-30 14:02 eduardo_Argal Triggers an Emergency Pack => No
2017-03-30 14:06 shuehner Issue Monitored: shuehner
2017-03-30 14:15 eduardo_Argal Note Added: 0095711
2017-04-07 09:02 alostale Note Added: 0095887
2017-04-07 09:02 alostale Assigned To platform => eduardo_Argal
2017-04-07 09:02 alostale Status new => feedback
2017-06-06 16:01 eduardo_Argal Note Added: 0097169
2017-09-01 13:02 ngarcia Issue Monitored: ngarcia
2017-09-04 11:29 nitasujena OBNetwork customer No => Yes
2017-09-04 11:29 nitasujena Support ticket => 47951
2017-09-04 11:29 nitasujena Resolution time => 1506290400
2017-09-04 11:29 nitasujena Status feedback => new
2017-09-12 10:19 eduardo_Argal Assigned To eduardo_Argal => alostale
2017-09-12 12:43 alostale File Added: reproduce-issue-35653.diff
2017-09-12 12:55 alostale Assigned To alostale => platform
2017-09-12 12:55 alostale Category 00. Application dictionary => Y. DBSourceManager
2017-09-12 12:55 alostale Summary Compilation failing after updating a widgets module version => update.database can fail updating src tables with a check constraint where more than one column participates
2017-09-12 12:55 alostale Description Updated View Revisions
2017-09-12 12:55 alostale Steps to Reproduce Updated View Revisions
2017-09-12 12:55 alostale Proposed Solution updated
2017-09-13 09:51 alostale Status new => scheduled
2017-09-13 09:51 alostale Assigned To platform => alostale
2017-09-25 10:55 alostale Relationship added related to 0036937
2017-09-25 11:16 alostale Relationship added related to 0036938
2017-09-25 11:23 hgbot Checkin
2017-09-25 11:23 hgbot Note Added: 0099526
2017-09-25 11:23 hgbot Checkin
2017-09-25 11:23 hgbot Note Added: 0099528
2017-09-25 11:23 hgbot Status scheduled => resolved
2017-09-25 11:23 hgbot Resolution open => fixed
2017-09-25 11:23 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/5778a1eb46718a99ff7479e483a108f24a529198 [^]
2017-09-25 11:23 hgbot Checkin
2017-09-25 11:23 hgbot Note Added: 0099529
2017-09-25 11:23 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/5778a1eb46718a99ff7479e483a108f24a529198 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/d0d4150b7e96d10312b126671ea0f7e693c61e1b [^]
2017-09-25 12:22 alostale Review Assigned To => AugustoMauch
2017-09-25 15:58 AugustoMauch Note Added: 0099563
2017-09-25 15:58 AugustoMauch Status resolved => closed
2017-09-26 15:25 hudsonbot Checkin
2017-09-26 15:25 hudsonbot Note Added: 0099589
2017-10-02 13:52 inigosanchez Relationship added causes 0036984

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker