Debugging WordPress SQL Queries

There are various ways to investigate/debug issues with SQL statements within WordPress. I have outlined the most relevant methods below.

Enable Debugging

First, the debug mode needs to be enabled. This can be done by adding the following entries to the wp-config.php file:

define( 'WP_DEBUG', true ); // Enable debugging
define( 'WP_DEBUG_LOG', true ); // Write debug information to /wp-content/debug.log
define( 'WP_DEBUG_DISPLAY', false ); // Do not display debug information

NOTE: WP_DEBUG_DISPLAY should never be enabled on production websites. Debug information may contain sensitive data. Displaying this information poses a significant security risk. The debug.log file must also be protected as it is publicly accessible!

Query Debugging

$wpdb provides two methods to enable and disable query debugging:

global $wpdb;
$wpdb->show_errors(); // Displays error messages
// or
$wpdb->hide_errors(); // Hides error messages

$wpdb->insert(...);

NOTE: $wpdb->show_errors() displays error messages even if WP_DEBUG is set to false! For this reason, the following statement should always be used:

global $wpdb;

if ( defined( 'WP_DEBUG' ) && true === WP_DEBUG ) {
    $wpdb->show_errors();
}

To output an error, you can use the following command:

$wpdb->print_error();

This command checks if there was an error when executing the last SQL statement and displays it.

It is also important to know that $wpdb->insert() returns false if an error occurred:

global $wpdb;
if ( !$wpdb->insert() ) {
    echo "An error occurred";
}

All Queries

To get all SQL queries executed by WordPress, SAVEQUERIES needs to be enabled:

define( 'SAVEQUERIES', true );

Now, all queries will be logged in the $wpdb->queries array. The following code outputs them:

if ( current_user_can( 'administrator' ) ) {
    global $wpdb;
    echo "";
    print_r( $wpdb->queries );
    echo "";
}

In this article, it is explained how to log the queries.

Common Issue

What if there is no error message, but no entry is being written to the database? A common problem is that the data being written to the database contains one or more invalid characters. For example, if there is a field in the database of type varchar(50) and a string with 60 characters is being inserted, it will fail without an error message! For this reason, it is important to always check if the data meets the requirements before executing insert statements.

Multisite

Finally, for WordPress Multisites, in order to debug queries, the constant DIEONDBERROR needs to be set to true in the wp-config.php file:

define( 'DIEONDBERROR', true );