CDbException when request user list

[GoogleCodeExporter]

When I requested for User List
http://myurl/yii-skeleton-app/user/list
 I get exception:
<code>
CDbException

Description

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax 
error or access violation: 1064 You have an error in your SQL syntax; check 
the manual that corresponds to your MySQL server version for the right 
syntax to use near 'group.`id` AS `t1_c0`, group.`name` AS `t1_c1`, 
group.`created` AS `t1_c2`, grou' at line 1

Source File

F:\Zend\Apache2\htdocs\yii\framework\db\CDbCommand.php(319)

00307: 
00308:             if($this->_connection->enableProfiling)
00309:                 Yii::endProfile('system.db.CDbCommand.query('.$this-
>getText().')','system.db.CDbCommand.query');
00310: 
00311:             return $result;
00312:         }
00313:         catch(Exception $e)
00314:         {
00315:             if($this->_connection->enableProfiling)
00316:                 Yii::endProfile('system.db.CDbCommand.query('.$this-
>getText().')','system.db.CDbCommand.query');
00317:             Yii::log('Error in querying SQL: '.$this-
>getText().$params,CLogger::LEVEL_ERROR,'system.db.CDbCommand');
00318:             //echo $this->getText().$params;
00319:             throw new CDbException(Yii::t('yii','CDbCommand failed 
to execute the SQL statement: {error}',
00320:                 array('{error}'=>$e->getMessage())));
00321:         }
00322:     }
00323: }
Stack Trace

#0 F:\Zend\Apache2\htdocs\yii\framework\db\CDbCommand.php(237): CDbCommand-
>queryInternal('fetchAll', 2)
#1 F:\Zend\Apache2\htdocs\yii\framework\db\ar\CActiveFinder.php(558): 
CDbCommand->queryAll()
#2 F:\Zend\Apache2\htdocs\yii\framework\db\ar\CActiveFinder.php(395): 
CJoinElement->runQuery(Object(CJoinQuery))
#3 F:\Zend\Apache2\htdocs\yii\framework\db\ar\CActiveFinder.php(80): 
CJoinElement->find(Object(CDbCriteria))
#4 F:\Zend\Apache2\htdocs\yii\framework\db\ar\CActiveFinder.php(108): 
CActiveFinder->query(Object(CDbCriteria), true)
#5 F:\Zend\Apache2\htdocs\yii\demos\yii-skeleton-
app\protected\controllers\UserController.php(68): CActiveFinder-
>findAll(Object(CDbCriteria))
#6 F:\Zend\Apache2\htdocs\yii\framework\web\actions\CInlineAction.php(32): 
UserController->actionList()
#7 F:\Zend\Apache2\htdocs\yii\framework\web\CController.php(300): 
CInlineAction->run()
#8 F:\Zend\Apache2\htdocs\yii\framework\web\filters\CFilterChain.php(129): 
CController->runAction(Object(CInlineAction))
#9 F:\Zend\Apache2\htdocs\yii\framework\web\filters\CFilter.php(41): 
CFilterChain->run()
#10 F:\Zend\Apache2\htdocs\yii\demos\yii-skeleton-
app\protected\components\Controller.php(7): CFilter-
>filter(Object(CFilterChain))
#11 F:\Zend\Apache2\htdocs\yii\framework\web\filters\CInlineFilter.php(59): 
Controller->filterAccessControl(Object(CFilterChain))
#12 F:\Zend\Apache2\htdocs\yii\framework\web\filters\CFilterChain.php(126): 
CInlineFilter->filter(Object(CFilterChain))
#13 F:\Zend\Apache2\htdocs\yii\framework\web\CController.php(283): 
CFilterChain->run()
#14 F:\Zend\Apache2\htdocs\yii\framework\web\CController.php(257): 
CController->runActionWithFilters(Object(CInlineAction), Array)
#15 F:\Zend\Apache2\htdocs\yii\framework\web\CWebApplication.php(332): 
CController->run('list')
#16 F:\Zend\Apache2\htdocs\yii\framework\web\CWebApplication.php(120): 
CWebApplication->runController('user/list')
#17 F:\Zend\Apache2\htdocs\yii\framework\base\CApplication.php(133): 
CWebApplication->processRequest()
#18 F:\Zend\Apache2\htdocs\yii\demos\yii-skeleton-app\index.php(11): 
CApplication->run()
#19 {main}

</code>

I viewed the html source, it appeared as following:

<code>
/*<![CDATA[*/
if(typeof(console)=='object')
{
    console.group("Application Log");
    console.log("[18:16:47.671][trace][system.db.CDbConnection] Opening 
DB connection");
    console.log("[18:16:47.755][trace][system.db.CDbCommand] Querying 
SQL: SHOW COLUMNS FROM `user`");
    console.log("[18:16:47.703][trace][system.db.CDbCommand] Querying 
SQL: SHOW CREATE TABLE `user`");
    console.log("[18:16:47.703][trace][system.db.ar.CActiveRecord] 
User.count()");
    console.log("[18:16:47.703][trace][system.db.CDbCommand] Querying 
SQL: SELECT COUNT(*) FROM `user`");
    console.log("[18:16:47.703][trace][system.db.CDbCommand] Querying 
SQL: SHOW COLUMNS FROM `group`");
    console.log("[18:16:47.778][trace][system.db.CDbCommand] Querying 
SQL: SHOW CREATE TABLE `group`");
    console.log("[18:16:47.786][trace][system.db.CDbCommand] Querying 
SQL: SHOW COLUMNS FROM `post`");
    console.log("[18:16:47.795][trace][system.db.CDbCommand] Querying 
SQL: SHOW CREATE TABLE `post`");
    console.log("[18:16:47.734][trace][system.db.ar.CActiveRecord] 
User.findAll() eagerly");
    console.log("[18:16:47.734][trace][system.db.CDbCommand] Querying 
SQL: SELECT `user`.`id` AS `t0_c0`, `user`.`username` AS `t0_c1`, 
`user`.`password` AS `t0_c2`, `user`.`email` AS `t0_c3`, 
`user`.`email_visible` AS `t0_c4`, `user`.`notify_comments` AS `t0_c5`, 
`user`.`notify_messages` AS `t0_c6`, `user`.`about` AS `t0_c7`, 
`user`.`aboutParsed` AS `t0_c8`, `user`.`group_id` AS `t0_c9`, 
`user`.`email_confirmed` AS `t0_c10`, `user`.`created` AS `t0_c11`, 
`user`.`modified` AS `t0_c12`, group.`id` AS `t1_c0`, group.`name` AS 
`t1_c1`, group.`created` AS `t1_c2`, group.`modified` AS `t1_c3` FROM 
`user`  LEFT OUTER JOIN `group` group ON (`user`.`group_id`=group.`id`) 
ORDER BY `user`.`created` DESC LIMIT 25");
    console.error("[18:16:47.734][error][system.db.CDbCommand] Error in 
querying SQL: SELECT `user`.`id` AS `t0_c0`, `user`.`username` AS `t0_c1`, 
`user`.`password` AS `t0_c2`, `user`.`email` AS `t0_c3`, 
`user`.`email_visible` AS `t0_c4`, `user`.`notify_comments` AS `t0_c5`, 
`user`.`notify_messages` AS `t0_c6`, `user`.`about` AS `t0_c7`, 
`user`.`aboutParsed` AS `t0_c8`, `user`.`group_id` AS `t0_c9`, 
`user`.`email_confirmed` AS `t0_c10`, `user`.`created` AS `t0_c11`, 
`user`.`modified` AS `t0_c12`, group.`id` AS `t1_c0`, group.`name` AS 
`t1_c1`, group.`created` AS `t1_c2`, group.`modified` AS `t1_c3` FROM 
`user`  LEFT OUTER JOIN `group` group ON (`user`.`group_id`=group.`id`) 
ORDER BY `user`.`created` DESC LIMIT 25");
    console.groupEnd();
}
/*]]>*/
</code>
By the Consequence, The sql command was wrong.
How to resolve this?

Original issue reported on code.google.com by khs.tho...@gmail.com on 14 Aug 2009 at 10:21

[GoogleCodeExporter]

Hi

I have the same issue. It's a windows install with release 1400 of yii, mysql 
5, 
checkout of this skeleton from today. 
I modified the showScriptName to true in my test.php config file for a 
production/test build. 

The statement should be:

....LEFT OUTER JOIN `group`  ON (`user`.`group_id`=`group`.`id`)
ORDER BY `user`.`created` DESC LIMIT 25

So with quotes around the join argument table name and with one word group less 
before the ON.

Maybe the group table names confuses yii? The join is created in the 
UserController.php around line 68, but I did not yet find how to solve this.

Original comment by wessel.l...@gmail.com on 7 Sep 2009 at 9:34

[GoogleCodeExporter]

Prefixing the table 
(http://php-thoughts.cubedwater.com/2009/table-prefixes/#comments) 
did not solve my issue, but changing the model class Group to Groep (and all 
kind of 
references of course) did fix it!

I wonder if this is reproducable by others and if not why this happened in my 
configuration.

Original comment by wessel.l...@gmail.com on 7 Sep 2009 at 10:06