PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Log4j2 properties file JDBC appender – cast context variable to uuid

I have the following database table (PostgreSQL)

create table audit_read
(
    level text,
    account_id uuid,
    message text
);

I want to insert a database record when a logging event occurs. I have therefore the following configuration (properties file):

appender.read.type = JDBC
appender.read.name = READ
logger.read = debug, READ
logger.read.name = audit.read
logger.read.additivity = false
appender.read.connectionSource.connectionString=jdbc:postgresql://localhost:5432/mydatabase
appender.read.connectionSource.type = DriverManager
appender.read.connectionSource.driverClassName = org.postgresql.Driver
appender.read.connectionSource.username = myusername
appender.read.connectionSource.password = mypassword

appender.read.tableName=runtime_audit.audit_read
appender.read.ignoreExceptions=false

appender.read.columnConfigs[0].type = COLUMN
appender.read.columnConfigs[0].name = level
appender.read.columnConfigs[0].pattern = %p
appender.read.columnConfigs[0].isUnicode = false

appender.read.columnConfigs[1].type = COLUMN
appender.read.columnConfigs[1].name = account_id
appender.read.columnConfigs[1].pattern = %X{currentUser.id}
appender.read.columnConfigs[1].isUnicode = false

appender.read.columnConfigs[2].type = COLUMN
appender.read.columnConfigs[2].name = message
appender.read.columnConfigs[2].pattern = %m
appender.read.columnConfigs[2].isUnicode =false

When I trigger the logging event I get the following error:

ERROR org.apache.logging.log4j.core.appender.db.DbAppenderLoggingException: 
Failed to insert record for log event in JDBC manager: 
org.postgresql.util.PSQLException: 
ERROR: column "account_id" is of type uuid but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

When I log the logging event to a file I get the following log line:

INFO - 0c5c1ad7-14e2-4215-8919-15740d8a8d72 - resource X was read

which indicate that my context variables are correctly filled in.

My question is thus how can I cast my context variable to a uuid? I’ve read the documentation and suspect that I will have to use the ColumnMapping parameter but I can not seem to comprehend how to configure this. Or perhaps something with UuidConverter?

Tags:

2

Answers


  1. 0

    The documentation page
    that you linked says:

    Note that as of Log4j 2.8, there are two ways to configure log event to column mappings: the original
    ColumnConfig style that only allows strings and timestamps, and the new ColumnMapping plugin that uses
    Log4j’s built-in type conversion to allow for more data types

    Since you want an UUID, it means that you need to switch to ColumnMapping.

    The column type is indicated by the type parameter; however it creates a conflict
    when using the properties file format, so you will need to switch to another one, for example XML:

    <Configuration>
  <Appenders>
    <JDBC name="READ" tableName="runtime_audit.audit_read" ignoreExceptions="false">
      <DriverManager connectionString="jdbc:postgresql://localhost:5432/mydatabase" driverClassName="org.postgresql.Driver" userName="myusername" password="mypassword"/>
      <ColumnMapping name="level" pattern="%p"/>
      <ColumnMapping name="account_id" pattern="%X{currentUser.id}" type="java.util.UUID"/>
      <ColumnMapping name="message" pattern="%m"/>
    </JDBC>
  </Appenders>
  <Loggers>
    <Logger name="audit.read" level="debug" additivity="false">
      <AppenderRef ref="READ"/>
    </Logger>
    <Root/>
  </Loggers>
</Configuration>
    Login or Signup to reply.
  2. 0

    So – DB expects a UUID, but log4j2 is passing a string. You could simply change the SQL to expect a VARCHAR(40) perhaps, if the contents of the string are otherwise fine?

    You also seem to be missing a primary key on your table, and a timestamp, which will likely be useful in any real world system.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search