Drupal Debugging: Clues from the Database

Have you ever chased a bug down a rabbit hole and spent a few hours down there only to come back up for air thinking "why, oh why does the bug taunt me"? If you have then you know that every clue can count. Any given clue can send you on the shortest path from A to B, or it can send you spiraling. When looking for clues always remember that your code is not the only place to look. Sometimes valuable hints can be gained from the database as well.

In my not-so-hypothetical scenario, I had a client site where their in-house team had been contributing work towards a shared development environment. The dev instance had a feature that recently stopped functioning properly.

To debug I did the following:

• Tried to reproduce in dev. (I could.)
• Tried to reproduce in my local. (I could not.)
• My local was about a week out of date. So I did a git pull and cleared the Drupal cache for good measure. Tried to reproduce in my local. (I could not.)
• The code in dev and my local matched. My database however was not synced. (Potential clues lurking... I could smell them.)
• I did a mysqldump of the dev environment's database and pulled it local. Created a new local DB and loaded in the dump from dev. (Now I have both. Compare and contrast time.)
• Switched my local Drupal instance to use the new DB I just created from dev. Tried to reproduce in my local. (I could. I can now taste the clues. Kinda salty.)

So at that point I had reason to believe that something somewhere in the database had changed that resulted in the bug showing up. Now in the spirit of "old tricks are the best tricks" I grabbed my SQL Hat & Query Pants, opened up my favorite query browser (which is technically HeidiSQL, but these days I'm on a Mac, so I am using Sequel Pro), and I fashioned some queries to see what had changed between the two copies of the database that were a week apart.

I used the following queries below in my bug hunt. I keep a library of handy queries and I basically just copy and pasted these into my query browser's query window then did 'run current query' down the list one at a time.

# Set some variables for the DB names so you don't have to retype them.
SET @prev='my_local_db';
SET @cur='the_dev_db';
 
# Check for variables that are new.
SET @q = CONCAT(
'SELECT 
* 
FROM ', @cur, '.variable 
LEFT JOIN ', @prev , '.variable 
ON ', @prev , '.variable.name = ', @cur, '.variable.name
WHERE ', @prev , '.variable.name IS NULL');
PREPARE qry FROM @q;
EXECUTE qry;
 
# Check for variables that are different.
SET @q = CONCAT(
'SELECT 
', @cur, '.variable.name AS name, 
', @prev , '.variable.value AS old_value, 
', @cur, '.variable.value AS new_value 
FROM ', @cur, '.variable 
INNER JOIN ', @prev , '.variable 
ON ', @prev , '.variable.name = ', @cur, '.variable.name
WHERE ', @cur, '.variable.value != ', @prev , '.variable.value');
PREPARE qry FROM @q;
EXECUTE qry;
 
# Check for new system rows
SET @q = CONCAT(
'SELECT 
* 
FROM ', @cur, '.system
LEFT JOIN ', @prev , '.system
ON ', @prev , '.system.name = ', @cur, '.system.name
WHERE ', @prev , '.system.name iS NULL');
PREPARE qry FROM @q;
EXECUTE qry;
 
# Check for system rows that are different
SET @q = CONCAT(
'SELECT 
', @cur, '.system.name AS name, 
', @prev , '.system.status AS old_status, 
', @cur, '.system.status AS new_status, 
', @prev , '.system.schema_version AS old_shema_version, 
', @cur, '.system.schema_version AS new_schema_version,
', @prev , '.system.info AS old_info, 
', @cur, '.system.info AS new_info 
FROM ', @cur, '.system
INNER JOIN ', @prev , '.system
ON ', @prev , '.system.name = ', @cur, '.system.name
WHERE ', @cur, '.system.info != ', @prev , '.system.info
OR ', @cur, '.system.status != ', @prev , '.system.status
OR ', @cur, '.system.schema_version != ', @prev , '.system.schema_version');
PREPARE qry FROM @q;
EXECUTE qry;
 
# Check for field_configs that are different.
SET @q = CONCAT(
'SELECT 
', @cur, '.field_config.field_name, 
', @prev , '.field_config.data AS old_data, 
', @cur, '.field_config.data AS new_data
FROM ', @cur, '.field_config 
INNER JOIN ', @prev , '.field_config
ON ', @prev , '.field_config.field_name = ', @cur, '.field_config.field_name
WHERE ', @cur, '.field_config.data != ', @prev , '.field_config.data');
PREPARE qry FROM @q;
EXECUTE qry;
 
DEALLOCATE PREPARE qry;

If you look at the queries above you can see the basic pattern. I am taking the most likely suspects such as the variable and system tables, joining the version from the working and dysfunctional databases, then checking for changes and new records. You can adapt this pattern to create similar queries on any tables that you suspect clues may be hiding.

Remember: the goal here is to look for clues. The solution / resolution will likely be elsewhere. We are simply narrowing the possible "elsewheres".

Analyzing the result sets can be more art form than science. What you are looking for will vary greatly depending on your issue. Clear your mind; let the force flow through you. Good luck.

Running these queries showed me several changes had been made to the site's configuration. This greatly narrowed my search from "all the dang Drupal" down to 2-3 specific dark alleys. Within a few minutes I found that an obscure contrib module was causing an error that resulted in a completely unrelated module failing after the config settings were changed. I patched the module, squashed the bug for our client and likely others in the community, and we were on our way.

Was the solution in the database: no. Did the database give me valuable clues that likely saved me a few hours: yep.

Note: There are also several tools out there for doing diff style comparisons on databases. I've had varied luck with these tools and typically find that they have more of an all or nothing approach as opposed to the more strategic queries I write myself. That said, the Power Tools provided by SQLYog are decent.