Bug 1110277 - RHQ_EVENT table's detail field limit of 4000 character will break when a log message contains multibyte characters (UTF-8)
Summary: RHQ_EVENT table's detail field limit of 4000 character will break when a log ...
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: JBoss Operations Network
Classification: JBoss
Component: Database
Version: JON 3.1.2,JON 3.2,JON 3.2.1
Hardware: Unspecified
OS: Unspecified
urgent
urgent
Target Milestone: DR03
: JON 3.2.2
Assignee: Jay Shaughnessy
QA Contact: Garik Khachikyan
URL:
Whiteboard:
Depends On:
Blocks: 1110278 1114155
TreeView+ depends on / blocked
 
Reported: 2014-06-17 11:10 UTC by Tom Fonteyne
Modified: 2019-09-12 07:54 UTC (History)
7 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
: 1110278 1114155 (view as bug list)
Environment:
Oracle 11g
Last Closed: 2014-07-29 00:17:23 UTC
Type: Bug


Attachments (Terms of Use)
attachment#1 (8.59 KB, text/x-log)
2014-07-03 13:56 UTC, Garik Khachikyan
no flags Details
attachment no2 (225.76 KB, image/png)
2014-07-03 13:58 UTC, Garik Khachikyan
no flags Details
attachment no3 (157.85 KB, image/png)
2014-07-03 13:58 UTC, Garik Khachikyan
no flags Details
attachment no1 (8.59 KB, text/x-log)
2014-07-03 13:59 UTC, Garik Khachikyan
no flags Details
attachment no4 (123.60 KB, image/png)
2014-07-03 14:00 UTC, Garik Khachikyan
no flags Details
attachment no5 (124.23 KB, image/png)
2014-07-03 14:00 UTC, Garik Khachikyan
no flags Details


Links
System ID Priority Status Summary Last Updated
Red Hat Knowledge Base (Solution) 876383 None None None Never

Description Tom Fonteyne 2014-06-17 11:10:49 UTC
JON 3.1.2 and JON 3.2.0

org/​ rhq/​ core/​ domain/​ event/​ Event.java

126     @Column(name = "DETAIL", length = 4000, nullable = false)
127     private String detail;

so the detail string is trimming the text to 4000 UTF8 characters.


org/​ rhq/​ enterprise/​ server/​ event/​ EventManagerBean.java 

is responsible to insert the event into the database:

112     public void addEventData(Map<EventSource, Set<Event>> events) {
...
167             // Then insert the "values" (i.e. the Events).
168             ps = conn.prepareStatement(statementSql);
169             try {
170                 for (EventSource eventSource : events.keySet()) {
171                     Set<Event> eventData = events.get(eventSource);
172                     for (Event event : eventData) {
....
181                         ps.setString(paramIndex++, event.getDetail());

However, then the database (oracle in our case) has not been setup to use UTF-8, but only a standard 8-bit character set then trying to insert a "detail" which has international (e.g. 16 bit) characters in it will break the 4000 limit with:

ORA-01461: can bind a LONG value only for insert into a LONG column

because the number of bytes form the "detail" will be bigger then 4000.

Solution:

- make the database being UTF-8 a requirement (JON installer to check)

or modify the above code:

  byte[] detailBytes = event.getDetail().getBytes();
  ps.setBytes(paramIndex++,Arrays.copyOf(detailBytes, 4000));

Comment 1 Tom Fonteyne 2014-06-18 13:20:19 UTC
The proposed code is in fact not suited as it's not the perfect solution.

Even when the database is set to UTF-8, this can still be an issue when NLS_LENGTH_SEMANTICS is set to BYTE

Ideally, when installing JON, we should require:

- Oracle database is set to UTF-8.
- NLS_LENGTH_SEMANTICS is set to CHAR

or

- Oracle database is set to UTF-8.

and all VARCHAR2 fields should be created as (for example) VARCHAR2(4000 CHAR)

as a temporary fix, I suppose we could do a simple type change on the "detail" field to be VARCHAR2(4000 CHAR)

The customer also came up with a very nice statement to do this on the whole database:

select 'alter table ' || table_name || ' modify ' || column_name || ' ' || data_type || '(' || data_length || ' char);'  from user_tab_columns where data_type = 'VARCHAR2';

(not tested by myself)

Comment 2 Tom Fonteyne 2014-06-20 10:59:08 UTC
ok... been digging deeper.

As it turns out, the varchar2 field has a hard limit of 4000 BYTES ... regardless whether you use BYTE or CHAR semantics

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF50955

So the only solutions are:

no schema change + use
  byte[] detailBytes = event.getDetail().getBytes();
  ps.setBytes(paramIndex++,Arrays.copyOf(detailBytes, 4000));

schema change and make the field a CLOB + modify the code to deal with that.

Comment 3 Larry O'Leary 2014-06-21 02:30:10 UTC
This seems pretty critical as multiple users have hit the issue in a very short time.

It isn't clear why this wasn't discovered earlier though as I don't see any changes here. Therefore, this doesn't seem like a regression but a long standing bug that was never discovered.

The other possibility is that perhaps Hibernate was configured to deal with this in some way in previous releases. However, that doesn't seem to be the case either and this issue might actually be a "known issue" in Hibernate based on https://hibernate.atlassian.net/browse/HHH-898 as it seems to indicate that by switching it from VARCHAR2(<length>) to VARCHAR2(<length>, char) we will somehow gain the extra width needed for multibyte characters. This just isn't the case.

I therefore still think there is a bug in Hibernate but I also believe that it is the application that needs to ensure that it doesn't exceed the limit. Perhaps Hibernate should be throwing an exception when more then [column] size bytes are written?

In any event, I think the best solution here is two fold:

 1) Short term (3.x):

    As indicated in comment 2:

> no schema change + use
>   byte[] detailBytes = event.getDetail().getBytes();
>   ps.setBytes(paramIndex++,Arrays.copyOf(detailBytes, 4000));

    When event details are processed, we check to see how many bytes they will use up. If larger then 4000, then we truncate it. I would say log a warning stating we are truncating the data but we aren't doing that right now even with single byte characters that exceed the 4000 byte maximum size.

 2) Long term (4.x):
    As indicated in comment 2:

> schema change and make the field a CLOB + modify the code to deal with that.

    CLOB or other large text/string type seems more appropriate. Perhaps a hybrid of the two would even be doable if there is concern of the database just using too much space for shorter messages. For example, use a string type that is only 1000 bytes. If the data exceeds 1000 bytes, write it in a clob in some other 'special' table and stick some kind of marker in the event detail table to indicate where the clob is.

Comment 4 Heiko W. Rupp 2014-06-21 11:17:05 UTC
Very short term (3.2.x) only the code change should happen, to avoid a schema change in a cp.

For 3.3 we may perhaps make the schema change.

Longterm it is likely that events data end in Storage Nodes or even a totally different system (ElasticSearch comes to mind).

Comment 5 Jay Shaughnessy 2014-06-23 15:52:40 UTC
Right, Oracle imposes a varchar2(4000) limit.  And furthermore, it's a hard 4000 byte limit regardless of character encoding.  So, we define varchar2(4000) for our longest varchar2 declarations.

Postgres, on the other hand, does not have this limit and can store varying strings up to ~1G (although if you're going to go that big you should use a clob).  We still max out at 4000 for consistency in the DDL, but on Postgres it can truly be 4000 chars, regardless of encoding.

So, any trimming we do needs to be DatabaseType dependent.  Event detail is not the only place this needs to happen, there are a few places where we should actively check the maximum.

I would recommend not converting anything to CLOB at this point and to wait to see what we do design-wise for log storage, which as Heiko mentions would most likely going to be outside of the RDB anyway.

I'll add the code to conditionally trim based on db vendor.  One small nit is that a straight trim of chars to bytes for oracle could clip a multi-byte character and make it unreadable.  I'm not sure if we need to protect against that but I may add that protection just to be safe.

Comment 6 Jay Shaughnessy 2014-06-24 16:42:43 UTC
I've pushed a commit to master and if jenkins runs clean, and I get no negative review comments, will cherry-pick to release/3.2.x.

MASTER commit 1d836a790f8e8d2da79a31e8c796324e85ae7851
Author: Jay Shaughnessy <jshaughn@redhat.com>
Date:   Mon Jun 23 16:07:31 2014 -0400

    This is a problem in a few places, not just events.  We're not
    properly protecting against the 4000 byte limit on oracle, just
    the 4000 character limit declared on the varchar2 field.
    - Add DatabaseType.getString() which can truncate as needed in
      a vendor-specific way.
    - Use the new getString() to store safe versions of event detail,
      alert notification log message, and calltime dataset destination.

Comment 8 Jay Shaughnessy 2014-06-24 21:12:06 UTC
release/jon3.2.x commit 6a5f14b4089dfe2e17cbc926b22685baec2de206
Author: Jay Shaughnessy <jshaughn@redhat.com>
Date:   Mon Jun 23 16:07:31 2014 -0400

Conflicts:
    modules/core/dbutils/src/main/java/org/rhq/core/db/DatabaseType.java
    modules/core/dbutils/src/main/java/org/rhq/core/db/OracleDatabaseType.java
    modules/core/domain/src/main/java/org/rhq/core/domain/resource/Resource.java
    modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/event/EventManagerBean.java
    modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/measurement/CallTimeDataManagerBean.java

Cherry-Pick master 1d836a790f8e8d2da79a31e8c796324e85ae7851

Comment 9 Simeon Pinder 2014-06-30 06:03:08 UTC
Moving to ON_QA as available for test in latest build:
http://jon01.mw.lab.eng.bos.redhat.com:8042/dist/release/jon/3.2.2.GA/6-28-2014/

Comment 10 Garik Khachikyan 2014-07-02 12:02:09 UTC
taking QA contact.

Comment 11 Garik Khachikyan 2014-07-03 13:54:43 UTC
scenarios performed:

1. preparing a custom log file (see attachment #1 [details])
2. adding a "Log Event Source" and enable it via "http://server:7080/coregui/#Resource/<RHQ-server-resource-id>/Inventory/ConnectionSettings" property of "Log Event Sources" (see picture in #2)
3. adjust the time properly in that log file
4. wait until the logs get parsed (see picture #3)

tested and results are shown in picture #4(oracle) and #5(postgres)

all passed.

checked version: JON 3.2.2 DR3

Comment 12 Garik Khachikyan 2014-07-03 13:56:05 UTC
Created attachment 914476 [details]
attachment#1 [details]

Comment 13 Garik Khachikyan 2014-07-03 13:58:00 UTC
Created attachment 914477 [details]
attachment no2

Comment 14 Garik Khachikyan 2014-07-03 13:58:47 UTC
Created attachment 914478 [details]
attachment no3

Comment 15 Garik Khachikyan 2014-07-03 13:59:28 UTC
Created attachment 914479 [details]
attachment no1

Comment 16 Garik Khachikyan 2014-07-03 14:00:23 UTC
Created attachment 914480 [details]
attachment no4

Comment 17 Garik Khachikyan 2014-07-03 14:00:50 UTC
Created attachment 914481 [details]
attachment no5

Comment 18 Garik Khachikyan 2014-07-03 14:01:07 UTC
# VERIFIED

Comment 19 Larry O'Leary 2014-07-29 00:17:23 UTC
This has been verified and released in Red Hat JBoss Operations Network 3.2 Update 02 (3.2.2) available from the Red Hat Customer Portal[1].



[1]: https://access.redhat.com/jbossnetwork/restricted/softwareDetail.html?softwareId=31783


Note You need to log in before you can comment on or make changes to this bug.