Error executing CDBCommand

Yii 1.1 Installation and Setup
1

Hi!,

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;

Error:




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.

2

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

3

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

4

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:

5

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?

6

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