Browsing the Drupal 7 Variable Table

Drupal's variable table -- where most admin settings are saved -- had an overhaul between Drupal 6 and Drupal 7. No longer is it simple to browse the table with the SQL command line or using tools such as phpMyAdmin. Here are a few tricks to make browsing this table simple again.

The value of most of Drupal’s admin settings are saved in the variable table. In Drupal 6 is was easy to browse through this table – the value field was longtext and the value being saved was serialized allowing storage of both simple data (strings, numbers) and complicated data (arrays, objects). You could also find all instances of a given value with simple SQL:

SELECT * FROM variable WHERE value LIKE "%some_value%" 

But serializing doesn’t work well for objects with protected or private members or for binary data.  So the value column became a blob in Drupal 7.  This allowed us much greater flexibility in what we can save in the variable table at the cost of easy “reading” of the values in that table.  (It also prevents us from indexing that column, but that was already the case since we stored only serialized data in the D6 version of this column).

Here are some tricks I’ve been using to make it easy to browse the variable table in Drupal 7:

• When using SQL to browse the variable table:
  1. Use the MySQL command CONVERT to un-blob data you’re displaying.

     SELECT CONVERT (value USING utf8) FROM variable WHERE name="site_mail" 
     

  2. Use the MySQL command CAST to un-blob data you’re querying.

     SELECT * FROM variable WHERE CAST(value AS CHAR) LIKE "%some_value%" 
     

• In phpMyAdmin, select the Show blob contents option. This lets you browse a tables just like you did in Drupal 6. Just beware of actual binary data stored in blobs, they don't display well.
  
  

I feel the need to point out that this is exactly NOT how you would do it if you were writing code for Drupal – that’s what the variable_set() and variable_get() functions are for.

Also, having never worked on a PostgreSQL project, I have no idea if CONVERT and CAST work similarly on that platform.