MySQL Error - Commands out of sync; you can't

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

Answer 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!

Answer 2

In file

system/database/drivers/mysqli/mysqli_result.php

Add:

function next_result()
{
    if (is_object($this->conn_id))
    {
        return mysqli_next_result($this->conn_id);
    }
}

function result($type = 'object')
{
    $result = array();

    if ($type == 'array') $result = $this->result_array();
    else if ($type == 'object') $result = $this->result_object();
    else $result = $this->custom_result_object($type);

    $this->next_result();

    return $result;
}

Answer 3

I faced similary problem when I was running stored procedure in a loop. The below code I was using:

foreach ($month_list as $month_row) 
{

  $start_date = $month_row->adate;
  $end_date = date("Y-m-d", strtotime("last day of " . date("F", strtotime($start_date)) . " " . date("Y", strtotime($start_date))));

  $qry_res = $this->db->query("call staff_attendance('$start_date', '$end_date')");

  $res = $qry_res->result();

  $qry_res->next_result();
  $qry_res->free_result();

  $compiled_months[] = $res;

}

In this case the temporary table I created was being complained as table already exists.

So, used this approach instead:

foreach ($month_list as $month_row) 
{

  $start_date = $month_row->adate;
  $end_date = date("Y-m-d", strtotime("last day of " . date("F", strtotime($start_date)) . " " . date("Y", strtotime($start_date))));

  $compiled_months[] = $this->db->query("call staff_attendance('$start_date', '$end_date')")->result();
  $this->db->close();
  $this->db->initialize();

}

Now execution was perfect

Answer 4

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