源码剖析Yii错误 Invalid parameter number: no parameters were bound
来源:互联网 发布:淘宝上如何买衣服 编辑:程序博客网 时间:2024/06/05 00:57
ActiveRecord使用的一个陷阱导致 Invalid parameter number: no parameters were bound
请看下面的例子
$criteria = new CDbCriteria();$criteria->select = "*";$model = Biubiu::model();$ids = range(160,163);$criteria->addInCondition("id", $ids);$model->findByPk(160);//某次操作sleep(32);//处理其他事情花费了较长时间。$result = $model->findAll($criteria);<1>//$result = $model->getCommandBuilder()->createFindCommand($model->getTableSchema(),$criteria)->queryAll();<2>if($result){ echo count($result);}
为了体现这个问题,我的本地数据库wait_timeout = 30
那么会出现下面的问题:
exception 'Exception' with message 'exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound. The SQL statement executed was: SELECT * FROM `t_biubiu` `t` WHERE id IN (:ycp0, :ycp1, :ycp2, :ycp3). Bound with :ycp0=160, :ycp1=161, :ycp2=162, :ycp3=163' in E:\xxxx\framework\db\CDbCommand.php:569Stack trace:#0 E:\xxxx\framework\db\CDbCommand.php(578): CDbCommand->queryInternalAll('fetchAll', Array, Array)#1 E:\xxxx\framework\db\CDbCommand.php(418): CDbCommand->queryInternal('fetchAll', Array, Array)#2 E:\xxxx\framework\db\ar\CActiveRecord.php(1356): CDbCommand->queryAll()#3 E:\xxxx\framework\db\ar\CActiveRecord.php(1475): CActiveRecord->query(Object(CDbCriteria), true)#4 E:\xxxx\experiment\protected\commands\YiiCommand.php(18): CActiveRecord->findAll(Object(CDbCriteria))
我的另一片文章和这个问题的原因都是一样的:Yii 数据库重连告别General error: 2006 MySQL server has gone away
以我所见的解决方法就是 :
1—— 尽量避免上面的这种写法,直接addInCondition,把id填入SQL
$criteria->addCondition("id in (". join(",",$ids) . ")");
2——就是重连。
分析:
并不是参数没有绑定,查看findAll()的源码
public function findAll($condition='',$params=array()){ Yii::trace(get_class($this).'.findAll()','system.db.ar.CActiveRecord'); $criteria=$this->getCommandBuilder()->createCriteria($condition,$params); return $this->query($criteria,true);}
query实际上执行的是:
$command=$this->getCommandBuilder()->createFindCommand($this->getTableSchema(),$criteria);$command->queryAll();
而createFindCommand()
调用bindValue(),里面的代码如下:
$this->_statement->bindValue($name,$value,$this->_connection->getPdoType(gettype($value)));
连接已经timeout,失效了。bindValue无用。
queryAll() --> queryInternal('fetchAll',PDO::FETCH_ASSOC,[]);-->三次调用 queryInternalAll('fetchAll',PDO::FETCH_ASSOC,[])
简化的queryInternalAll如下:
private function queryInternalAll($method,$mode,$params=array()){ $params=array_merge($this->params,$params); try { $this->prepare(); @$this->_statement->execute(); { $mode=(array)$mode; call_user_func_array(array($this->_statement, 'setFetchMode'), $mode); $result=$this->_statement->$method(); $this->_statement->closeCursor(); } return $result; } catch(Exception $e) { $errorInfo=$e instanceof PDOException ? $e->errorInfo : null; $message=$e->getMessage(); if(YII_DEBUG) $message.='. The SQL statement executed was: '.$this->getText().$par; if(!empty($errorInfo) && (2006 == $errorInfo[1] || 2013 == $errorInfo[1])) { $this->_connection->setActive(false); $this->cancel(); } throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}', array('{error}'=>$message)),(int)$e->getCode(),$errorInfo); }}
这样就看到了
exception ‘Exception’ with message ‘exception ‘CDbException’ with message ‘CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound. The SQL statement executed was:
另外:设置db属性:
'attributes' => [ PDO::ATTR_TIMEOUT => 600,],
是没有用处的。
但可以考虑使用:
Yii::$app->db->createCommand('SET SESSION wait_timeout = 28800;')->execute();
重试机制:
//Biubiu::getDb()->close(); 这么搞也行但是每次都会关闭。 do{ try{ $result = Biubiu::find()->select("id,value")->where(['id'=>$ids])->all(); foreach ($result as $one){ echo $one->id . ">" .$one->value . PHP_EOL; } }catch (\yii\db\Exception $e){ Biubiu::getDb()->close(); Biubiu::getDb()->open(); if(strpos($e->getMessage(), 'MySQL server has gone away')===false){ throw new Exception($e); } } }while(--$retry);
执行时间较长的脚本,并且一段时间就会结束的,不能用持久化连接PDO::ATTR_PERSISTENT => true
,适合用这种做法进行重连,能有效防止闪断等超时错误。
前面提到的文章给出的解决办法并不适合大量反复的数据库访问,会多很多不必要的ping操作。然而就几千条的数据,就不必在乎其带来的性能影响。
- 源码剖析Yii错误 Invalid parameter number: no parameters were bound
- 出现Invalid parameter number: no parameters were boundStack
- Invalid parameter number: number of bound variables does not match number of tokens
- ThinkPHP5更新数据遇到SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters
- mybatis错误: Invalid bound statement
- mybatis错误Parameter index out of range (1 > number of parameters, which is 0)
- mybatis错误: Invalid bound statement (not found)
- mybatis错误:Invalid bound statement (not found)
- mybatis错误:Invalid bound statement (not found)
- mybatis 错误排查BindingException: Invalid bound statement
- mybatis错误:Invalid bound statement (not found)
- mybatis错误 Invalid bound statement (not found)
- Invalid bound statement 错误的可能原因
- Invalid bound statement (not found)错误
- [MyBatis]Invalid bound statement (not found)错误
- mybatis错误:Invalid bound statement (not found)
- MyBatis Invalid bound statement (not found)错误
- mybatis错误:Invalid bound statement (not found)
- Coursera Machine Learning 第二周 quiz Linear Regression with Multiple Variables 习题答案
- Catch That Cow(BFS和queue()用法)
- Java将小写金额转换成大写
- 子进程和父进程是否共用同一个全局变量
- ALV 动态显示列Demo
- 源码剖析Yii错误 Invalid parameter number: no parameters were bound
- 1613-3-傅溥衍 总结《2016年11月9日》【连续四十天总结】
- SqlServer数据库定时删除系统日志
- Node.js入门学习笔记-IDE选择/配置之WebStorm(windows)
- uml(四) 包图
- 区块链公开课
- 将java项目部署到linux服务器
- 基于注解的SpringMVC简单介绍
- 安卓开发通过自定义Gradle插件实现自动化埋点