Error in querying SQL

Yii 1.1 General Discussion
1

I’ve got a Yii app with a MySQL database, processing about 20,000 requests per day. About 50-100 times a day I get errors like the ones shown below. I’ve tweaked the maximum number of MySql connections and believe it to be at a good setting. I’ve also got schema caching turned on (‘schemaCachingDuration’ => ‘300’). My db connection string is like:

‘connectionString’ => ‘mysql:host=127.0.0.1;dbname=mydbname’,

Sample error is shown below. Anyone else seeing this kind of thing?

[sql]

2011/02/17 16:47:04 [error] [system.db.CDbCommand] Error in querying SQL:

SHOW COLUMNS FROM user

2011/02/17 16:47:04 [error] [exception.CDbException] exception ‘CDbException’ with message

‘The table “user” for active record class “User” cannot be found in the database.’

in /home/moi/src/yii/framework/db/ar/CActiveRecord.php:2159

Stack trace:

#0 /home/moi/src/yii/framework/db/ar/CActiveRecord.php(353):

ActiveRecordMetaData->__construct(Object(User))

#1 /home/moi/src/myproj/www/protected/models/User.php(24):

CActiveRecord::model(‘Bonus’)

…

[/sql]

One more thing. When this error arises, it arises for every db table in my schema.

Thanks in advance…

Emily

2

Yes, I see this error intermittently at my hosting several times a day. Yii 1.1.5, debug disabled.

For testing purposes I have the browser refreshing a view containing (no more than) 10 records every tenth second. Due to caching the db shouldn’t be accessed more often than once a minute Actually a DAO scan of the same table (with occasional update/delete) is performed on every refresh (every 10th second). Typical record count is 5-25.

I also have two local instances of the same application. Yii 1.1.6 (+svn), debug enabled. On the local servers I’ve never experienced this problem.

BTW I’ve never touched schemaCachingDuration.

Edit:

Forgot to mention DB type and versions.

Hosting: PHP 5.3.5, MySQL 5.0.51a (TCP/IP).

Local1: PHP 5.3.3, MySQL 5.1.49 (Unix socket).

Local2: PHP 5.3.2, MySQL 5.1.41 (Unix socket).

/Tommy

3

Some thoughts on initial reading:

  1. The line numbers you’ve quoted aren’t the same as I’m using, which tells me that you’re probably running an older version of Yii. Might be worth trying an update.

  2. It looks like the getDbConnection() method of CActiveRecord just returns the connection without checking the open/closed. You could hack your getDbConnection() to always check for open as follows:

old:




		if(self::$db!==null)

			return self::$db;

		else

new


		

if(self::$db!==null) {

 self::$db->setActive(true);

 return self::$db;

} else

This may not be enough as the setActive() call only checks if the _pdo instance is null. You might have to do a bit more work to ensure that it didn’t get closed by some other means. (Write your own _pdo checker method in CDbConnection that tries a simple query and if fails nukes the pdo instance and creates a new one.)

This isn’t ideal, as really if you’re running queries on a closed connection you’ve got a bigger problem.

  1. Check your log statements - are you seeing a database closed log entry? Is the db somehow getting closed?
4

Thanks for these suggestions.It turned out that settings in my my.ini file were at fault. After analyzing and tweaking the following values, MySQL is no longer running out of memory and "going away":

max_connections

query_cache_size

table_cache

max_allowed_packet

wait_timeout

Thx again!

:mellow:

5

It could be MySQL problem(imho 90% that’s it).

6

So, I thought my db tuning had fixed the issue, but "MySQL server went away" continued to rear ugly head.

I was loathe to “hack” the Yii framework as suggested, because I’m likely to forget the change and write over it the next time Yii is updated.

Instead, this works without hacking:

[list=1]

[*]Create MyActiveRecord as an extension of CActiveRecord

[*]Override getDbConnection() function, making change suggested above

[*]Whenever extending CActiveRecord, extend MyActiveRecord instead

[*]Create MyDbConnection as an extension of CDbConnection

[*]Create a new function, refreshDb()

[*]In any cases where an application incurs delays, call refreshDb()

before doing anything related to the db. This is especially useful

if you’re making db calls from within a for loop which takes a long

time to execute.

[*]Modify the ‘db’ component in your config file, to use the MyDbConnection class

[/list]

Code for MyActiveRecord:





abstract class MyActiveRecord extends CActiveRecord {

/**

* @return CDbConnection the database connection used by active record.

* By default, the "db" application component is used as the database connection.

*

* This function is overridden to protect against "MySQL server went away errors".

* The only change is to make sure the current db connection is ACTIVE before returning it.

*/

public function getDbConnection()	{

   if (self::$db!==null) {

      self::$db->setActive(true); // Make sure conn is ACTIVE before returning!

      return self::$db;

   } else {

      self::$db=Yii::app()->getDb();

      if (self::$db instanceof CDbConnection)

         return self::$db;

      else

         throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));

      }

   }

}

Code for MyDbConnection:




class MllDbConnection extends CDbConnection {

   /**

    * Refresh the DB, in an attempt to nix the "Mysql server went away" error.

    * Yii forum post:

    * @link http://tinyurl.com/6bopnb5

    */

   public function refreshDb() {

      self::close();

      self::open();

   }

}

Call refreshDb() in any time-intensive for loops, etc. in your application:




 Yii::app()->db->refreshDb();

Modify definition of ‘db’ component in your main config file:




 'db'=>array(

      'class' => 'MllDbConnection',

      'connectionString' => 'mysql:host=127.0.0.1;dbname=mydb',

      // etc.