How to trace SQL requests for H2 in-memory databases?

Question

How can I trace SQL requests for H2 in-memory embedded databases?

The documentation says the trace file is located in the same directory as the database file, but for in-memory embedded database I have no database directory (under Windows).

I tried to redirect traces to a given file with slf4j/logback by using

TRACE_LEVEL_FIle=4;TRACE_LEVEL_SYSTEM_OUT=3

in the database URL and the following logback.xml config file but with no luck:

<configuration scan="true">
  <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <file>mylogfile.log</file>
    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
      <fileNamePattern>%d{yyyyMMdd}_mylogfile.log</fileNamePattern>
    </rollingPolicy>
    <encoder>
      <pattern>%date{yyyyMMdd HH:mm:ss} %contextName[%thread] %level %logger{0} - %msg%n</pattern>
    </encoder>
  </appender>

  <logger name="com.myapp" level="WARN">
    <appender-ref ref="FILE" />
  </logger>

  <logger name="h2database" level="TRACE">
    <appender-ref ref="FILE" />
  </logger> -->

  <root level="WARN">
    <appender-ref ref="FILE" />
  </root>
</configuration>

My logback config file works correctly for other logs.

Any idea?

See also http://stackoverflow.com/questions/6124886/h2-and-query-logging — Vadzim, Jan 20 '17 at 12:28

score 20 · Accepted Answer · edited May 20 '21 at 12:06

20

I'm not completely sure, but according to my test it seems if you remove TRACE_LEVEL_SYSTEM_OUT=3 then it would work. Could you try this? Example database URL:

jdbc:h2:mem:test;TRACE_LEVEL_FILE=4

instead of

jdbc:h2:mem:test;TRACE_LEVEL_FILE=4;TRACE_LEVEL_SYSTEM_OUT=3

http://www.h2database.com/html/features.html#trace_options

edited May 20 '21 at 12:06

gavenkoa

45,285
19
251
303

answered Mar 17 '12 at 09:28

Thomas Mueller

48,905
14
116
132

1

Thank you. I managed to get it work correctly with a unit test. The problem seems to come from the application I'm using which connects directly to the database and its logging configuration (using commons logging) that makes the logs always redirected to console and not to the log file as I want. – user1274500 Mar 21 '12 at 09:50

Yura · Answer 2 · 2021-02-01T17:47:27.200

-1

The maximum trace level is 3 (check source code), that's why it failed in your case

edited Feb 01 '21 at 17:47

answered Dec 08 '19 at 22:11

Yura

1,733
1
20
19

In 1.4.200 this is definitely not true. This is from the [documentation](http://www.h2database.com/html/features.html#trace_options): `To enable SLF4J, set the file trace level to 4 in the database URL`. – Roman-Stop RU aggression in UA Oct 13 '21 at 12:43

How to trace SQL requests for H2 in-memory databases?

2 Answers2