MySQL 错误 - 命令不同步；你现在不能运行这个命令

2022-01-22 00:00:00 pivot php mysql codeigniter stored-procedures

我正在使用 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驱动处理存储过程有bug.

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

这解决了问题！

相关文章