log4j JDBCAppender rotate table name

I have successfully configured an application that uses log4j for it's logging to log into a MySQL database. (Using org.apache.log4j.jdbc.JDBCAppender).

I also have some perl applications that log into the database as well. My perl apps are setup so that the name of the database table changes every month (log_2010_11, log_2010_10 etc). At the end of each month, I run reporting scripts on the month just completed, dump the table to an external file (which gets compressed and archived), and then drop the table. This way the total size of the logging database stays within sensible limits.

I would like to do the same with log4j, but there does not appear to be a log4j appender suitable for the purpose.

Is it possible to do something like this:

log4j.appender.SQ=org.apache.log4j.jdbc.JDBCRollingAppender

log4j.appender.SQ.Driver=com.mysql.jdbc.开发者_运维知识库Driver

log4j.appender.SQ.URL=jdbc:mysql://localhost:3306/logs_{%year}_{%month}

Thank you.

I figured out how to do this:

log4j.appender.SQ=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.SQ.Driver=com.mysql.jdbc.Driver
log4j.appender.SQ.URL=jdbc:mysql://localhost:3306/logs
log4j.appender.SQ.sql=INSERT INTO accesslog_%d{yyyy_MM} (date, time, tz, ...

It appears you can just put date format strings into the SQL statement, and JDBCAppender will expand them and log into the coresponding table.

However, it will not create new tables at the start of the new month, so currently I have to manualy create the tables beforehand, which is far from ideal.

You'd have to write your own appender to do this.

Another option would be to stay with the existing appender and do this:

You have a table in your database named log. Why not make a Perl script that makes a new table at end of every month, let's say log_12 for December, copies everything from log to log_12 and then delete everything from log? That way you don't have to mess around with making another appender.

How about a script to run monthly and dump that particular table into a back up file and then zip it for archiving. Upon complete, truncate the table or delete rows within date range.