MySQL Error - Commands out of sync; you can't run this command now

Question

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

Solution 1

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!

Solution 2

Try:

<?php
while($dbms->more_results() && $dbms->next_result())
    {
        $result = $dbms->store_result();

        if(is_object($result)){ $result->free(); }

        unset($result);
    }
?>

After procedure call. MySQLi can't call another procedure, while has previous results. You should use free() for each of them before any further procedure call or query execution.

Source: http://php.net/manual/en/mysqli.query.php

Comments

[Guns]

This is not working! It worked once, but getting the error message again! :(

[BlitZ]

You should do it after ANY SQL procedure call and obtainment of it's results. I dont know how it should look while using you database wrapper. Still, you probably closed DB connection` here: $db->close(); $db = $this->load->database('mailbox',TRUE);. I don't think it needed there.

[idok]

If using mysqli, you could just call mysqli_next_result($this->db->conn_id); after the procedure call, from the model itself, instead of having to modify the mysqli library