Cloud
Data Center 9.1
Versions

Enabling Detailed SQL Logging

Troubleshooting Problems and Requesting Technical Support

On this page

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

Confluence uses the open source persistence framework Hibernate. This page tells you how to configure Confluence's logging to report individual SQL requests that are sent to the database by Hibernate. It is useful for troubleshooting the following events:

  • XML site backups that fail to import.
  • Exceptions caused by an illegal database operation.
  • Problems caused by long-running database queries. 

SQL debug logging with parameters is extremely verbose, and should only be enabled temporarily, while you're troubleshooting.

Related pages:

If you have a database problem and need support assistance, it's a good idea to temporarily enable detailed SQL logging before sending us the log files. This will assist us in determining what SQL queries were running during the reported problem. After you've enabled hibernate logging, replicate the action that you suspect is causing the error. This is the best way to ensure that the Confluence log file contains relevant SQL logging.

Log SQL queries with parameters

To enable temporary SQL logging:

  1. Go to Administration menu , then General Configuration. > Logging and Profiling.
  2. Choose Enable SQL Logging. 

This logging level will persist until you choose Disable SQL Logging or Confluence is restarted. If you need the logging levels to persist (useful for troubleshooting startup problems related to your database, for example) follow the steps below to modify log4j.properties.

To enable persistent SQL logging:

(warning) Use this method if you're using Confluence 5.9.10 or earlier to ensure that parameters are also logged. You can revert the changes once you're done troubleshooting.

  1. Stop Confluence.

  2. Edit <install-directory>\confluence\WEB-INF\classes\log4j.properties

  3. Change the following lines from ERROR to DEBUG:

    ## log hibernate prepared statements/SQL queries (equivalent to setting 'hibernate.show_sql' to 'true')
## DEBUG, TRACE or ALL is required to see query strings
log4j.logger.org.hibernate.SQL=debug

  4. Uncomment the following lines:

    ## log hibernate prepared statement parameter values. Note: due to caching in net.sf.hibernate.type.NullableType, requires restart to take effect
## TRACE or ALL is required to see parameter values
## Note that log4j.appender.confluencelog.Threshold (or other appenders) also must be TRACE or ALL to see any trace messages in the logs
log4j.logger.org.hibernate.type=trace

  5. Change the following lines from DEBUG to TRACE:

    log4j.appender.confluencelog.Threshold=TRACE
  6. Start Confluence.

This logging level will persist, even when Confluence is restarted.  As SQL logging is very verbose, you should revert your changes to log4j.properties  once you no longer need the additional info.

(warning) Note: For SQL Logging of AO Tables, insert this class with DEBUG Level:

Last modified on Feb 28, 2019

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.