1.0.1 broke my app

I just upgraded to 1.0.1 … and my app stopped working with this message

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 'ORDER BY t1.roomId' at line 1

Source File

/home/projects/nyy2/.framework/db/CDbCommand.php(284)

00272:            if($this->_statement instanceof PDOStatement)

00273:                $this->_statement->execute();

00274:            else

00275:                $this->_statement=$this->getConnection()->getPdoInstance()->query($this->getText());

00276:            if($method==='')

00277:                return new CDbDataReader($this);

00278:            $result=$this->_statement->{$method}($mode);

00279:            $this->_statement->closeCursor();

00280:            return $result;

00281:        }

00282:        catch(Exception $e)

00283:        {

00284: throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',

00285:                array('{error}'=>$e->getMessage())));

00286:        }

00287:    }

00288: }

If I switch back to 1.0.0, all is functional again.

If I remove the 'order' param in the relationship, I still get the same error (just with an empty string).

Any ideas ?

Could you please show the callstack and your code that would result in this exception?

Hi Qiang

Here's the stack trace

Stack Trace

#0 /home/projects/nyy2/.framework/db/CDbCommand.php(218): CDbCommand->queryInternal('fetchAll', 2)

#1 /home/projects/nyy2/.framework/db/ar/CActiveFinder.php(366): CDbCommand->queryAll()

#2 /home/projects/nyy2/.framework/db/ar/CActiveFinder.php(305): CJoinElement->runQuery(Object(CJoinQuery))

#3 /home/projects/nyy2/.framework/db/ar/CActiveFinder.php(140): CJoinElement->lazyFind(Object(Booking))

#4 /home/projects/nyy2/.framework/db/ar/CActiveRecord.php(434): CActiveFinder->lazyFind(Object(Booking))

#5 /home/projects/nyy2/protected/views/booking/_bfBooking.partial.php(169): CActiveRecord->__isset('bookingRooms')

#6 /home/projects/nyy2/.framework/web/CBaseController.php(119): require('/home/projects/…')

#7 /home/projects/nyy2/.framework/web/CBaseController.php(88): CBaseController->renderInternal('/home/projects/…', Array, true)

#8 /home/projects/nyy2/.framework/web/CController.php(572): CBaseController->renderFile('/home/projects/…', Array, true)

#9 /home/projects/nyy2/protected/views/booking/bookingForm.partial.php(19): CController->renderPartial('_bfBooking.part…', Array)

#10 /home/projects/nyy2/.framework/web/CBaseController.php(119): require('/home/projects/…')

#11 /home/projects/nyy2/.framework/web/CBaseController.php(88): CBaseController->renderInternal('/home/projects/…', Array, true)

#12 /home/projects/nyy2/.framework/web/CController.php(572): CBaseController->renderFile('/home/projects/…', Array, true)

#13 /home/projects/nyy2/protected/views/booking/create.php(4): CController->renderPartial('bookingForm.par…', Array)

#14 /home/projects/nyy2/.framework/web/CBaseController.php(119): require('/home/projects/…')

#15 /home/projects/nyy2/.framework/web/CBaseController.php(88): CBaseController->renderInternal('/home/projects/…', Array, true)

#16 /home/projects/nyy2/.framework/web/CController.php(572): CBaseController->renderFile('/home/projects/…', Array, true)

#17 /home/projects/nyy2/.framework/web/CController.php(505): CController->renderPartial('create', Array, true)

#18 /home/projects/nyy2/protected/controllers/BookingController.php(64): CController->render('create', Array)

#19 /home/projects/nyy2/.framework/web/actions/CInlineAction.php(32): BookingController->actionCreate()

#20 /home/projects/nyy2/.framework/web/CController.php(259): CInlineAction->run()

#21 /home/projects/nyy2/.framework/web/filters/CFilterChain.php(128): CController->runAction(Object(CInlineAction))

#22 /home/projects/nyy2/.framework/web/filters/CFilter.php(41): CFilterChain->run()

#23 /home/projects/nyy2/.framework/web/CController.php(799): CFilter->filter(Object(CFilterChain))

#24 /home/projects/nyy2/.framework/web/filters/CInlineFilter.php(54): CController->filterAccessControl(Object(CFilterChain))

#25 /home/projects/nyy2/.framework/web/filters/CFilterChain.php(125): CInlineFilter->filter(Object(CFilterChain))

#26 /home/projects/nyy2/.framework/web/CController.php(242): CFilterChain->run()

#27 /home/projects/nyy2/.framework/web/CController.php(219): CController->runActionWithFilters(Object(CInlineAction), Array)

#28 /home/projects/nyy2/.framework/web/CWebApplication.php(150): CController->run('create')

#29 /home/projects/nyy2/.framework/web/CWebApplication.php(121): CWebApplication->runController('booking', 'create')

#30 /home/projects/nyy2/.framework/base/CApplication.php(162): CWebApplication->processRequest()

#31 /home/projects/nyy2/index.php(11): CApplication->run()

#32 {main}

DB is MySQL, InnoDB tables with FK (BookingRoom.bookingId)=>(Booking.id)

Relation in Booking model is:

public function relations()

{

  return array('bookingRooms'=>array(self::HAS_MANY, 'BookingRoom', 'bookingId','order'=>'??.roomId'),);

}

Trivial loop

<?php foreach($booking->bookingGroups as $group): ?>

<?php endforeach; ?>

is the piece of code that throws the error

All works fine with 1.0.0 (r322).

Regards,

Synthetic

Thanks. Could you please turn on logging and show me the SQL statements that have problem?

This is the log for 1 request - there's a WHERE statement that shouldn't be there.

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "log" application component

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "urlManager" application component

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "request" application component

2009/01/05 21:28:35 [trace] [system.web.filters.CFilterChain] Running filter BookingController.filteraccessControl()

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "user" application component

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "session" application component

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "db" application component

2009/01/05 21:28:35 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM Booking

2009/01/05 21:28:35 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE Booking

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "clientScript" application component

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "assetManager" application component

2009/01/05 21:28:35 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM BookingGroup

2009/01/05 21:28:35 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE BookingGroup

2009/01/05 21:28:35 [trace] [system.db.CDbCommand] query with SQL: SELECT Booking.id AS t0_c0, t1.id AS t1_c0, t1.bookingId AS t1_c1, t1.roomGroupId AS t1_c2, t1.numberOfPeople AS t1_c3 FROM Booking LEFT OUTER JOIN BookingGroup t1 ON t1.bookingId=Booking.id WHERE Booking.id= ORDER BY t1.roomGroupId

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "coreMessages" application component

2009/01/05 21:28:35 [trace] [system.base.CApplication] Loading "errorHandler" application component

Actually, thinking about it logically (not always my strong-point …), it shouldn't be firing an SQL at all as this is on a new Booking, so this

<?php if(isset($booking->bookingGroups)): ?>

  <?php foreach($booking->bookingGroups as $group): ?>

  <?php endforeach; ?>

<?php endif; ?>

should simply return false & skip the loop, which is what happens in 1.0.0 - see trace below

2009/01/05 21:29:03 [trace] [system.base.CApplication] Loading "log" application component

2009/01/05 21:29:03 [trace] [system.base.CApplication] Loading "urlManager" application component

2009/01/05 21:29:03 [trace] [system.base.CApplication] Loading "request" application component

2009/01/05 21:29:03 [trace] [system.web.filters.CFilterChain] Running filter BookingController.filteraccessControl()

2009/01/05 21:29:03 [trace] [system.base.CApplication] Loading "user" application component

2009/01/05 21:29:03 [trace] [system.base.CApplication] Loading "session" application component

2009/01/05 21:29:03 [trace] [system.base.CApplication] Loading "db" application component

2009/01/05 21:29:03 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM Booking

2009/01/05 21:29:03 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE Booking

2009/01/05 21:29:03 [trace] [system.base.CApplication] Loading "clientScript" application component

2009/01/05 21:29:03 [trace] [system.base.CApplication] Loading "assetManager" application component

2009/01/05 21:29:04 [trace] [system.base.CApplication] Loading "log" application component

2009/01/05 21:29:04 [trace] [system.base.CApplication] Loading "urlManager" application component

2009/01/05 21:29:04 [trace] [system.base.CApplication] Loading "request" application component

2009/01/05 21:29:04 [trace] [system.base.CApplication] Loading "coreMessages" application component

2009/01/05 21:29:04 [trace] [system.base.CApplication] Loading "errorHandler" application component

Yeah, that was what I just wanted to ask you about. :wink:

This changed was introduced in 1.0.1 because in some scenarios, you may want to ask for the related objects of a new AR. The key point is that when you are asking for related objects, you need to make sure the AR has the needed PK value. Otherwise it will fail.

Yup, simply changing

<?php if(isset($booking->bookingGroups)): ?>

to

<?php if(isset($booking->id) && isset($booking->bookingGroups)): ?>

did the trick.

Thanks for pointing me in the right direction.

Rgds,

synthetic