1.0.1 broke my app

Archives Yii 1.0.x Discussions
1

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 ?

2

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

3

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

4

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

5

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

6

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

7

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.

8

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