目的

このナレッジベース記事では、Jira 内の詳細な SQL ロギングを有効化する方法を説明します。これは次のような場合に使用できます。

• データベースの SQL の問題のトラブルシューティング
• 特定のユーザー アクションで送信された SQL を判断するための、GUI アクションのリバースエンジニアリング

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.

環境

Jira Server/Data Center on any version from Jira 8.0.0.

ソリューション

To temporarily enable SQL logging

1. Log into Jira as a System Administrator.

2. [管理] > [システム] > [ロギングとプロファイリング] に移動します。

3. OfBiz の場合: 

   1. 次のように SQL ロギングを有効化します。

      

   2. 結果のログは <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. 結果のログは <JIRA HOME>/log/atlassian-jira.log に記録されます。

 これらの設定はメモリに保管されるため、再起動すると失われます。

To permanently enable SQL logging

Solution For Jira versions below 9.5.0

To have Jira persist the DEBUG settings, they need to be changed in the log4j.properties file as below:

1. Look for the line below, ands change OFF to ON:

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

2. For the Active Object java.ao.sql, look for the lines below and set WARN to TRACE:


   log4j.logger.net.java.ao.sql  = WARN, console, filelog

   


3. For the Active Object com.querydsl.sql, append the following lines at the end of the file:

   log4j.logger.com.querydsl.sql = TRACE, console, sqllog
   log4j.additivity.com.querydsl.sql = false

Solution For Jira versions from 9.5.0

As mentioned in the Jira 9.5.0 Release notes, Log4j was upgraded to the version 2.17.2, which means that, to enable DEBUG packages permanently, a different file needs to be modified.

To have Jira persist the DEBUG settings, they need to be changed in the log4j2.xml file as below:

1. Look for the line below, ands change OFF to ON:

   <Logger name="com.atlassian.jira.ofbiz.LoggingSQLInterceptor" level="OFF" additivity="false">
       <AppenderRef ref="sqllog"/>
   </Logger>

2. For the Active Object java.ao.sql, look for the lines below and set WARN to TRACE:


   <Logger name="net.java.ao.sql" level="WARN" additivity="false">
       <AppenderRef ref="filelog"/>
   </Logger>

   


3. For the Active Object com.querydsl.sql, append the following lines at the end of the file:

   <Logger name="com.querydsl.sql" level="TRACE" additivity="false">
       <AppenderRef ref="sqllog"/>
   </Logger>