Enabling Detailed SQL Logging
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:
- Go to Administration menu
, then General Configuration > Logging and Profiling. - 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 log4j2.xml.
To enable persistent SQL logging:
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.
- Stop Confluence.
Edit
<install-directory>\confluence\WEB-INF\classes\log4j2.xmlChange the following lines from
ERRORtoDEBUG:<!--Log hibernate prepared statements/SQL queries (equivalent to setting 'hibernate.show_sql' to 'true').--> <!--DEBUG, TRACE or ALL is required to see query strings.--> <Logger level="debug" name="org.hibernate.SQL" additivity="false"> <AppenderRef ref="sqllog"/> </Logger>Directly after it, add the following lines to log parameter values:
<!--Log hibernate prepared statement parameter values. TRACE or ALL is required to see parameter values.--> <Logger level="trace" name="org.hibernate.orm.jdbc.bind" additivity="false"> <AppenderRef ref="sqllog"/> </Logger>- Start Confluence.
This logging level will persist, even when Confluence is restarted. As SQL logging is very verbose, you should revert your changes to log4j2.xml once you no longer need the additional info.
Note: For SQL Logging of AO Tables, insert this class with DEBUG Level:
You can add it in theAdministration menu , then General Configuration > Logging and Profiling or add it to the <Loggers> section of log4j2.xml :
<Logger level="debug" name="net.java.ao"/>These entries are written to the main application log, atlassian-confluence.log . Remove the logger once you no longer need it.