Debugging Database Operations in Yii

September 23, 2013 — 6 Comments
The Yii Book If you like my writing on the Yii framework, you'll love "The Yii Book"!

Whenever you begin working with a database, you introduce more possible causes of errors. Thus, you must learn additional debugging strategies. When using PHP to run queries on the database, the problems you might encounter include:

  • An inability to connect to the database
  • A database error thrown because of a query
  • The query not returning the results or having the effect that you expect
  • None of the above, and yet, the output is still incorrect

On a non-framework site, you just need to watch for database errors to catch the first two types of problems. There’s a simple and standard approach for debugging the last two types:

  1. Use PHP to print out the query being run.
  2. Run the same query using another interface to confirm the results.
  3. Debug the query until you get the results you want.

When using a framework, these same debugging techniques are a little less obvious, in part because you may not be directly touching the underlying SQL commands. Thankfully, Yii will still be quite helpful, if you know what switches to flip.

This is an excerpt from Chapter 8, “Working with Databases,” of “The Yii Book“.

First of all, while developing your site, enable CWebLogRoute:

# protected/config/main.php "components" section
'log'=>array(
    'class'=>'CLogRouter',
    'routes'=>array(
        array(
            'class'=>'CFileLogRoute',
            'levels'=>'error, warning',
        ),
        array(
            'class'=>'CWebLogRoute',
        ),
    ),
),

This will show, in your browser, everything being done by the framework including what queries are being run.

CWebLog Output

Here, the Web log router is showing one of the queries required to fetch a page record.

But there’s one more thing you should do to make debugging SQL problems easier…

Many queries will use parameters, separating the core of the query from the specific (and often user-provided) values used by it. To see the entire query, with the parameter values in place, you must also set the CDbConnection class’s enableParamLogging attribute to true:

# protected/config/main.php "components" section
'db'=>array(
    'connectionString' =>
        'mysql:host=localhost;dbname=test',
    'emulatePrepare' => true,
    'enableParamLogging' => true,
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
),

Now you’ll be able to see the entire query in your output, including the query’s parameter values.

{WARNING} Public display of errors and detailed logging are terrible for a site’s security and performance. Both should only be used during the development of a site.

If you enjoyed this post, then please consider following me using your favorite social media, the RSS feed, and/or by subscribing to my newsletter. Or go crazy, and buy one or more of my books . Thanks!

6 responses to Debugging Database Operations in Yii

  1. Thank you.

    I was just trying to figure out how to do exactly this when I received your email about this post.
    This post saved me a lot of time and trail and error, that I would have experienced, should I
    have not received your great post.

  2. enableProfiling could also help. Also those log routes are bit generic, i like to do it like:
    ‘db-queries’ => array(
    ‘class’ => ‘SFileLogRoute’,
    ‘levels’ => ‘error,warning,trace,info’,
    ‘logFile’ => ‘db-queries.log’,
    ‘categories’ => ‘system.db.*’,
    ),

  3. Thanks for the tip. I always appreciate your writings.
    My Opera browser does not show your text correctly. You may need a tweek on your website. Here are the last lines in your text:
    QUOTE:Now you’ll be able to see the entire query in your output, including the query’s parameter values.

    {WARNING} Public display of errors and detailed logging are terrible for a site’s security and performance. Both should only be used during the development of a site.UNQUOTE

Comments are great, but I'd strongly prefer any requests for assistance get made in the support forums. Thanks!