Issue with writing Web Player logs to Oracle Database – Revvity Signals

Date Posted: 2017-08-29
Product: TIBCO Spotfire®

Problem:

Issue with writing Web Player logs to Oracle Database

Solution:

Description:
Some log messages not get written into the Oracle Database when database logging of Web Player is enabled.
Example: AuditLog with ADONETAppender.

Symptoms:
1). In some cases nothing will be written into the database with the option bufferSize=10 and only a few message will be written with bufferSize < 10.
2). The following error message will be logged if a user enables log4net internal debugging.

------------------------------------
system.Data.OracleClient.OracleException (0x80131938): ORA-00001: unique constraint (<CONSTRAINT NAME>) violated
------------------------------------

Cause:
This ERROR message indicates that a unique constraint exists on the table which prevents the messages from being logged. With bufferSize > 1, a group of insert statements is sent to the database as one transaction. If any of those are duplicates, then the whole transaction is rolled back. This result is no logging or only fewer message logging with respect to the Log File. This issue can also occur with any other Database with UNIQUE Constraint.

The resolution depends on the type of Log and the user's requirement. The following workarounds can be implemented.

1). Set bufferSize = 1 so that it will write all messages except those which are not unique.

2). Drop the UNIQUE constraint from the database.

3). Adding more columns to the UNIQUE constraint can increase the uniqueness of the messages.

4). Handling the OracleException with IGNORE_ROW_ON_DUPKEY_INDEX feature introduced in Oracle 11g. When IGNORE_ROW_ON_DUPKEY_INDEX hint is used in an SQL insert on a table with a unique key index. All duplicates will be silently ignored rather than causing the traditional error, ORA-00001 unique constraint violated.

http://www.dba-oracle.com/t_ignore_row_on_dupkey_index.htm

http://logging.apache.org/log4net/release/faq.html