Logging SQL Statements in EAP 6.1

[aaroncirillo]

I am attempting to log all outbound SQL statements for a datasource in EAP 6.1 so I can debug a switchyard project I am working on. According to the documentation I read this should be possible by modifying the datasource you want to log and adding a new logger. So here is what I changed in my standalone.xml file:

I added this to the logging subsystem section:

               <logger category="jboss.jdbc.spy">

                     <level name="TRACE"/>

               </logger>

I updated my datasource to enable the spy logging:

                  <datasource jta="false" jndi-name="java:/jdbc/GenesisDS" pool-name="GenesisDS" enabled="true" spy="true" use-ccm="false">

                    <connection-url>jdbc:oracle:thin:@xxxxx:1537:GENESIS</connection-url>

                    <driver-class>oracle.jdbc.OracleDriver</driver-class>

                    <driver>ojdbc6-11.2.0.jar</driver>

                    <security>

                        <user-name>xxxxx</user-name>

                        <password>xxxxxx</password>

                    </security>

                    <validation>

                        <validate-on-match>false</validate-on-match>

                        <background-validation>false</background-validation>

                    </validation>

                    <statement>

                        <share-prepared-statements>false</share-prepared-statements>

                    </statement>

                </datasource>

After I made these changes I restarted EAP and I expected to see the sql statements being made by this datasource logged in the server.log file, however they are not showing up. Am I doing something incorrectly here? Also, if there is a Switchyard way to do this I am open to that too. I am using a SQL binding on a reference in my project, so if that can somehow log the SQL it is using that would be fine.

Thanks

[aaroncirillo]

I got some help on the wildfly IRC channel. The problem was that my console handler was set to INFO, so I guess it was filtering out any TRACE messages that were being generated. This is the modification I made in standalone.xml:

           <console-handler name="CONSOLE">

                <level name="TRACE"/>

                <formatter>

                    <pattern-formatter pattern="%K{level}%d{HH:mm:ss,SSS} %-5p [%c] (%t) %s%E%n"/>

                </formatter>

            </console-handler>

Thanks to double_p in #wildfly on freenode for the answer here. I can now see all of the SQL being generated on the datasource I enabled the spy logging on.

[masummymesingh]

i am facing similar problem ?

https://developer.jboss.org/thread/250027

pls help me ?

[rsandoz1]

I know this is old, but it is the first thing I found on google with "logging SQL with jboss".  Anyway, spy works great, but it floods the log way too much for an enterprise level application.  One thing I noticed is a line for every ResultSet item.  In case you, like me, don't want this, I use a tag called <filter-spec> to clean these out:

<console-handler name="CONSOLE">

   <filter-spec value="not(match(&quot;\\[ResultSet\\]&quot;))"/>

[andey]

Hi,

In order to enable the above logging in EAP 6, add the following <logger> configuration inside logging subsystem (in standalone(-*).xml for standalone mode /domain.xml` for domain mode):

~~~

<subsystem xmlns="urn:jboss:domain:logging:1.2">
  <console-handler name="CONSOLE">
  ...(snip)...
  </console-handler>
  <periodic-rotating-file-handler name="FILE" autoflush="true">
  ...(snip)...
  </periodic-rotating-file-handler>

  ...(snip)...

  <!-- ADDED THE FOLLOWING -->
  <logger category="org.hibernate.SQL">
  <level name="DEBUG"/>
  </logger>
  <logger category="org.hibernate.type">
  <level name="TRACE"/>
  </logger>
  <!-- ADDED ABOVE LINES-->

  <root-logger>
  ...(snip)...
  </root-logger>
  </subsystem>

~~~

You can also:

JBoss EAP 6 provides a very efficient way of enabling the spy logging which can log all the information related to the connections/statements and any method which is invoked on the JDBC Objects.

Add the category jboss.jdbc.spy in the logging subsystem related to your configuration file standalone*.xml or if you run in domain mode to the related profile of the domain.xml.

Add the spy flag to the datasource

The datasource contain an attribute to enable the jdbc logging. The attribute spy is set to false by default.

~~~

<datasource jndi-name="java:boss/datasources/MySQLDS" pool-name="MySQLDS_Pool" enabled="true" spy="true">

~~~

Note: It is possible to use separate datasource configurations, so it is important to check if your server contains such separate files for datasource configuration in development, then you will need to add this parameter on that separated file.

Enable the logging

~~~

<logger category="jboss.jdbc.spy">

   <level name="TRACE"/>

</logger>

~~~

Use CLI to enable monitoring

To enable the datasource spy flag and add the logger the following CLI commands can be used.

~~~

/subsystem=logging/logger=jboss.jdbc.spy:add(level=TRACE)

# optional to enable log output in the console

/subsystem=logging/console-handler=CONSOLE:write-attribute(name=level, value=TRACE)

/subsystem=datasources/data-source=MySQLDS_Pool:write-attribute(name=spy, value=true)

~~~

Remember that the commands must use the profile=ProfileName prefix if the domain mode is used.

You need to restart the server to enable the spy logging. Remember that the DEBUG logging will not shown at the CONSOLE by default.