Jenkins logging directly to MySQL database

There are cases if you like to send the log output from Jenkins directly to a database for better querying or just as a step for further processing them.

As Jenkins is not really admin-friendly with redirecting logs, we have to use the „init.d“ feature of Jenkins (https://wiki.jenkins-ci.org/display/JENKINS/Configuring+Jenkins+upon+start+up). Thus you can simply put an init.groovy file into the jenkins home directory. In this init file you can do a lot of stuff like setting some variables, disabling the sending of usage statistics and so on. And you can hook into the LogManager of Jenkins: https://wiki.jenkins-ci.org/display/JENKINS/Logging

In this example we are using Jenkins 1.651.3

Jenkins is using the java.util.logging package, so first of all we need a handler for sending LogRecords to a MySQL database:

@Grapes([
    @Grab(group='mysql', module='mysql-connector-java', version='5.1.40'),
    @GrabConfig(systemClassLoader = true)

])

import java.util.logging.*;
import java.sql.*;

class MySqlHandler extends Handler {

    def driverString = "com.mysql.jdbc.Driver";
    def insertSQL = "insert into log (level,logger,message,sequence,sourceClass,sourceMethod,threadID,timeEntered) values (?,?,?,?,?,?,?,?)";
    def connectionString;
    def connection;
    def prepClear;
    def prepInsert;

    public MySqlHandler(String connectionString) {
        try {
            this.connectionString = connectionString;
            Class.forName(driverString);
            connection = DriverManager.getConnection(connectionString);
            prepInsert = connection.prepareStatement(insertSQL);
            prepClear = connection.prepareStatement(clearSQL);
        } catch (Exception e) {
            System.err.println("Error on open: " + e.getMessage());
        }
    }

    public void publish(LogRecord record) {
        try {
            prepInsert.setInt(1,record.getLevel().intValue());
            prepInsert.setString(2,truncate(record.getLoggerName(),63));
            prepInsert.setString(3,truncate(record.getMessage(),255));
            prepInsert.setLong(4,record.getSequenceNumber());
            prepInsert.setString(5,truncate(record.getSourceClassName(),63));
            prepInsert.setString(6,truncate(record.getSourceMethodName(),31));
            prepInsert.setInt(7,record.getThreadID());
            prepInsert.setTimestamp(8,new Timestamp(System.currentTimeMillis()) );
            prepInsert.executeUpdate();
        } catch ( SQLException e ) {
            System.err.println("Error on open: " + e.getMessage());
        }
    }

    public void close() {
        try {
            if ( connection!=null )
                connection.close();
        } catch ( SQLException e ) {
            System.err.println("Error on close: " + e);
        }
    }

    public void clear() {
        // nop
    }

    public void flush() {
        // nop
    }

    private String truncate(String str,int length) {
        if (str.length() < length) { return str; }
        return str.substring(0,length);
    }

}


// try the above logger:
Logger logger = Logger.getLogger("the-awesome-logger")
logger.addHandler(new MySqlHandler("jdbc:mysql://localhost:3306/jenkinslogs?user=user1&password=topSecret&database=log"))
logger.info ("may the force be with you")

And of course we need the database with user „user1“ and password „topSecret“:

CREATE TABLE log (
  level integer NOT NULL,
  logger varchar(64) NOT NULL,
  message varchar(255) NOT NULL,
  sequence integer NOT NULL,
  sourceClass varchar(64) NOT NULL,
  sourceMethod varchar(32) NOT NULL,
  threadID integer NOT NULL,
  timeEntered datetime NOT NULL)

Running the groovy file from above (while mighty grab tool cares for our dependencies):

$ groovy -Dgroovy.grape.report.downloads=true mysqlhandler-test.groovy
$ echo "SELECT * FROM log" | mysql jenkinslogs -uuser1 -ptopSecret
level   logger  message sequence        sourceClass     sourceMethod    threadID        timeEntered
800     the-awesome-logger      may the force be with you       0       java_util_logging_Logger$info$1 call    1       2017-01-27 23:55:23

So far ok. Now we have a piece of Groovy-Code that is able to send LogRecords into a MySQL database. Time to connect it to Jenkins: We need an init.groovy file in jenkins home directory containing the MySQL-handler:

import java.util.logging.*;
import java.sql.*;

class MySqlHandler extends Handler {

    def driverString = "com.mysql.jdbc.Driver";
    def insertSQL = "insert into log (level,logger,message,sequence,sourceClass,sourceMethod,threadID,timeEntered) values (?,?,?,?,?,?,?,?)";
    def connectionString;
    def connection;
    def prepClear;
    def prepInsert;

    public MySqlHandler(String connectionString) {
        try {
            this.connectionString = connectionString;
            Class.forName(driverString);
            connection = DriverManager.getConnection(connectionString);
            prepInsert = connection.prepareStatement(insertSQL);
            prepClear = connection.prepareStatement(clearSQL);
        } catch (Exception e) {
            System.err.println("Error on open: " + e.getMessage());
        }
    }

    public void publish(LogRecord record) {
        try {
            // jenkins stores the build id and the status in params field of the logrecored
            def msg = record.getMessage()
            def msgParams = record.getParameters()
            def buildNameAndNumber = msgParams[0]
            def buildStatus = msgParams[1]
            def completeMsg = "${buildNameAndNumber}: ${msg}: ${buildStatus}"

            prepInsert.setInt(1,record.getLevel().intValue());
            prepInsert.setString(2,truncate(record.getLoggerName(),63));
            prepInsert.setString(3,completeMsg);
            prepInsert.setLong(4,record.getSequenceNumber());
            prepInsert.setString(5,truncate(record.getSourceClassName(),63));
            prepInsert.setString(6,truncate(record.getSourceMethodName(),31));
            prepInsert.setInt(7,record.getThreadID());
            prepInsert.setTimestamp(8,new Timestamp(System.currentTimeMillis()) );
            prepInsert.executeUpdate();
        } catch ( SQLException e ) {
            System.err.println("Error on open: " + e.getMessage());
        }
    }

    public void close() {
        try {
            if ( connection!=null )
                connection.close();
        } catch ( SQLException e ) {
            System.err.println("Error on close: " + e);
        }
    }

    public void clear() {
        // nop
    }

    public void flush() {
        // nop
    }

    private String truncate(String str,int length) {
        if (str.length() < length) { return str; }
        return str.substring(0,length);
    }
}

def RunLogger = LogManager.getLogManager().getLogger("hudson.model.Run")
MySqlHandler handler = new MySqlHandler("jdbc:mysql://localhost:3306/jenkinslogs?user=user1&password=topSecret&database=log");
RunLogger.addHandler(handler)

As you can see here, we cannot use Grab inside an init.groovy file. This is because of the Jenkins own managed Classpath. A quick workaround to provide the groovy script with the mysql-driver is to put mysql-connector-java--bin.jar to $JENKINS_HOME/war/WEB-INF/lib directory when running the jenkins with its integrated winstone server. If you are running Jenkins inside Tomcat there might be other ways to add the mysql-driver to the classpath where the init.groovy can access it.

Looking at the database you can see, that the jenkins log message was successfully persisted in database:

$ echo "SELECT * FROM log" | mysql jenkinslogs -uuser1 -ptopSecret
level   logger  message sequence        sourceClass     sourceMethod    threadID        timeEntered
800     hudson.model.Run        'testjob #7: {0} main build action completed: {1}: SUCCESS'    24      hudson.model.Run        execute 31      2017-01-28 0:7:07

Note the additional processing of parameters and message in the MySQL-Handler: Jenkins uses the parameters field. You might adapt the MySQL-handler to your needs. It would also be possible to store the job name and status in separate fields for better crawling the tables.

All over: This is obviously not the solution with the best performance, but it’s quite simple and one step further then just obtain some logfiles from disk. And it shows how powerful this groovy initialization mechanism of Jenkins can be 🙂

References: