mysql 命令不同步,MySQL错误-命令不同步；您现在不能运行此命令

I am using MySQL with PHP, Codeigniter. I had a question which was answered by bluefeet in the post here

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;

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;

}

解决方案

Got the Answer! It seems like codeigniter's mysql driver has bugs handling stored procedures.

I changed the drivers from mysql to mysqli in the config/database file by changing

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

to

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

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);

}

}

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!