Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0035628Openbravo ERPA. Platformpublic2017-03-28 09:502017-03-31 09:04
alostale 
alostale 
urgentmajorsometimes
closedfixed 
5
 
3.0PR17Q2 
caristu
Core
No
0035628: ORA: sometimes update.database after install.source detects db changes
Using 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 [^]
Not 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 [^]
Exclude 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.
No tags attached.
related to defect 0034115 closed platform ad_db_modified does not take into account check constraints 
related to feature request 0035629 acknowledged platform add per DB object info to check for model DB changes 
diff db_modified-issue.diff (6,362) 2017-03-28 09:51
https://issues.openbravo.com/file_download.php?file_id=10610&type=bug
Issue History
2017-03-28 09:50alostaleNew Issue
2017-03-28 09:50alostaleAssigned To => alostale
2017-03-28 09:50alostaleModules => Core
2017-03-28 09:50alostaleTriggers an Emergency Pack => No
2017-03-28 09:51alostaleFile Added: db_modified-issue.diff
2017-03-28 09:52alostaleDescription Updatedbug_revision_view_page.php?rev_id=14895#r14895
2017-03-28 09:53alostaleDescription Updatedbug_revision_view_page.php?rev_id=14896#r14896
2017-03-28 09:56alostaleDescription Updatedbug_revision_view_page.php?rev_id=14897#r14897
2017-03-28 09:57alostaleDescription Updatedbug_revision_view_page.php?rev_id=14898#r14898
2017-03-28 10:00alostaleDescription Updatedbug_revision_view_page.php?rev_id=14899#r14899
2017-03-28 10:02alostaleDescription Updatedbug_revision_view_page.php?rev_id=14900#r14900
2017-03-28 10:03alostaleProposed Solution updated
2017-03-28 10:11alostaleProposed Solution updated
2017-03-28 10:18alostaleProposed Solution updated
2017-03-28 10:19alostaleRelationship addedrelated to 0034115
2017-03-28 10:24alostaleRelationship addedrelated to 0035629
2017-03-28 10:30alostaleStatusnew => acknowledged
2017-03-28 10:42alostaleProposed Solution updated
2017-03-28 10:51alostaleProposed Solution updated
2017-03-28 10:58alostaleReview Assigned To => caristu
2017-03-28 11:09hgbotCheckin
2017-03-28 11:09hgbotNote Added: 0095599
2017-03-28 11:09hgbotStatusacknowledged => resolved
2017-03-28 11:09hgbotResolutionopen => fixed
2017-03-28 11:09hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/ca589943eb25c8b1a6eb8a0aaf10c13463970632 [^]
2017-03-28 16:57hudsonbotCheckin
2017-03-28 16:57hudsonbotNote Added: 0095611
2017-03-29 09:27SandrahuguetNote Added: 0095627
2017-03-31 09:04caristuNote Added: 0095744
2017-03-31 09:04caristuStatusresolved => closed
2017-03-31 09:04caristuFixed in Version => 3.0PR17Q2

Notes
(0095599)
hgbot   
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   
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   
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   
2017-03-31 09:04   
Code reviewed