php mysql运行无错误,php – MySQL错误 – 命令不同步;你现在不能运行这个命令

我正在使用MySQL和PHP,Codeigniter.我有一个问题,在here之后由bluefeet回答

我为bluefeet创建了第二个解决方案的存储过程.然而,它工作得很完美,而在生产环境中调用该过程时,所有其他用户都会收到错误

Commands out of sync; you can’t run this command now

不知道我怎么能克服这个错误.我还尝试在调用过程后关闭连接,但是,在关闭连接之前,将执行来自其他用户的查询.解决这个问题的任何解决办法？

下面是我使用的存储过程

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是

$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驱动程序有处理存储过程的错误.

我通过更改在配置/数据库文件中将驱动程序从mysql更改为mysqli

$db['default']['dbdriver'] = 'mysql';

至

$db['default']['dbdriver'] = 'mysqli';

发布我修改了system / database / drivers / mysqli / mysqli_result.php文件并添加了以下功能

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;

}

这解决了这个问题！

标签：php,mysql,stored-procedures,codeigniter,pivot

来源： https://codeday.me/bug/20190713/1446733.html