Have you ever wondered exactly what is happening under the hood when you load a CiviCRM search page, save a contribution, or run a complex mailing report? Understanding the CiviCRM MySQL queries being executed is essential for developers and administrators who need to troubleshoot performance bottlenecks, debug custom extensions, or simply verify data integrity.

While CiviCRM's abstraction layers (DAO and BAO) simplify database interactions, they can sometimes make it difficult to see the raw SQL being sent to the server. In this guide, we will explore four distinct methods to intercept and view these queries, ranging from simple configuration changes to advanced database-level logging.

1. Using civicrm.settings.php Constants

The most direct way to enable query logging for developers is by modifying your civicrm.settings.php file. This approach is powerful because it doesn't require access to the MySQL console and works across all Content Management Systems (CMS) like Drupal, WordPress, and Joomla.

The DAO Debug Constant

By adding a specific definition to your settings file, you can force CiviCRM to output database activity. Add the following line:

define('CIVICRM_DAO_DEBUG', 1);

When this constant is set to 1, CiviCRM will attempt to emit SQL statements. Note that the output will often appear directly on the screen (which can break the layout or AJAX responses) and will also be written to the CiviCRM log file.

The Dedicated Query Log Constant

If you prefer a cleaner approach that specifically targets SQL commands without cluttering your UI, use the query log constant:

define('CIVICRM_DEBUG_LOG_QUERY', 1);

This constant adds every executed SQL command to the log files located in your ConfigAndLog directory. Be warned: these files can grow very large, very quickly. It is best practice to enable this only for the duration of your debugging session and then disable it immediately.

2. Locating Your CiviCRM Log Files

When you enable the debug constants mentioned above, you need to know where to find the output. CiviCRM stores its logs in different locations depending on your CMS environment. Look for files named CiviCRM.<unique_id>.log in the following directories:

  • Drupal 7/8/9/10: sites/default/files/civicrm/ConfigAndLog/ or web/sites/default/files/civicrm/ConfigAndLog/
  • WordPress: wp-content/plugins/files/civicrm/ConfigAndLog/ (or within the uploads directory depending on your configuration)
  • Joomla: media/civicrm/ConfigAndLog/

You can use a terminal command like tail -f to watch these logs in real-time as you navigate through your CiviCRM site:

tail -f /path/to/your/civicrm/ConfigAndLog/CiviCRM.log

3. Enabling the MySQL General Log

Sometimes you need to see everything—including queries that might be happening outside of the CiviCRM DAO layer. In these cases, enabling the MySQL general log is the most comprehensive method. Warning: Never do this on a high-traffic production server, as it will significantly impact performance and consume disk space.

From your MySQL console or a tool like MySQL Workbench, you can toggle logging on the fly without restarting the database service:

-- Enable logging to a table for easy querying
SET global general_log = 1;
SET global log_output = 'table';

Once logging is active, you can query the log table directly to find the specific statements you are looking for. For example, to see queries run in the last 15 seconds:

select * 
from mysql.general_log 
where 
  command_type = 'Query' and 
  event_time > now() - interval 15 second;

If you are looking for specific interactions with the contacts table, you can filter the results:

select * from mysql.general_log 
where 
  argument like '%civicrm_contact%' and 
  argument not like '%general_log%' 
order by event_time desc;

When you are finished, remember to clear the logs and turn the feature off:

truncate table mysql.general_log; 
SET global general_log = 0;

4. Debugging SQL in CiviCRM Reports

If you are specifically trying to debug a CiviCRM Report, you may not need to touch code or database settings at all. The Extended Reports extension is a highly recommended tool for the CiviCRM community.

Once installed, many of the reports provided by this extension include an option to display the full SQL query directly in the browser. This is incredibly helpful for understanding how filters and fields are being translated into SELECT statements and JOIN clauses.

Frequently Asked Questions

Can I see SQL queries without modifying files?

Yes, if you use the MySQL General Log method or the Extended Reports extension, you can view queries without touching civicrm.settings.php. However, the General Log method requires database administrative privileges.

Why is my log file empty after enabling CIVICRM_DAO_DEBUG?

Ensure that the web server has write permissions to the ConfigAndLog directory. If CiviCRM cannot write to the file system, your debug output will be lost. Also, check if your CMS or a server-level cache is preventing the settings file from being reloaded.

Does viewing queries expose sensitive data?

Yes. SQL logs contain raw data, including personally identifiable information (PII) and potentially sensitive metadata. Always delete or truncate log files after your debugging session is complete, and never share raw logs on public forums without sanitizing them.

Wrapping Up

Whether you use the built-in CiviCRM constants, the MySQL general log, or specialized extensions, having visibility into your database layer is a vital skill for any CiviCRM expert. For most routine development tasks, define('CIVICRM_DEBUG_LOG_QUERY', 1); provides the best balance of detail and ease of use. For deep-dive performance tuning, the MySQL General Log remains the gold standard.

Always remember to verify your CiviCRM version and environment settings, as directory paths and specific behavior can vary between major releases. Happy debugging!