JIRA Developer Documentation : Logging JIRA SQL queries

Enabling Application Logging

To debug an SQL query run by JIRA, modify the following line in <install-directory>/atlassian-jira/WEB-INF/classes/log4j.properties:

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

Change it to:

1
2
log4j.logger.com.atlassian.jira.ofbiz.LoggingSQLInterceptor = ON, sqllog

The logging will be output to <jira home directory>/log/atlassian-jira-sql.log after a restart. See Where are the application server logs? for more information on where the logs are located.

Adding Database SQL Query Logging

Often, good logging is available from native database tools. If you've already identified a questionable SQL query, the application logging will take you to the next step, as it logs the Java stack trace associated with the SQL query.

Assessment

Logging output should look like:

1
2
WHERE ENTITY_NAME='jira.properties' AND ENTITY_ID='1' AND PROPERTY_KEY='License Hash 1 Text'"
2010-01-22 11:42:35,897 main     call stack
   ...
   at com.opensymphony.module.propertyset.ofbiz.OFBizPropertySet.findPropertyEntry(OFBizPropertySet.java:223)
   at com.opensymphony.module.propertyset.ofbiz.OFBizPropertySet.getType(OFBizPropertySet.java:107)
   at com.atlassian.jira.propertyset.PropertySetCache.cloneProperty(PropertySetCache.java:319)
   at com.atlassian.jira.propertyset.PropertySetCache.bulkLoad(PropertySetCache.java:303)
   at com.atlassian.jira.propertyset.JiraCachingPropertySet.init(JiraCachingPropertySet.java:834)
   at com.opensymphony.module.propertyset.PropertySetManager.getInstance(PropertySetManager.java:45)
   at com.opensymphony.module.propertyset.PropertySetManager.getInstance(PropertySetManager.java:22)
   at com.atlassian.jira.config.properties.PropertiesManager.loadPropertySet(PropertiesManager.java:83)
   at com.atlassian.jira.config.properties.PropertiesManager.<init>(PropertiesManager.java:29)
   at com.atlassian.jira.config.properties.PropertiesManager.getInstance(PropertiesManager.java:39)
   at com.atlassian.jira.config.properties.ApplicationPropertiesImpl.getPropertiesManager(ApplicationPropertiesImpl.java:69)
   at com.atlassian.jira.config.properties.ApplicationPropertiesImpl.getString(ApplicationPropertiesImpl.java:117)
   at com.atlassian.jira.config.properties.ApplicationPropertiesImpl.getDefaultBackedString(ApplicationPropertiesImpl.java:143)
   at com.atlassian.jira.ComponentManager.registerExtensions(ComponentManager.java:299)
   at com.atlassian.jira.ComponentManager.initialise(ComponentManager.java:195)
   at com.atlassian.jira.ComponentManager.<init>(ComponentManager.java:181)
   at com.atlassian.jira.ComponentManager.<clinit>(ComponentManager.java:159)
   at com.atlassian.jira.upgrade.ConsistencyLauncher.launchConsistencyChecker(ConsistencyLauncher.java:63)

This shows both the SQL query and the stacktrace that called it. You can associate what plugin or JIRA function is causing heavy load on your database.