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));
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)
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.
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.
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).
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.
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> 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.
release/jon3.2.x commit 6a5f14b4089dfe2e17cbc926b22685baec2de206 Author: Jay Shaughnessy <jshaughn> 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
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/
taking QA contact.
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
Created attachment 914476 [details] attachment#1 [details]
Created attachment 914477 [details] attachment no2
Created attachment 914478 [details] attachment no3
Created attachment 914479 [details] attachment no1
Created attachment 914480 [details] attachment no4
Created attachment 914481 [details] attachment no5
# VERIFIED
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