Thursday, November 29, 2012

Log4jdbc+Grails+Tomcat7

As your Grails application grows in size and popularity you will find yourself with performance and concurrency problems to overcome. As part of this process you will likely find yourself debugging the raw sql sent to the database. Hibernate comes with functionality to do this, but it is not very most user friendly. The most popular tool for sql debugging is P6spy, but an up and coming alternative is Log4jdbc. Log4jdbc works similar to P6Spy in that you replace your usual sql driver with one provided by the tool.
 //driverClassName = "org.h2.Driver"
  driverClassName = "net.sf.log4jdbc.DriverSpy"

You then have to make a change to the database connection url. You must add log4jdbc to the protocol component. Log4jdbc should then be able to detect for which driver it is proxying for.
jdbc:log4jdbc:mysql://localhost/mydatabase
Next you need to add appropriately to the logging configuration of your Grails app. Log4jdbc provides lots of options. I like to use jdbcsqltiming; it captures the sql statements, and their duration:
info 'jdbcsqltiming'
If you try the above in your development environment, it should work well. Now we move to trying to add it to a production / staging environment which uses Tomcat7 and MySql. If you simply try to apply the same settings as above, you might see the following error:
ClassNotFoundException net.sf.log4jdbc.DriverSpy
The reason for this is due to how Tomcat7 requires jdbc drivers to be placed in a common lib folder such as $TOMCAT_HOME/lib. So like your typical database driver, log4jdbc's must also be placed in the common lib folder. Unfortunately the log4jdbc driver has a number of logging related dependencies, so you must also place these in the common lib folder. Here is what I added to $TOMCAT_HOME/lib :
commons-logging-1.1.1.jar
jcl-over-slf4j-1.6.2.jar
jul-to-slf4j-1.6.2.jar
log4j-1.2.16.jar
log4jdbc4-1.2.jar
slf-api-1.6.4.jar
slf4j-log4j12-1.6.4.jar
You might see some warnings about your path containing multiple SLF4J bindings, but regardless your application should now be working with sql statements flying into your logs.

No comments:

Post a Comment