title: MySQL Error - Commands out of sync; you can't run this command now
body: <p>I am using MySQL with PHP, Codeigniter. I had a question which was answered by bluefeet in the post <a href="https://stackoverflow.com/questions/16012098/mysql-show-data-in-pivot-view/16013046#16013046">here</a></p>
  
  <p>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 </p>
  
  <blockquote>
    <p>Commands out of sync; you can't run this command now</p>
  </blockquote>
  
  <p>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?</p>
  
  <p>Below is the stored procedure that i have used</p>
  
  <pre><code>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 &gt;= (select date_sub(max(mailtime), interval 8 DAY)
                     from circle_pending_temp);
  
  SET @sql 
    = CONCAT('SELECT coalesce(email_Circle, ''Grand Total'') Circle,
                max(`&lt; 9 days`) `&lt; 9 days`, ', @sql, ' ,
                count(*) GrandTotal
              from
              (
                select c.email_Circle,
                  date_format(c.mailtime, ''%e-%b'') maildate,
                  coalesce(o.`&lt; 9 days`, 0) `&lt; 9 days`
                from circle_pending_temp c
                left join
                (
                  select email_Circle,
                    count(*) `&lt; 9 days`
                  from circle_pending_temp
                  where mailtime &lt;= (select date_sub(max(mailtime), interval 8 DAY)
                                      from circle_pending_temp)
                ) o
                  on c.email_Circle = o.email_Circle
                where c.mailtime &gt;= (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;
  </code></pre>
  
  <p>The PHP that i use for calling this procedure is</p>
  
  <pre><code>    $db = $this-&gt;load-&gt;database('mailbox',TRUE);
      $res = $db-&gt;query('Call circle_pending_p()');
      echo $db-&gt;_error_message();
      $db-&gt;close();
      $db = $this-&gt;load-&gt;database('mailbox',TRUE);
      if ($res-&gt;num_rows() &gt; 0) {
          return $res-&gt;result_array();
      } else {
          return 0;
      }
  </code></pre>