Log4j and MySQL

When configuring log4j with an appender to log to a MySQL database there are some nuances you might need to be aware of.

First, here is what the relevant settings in the log4j.properties file should look like:

# Set root logger level to DEBUG and its only appender to DB.
log4j.rootLogger=DEBUG, DB

# The database logger
log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.DB.URL=jdbc:mysql://myserver:3306/mydb?autoReconnect=true
log4j.appender.DB.driver=com.mysql.jdbc.jdbc2.optional.MysqlDataSource
log4j.appender.DB.user=myuser
log4j.appender.DB.password=mypassword
log4j.appender.DB.sql=INSERT INTO mytable (mycolmn) VALUES ("%m")

Two things to note:

1. I had to specify the driver property because log4j couldn't automatically determine that or was defaulting to an incorrect driver class name. I was using log4j 1.2.12 and MySQL Connector/J version 5.1.5
2. The values being inserted in the database need to be enclosed in double quotes (") as opposed to the normal single quote (') you would use in SQL syntax

Reference:

http://forum.java.sun.com/thread.jspa?threadID=5227993&tstart=105