MySQL错误-命令不同步；您现在不能运行此命令 [英] MySQL Error - Commands out of sync; you can't run this command now

问题描述

我正在将MySQL与PHP，Codeigniter结合使用.我有一个问题，在此处

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.

我通过更改

$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屋！