Sunday, March 15, 2015

Troubleshooting WSO2 server database operations with log4jdbc-log4j2

If you are using WSO2 Carbon based servers and are facing issues related to the database, there are few steps you should take in order to rectify those issue. Since Carbon 4.2.0 based products use Tomcat JDBC Connection Pool, first thing you could do is to try tuning the datasource parameters in the master-datasources.xml (or *-datasources.xml) file located in the ${CARBON_HOME}/repository/conf/datasources/ directory. Some of the parameters you might want to double check is;

  1. Set the "validationQuery" parameter 
  2. Set "testOnBurrow" to "true"
  3. Set a "validationInterval" and try tuning it to fit your environment
For a detailed explanation about those properties and also addition parameters that can be used to tune the JDBC pool, please visit the Tomcat site listed above.

Even though these parameters might help fix some of the JDBC issues you'd encounter, there might be instances where you'd want additional information to understand what's going on between the WSO2 server and the underlying database. 

We can use log4jdbc-log4j2 which is an improvement of the log4jdbc to do an in depth analysis JDBC operations between the WSO2 server and the database. In this post I'll be explaining how to configure log4jdbc-log4j2 with WSO2 servers.  

To setup a WSO2 server to log4jdbc-log4j2, follow the steps below (In the post I am assuming that the server has already been configured to point to the external database and setup with the necessary JDBC driver etc)
  1. Download log4jdbc-log4j2 jar and copy it to the ${CARBON_HOME}/repository/components/lib directory. 
  2. Prepend "jdbc:log4" to the JDBC url, <url> parameter in the datasource configuration, so the url would look like;
  3. jdbc:log4jdbc:mysql://localhost:3306/governance
  4. Change the "driverClassName" to "net.sf.log4jdbc.sql.jdbcapi.DriverSpy" as follows;
  5. net.sf.log4jdbc.sql.jdbcapi.DriverSpy
  6. To direct the log4jdbc-log4j2 output to a separate log file, add the following entries to the file located in the conf/ directory
  7. log4j.logger.jdbc.connection=DEBUG, MySQL
    log4j.logger.jdbc.audit=DEBUG, MySQL
  8. Finally, you need to start the server with the system property;

Note: You can set the system property in the file located in the bin/ directory for ease of use.

Now that you have the log4jdbc-log4j library and the required configurations in place, you can start using the server. The JDBC debug logs will be printed in the mysql-profile.log file located in the logs/ directory. There are six different loggers you can use to troubleshoot different types of problems, check section 4.2.2 of this page for more information on the different logging options.

Good luck !!!