php MySQL 错误 - 命令不同步；你现在不能运行这个命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16029729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Error - Commands out of sync; you can't run this command now
提问by Guns
I am using MySQL with PHP, Codeigniter. I had a question which was answered by bluefeet in the post here
我在 PHP、Codeigniter 中使用 MySQL。我有一个问题，bluefeet 在这里的帖子中回答了这个问题
I created a stored procedure for the second solution by bluefeet. It works perfect, however, while the procedure is called in the production environment, all the other users get the error
我为 bluefeet 的第二个解决方案创建了一个存储过程。它工作得很好，但是，在生产环境中调用该过程时，所有其他用户都会收到错误
Commands out of sync; you can't run this command now
命令不同步；你现在不能运行这个命令
Not sure how can i overcome with this error. I also tried closing the connection after the procedure is called, however, Queries from other users are executed before the connection is closed. Any work-around for this issue?
不知道我该如何克服这个错误。我也尝试在调用过程后关闭连接，但是，在关闭连接之前执行来自其他用户的查询。此问题的任何解决方法？
Below is the stored procedure that i have used
下面是我使用的存储过程
DROP PROCEDURE IF EXISTS mailbox.circle_pending_p;
CREATE PROCEDURE mailbox.`circle_pending_p`()
BEGIN
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(CASE WHEN maildate = ''',
date_format(mailtime, '%e-%b'),
''' THEN 1 else 0 END) AS `',
date_format(mailtime, '%e-%b'), '`'
)
) INTO @sql
FROM circle_pending_temp
WHERE mailtime >= (select date_sub(max(mailtime), interval 8 DAY)
from circle_pending_temp);
SET @sql
= CONCAT('SELECT coalesce(email_Circle, ''Grand Total'') Circle,
max(`< 9 days`) `< 9 days`, ', @sql, ' ,
count(*) GrandTotal
from
(
select c.email_Circle,
date_format(c.mailtime, ''%e-%b'') maildate,
coalesce(o.`< 9 days`, 0) `< 9 days`
from circle_pending_temp c
left join
(
select email_Circle,
count(*) `< 9 days`
from circle_pending_temp
where mailtime <= (select date_sub(max(mailtime), interval 8 DAY)
from circle_pending_temp)
) o
on c.email_Circle = o.email_Circle
where c.mailtime >= (select date_sub(max(mailtime), interval 8 DAY)
from circle_pending_temp)
) d
group by email_Circle with rollup ');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
The PHP that i use for calling this procedure is
我用于调用此过程的 PHP 是
$db = $this->load->database('mailbox',TRUE);
$res = $db->query('Call circle_pending_p()');
echo $db->_error_message();
$db->close();
$db = $this->load->database('mailbox',TRUE);
if ($res->num_rows() > 0) {
return $res->result_array();
} else {
return 0;
}
回答by Guns
Got the Answer! It seems like codeigniter's mysql driver has bugs handling stored procedures.
得到了答案！似乎 codeigniter 的 mysql 驱动程序在处理存储过程时存在错误。
I changed the drivers from mysql to mysqli in the config/databasefile by changing
我通过更改在配置/数据库文件中将驱动程序从 mysql 更改为 mysqli
$db['default']['dbdriver'] = 'mysql';
to
到
$db['default']['dbdriver'] = 'mysqli';
Post that i modified the system/database/drivers/mysqli/mysqli_result.phpfile and added the below function
发布我修改了system/database/drivers/mysqli/mysqli_result.php文件并添加了以下功能
function next_result()
{
if (is_object($this->conn_id))
{
return mysqli_next_result($this->conn_id);
}
}
and modified the model as below
并修改模型如下
$db = $this->load->database('mailbox',TRUE);
$qry_res = $db->query('Call circle_pending_p()');
echo $db->_error_message();
$res = $qry_res->result_array();
$qry_res->next_result();
$qry_res->free_result();
if (count($res) > 0) {
return $res;
} else {
return 0;
}
This solved the problem!
这解决了问题！
回答by BlitZ
Try:
尝试：
<?php
while($dbms->more_results() && $dbms->next_result())
{
$result = $dbms->store_result();
if(is_object($result)){ $result->free(); }
unset($result);
}
?>
After procedure call. MySQLican't call another procedure, while has previous results.
You should use free()for each of them before any further procedure call or query execution.
程序调用后。MySQLi不能调用另一个过程，而有以前的结果。free()在任何进一步的过程调用或查询执行之前，您应该使用它们中的每一个。
回答by nthaih
In file
在文件中
system/database/drivers/mysqli/mysqli_result.php
Add:
添加：
function next_result()
{
if (is_object($this->conn_id))
{
return mysqli_next_result($this->conn_id);
}
}
function result($type = 'object')
{
$result = array();
if ($type == 'array') $result = $this->result_array();
else if ($type == 'object') $result = $this->result_object();
else $result = $this->custom_result_object($type);
$this->next_result();
return $result;
}
回答by Active India
I faced similary problem when I was running stored procedure in a loop. The below code I was using:
当我在循环中运行存储过程时，我遇到了类似的问题。我使用的以下代码：
foreach ($month_list as $month_row)
{
$start_date = $month_row->adate;
$end_date = date("Y-m-d", strtotime("last day of " . date("F", strtotime($start_date)) . " " . date("Y", strtotime($start_date))));
$qry_res = $this->db->query("call staff_attendance('$start_date', '$end_date')");
$res = $qry_res->result();
$qry_res->next_result();
$qry_res->free_result();
$compiled_months[] = $res;
}
In this case the temporary table I created was being complained as table already exists.
在这种情况下，我创建的临时表被投诉，因为表已经存在。
So, used this approach instead:
所以，改用这种方法：
foreach ($month_list as $month_row)
{
$start_date = $month_row->adate;
$end_date = date("Y-m-d", strtotime("last day of " . date("F", strtotime($start_date)) . " " . date("Y", strtotime($start_date))));
$compiled_months[] = $this->db->query("call staff_attendance('$start_date', '$end_date')")->result();
$this->db->close();
$this->db->initialize();
}
Now execution was perfect
现在执行是完美的
