DB error only when YII_DEBUG is false?

Yii 1.1 General Discussion
1

I’m getting the following error, but ONLY when YII_DEBUG is set to false:




CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number

As soon as I set YII_DEBUG to true, everything works fine. The error and stack trace is:

The relevant methods from my Stop model look like this:




  public function distance($latitude, $longitude, $units) {

    	$multiplier = Distance::getMultiplier($units);

    	$this->getDbCriteria()->mergeWith(array(

    		'select'=> array("`t`.*", "GeoDistance(location, POINT(:longitude_d, :latitude_d))*$multiplier as distance"),

     	    	'params' => array(

     	    		':latitude_d' => $latitude, 

     	    		':longitude_d' => $longitude)

    	));

    	 

    	return $this;

    }

    

    public function nearby($latitude, $longitude, $distance = 0, $limit = -1, $units = Distance::UNIT_DEFAULT) {

    	$multiplier = Distance::getMultiplier($units);

    	$this->getDbCriteria()->mergeWith(array(

    		'condition' => "(:max_distance=0 OR GeoDistance(location, POINT(:longitude_n, :latitude_n))*$multiplier<:max_distance)",

    		'limit' => $limit,

    		'params' => array(

    			':max_distance' => $distance,

    			':latitude_n' => $latitude,

    			':longitude_n' => $longitude,

    		),

    		'order' => 'distance ASC'

    	));

    	return $this;

    }

    

    public function findNearby($latitude, $longitude, $distance = 0, $limit = -1, $units = Distance::UNIT_DEFAULT) {

    	return $this->distance($latitude, $longitude, $units)->nearby($latitude, $longitude, $distance, $limit, $units)->findAll();

    }

}

Basically, I have a MySQL function called GeoDistance that calculates the distance between two latitude/longitude locations, and so what this is doing is finding all Stop’s that are within a certain distance. I thought maybe there was a conflict between the parameter names, so that’s why there’s the “_n” and “_d” postfix added. But that didn’t help.

Do parameters get bound differently when YII_DEBUG is set to false? I can’t figure out why this would only work when YII_DEBUG is true. Does anyone have any insight into this?

Thanks!

2

Well I guess sleeping on this helped, as I was able to get it to work this morning. So in case someone else has the same issue, here is what I figured out:

The issue appeared to be with using the “:max_distance” parameter twice in the nearby() method’s SQL condition, but only defining it once as a parameter. I’m not sure why exactly, but there must be some parameter optimization done when YII_DEBUG is false. When I created two separate parameters and just assigned them the same value it started working:




    public function nearby($latitude, $longitude, $distance = 0, $limit = -1, $units = Distance::UNIT_DEFAULT) {

    	$multiplier = Distance::getMultiplier($units);

    	$this->getDbCriteria()->mergeWith(array(

    		'condition' => "(:max_distance_1=0 OR GeoDistance(location, POINT(:longitude_n, :latitude_n))*$multiplier<:max_distance_2)",

    		'limit' => $limit,

    		'params' => array(

    			':max_distance_1' => $distance,

    	    		':max_distance_2' => $distance,

    			':latitude_n' => $latitude,

    			':longitude_n' => $longitude,

    		),

    		'order' => 'distance ASC'

    	));

    	return $this;

    }