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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0026487
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] Y. DBSourceManagermajoralways2014-05-08 06:202014-06-10 22:24
ReportersjkumarView Statuspublic 
Assigned Toalostale 
PrioritynormalResolutionfixedFixed in Version3.0PR14Q3
StatusclosedFix in branchpiFixed in SCM revision7116ef0da09d
ProjectionnoneETAnoneTarget Version3.0PR14Q3
OSAnyDatabaseOracleJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0026487: update.database rebuilds unchanged tables (Oracle, charset UTF8)

DescriptionLogging this issue as per communication with Ivan Perdomo.

Ant smartbuild building tables that has not been changed too.

I installed some packages today and did an ant smartbuild
-Dlocal=no
- Dforce=true and noticed that is was needlessly rebuilding tables
because they had not changed. On investigating the issue I noticed
it looks like it is caused by something we noticed during training.
Openbravo seems to be using the DATA_LENGTH column from
user_tab_columns to determine column size instead of CHAR_LENGTH.
The result of this is that it things any table with NVARCHAR2 or
NCHAR data types has changed and rebuilds the table.

The situation seems worse for NCHAR because that data type pads the
value with spaces. Luckily there are only 2 tables that have NCHAR
data type (C_UOM and C_UOM_TRL),

So, for example, when the build comes to c_uom_trl which looks like
this
> >>
(C_UOM_TRL_ID NOT NULL VARCHAR2(32)
C_UOM_ID NOT NULL VARCHAR2(32)
AD_LANGUAGE NOT NULL VARCHAR2(6)
AD_CLIENT_ID NOT NULL VARCHAR2(32)
AD_ORG_ID NOT NULL VARCHAR2(32)
ISACTIVE NOT NULL CHAR(1)
CREATED NOT NULL DATE
CREATEDBY NOT NULL VARCHAR2(32)
UPDATED NOT NULL DATE
UPDATEDBY NOT NULL VARCHAR2(32)
UOMSYMBOL NCHAR(3)
NAME NOT NULL NVARCHAR2(60)
DESCRIPTION NVARCHAR2(255)
ISTRANSLATED NOT NULL CHAR(1)
)

It decides that columns UOMSYMBOL,NAME,DESCRIPTION are the wrong
size
- it the case of UOMSYMBOL it thinks it is currently NCHAR9 instead
of NCHAR3. So the build creates a temporary table like this and
inserts data into it.

 (C_UOM_TRL_ID NOT NULL VARCHAR2(32)
C_UOM_ID NOT NULL VARCHAR2(32)
AD_LANGUAGE NOT NULL VARCHAR2(6)
AD_CLIENT_ID NOT NULL VARCHAR2(32)
AD_ORG_ID NOT NULL VARCHAR2(32)
ISACTIVE NOT NULL CHAR(1)
CREATED NOT NULL DATE
CREATEDBY NOT NULL VARCHAR2(32)
UPDATED NOT NULL DATE
UPDATEDBY NOT NULL VARCHAR2(32)
UOMSYMBOL *NCHAR(9)*
NAME NOT NULL *NVARCHAR2(180)*
DESCRIPTION *NVARCHAR2(765)*
ISTRANSLATED NOT NULL CHAR(1)
)

Next the build drops table C_UOM and recreates it exactly the same
shape as it was before, then it tries to insert the data from the
temp table back into the original table. The NVARCHAR columns are
OK because number of bytes will still fit into the original table.
But the NCHAR column will cause and error because the value has
been padded to 9 characters, which is too large to fit in a column
for 3 characters. So the build fails.

I'm not sure if there is some setting somewhere which will handle
this case. I assume there must be otherwise no one would have a
clean build when they run smartbuild with local and force options

Steps To ReproducePlease see the description.
TagsNo tags attached.
Attached Fileslog file icon update.log [^] (1,165,458 bytes) 2014-05-10 20:05
png file icon CharsetScreenShot.png [^] (57,817 bytes) 2014-05-23 21:03


log file icon update Issue 0026487.log [^] (3,605,244 bytes) 2014-05-23 21:03
diff file icon test-ora-utf8.diff [^] (1,685 bytes) 2014-05-28 13:29 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0067077)
sjkumar (viewer)
2014-05-10 20:05

 See the log (update.log) of the below command (as suggested by Ivan) -

ant update.database -Dforce=yes -Dbbdd.verbosity=DEBUG | tee update.log
(0067417)
sjkumar (viewer)
2014-05-23 21:02

 Hello - Please see the screenshot for the database setup with this character set where you then can reproduce this issue. The file name is CharsetScreenShot.png

The other file (name - update Issue 0026487.log) is the log from the command ant smartbuild -Dlocal=no -Dforce=Y with debug.
(0067481)
alostale (viewer)
2014-05-27 16:31
edited on: 2014-05-27 16:32

 It's failing when NLS_CHARACTERSET is UTF8 (default is AL32UTF8 and works fine)

In this case in log appears

...
     [java] 28545 DEBUG - Processing Column UOMSYMBOL of table C_UOM (changed because of the size)
     [java] 28545 DEBUG - Processing Column NAME of table C_UOM (changed because of the size)
     [java] 28545 DEBUG - Processing Column DESCRIPTION of table C_UOM (changed because of the size)
...
     [java] CREATE TABLE C_UOM_
     [java] (
...
     [java] UOMSYMBOL NCHAR(9),
     [java] NAME NVARCHAR2(180) NOT NULL,
     [java] DESCRIPTION NVARCHAR2(765),
...
     [java] )

...
     [java] CREATE TABLE C_UOM
     [java] (
...
     [java] UOMSYMBOL NCHAR(3),
     [java] NAME NVARCHAR2(60) NOT NULL,
     [java] DESCRIPTION NVARCHAR2(255),
...
     [java] )
...
     [java] org.apache.ddlutils.DatabaseOperationException: Error while executing a critical SQL to recreate a database table: -- END
C_UOM_ID,AD_CLIENT_ID,AD_ORG_ID,ISACTIVE,CREATED,UPDATED,CREATEDBY,UPDATEDBY,X12DE355,UOMSYMBOL,NAME,DESCRIPTION,STDPRECISION,COSTINGPRECISION,ISDEFAULT,BREAKDOWN,UOM_TYPE FROM C_UOM_.
     [java] You should recover a backup of the database if possible. If it's not, take into account that there is an auxiliary table which still contains the original data, which can be recovered from it. If a update.database or smartbuild is done, this auxiliary table will be deleted, and all its data will be lost forever. For more information, visit the page: http://wiki.openbravo.com/wiki/Update_Tips [^]
     [java] at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:370)
     [java] at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:319)
     [java] at org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:857)
     [java] at org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:834)
     [java] at org.openbravo.ddlutils.task.AlterDatabaseDataAll.doExecute(AlterDatabaseDataAll.java:156)
     [java] at org.openbravo.ddlutils.task.BaseDatabaseTask.execute(BaseDatabaseTask.java:86)
     [java] at org.openbravo.ddlutils.task.AlterDatabaseJava.main(AlterDatabaseJava.java:38)
     [java] Caused by: java.sql.SQLException: ORA-12899: value too large for column "TAD"."C_UOM"."UOMSYMBOL" (actual: 9, maximum: 3)
(0067530)
hgbot (developer)
2014-05-28 13:13

 Repository: erp/devel/pi
Changeset: fc1e0b6f0a4b8d06b8aed86d37a1fcaca1346ccf
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed May 28 13:12:59 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/fc1e0b6f0a4b8d06b8aed86d37a1fcaca1346ccf [^]

fixed bug 26487: update.database rebuilds unchanged tables (Oracle, UTF8)

---
M src-db/database/lib/dbsourcemanager.jar
---
(0067531)
hgbot (developer)
2014-05-28 13:13

 Repository: erp/devel/dbsm-main
Changeset: 7116ef0da09d597de00749cc3976323452275f15
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed May 28 13:13:23 2014 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/7116ef0da09d597de00749cc3976323452275f15 [^]

fixed bug 26487: update.database rebuilds unchanged tables (Oracle, UTF8)

---
M src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java
---
(0067533)
alostale (viewer)
2014-05-28 13:26

 Executed test cases, both in a DB with NLS_CHARACTERSET being UTF8 and in another one being AL32UTF8.

* case 1: update.database without XML changes does not recreate any table
   -execute twice ant update.database -Dforce=yes -Dbbdd.verbosity=DEBUG and check 2nd execution does not include any table recreation

* case 2: xml changes are correctly updated in db
   -apply attached test-ora-utf8.diff which does modifications in some columns in C_OUM table
   -execute update.database
   -check C_OUM table definition in Oracle, it should be like this:
  

                 original after xml update
C_UOM_ID VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
AD_CLIENT_ID VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
AD_ORG_ID VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
ISACTIVE CHAR(1 BYTE) CHAR(1 BYTE)
CREATED DATE DATE
UPDATED DATE DATE
CREATEDBY VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
UPDATEDBY VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
X12DE355 CHAR(2 BYTE) CHAR(3 BYTE)
UOMSYMBOL NCHAR(3 CHAR) NCHAR(4 CHAR)
NAME NVARCHAR2(60 CHAR) NVARCHAR2(65 CHAR)
DESCRIPTION NVARCHAR2(255 CHAR) NVARCHAR2(255 CHAR)
STDPRECISION NUMBER(10,0) NUMBER(10,0)
COSTINGPRECISION NUMBER(10,0) NUMBER(10,0)
ISDEFAULT CHAR(1 BYTE) CHAR(1 BYTE)
BREAKDOWN CHAR(1 BYTE) CHAR(1 BYTE)
UOM_TYPE VARCHAR2(60 BYTE) VARCHAR2(65 BYTE)

* case 3: changed in DB are reverted when updating from xml
  - modify C_OUM_TRL table in DB to set the orig values listed below
  - execute update.database
  - check values are reverted to match xml definition

                orig changed in db after update (revert)
C_UOM_TRL_ID VARCHAR2(32 BYTE) VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
C_UOM_ID VARCHAR2(32 BYTE) VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
AD_LANGUAGE VARCHAR2(6 BYTE) VARCHAR2(6 BYTE) VARCHAR2(6 BYTE)
AD_CLIENT_ID VARCHAR2(32 BYTE) VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
AD_ORG_ID VARCHAR2(32 BYTE) VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
ISACTIVE CHAR(1 BYTE) CHAR(1 BYTE) CHAR(1 BYTE)
CREATED DATE DATE DATE
CREATEDBY VARCHAR2(32 BYTE) VARCHAR2(320 BYTE) VARCHAR2(32 BYTE)
UPDATED DATE DATE DATE
UPDATEDBY VARCHAR2(32 BYTE) VARCHAR2(32 BYTE) VARCHAR2(32 BYTE)
UOMSYMBOL NCHAR(3 CHAR) NCHAR(2 CHAR) NCHAR(3 CHAR)
NAME NVARCHAR2(60 CHAR) NVARCHAR2(55 CHAR) NVARCHAR2(60 CHAR)
DESCRIPTION NVARCHAR2(255 CHAR) NVARCHAR2(255 CHAR) NVARCHAR2(255 CHAR)
ISTRANSLATED CHAR(1 BYTE) CHAR(1 BYTE) CHAR(1 BYTE)

* case 4: DB changes are correctly exported
  -set core in development
  -revert changes applied by test-ora-utf8.diff
  -export.database
  -check changes in xml are equivalent to test-ora-utf8.diff
(0067583)
hudsonbot (viewer)
2014-05-29 23:01

 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/bd7f8b6f935a [^]
Maturity status: Test
(0067815)
hgbot (developer)
2014-06-09 08:30

 Repository: erp/devel/pi
Changeset: 9677c5de7bf87d65f6869458fa1af691cbf0b415
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Mon Jun 09 08:29:28 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/9677c5de7bf87d65f6869458fa1af691cbf0b415 [^]

related to bug 26487: lowered message level from info to debug

---
M src-db/database/lib/dbsourcemanager.jar
---
(0067820)
hgbot (developer)
2014-06-09 10:48

 Repository: erp/devel/dbsm-main
Changeset: 6c169adfa383188aa6f4e6cfd40de3fcbeb9e5e6
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Mon Jun 09 08:26:37 2014 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/6c169adfa383188aa6f4e6cfd40de3fcbeb9e5e6 [^]

related to bug 26487: lowered message level from info to debug

---
M src/org/openbravo/ddlutils/util/DBSMOBUtil.java
---
(0067821)
marvintm (viewer)
2014-06-09 10:51

 Reviewed.
(0067889)
hudsonbot (viewer)
2014-06-10 22:24

 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/763f16c2ad2e [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2014-05-08 06:20 sjkumar New Issue
2014-05-08 06:20 sjkumar Assigned To => marvintm
2014-05-08 06:20 sjkumar Modules => Core
2014-05-08 06:20 sjkumar Triggers an Emergency Pack => No
2014-05-10 20:05 sjkumar File Added: update.log
2014-05-10 20:05 sjkumar Note Added: 0067077
2014-05-23 21:02 sjkumar Note Added: 0067417
2014-05-23 21:03 sjkumar File Added: CharsetScreenShot.png
2014-05-23 21:03 sjkumar File Added: update Issue 0026487.log
2014-05-27 16:31 alostale Note Added: 0067481
2014-05-27 16:32 alostale Note Edited: 0067481 View Revisions
2014-05-27 16:33 alostale OBNetwork customer => No
2014-05-27 16:33 alostale Target Version => PR14Q3
2014-05-27 17:45 shuehner Issue Monitored: shuehner
2014-05-27 17:47 alostale Summary Ant smartbuild building tables that has not been changed too => update.database rebuilds unchanged tables (Oracle, charset UTF8)
2014-05-27 17:48 alostale OBNetwork customer No => Yes
2014-05-27 17:48 alostale Assigned To marvintm => alostale
2014-05-28 02:25 johnfandl Issue Monitored: johnfandl
2014-05-28 13:13 hgbot Checkin
2014-05-28 13:13 hgbot Note Added: 0067530
2014-05-28 13:13 hgbot Status new => resolved
2014-05-28 13:13 hgbot Resolution open => fixed
2014-05-28 13:13 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/fc1e0b6f0a4b8d06b8aed86d37a1fcaca1346ccf [^]
2014-05-28 13:13 hgbot Checkin
2014-05-28 13:13 hgbot Note Added: 0067531
2014-05-28 13:13 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/fc1e0b6f0a4b8d06b8aed86d37a1fcaca1346ccf [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/7116ef0da09d597de00749cc3976323452275f15 [^]
2014-05-28 13:26 alostale Note Added: 0067533
2014-05-28 13:28 alostale Status resolved => new
2014-05-28 13:28 alostale Resolution fixed => open
2014-05-28 13:29 alostale File Added: test-ora-utf8.diff
2014-05-28 13:29 alostale Review Assigned To => marvintm
2014-05-28 13:29 alostale Status new => scheduled
2014-05-28 13:29 alostale fix_in_branch => pi
2014-05-28 13:29 alostale Status scheduled => resolved
2014-05-28 13:29 alostale Fixed in Version => PR14Q3
2014-05-28 13:29 alostale Resolution open => fixed
2014-05-29 23:01 hudsonbot Checkin
2014-05-29 23:01 hudsonbot Note Added: 0067583
2014-06-09 08:30 hgbot Checkin
2014-06-09 08:30 hgbot Note Added: 0067815
2014-06-09 10:48 hgbot Checkin
2014-06-09 10:48 hgbot Note Added: 0067820
2014-06-09 10:51 marvintm Note Added: 0067821
2014-06-09 10:51 marvintm Status resolved => closed
2014-06-10 22:24 hudsonbot Checkin
2014-06-10 22:24 hudsonbot Note Added: 0067889

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker