Enable SQL query logging in Jira server
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.
Solution
To temporarily enable SQL logging:
- Log into JIRA as an Administrator.
Go to Administration > System > Logging and Profiling.
OfBiz:
Enable SQL logging below:
The resulting logs will be in
<JIRA HOME>/log/atlassian-jira-sql.log
.
Active Objects:
Search for
net.java.ao.sql
and set it toTRACE
:- In the logging page, click at the Configure option as shown below. Add the package com.querydsl.sql and set it to TRACE.
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:
- To have JIRA persist the settings they can be changed in the
log4j.properties
file. For example: OfBiz, set this to
OFF
toON
:log4j.logger.com.atlassian.jira.ofbiz.LoggingSQLInterceptor = OFF, sqllog
Active Objects, set
WARN
toTRACE
.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.