My Oracle Support Banner

After upgrade to BDA V4.8 or Higher Navigator Audit Service Fails to Log Hive Audit Events - Raises: "SqlExceptionHelper [*@NavigatorServer-#]: Incorrect string value: '\*\*' for column 'OPERATION_TEXT' at row 1 (Doc ID 2298136.1)

Last updated on MARCH 19, 2020

Applies to:

Big Data Appliance Integrated Software - Version 4.8.0 and later
Linux x86-64

Purpose

NOTE: In the examples that follow, user details, cluster names, hostnames, directory paths, filenames, etc. represent a fictitious sample (and are used to provide an illustrative example only). Any similarity to actual persons, or entities, living or dead, is purely coincidental and not intended in any manner. 

After upgrade to BDA V4.8 (CDH 5.10.1) or possibly higher the Navigator Audit Service fails to log Hive Audit events. Instead an error like that below is raised in the Navigator Audit server logs.

2017-05-25 23:08:10,x WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper [*@NavigatorServer-#]: SQL Error: 1366, SQLState: HY000
2017-05-25 23:08:10,y ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper [*@NavigatorServer-#]: Incorrect string value: '\XEF\XAF\XBD' for column 'OPERATION_TEXT' at row 1
2017-05-25 23:08:10,z WARN com.cloudera.navigator.NavigatorServer [*@NavigatorServer-#]: Error persisting events for service hive
org.hibernate.exception.GenericJDBCException: error executing work at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
...
Caused by: java.sql.BatchUpdateException: Incorrect string value: '\XEF\XAF\XBD' for column 'OPERATION_TEXT' at row 1
...
Caused by: java.sql.SQLException: Incorrect string value: '\XEF\XAF\XBD' for column 'OPERATION_TEXT' at row 1

The main verification in the Navigator logs is a type exception when a Cloudera Manager agent tries to write in the Audit server like below:

2017-05-25 23:08:10,a WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper [*@NavigatorServer-#]: SQL Error: 1366, SQLState: HY000
2017-05-25 23:08:10,b ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper [*@NavigatorServer-#]: Incorrect string value: '\XEF\XAF\XBD' for column 'OPERATION_TEXT' at row 1
2017-05-25 23:08:10,c WARN com.cloudera.navigator.NavigatorServer [*@NavigatorServer-#]: Error persisting events for service impala org.hibernate.exception.GenericJDBCException: error executing work

The problem cause is that the the databases for Activity Monitor, Reports Manager, Hive Metastore Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server must be configured to support a UTF-8 character set encoding, but this is not the case on the BDA.   For details on creating the Activity Monitor, Reports Manager, Hive Metastore Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server databases see the section in the Cloudera documentation on "Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server".  The issue is tracked as an internal BDA bug.

The purpose of this note is to provide the steps to convert the navigator database to a UTF-8 character set encoding on BDA versions 4.14 and lower.

Note that in BDA 5.1 if the navigator database character set is latin1 and not UTF-8 and the character set of any of the columns in the database are latin1 then the navigator_conversion_gen_script.sql needs to be edited as described in the "Prerequisites" section. The reason for the edits to the script is that in BDA versions through BDA 4.14 the character set of the columns in the navigator database is UTF-8. In BDA 5.1 the character set of the columns in the navigator database are a combination of UTF-8 and utf8mb4. The navigator_conversion_gen_script.sql as is converts the character set of any navigator database columns which are not UTF-8 to UTF-8. Since BDA 5.1 uses a character set of UTF-8 and utf8mb4 converting non-UTF-8/non-utf8mb4 columns to UTF-8 is not safe. Therefore on BDA 5.1 the navigator_conversion_gen_script.sql script needs to be edited to convert non-UTF-8/non-utf8mb4 columns to utf8mb4.

Note: The latin1 character set is compatible with BDA releases. Therefore database character set migration is only recommended in cases where there are character set conversion errors preventing auditing.  In the case of a healthy Navigator Audit Service we do not suggest implementing the steps in this note.  We have generally not seen these errors occurring in BDA releases newer than BDA v4.8.0.  Hence only in cases where the Navigator Audit Service fails to log Hive Audit events and errors out as described here are the steps recommended.

 

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Details
 Overview
 Prerequisites
 Detailed Steps
 Steps to restore the navigator database backup

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.