我现在用
$sql="SELECT * FROM news";
$dataReader=Yii::app()->db->createCommand($sql)->query();
while(($row=$dataReader->read())!==false) {
...
}
CDbCommand 无法执行 SQL 语句: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
直接把mysql读挂了,请问有更好的办法么?
qiang
(Qiang Xue)
April 13, 2010, 12:25am
6
在这句话之前,你执行过什么语句?能把前后的代码都给出来么?
throw new CHttpException(400,'小心');
ob_end_flush();//关闭缓存
set_time_limit(0);
ob_implicit_flush(); //这个语句将强制每当有输出就自动刷新,相当于在每个echo后,调用flush()
Yii::app()->log->routes['web']->enabled =false;
Yii::app()->getDb()->createCommand('OPTIMIZE TABLE `news`')->execute();
//清空数据
$sql="SELECT MAX(id) FROM newver.news_text";
$maxId=Yii::app()->dbOld->createCommand($sql)->queryScalar();
Yii::app()->getDb()->createCommand("DELETE FROM news WHERE id<=".$maxId)->execute();
$totalAll=Yii::app()->dbOld->createCommand("SELECT COUNT(*) FROM newver.news_text")->queryScalar();
echo '总共有 '.$totalAll.' 条数据<hr />';
$successCount=$failCount=0;
$insertSql = 'INSERT INTO {{news}}
(id,uuid,title,content,`from`,cat_id,created,updated,hits,tags,b1,b2,b3)
VALUES
(:id,:uuid,:title,:content,:from,:cat_id,:created,:updated,:hits,:tags,:b1,:b2,:b3)';
$cmd = Yii::app()->getDb()->createCommand($insertSql);
$cmd->bindParam('id', $id);
$cmd->bindParam('uuid', $uuid);
$cmd->bindParam('title', $title);
$cmd->bindParam('content', $content);
$cmd->bindParam('from', $from);
$cmd->bindParam('cat_id', $cat_id);
$cmd->bindParam('updated', $updated);
$cmd->bindParam('created', $created);
$cmd->bindParam('hits', $hits);
$cmd->bindParam('tags', $tags);
$cmd->bindParam('b1', $b1);
$cmd->bindParam('b2', $b2);
$cmd->bindParam('b3', $b3);
for ($i=0;$i<$totalAll;$i=$i+300) {
$sql="SELECT * FROM newver.news_text limit $i,300";
$dataReader=Yii::app()->dbOld->createCommand($sql)->query();
while(($row=$dataReader->read())!==false) {
$id=$row['id'];
$uuid=String::uuid();
$title=$row['title'];
$content=$row['newstext'];
$from=$row['origin'];
$updated=date('Y-m-d H:i:s',$row['addtime']);
$created=date('Y-m-d H:i:s',$row['addtime']);
$hits=$row['hits'];
$tags=$row['text_key'];
$b1=$row['if_hot'];
$b2=$row['indextop'];
$b3=$row['if_pic'];
$cat_id=(int)Yii::app()->getDb()->createCommand("SELECT id FROM news_cat WHERE lvl=2 AND old_id='{$row['type_id']}'")->queryScalar();
echo $id.'('.$title.')';
if ($cmd->execute()) {
echo '<font color=red>导入成功</font>';
$successCount++;
} else {
echo '<font color=blue>导入失败</font>';
$failCount++;
}
echo '<br />';
flush();
usleep(5);
}
echo '<br />';
}
echo '<hr />成功('.$successCount.') 失败('.$failCount.')';
以上是完整的代码了,呵呵,我现在用for来拆分
如果不用for来进行limit限制读取的话,老数据库挂的时候,新数据库一条新的记录都还没有。
qiang
(Qiang Xue)
April 13, 2010, 1:19am
10
这个可能是mysql buffered query导致的 (因为你用了嵌套的读)。你可以试试在一开始的时候调用:
Yii::app()->db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
谢谢,我尝试一下,强说的“因为你用了嵌套的读”是指哪句呀?
因为我本来是没有for循环,而出错。
qiang
(Qiang Xue)
April 13, 2010, 1:30am
12
应该是这个:
$dataReader=Yii::app()->dbOld->createCommand($sql)->query();
while(($row=$dataReader->read())!==false) {
.....
$cat_id=(int)Yii::app()->getDb()->createCommand("SELECT id FROM news_cat WHERE lvl=2 AND old_id='{$row['type_id']}'")->queryScalar();
第一句的reader还没读完数据,接着又执行新的SQL。某些mysql服务器或驱动可能会出现问题。
强,太厉害了,哈哈。现在导入很流畅,不会出现一直的读的状态了……very good
你上面说的新的SQL ,读取是dbOld,另外一个是db。不一样的连接,也会出现问题吗?
qiang
(Qiang Xue)
April 13, 2010, 1:43am
14
有可能的。这个可能是mysql PDO的限制。具体我也不清楚。你可以google一下。
强:非常不好意思啊,刚刚我在头上加了
Yii::app()->db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
Yii::app()->dbOld->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
却忘记去掉for循环。现在去掉了,这个问题又来了。
错误信息
CDbException
描述
CDbCommand 无法执行 SQL 语句: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
源文件
/home/home.net/wzhouse/lib/yii/framework/db/CDbCommand.php(375)
00363: }
00364:
00365: if($this->_connection->enableProfiling)
00366: Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');
00367:
00368: return $result;
00369: }
00370: catch(Exception $e)
00371: {
00372: if($this->_connection->enableProfiling)
00373: Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');
00374: Yii::log('Error in querying SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');
00375: throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
00376: array('{error}'=>$e->getMessage())));
00377: }
00378: }
00379: }
堆栈追踪
#0 /home/home.net/wzhouse/lib/yii/framework/db/CDbCommand.php(302): CDbCommand->queryInternal('fetchColumn', 0, Array)
#1 /home/home.net/wzhouse/0577/protected/modules/admin/views/doOld/pages/news.php(60): CDbCommand->queryScalar()
#2 /home/home.net/wzhouse/lib/yii/framework/web/CBaseController.php(119): require('/home/home.net/...')
#3 /home/home.net/wzhouse/lib/yii/framework/web/CBaseController.php(88): CBaseController->renderInternal('/home/home.net/...', NULL, true)
#4 /home/home.net/wzhouse/lib/yii/framework/web/CController.php(748): CBaseController->renderFile('/home/home.net/...', NULL, true)
#5 /home/home.net/wzhouse/lib/yii/framework/web/CController.php(687): CController->renderPartial('pages/news', NULL, true)
#6 /home/home.net/wzhouse/lib/yii/framework/web/actions/CViewAction.php(138): CController->render('pages/news')
#7 /home/home.net/wzhouse/lib/yii/framework/web/CController.php(300): CViewAction->run()
#8 /home/home.net/wzhouse/lib/yii/framework/web/CController.php(278): CController->runAction(Object(CViewAction))
#9 /home/home.net/wzhouse/lib/yii/framework/web/CController.php(257): CController->runActionWithFilters(Object(CViewAction), Array)
#10 /home/home.net/wzhouse/lib/yii/framework/web/CWebApplication.php(320): CController->run('page')
#11 /home/home.net/wzhouse/lib/yii/framework/web/CWebApplication.php(120): CWebApplication->runController('admin/doOld/pag...')
#12 /home/home.net/wzhouse/lib/yii/framework/base/CApplication.php(135): CWebApplication->processRequest()
#13 /home/home.net/wzhouse/0577/index.php(21): CApplication->run()
#14 {main}
qiang
(Qiang Xue)
April 13, 2010, 2:06am
18
那看来不是这个问题。你在while循环体的最后加一句试试:$dataReader->close()
这样可以释放cursor。
我现在直接
$sql="SELECT * FROM newver.news_text";
$dataReader=Yii::app()->dbOld->createCommand($sql)->query();
while(($row=$dataReader->read())!==false) {
$dataReader->close();
}
也不行
数据总共才2万多条,3万不到。我查了一下进程状态,一直是“Writing to net”