Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0026487Openbravo ERPY. DBSourceManagerpublic2014-05-08 06:202014-06-10 22:24
Reportersjkumar 
Assigned Toalostale 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR14Q3Fixed in Version3.0PR14Q3 
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
Summary0026487: 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.
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Fileslog update.log (1,165,458) 2014-05-10 20:05
https://issues.openbravo.com/file_download.php?file_id=6921&type=bug
png CharsetScreenShot.png (57,817) 2014-05-23 21:03
https://issues.openbravo.com/file_download.php?file_id=6946&type=bug
png

log update Issue 0026487.log (3,605,244) 2014-05-23 21:03
https://issues.openbravo.com/file_download.php?file_id=6947&type=bug
diff test-ora-utf8.diff (1,685) 2014-05-28 13:29
https://issues.openbravo.com/file_download.php?file_id=6960&type=bug
Issue History
Date ModifiedUsernameFieldChange
2014-05-08 06:20sjkumarNew Issue
2014-05-08 06:20sjkumarAssigned To => marvintm
2014-05-08 06:20sjkumarModules => Core
2014-05-08 06:20sjkumarTriggers an Emergency Pack => No
2014-05-10 20:05sjkumarFile Added: update.log
2014-05-10 20:05sjkumarNote Added: 0067077
2014-05-23 21:02sjkumarNote Added: 0067417
2014-05-23 21:03sjkumarFile Added: CharsetScreenShot.png
2014-05-23 21:03sjkumarFile Added: update Issue 0026487.log
2014-05-27 16:31alostaleNote Added: 0067481
2014-05-27 16:32alostaleNote Edited: 0067481bug_revision_view_page.php?bugnote_id=0067481#r5890
2014-05-27 16:33alostaleOBNetwork customer => No
2014-05-27 16:33alostaleTarget Version => PR14Q3
2014-05-27 17:45shuehnerIssue Monitored: shuehner
2014-05-27 17:47alostaleSummaryAnt smartbuild building tables that has not been changed too => update.database rebuilds unchanged tables (Oracle, charset UTF8)
2014-05-27 17:48alostaleOBNetwork customerNo => Yes
2014-05-27 17:48alostaleAssigned Tomarvintm => alostale
2014-05-28 02:25johnfandlIssue Monitored: johnfandl
2014-05-28 13:13hgbotCheckin
2014-05-28 13:13hgbotNote Added: 0067530
2014-05-28 13:13hgbotStatusnew => resolved
2014-05-28 13:13hgbotResolutionopen => fixed
2014-05-28 13:13hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/fc1e0b6f0a4b8d06b8aed86d37a1fcaca1346ccf [^]
2014-05-28 13:13hgbotCheckin
2014-05-28 13:13hgbotNote Added: 0067531
2014-05-28 13:13hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/fc1e0b6f0a4b8d06b8aed86d37a1fcaca1346ccf [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/7116ef0da09d597de00749cc3976323452275f15 [^]
2014-05-28 13:26alostaleNote Added: 0067533
2014-05-28 13:28alostaleStatusresolved => new
2014-05-28 13:28alostaleResolutionfixed => open
2014-05-28 13:29alostaleFile Added: test-ora-utf8.diff
2014-05-28 13:29alostaleReview Assigned To => marvintm
2014-05-28 13:29alostaleStatusnew => scheduled
2014-05-28 13:29alostalefix_in_branch => pi
2014-05-28 13:29alostaleStatusscheduled => resolved
2014-05-28 13:29alostaleFixed in Version => PR14Q3
2014-05-28 13:29alostaleResolutionopen => fixed
2014-05-29 23:01hudsonbotCheckin
2014-05-29 23:01hudsonbotNote Added: 0067583
2014-06-09 08:30hgbotCheckin
2014-06-09 08:30hgbotNote Added: 0067815
2014-06-09 10:48hgbotCheckin
2014-06-09 10:48hgbotNote Added: 0067820
2014-06-09 10:51marvintmNote Added: 0067821
2014-06-09 10:51marvintmStatusresolved => closed
2014-06-10 22:24hudsonbotCheckin
2014-06-10 22:24hudsonbotNote Added: 0067889

Notes
(0067077)
sjkumar   
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   
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   
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   
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   
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   
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   
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   
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   
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   
2014-06-09 10:51   
Reviewed.
(0067889)
hudsonbot   
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