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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0035628
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformmajorsometimes2017-03-28 09:502017-03-31 09:04
ReporteralostaleView Statuspublic 
Assigned Toalostale 
PriorityurgentResolutionfixedFixed in Version3.0PR17Q2
StatusclosedFix in branchFixed in SCM revisionca589943eb25
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseOracleJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tocaristu
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035628: ORA: sometimes update.database after install.source detects db changes

DescriptionUsing Oracle 12 in some occasions, update.database just after install source detects local structural DB changes.

The problem is caused by Oracle's virtual columns [1] which are included within the CRC used to check local changes, as their names might not be consistent, they can cause this situation. (See below for more details)

== Detailed analysis ==

Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 
'diff values' end 
from db_checksums where old_crc = new_crc and obj_type='col' 
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;

type name                        same values
---- --------------------------- -----------
col  AD_MODEL_OBJECT.UPDATEDBY   same values

-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40), 
       case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' 
else 'diff values' end 
from db_checksums where old_crc != new_crc and obj_type='col' 
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')

order by 2;

type name                                  same values
---- ------------------------------------- -----------
col  AD_MODEL_OBJECT_MAPPING.AD_CLIENT_ID  same values

-- objects with different value to calculate crc
select count(1) from db_checksums where CAST(old_value AS VARCHAR2(100)) !=  CAST(old_value AS VARCHAR2(100));


0

-- objects present in one check but not in the other
select substr(obj_type,1,5) as ob_type, substr(obj_name,1,40) as ob_name,
       case when old_value is null then 'null' else 'not null' end as old, 
       case when new_value is null then 'null' else 'not null' end as new
       from db_checksums where old_value is null or new_value is null
       order by 1, 2;

OB_TYPE 	     OB_NAME										      OLD      NEW
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 
-------- --------
col		     AD_MODEL_OBJECT.SYS_STSE58YAH1UUV2MU99DR						      null     not null
col		     AD_MODEL_OBJECT.SYS_STSZ64L#G7R$IW913WO7						      null     not null
col		     AD_ORG.SYS_STSBTFDMLW$XN5GQX0VD5WXNN9						      null     not null
col		     C_TAXCATEGORY.SYS_STST77VG34JNVJ$RBGUB46						      null     not null
col		     C_UOM.SYS_STST77VG34JNVJ$RBGUB46USK1						      null     not null
col		     GL_CATEGORY.SYS_STST77VG34JNVJ$RBGUB46US						      null     not null
col		     M_LOCATOR.SYS_STST77VG34JNVJ$RBGUB46USK1						      null     not null
col		     OBPOS_APP_PAYMENT.SYS_STSV4HY32RGA6QC_6Y						      null     not null
col		     OBUIAPP_PARAMETER.SYS_STSDD52J8SWA3Y97GL						      null     not null

9 rows selected.

-- SYS_% columns
select table_name, column_name,data_default from user_tab_cols where column_name like 'SYS\_%' escape 
'\' order by 1,2

TABLE_NAME															 COLUMN_NAME										  DATA_DEFAULT
-------------------------------------------------------------------------------------------------------------------------------- 
-------------------------------------------------------------------------------------------------------------------------------- 
--------------------------------------------------------------------------------
AD_MODEL_OBJECT 														 SYS_STSE58YAH1UUV2MU99DR0UZYX# 							  SYS_OP_COMBINED_HASH("ISACTIVE","OBJECT_TYPE")

AD_MODEL_OBJECT 														 SYS_STSZ64L#G7R$IW913WO7OO2#VE 							  SYS_OP_COMBINED_HASH("ISACTIVE","ACTION","ISDEFAULT","AD_PROCESS_ID")

AD_ORG																 SYS_STSBTFDMLW$XN5GQX0VD5WXNN9 							  SYS_OP_COMBINED_HASH("AD_ORG_ID","AD_CLIENT_ID","ISACTIVE")

C_BPARTNER															 SYS_NC00097$										  UPPER("NAME")
C_BPARTNER															 SYS_NC00098$										  UPPER("REFERENCENO")
C_BPARTNER															 SYS_NC00099$										  UPPER("VALUE")
C_ORDER 															 SYS_NC00102$										  UPPER("DOCUMENTNO")
C_TAXCATEGORY															 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")

C_UOM																 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")

FIN_PAYMENT															 SYS_NC00043$										  "RET_HIGVOL_ORACLE"."OBEQUALS"("GENERATED_CREDIT","USED_CREDIT")

GL_CATEGORY															 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")


TABLE_NAME															 COLUMN_NAME										  DATA_DEFAULT
-------------------------------------------------------------------------------------------------------------------------------- 
-------------------------------------------------------------------------------------------------------------------------------- 
--------------------------------------------------------------------------------
M_INOUT 															 SYS_NC00066$										  UPPER("DOCUMENTNO")
M_LOCATOR															 SYS_STST77VG34JNVJ$RBGUB46USK1 							  SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")

M_PRODUCT															 SYS_NC00110$										  UPPER("NAME")
M_PRODUCT															 SYS_NC00111$										  UPPER("UPC")
M_PRODUCT															 SYS_NC00112$										  UPPER("VALUE")
OBPOS_APP_PAYMENT														 SYS_STSV4HY32RGA6QC_6Y4KE76DMR 							  SYS_OP_COMBINED_HASH("VALUE","FIN_FINANCIAL_ACCOUNT_ID","NAME")

OBUIAPP_PARAMETER														 SYS_STSDD52J8SWA3Y97GLJ1AA8GM5 							  SYS_OP_COMBINED_HASH("AD_REFERENCE_ID","AD_REFERENCE_VALUE_ID","OBUIAPP_PROCESS_


18 rows selected.



[1] https://blogs.oracle.com/sql/entry/ora_54033_and_the_hidden [^]
Steps To ReproduceNot easy to reproduce locally, consistently occurs in [1] ie [2]:

1. Using Oracle: install.source
2. update database
  -> [java] Checking if database structure was modified locally.
     [java] Database has local changes. Update.database not done.
     [java] at org.openbravo.ddlutils.task.AlterDatabaseDataAll.doExecute(AlterDatabaseDataAll.java:163)

[1] https://ci.openbravo.com/view/retail/job/ret-higvol-oracle [^]
[2] https://ci.openbravo.com/view/retail/job/ret-higvol-oracle/684/console [^]
Proposed SolutionExclude virtual columns from CRC.

This can be done using the hidden_column property form user_tab_cols


select count(*) from user_tab_cols where column_name like 'SYS\_%' escape '\';

  COUNT(*)
----------
    18

select count(*) from user_tab_cols where hidden_column='YES';

  COUNT(*)
----------
    18



Note this will not cause false positives (other than the current ones) because the fix will change Ora pre script to modify ad_db_modified function not to include these columns. But DB modifications is checked fore applying pre script. So it will be compared once using old code, CRC will be updated with new code and future comparisons will occur with new code.
TagsNo tags attached.
Attached Filesdiff file icon db_modified-issue.diff [^] (6,362 bytes) 2017-03-28 09:51 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]
related to defect 0034115 closedplatform ad_db_modified does not take into account check constraints 
related to feature request 0035629 acknowledgedplatform add per DB object info to check for model DB changes 

-  Notes
(0095599)
hgbot (developer)
2017-03-28 11:09

Repository: erp/devel/pi
Changeset: ca589943eb25c8b1a6eb8a0aaf10c13463970632
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue Mar 28 11:09:21 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/ca589943eb25c8b1a6eb8a0aaf10c13463970632 [^]

fixed bug 35628: ORA: update.database after install.source detects db changes

  This problem occured sometimes, it was caused due to some hidden virtual columns
  appearing after install.source before updating database and they were included
  in the CRC used to check local changes.

  Fixed by removing hidden column from CRC.

---
M src-db/database/model/prescript-Oracle.sql
---
(0095611)
hudsonbot (developer)
2017-03-28 16:57

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/b8f24f04c24b [^]
Maturity status: Test
(0095627)
Sandrahuguet (developer)
2017-03-29 09:27

Issue fixed in http://ci.openbravo.com/view/retail/job/ret-higvol-oracle/ [^]

1. Install.source:
http://ci.openbravo.com/view/retail/job/ret-higvol-oracle/688/ [^]

2. Update.database:
http://ci.openbravo.com/view/retail/job/ret-higvol-oracle/689/ [^]
http://ci.openbravo.com/view/retail/job/ret-higvol-oracle/690/ [^]
(0095744)
caristu (developer)
2017-03-31 09:04

Code reviewed

- Issue History
Date Modified Username Field Change
2017-03-28 09:50 alostale New Issue
2017-03-28 09:50 alostale Assigned To => alostale
2017-03-28 09:50 alostale Modules => Core
2017-03-28 09:50 alostale Triggers an Emergency Pack => No
2017-03-28 09:51 alostale File Added: db_modified-issue.diff
2017-03-28 09:52 alostale Description Updated View Revisions
2017-03-28 09:53 alostale Description Updated View Revisions
2017-03-28 09:56 alostale Description Updated View Revisions
2017-03-28 09:57 alostale Description Updated View Revisions
2017-03-28 10:00 alostale Description Updated View Revisions
2017-03-28 10:02 alostale Description Updated View Revisions
2017-03-28 10:03 alostale Proposed Solution updated
2017-03-28 10:11 alostale Proposed Solution updated
2017-03-28 10:18 alostale Proposed Solution updated
2017-03-28 10:19 alostale Relationship added related to 0034115
2017-03-28 10:24 alostale Relationship added related to 0035629
2017-03-28 10:30 alostale Status new => acknowledged
2017-03-28 10:42 alostale Proposed Solution updated
2017-03-28 10:51 alostale Proposed Solution updated
2017-03-28 10:58 alostale Review Assigned To => caristu
2017-03-28 11:09 hgbot Checkin
2017-03-28 11:09 hgbot Note Added: 0095599
2017-03-28 11:09 hgbot Status acknowledged => resolved
2017-03-28 11:09 hgbot Resolution open => fixed
2017-03-28 11:09 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/ca589943eb25c8b1a6eb8a0aaf10c13463970632 [^]
2017-03-28 16:57 hudsonbot Checkin
2017-03-28 16:57 hudsonbot Note Added: 0095611
2017-03-29 09:27 Sandrahuguet Note Added: 0095627
2017-03-31 09:04 caristu Note Added: 0095744
2017-03-31 09:04 caristu Status resolved => closed
2017-03-31 09:04 caristu Fixed in Version => 3.0PR17Q2


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker