Enable SQL query logging in Jira server

Still need help?

The Atlassian Community is here for you.

Ask the community

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 > Troubleshooting and Support > 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. Add a new package; com.querydsl.sql and set it to TRACE.

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

(info) 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.  


Last modified on Sep 25, 2019

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.