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.

Solution

To temporarily enable SQL logging:

1. Log into JIRA as an 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:

1. To have JIRA persist the settings they can be changed in the log4j.properties file. For example:

2. OfBiz, set this to OFF to ON:

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

3. Active Objects, set WARN to TRACE.

   log4j.logger.net.java.ao.sql  = WARN, console, filelog
   
   
   # Append the following at the end of the file
   log4j.logger.com.querydsl.sql = TRACE, console, sqllog
   log4j.additivity.com.querydsl.sql = false

File Size Configuration

The default configuration will allow for 5 rolled log files of 20MB in size (~100MB total). To adjust this setting, update the log4j.properties file.