1

I have a spring based webapp using Hibernate to query mysql. When I enable trace logging by using log4j.logger.org.hibernate.type=TRACE, all queries with their parameters are logged to the console. This makes the webapp slow for search queries.

I need to log only modifications to the database. Preferably the insert/update query issued to the mysql server. What is the simplest and most reliable way of doing this?

Can I just ask log4j to log only insert queries?

P6SPY seems overkill for my purpose. http://sourceforge.net/projects/p6spy/

Rohit Banga
  • 18,458
  • 31
  • 113
  • 191
  • Check this : http://stackoverflow.com/a/436687/174184 and this : http://stackoverflow.com/a/639401/174184 . Combination of the two, might help you to an extent. – TJ- Jan 07 '13 at 18:16
  • possible duplicate of [Configuring Hibernate logging using Log4j XML config file?](http://stackoverflow.com/questions/436276/configuring-hibernate-logging-using-log4j-xml-config-file) – Ralph Jan 07 '13 at 18:33
  • Not really. When I set org.hibernate.SQL to ALL/TRACE and org.hibernate.type to TRACE it logs everything including values of all the returned rows. I just want to log the SQL queries with the parameters and that too just for inserts and updates. Otherwise things slow down a lot. I can't find a way to restrict the logging to just inserts and updates as I requested in the original question. – Rohit Banga Jan 07 '13 at 19:42

2 Answers2

2

log4j 2 extended filter support. You may want to give the RegexFilter a try.

For log4j 1.2.x there's StringMatchFilter which is part of the "extras" package.

Here is a quick example found online:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
  <appender name="CustomAppender" class="org.apache.log4j.RollingFileAppender">
    <param name="File" value="custom.log"/>
    <param name="Append" value="true"/>
    <param name="MaxFileSize" value="5000KB"/>
    <param name="maxBackupIndex" value="5"/> 
          <layout class="org.apache.log4j.PatternLayout">
                  <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss} [%t] %-5p - %m%n" />
          </layout>

          <filter class="org.apache.log4j.varia.StringMatchFilter">
                  <param name="StringToMatch" value="Here is DEBUG" />
                  <param name="AcceptOnMatch" value="true" />
          </filter>
  </appender>

  <root>
    <appender-ref ref="CustomAppender"/>
  </root>
</log4j:configuration>

However, AFAIU it does not allow you to match wildcards which means dead end in your case I guess. If you dig into the filters source code it should be pretty easy to write your own.

Marcel Stör
  • 22,695
  • 19
  • 92
  • 198
  • Would it be better if I just logged at the mysql level using `--general-log`? That way the actual sql query will be logged but the results will not be logged. If a SQL query returns 1000 results. Then dumping them all to the console makes my code very slow but it will be useful to dump just the Single query generating the results? In fact I just need to log inserts and updates which do not return results. But I don't see an easy way to log just inserts and updates with parameters without logging selects. What do you think about MySQL logging? – Rohit Banga Jan 07 '13 at 23:11
  • @iamrohitbanga, "Then dumping them all to the console makes my code very slow" - do you know that for sure i.e. did you actually measure that? I doubt it has a noticeable performance impact. "I just need to log inserts and updates" - I know that, that's why I directed you to log4j filters, they will fulfill your requirements. Of course you can always use database logging but then you have to search in two different log files for analysis. And you still have not fulfilled your requirement (not logging queries). – Marcel Stör Jan 08 '13 at 07:20
  • Yes it is definitely slower. Noticably slower for my sample data. I will look into log4j filters if logging with mysql does not go well. Thanks. – Rohit Banga Jan 09 '13 at 00:54
  • @iamrohitbanga,"if logging with mysql does not go well" - I'm sure it goes well but you cannot filter. – Marcel Stör Jan 09 '13 at 06:03
0

you can apply your filtering on: org.hibernate.type.descriptor.sql.BasicBinder instead

user2401885
  • 1