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

enter image description here

Error

enter image description here

score 3 · Answer 1 · answered Nov 17 '12 at 21:24

Depending on the MySQL version that you're using, you need to define the DATED field so it can accept a DATETIME value that contains fractions of a second. By default, DATETIME accepts values as 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 the fact that MySQL expects the fraction's delimiter to be a dot (.), rather than a comma. Your exception's stack trace shows a comma as the delimiter, which I believe is derived from your system's locale.

Sinisha Mihajlovski · Answer 2 · 2012-11-17T22:22:12.033

2

You can change the insert statement to change the date string into an appropriate datetime something like:

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

This is not the appropriate format, but just an idea of another approach.

I'm just looking into mysql millisecond handling, but I see the date string is: '2012-11-17 16:07:29,995', Is the 995 part milliseconds, or is the 29,995 part a decimal second?

edited Nov 17 '12 at 22:22

answered Nov 17 '12 at 22:14

Sinisha Mihajlovski

1,811
1
20
34

Another way to try is, override the JDBCAppender, and override the execute method: class MyJDBCAppender extends JDBCAppender { protected void execute(String sql) { super.execute(modifySqlStringToConformToMySql(sql)); }} and use that appender in your log4j.properties file – Sinisha Mihajlovski Nov 17 '12 at 22:46
I dont know how critical it is, but you can also go with the solution of configuring a default value for the column DATED to be the current date: http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column and define the insert statement without the date: log4j.appender.DB.sql=INSERT INTO logs (USER_ID, LOGGER, LEVEL, MESSAGE) VALUES('%x','%C','%p','%m') – Sinisha Mihajlovski Nov 17 '12 at 23:10

score 1 · Answer 3 · answered Feb 04 '15 at 05:44

1

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

into

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

answered Feb 04 '15 at 05:44

iCrazybest

2,935
2
24
24

Brice · Answer 4 · 2015-01-05T11:44:10.260

0

You can change the insert statement to convert the date string into an appropriate format. Something like:

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

Hope it helps.

edited Jan 05 '15 at 11:44

answered Jan 05 '15 at 10:20

Brice

1
1

Date format is not correct. MM = month, SS = milliseconds, så the time will be displayed incorrect. – homaxto Jan 09 '17 at 10:30

log4j: JDBCAppender Error for MySQL

4 Answers4