MySQL错误 - 命令不同步;您现在无法运行此命令 [英] MySQL Error - Commands out of sync; you can't run this command now
问题描述
我使用MySQL与PHP,Codeigniter。我有一个问题,由后面的此处的bluefeet回答
I am using MySQL with PHP, Codeigniter. I had a question which was answered by bluefeet in the post here
我通过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
命令不同步;您现在无法运行此命令
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;
我用于调用此过程的PHP是
The PHP that i use for calling this procedure is
$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;
}
推荐答案
似乎codeigniter的mysql驱动程序有错误处理存储过程。
Got the Answer! It seems like codeigniter's mysql driver has bugs handling stored procedures.
我在 config / database 文件中更改了驱动程序从mysql到mysqli
I changed the drivers from mysql to mysqli in the config/database file by changing
$db['default']['dbdriver'] = 'mysql';
到
$db['default']['dbdriver'] = 'mysqli';
我修改了 system / database / drivers / mysqli / mysqli_result.php 文件并添加了以下函数
Post that i modified the system/database/drivers/mysqli/mysqli_result.php file and added the below function
function next_result()
{
if (is_object($this->conn_id))
{
return mysqli_next_result($this->conn_id);
}
}
并修改以下模型
$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;
}
这解决了问题!
这篇关于MySQL错误 - 命令不同步;您现在无法运行此命令的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!