Log4j JDBCAppender to log stacktraces

Question

Using org.apache.log4j.jdbc.JDBCAppender, how can I get stracktraces logged with warn and error into the PatternLayout.

I'm logging like

logger.warn("warning description", e);
logger.error("error description", e);

I get the String descriptions into the table, but the Throwable's stacktrace is now where. Is there another parameter that I can access via the PatternLayout. Currently I am using

"INSERT INTO app_logs (app, log_date, log_level, location, loc, message) VALUES ('my-apps-name', '%d{ISO8601}','%p', '%C.java', '%C{1}.java:%L', '%m')" 

into a table

TABLE `app_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app` varchar(255) DEFAULT NULL,
  `log_date` varchar(255) DEFAULT NULL,
  `log_level` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `loc` varchar(255) DEFAULT NULL,
  `message` text, 
  PRIMARY KEY (`id`)
)

Answer 1

To extend on MikeNereson's answer, here is a log4j.properties that worked:

log4j.rootLogger=DEBUG,DB
log4j.appender.DB.driver=com.mysql.jdbc.Driver
log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.DB.URL=jdbc:mysql://server/db
log4j.appender.DB.user=user
log4j.appender.DB.password=pwd
log4j.appender.DB.layout.ConversionPattern=INSERT INTO app_logs (app, log_date, log_level, location, loc, message, throwable, stacktrace) VALUES ('appname', '%d{ISO8601}','%p', '%C.java', '%C{1}.java:%L', '%m', '%throwable{short}', '%throwable{100}')
log4j.appender.DB.layout=org.apache.log4j.EnhancedPatternLayout
log4j.category.ke.co=ERROR
log4j.category.ke.co.appender-ref=DB

Answer 2

I found the solution.

Replace the PatternLayout class with the EnhancedPatternLayout class.

org.apache.log4j.EnhancedPatternLayout

You'll also need to include the apache-log4j-extra dependency

Or include it in your pom:

<dependency>
  <groupId>log4j</groupId>
  <artifactId>apache-log4j-extras</artifactId>
  <version>1.1</version>
</dependency>

You now have access to %throwable

%throwable{short} or %throwable{1} will output the first line of stack trace. throwable{none} or throwable{0} will suppress the stack trace. %throwable{n} will output n lines of stack trace if a positive integer or omit the last -n lines if a negative integer.

I added to my table,

TABLE `app_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app` varchar(255) DEFAULT NULL,
  `log_date` varchar(255) DEFAULT NULL,
  `log_level` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `loc` varchar(255) DEFAULT NULL,
  `message` text,
  `throwable` text,
  `stacktrace` text,
  PRIMARY KEY (`id`)
)

And updated my pattern to populate these columns.

"INSERT INTO app_logs (app, log_date, log_level, location, loc, message, throwable, stacktrace) VALUES ('my-apps-name', '%d{ISO8601}','%p', '%C.java', '%C{1}.java:%L', '%m', '%throwable{short}', '%throwable{100}')"

Answer 3

Solution is we need to use EnhancedPattern Layout, by using this we can log the entire stack trace into DB. But there is one issue if we use this if the Stacktrace contain comma(,) , the message will not be get logged.I have solve this by over writing getLogStatement() which is available in JDBC appender Sourcecode

Follow the following steps

1. Download Log4j1.2.17. Enhanced patternlayout is available from log41.2.16 ver

2. Edit log4j properties file

   log4j.rootLogger = WARN, DB
   log4j.appender.DB=abc.xyz.MyJdbcAppender
   log4j.appender.DB.URL=jdbc:mysql://localhost/DBNAME
   log4j.appender.DB.driver=com.mysql.jdbc.Driver
   log4j.appender.DB.user=user_name
   log4j.appender.DB.password=password
   log4j.appender.DB.layout=org.apache.log4j.EnhancedPatternLayout
   log4j.appender.DB.conversionPattern=Insert into MylogFile(logid,loglevel,logcriteria,message,stacktrace,date) values (mysequence.nextval,’%p’,’%c’,
   ‘%m’,’%throwable{40},’%d{ABSOLUTE}’)
   

3. Now create a new class that will extend JDBCappender and overwrite getLogStatement():

   Public MyJdbcAppender extends JDBCAppender{
   protected String getLogStatement(LoggingEvent event) {
   if(null!=event.getThrowableInformation() && event.getThrowableInformation().getThrowable() instance of SQLException){
   SQLException myexce= new SQLException(event.
   getThrowableInformation().getThrowable().getMessage().
   replaceAll(“’”,” “),event.getThrowableInformation().getThrowable());
     LoggingEvent clone = new LoggingEvent(
       event.fqnOfCategoryClass,
       LogManager.getLogger(event.getLoggerName()),
       event.getLevel(),
   event.getLevel(),event.getMessage(),myexce); 
    return getLayout().format(clone);
   }
   return getLayout().format(event)
   }
   }
   

Answer 4

log4j 1.2.16+ 's EnhancedPatternLayout is not work! because it extends org.apache.log4j.Layout rather than org.apache.log4j.PatternLayout, but in JDBCAppender:

public void setSql(String s) {
    sqlStatement = s;
    if (getLayout() == null) {
        this.setLayout(new PatternLayout(s));
    }
    else {
        ((PatternLayout)getLayout()).setConversionPattern(s);  //Point1
    }
}

So if you use JDBCAppender like this:

log4j.appender.JDBC=org.apache.log4j.jdbc.JDBCAppender    
log4j.appender.JDBC.layout=org.apache.log4j.EnhancedPatternLayout
log4j.appender.JDBC.sql=INSERT INTO email_send_error(insert_date, level, location, message, stacktrace) VALUES (now(), '%p', '%C,%L', '%m', '%throwable{short}')

will throw a ClassCastException in Point1:

Caused by: java.lang.ClassCastException: org.apache.log4j.EnhancedPatternLayout cannot be cast to org.apache.log4j.PatternLayout
at org.apache.log4j.jdbc.JDBCAppender.setSql(JDBCAppender.java:330)

EDIT (after in-depth research):
Use log4j.appender.JDBC.layout.ConversionPattern instead of log4j.appender.JDBC.sql, will avoid above ClassCastException:

 log4j.appender.JDBC.layout.ConversionPattern=INSERT INTO email_send_error(insert_date, level, location, message, stacktrace) VALUES (now(), '%p', '%C,%L', '%m', '%throwable{short}')

and don't forget record the throwed exception to logger:

logger.error(String errorMsg, Throwabe e); // Dont forget e