Error executing CDBCommand


My problem: I need to execute a SQL sentence in my app, I’ve created a CDBCommand to execute it, the error and SQL are like the following.

Code to execute:

$select = "SELECT m.tableid, m.tablefield ";

$from = " FROM myschema.my_table m

          INNER JOIN myschema.other_table mr ON m.tableid = mr.relatedid AND mr.tableid = :mi";

$where = " WHERE m.tableid <> :mi";

$count = Yii::app()->db->createCommand('SELECT COUNT(*) ' . $from . $where)->queryScalar($params);

$dp = new CSqlDataProvider($select . $from . $where, array(

            'totalItemCount' => $count,

            'keyField' => 'tableid',

            'sort' => array('attributes' => array('tablefield')),

            'pagination' => false,

            'params' => $params


return $dp;


CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: 

Syntax error or access violation: 1142 SELECT command denied to user 'myusername'@'999.999.99.99' for table 'my_table'. 

The SQL statement executed was: 

SELECT COUNT(*) FROM my_schema.my_table m

INNER JOIN my_schema.my_other_table mr ON m.tableid = mr.relatedid AND mr.tableid = :mi WHERE m.relatedid <> :mi 

Data Base permissions to the user:

mysql> show grants


| Grants for myusername@%                                                       |


| GRANT USAGE ON *.* TO 'myusername'@'%' IDENTIFIED BY PASSWORD '*************' | 

| GRANT ALL PRIVILEGES ON `Schema`.* TO 'myusername'@'%'                        | 


The freak is that the error is generated just with CDBCommand, when I use CActiveRecord i.e. MyClass::model()->findByPk($id), or something else, every thing is fine, as far as I understand methods findBy… generates a SELECT to execute. Don’t know where my problem is.

BTW, the CSQLDataProvider is used to populate a CListView widget.

The problem is in production, in my local machine works fine.

Hi! I think I was wrong, is this the right forum to post this question?, if not, please some one to move it to general discussion?? , or has anyone had this problem before? :P

Umh, by any chance: Have you executed FLUSH PFIVILEGES after setting those grants in place?

Thanks for your replay, actually I’m not the DBA and the guy who gives the permissions, I’ll check that. But, why when I execute the findBy(…) methods, they don’t generate error?, and with CDBCommand it does… :unsure:

In your code in your first post, I am not able to see any assignment or declaration for $params. Do you have it in your code?

Solved. Thks you guys for your replies… It was something really stupid ;D , I was using in my commands another schema… :wacko: