Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0037702Openbravo ERPA. Platformpublic2018-01-22 20:222021-10-14 09:34
Reportershuehner 
Assigned Tocberner 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionPR20Q4 
Merge Request Status
Review Assigned Tocberner
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0037702: Hibernate update/insert to tables containing CLOB sometimes fail on Oracle (HHH-4635)
DescriptionOracle has a functional restriction meaning on tables mixing varchar & clob column which essentially means that clob column must be referenced last in the column list. Otherwise in case of the parameter values being sufficiently large for the previous varchar data the SQL statement fails with oracle specific error:

ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

Example of this behavior was found in this old issue: 27792

To reproduce an older version of the retail logclient table (from 16Q2) can be used as it had still both
a.) json column of type clob
b.) msg column of type varchar
c.) Alphabetic order on columns has json coming before msg (that is used by unfixed hibernate.

Example reproducer is short following java snippet (see steps to reproduce) which essentially builds a ca. 2k varchar value + a big value for the clob column and does an insert.

The issue was also be seen in the wild happening in a 16Q2 + oracle 11g instance.

NOTE:
Test case above cannot be reproduce in latest pi of retail as issue 30652 removed the dmsg varchar column as it contained duplicate info anyway.

But as the issue is generic -> reported here.

This seems to be fixed in hibernate upstream already but not in the version currently used in Openbravo yet:
https://hibernate.atlassian.net/browse/HHH-4635 [^]
This 2nd link also has an description of the issue and manual solution (rename columns) to change order:
https://bugs.eclipse.org/bugs/show_bug.cgi?id=486883 [^]
Steps To ReproduceRun following java-snippet against 16q2 retail (or similar table) with oracle:

    StringBuilder test = new StringBuilder();
    for (int i = 0; i < 66000; i++) {
      test.append("a");
    }
    String json = "test: " + test;
    StringBuilder test2 = new StringBuilder();
    for (int i = 0; i < 1950; i++) {
      test2.append("a");
    }
    logClient.setMsg("Some Msg Value: " + test2);
    logClient.setJson(json);

    OBDal.getInstance().save(logClient);
    OBDal.getInstance().flush();
Proposed SolutionMoved to design defect because fix requires to create a non trivial patch in Hibernate. Ideally would wait for library update.
Additional Information
TagsNo tags attached.
Relationships
related to feature request 0037064 closed caristu Openbravo ERP upgrade hibernate to 5.3.2 
related to defect 0030652 closed ranjith_qualiantech_com Retail Modules The JSON column in the Log Client table is redundant, and takes too much space 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-01-22 20:22shuehnerNew Issue
2018-01-22 20:22shuehnerAssigned To => platform
2018-01-22 20:22shuehnerModules => Core
2018-01-22 20:22shuehnerTriggers an Emergency Pack => No
2018-01-22 20:22shuehnerRelationship addedrelated to 0037064
2018-01-30 10:35alostaleStatusnew => acknowledged
2018-01-30 10:38alostaleTypedefect => design defect
2018-01-30 10:38alostaleProposed Solution updated
2018-08-09 08:24caristuRelationship addedrelated to 0030652
2018-08-10 15:22hgbotCheckin
2018-08-10 15:22hgbotNote Added: 0106290
2018-08-10 15:24caristuNote Added: 0106291
2021-10-06 13:35cbernerNote Added: 0132198
2021-10-14 09:30cbernerAssigned Toplatform => cberner
2021-10-14 09:30cbernerStatusacknowledged => scheduled
2021-10-14 09:34cbernerNote Added: 0132336
2021-10-14 09:34cbernerStatusscheduled => resolved
2021-10-14 09:34cbernerFixed in Version => PR20Q4
2021-10-14 09:34cbernerFixed in SCM revision => 4ce8dfed86f96817045d31e462d5cbc9eb4a3c27
2021-10-14 09:34cbernerResolutionopen => fixed
2021-10-14 09:34cbernerReview Assigned To => cberner
2021-10-14 09:34cbernerStatusresolved => closed

Notes
(0106290)
hgbot   
2018-08-10 15:22   
Repository: erp/mods/org.openbravo.platform.ci
Changeset: a9f468967bd85fa6ccf11c7b241a190ad1a90694
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Fri Aug 10 15:22:19 2018 +0200
URL: http://code.openbravo.com/erp/mods/org.openbravo.platform.ci/rev/a9f468967bd85fa6ccf11c7b241a190ad1a90694 [^]

related to issue 37702: created test case to reproduce the issue

---
A src-db/database/model/tables/OBPFCI_LOGCLIENT.xml
A src-db/database/sourcedata/AD_COLUMN.xml
A src-db/database/sourcedata/AD_PACKAGE.xml
A src-db/database/sourcedata/AD_TABLE.xml
A src-test/org/openbravo/platform/ci/InsertCLOB.java
---
(0106291)
caristu   
2018-08-10 15:24   
Issue can be still reproduced after 0037064

Although it seems to be fixed in Hibernate, it seems that to fix this issue it is also required to declare which are the Lob columns in the Hibernate mapping.
(0132198)
cberner   
2021-10-06 13:35   
This issue is still reproducible after https://issues.openbravo.com/view.php?id=45968 [^]

It seems however to only being reproducible with Oracle v12 and older, with Oracle v19 the test we have here https://gitlab.com/openbravo/ci/modules/org.openbravo.platform.ci/-/blob/master/src-test/org/openbravo/platform/ci/InsertCLOB.java [^] passes correctly.

It may have been fixed by Oracle in between version 12 and 19.
(0132336)
cberner   
2021-10-14 09:34   
This is no longer reproducible with our supported and recommended Oracle version 19. It is only reproducible with older versions, 11 and 12.

As such, because from PR20Q4 we don't support this version, the issue may be closed.