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.


It's important to note enabling SQL logging on production instances may cause a performance degradation on Jira, impacting the ability of end-users to conduct their work.

When possible, work on a lower environment or outside business hours to minimize impact on end-users. If that's not possible, you may consider enabling DEBUG level logs instead of TRACE to reduce the amount of logs generated and also slow down how often these logs will rotate.


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.

(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 Apr 3, 2023

Was this helpful?

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