Log4j: JDBCAppender error for MySQL

Question

How can I fix the error?

log4j.properties

# Define the root logger with appender file log4j.rootLogger = ALL, DB # Define the DB appender log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender # Set JDBC URL log4j.appender.DB.URL=jdbc:mysql://localhost/youtube # Set Database Driver log4j.appender.DB.driver=com.mysql.jdbc.Driver # Set database user name and password log4j.appender.DB.user=root log4j.appender.DB.password=root # Set the SQL statement to be executed. log4j.appender.DB.sql=INSERT INTO logs VALUES('%x','%d','%C','%p','%m') # Define the layout for file appender log4j.appender.DB.layout=org.apache.log4j.PatternLayout 

jdbcAppender.java

 public class jdbcAppender{ /* Get actual class name to be printed on */ static Logger log = Logger.getLogger(jdbcAppender.class.getName()); public static void main(String[] args) throws IOException,SQLException{ log.debug("Debug"); log.info("Info"); } } 

Database

Error

Answer 1

Depending on the version of MySQL you are using, you need to define a DATED field DATED that it can take a DATETIME value of fractions of a second. By default, DATETIME is set to YY-MM-DDDD HH:MM:SS .

Read about it here: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html#date-and-time-standard-sql-literals

Another problem you may run into is that MySQL expects the fraction separator to be a period ( . ), Not a comma. The exception stack trace shows the comma as a separator, which I assume is derived from the locale of your system.

Answer 2

You can modify the insert statement to change the date string to the corresponding datetime, for example:

 log4j.appender.DB.sql=INSERT INTO logs VALUES('%x',STR_TO_DATE( '%d', '%Y-%M-%d %H:%i' ),'%C','%p','%m') 

This is not an appropriate format, but simply the idea of ​​a different approach.

I'm just looking at mysql millisecond processing, but I see that the date string: '2012-11-17 16: 07: 29,995' is 995 parts of a millisecond or is 29 995 parts of a decimal second?

Answer 3

 log4j.appender.DB.sql=INSERT INTO logs VALUES('%x','%d','%C','%p','%m') 

in

 log4j.appender.DB.sql=INSERT INTO logs VALUES('%x','%d{yyyy-MM-dd HH:mm:ss}','%C','%p','%m') 

Answer 4

You can modify the insert statement to convert the date string to the appropriate format. Sort of:

 log4j.appender.DB.sql=INSERT INTO logs VALUES('%x','%d{yyyy-MM-DD HH:MM:SS}','%C','%p','%m') 

Hope this helps.