Purpose

This KB article instructs how to enable detailed SQL logging inside of JIRA. This can be used for:

• Troubleshooting SQL problems with the database.
• Reverse-engineering GUI actions to determine what SQL is submitted for certain user actions.

This is broken down into two sections, OfBiz and Active Objects. They are both persistence ORM frameworks which means they're libraries that can be used to manage the SQL sent to different database systems.

• OfBiz: Used by standard Jira functionality.
• Active Objects: Used by plugins, both third-party and bundled. Active Objects tables can be identified as they're named AO_<code>_TABLENAME.

Environment

Jira Server/Data Center on any version from Jira 8.0.0.

Solution

To temporarily enable SQL logging

1. Log into Jira as a System Administrator.

2. Go to Administration > System > Logging and Profiling.

3. OfBiz: 

   1. Enable SQL logging below:

      

   2. The resulting logs will be in <JIRA HOME>/log/atlassian-jira-sql.log.

4. Active Objects:

1. Search for net.java.ao.sql and set it to TRACE:

   

2. In the logging page, click at the Configure option as shown below. Add the package com.querydsl.sql and set it to TRACE:
   

3. The resulting logs will be in <JIRA HOME>/log/atlassian-jira.log.

 These settings will be lost on restart, as they're stored in memory.

To permanently enable SQL logging

Solution For Jira versions below 9.5.0

To have Jira persist the DEBUG settings, they need to be changed in the log4j.properties file as below:

1. Look for the line below, ands change OFF to ON:

   log4j.logger.com.atlassian.jira.ofbiz.LoggingSQLInterceptor = OFF, sqllog

2. For the Active Object java.ao.sql, look for the lines below and set WARN to TRACE:


   log4j.logger.net.java.ao.sql  = WARN, console, filelog

   


3. For the Active Object com.querydsl.sql, append the following lines at the end of the file:

   log4j.logger.com.querydsl.sql = TRACE, console, sqllog
   log4j.additivity.com.querydsl.sql = false

Solution For Jira versions from 9.5.0

As mentioned in the Jira 9.5.0 Release notes, Log4j was upgraded to the version 2.17.2, which means that, to enable DEBUG packages permanently, a different file needs to be modified.

To have Jira persist the DEBUG settings, they need to be changed in the log4j2.xml file as below:

1. Look for the line below, ands change OFF to ON:

   <Logger name="com.atlassian.jira.ofbiz.LoggingSQLInterceptor" level="OFF" additivity="false">
       <AppenderRef ref="sqllog"/>
   </Logger>

2. For the Active Object java.ao.sql, look for the lines below and set WARN to TRACE:


   <Logger name="net.java.ao.sql" level="WARN" additivity="false">
       <AppenderRef ref="filelog"/>
   </Logger>

   


3. For the Active Object com.querydsl.sql, append the following lines at the end of the file:

   <Logger name="com.querydsl.sql" level="TRACE" additivity="false">
       <AppenderRef ref="sqllog"/>
   </Logger>