Log4j2 properties file JDBC appender - cast context variable to uuid

Question

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?

Answer 1

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>