MySQL Stored Procedure in PHP gotcha

If you're getting a 'Commands out of sync' message after calling a Stored Procedure in MySQL 5+, it means there's some empty resultset in still in the resultset buffer. I'm not sure exactly why this is happening, but here's the workaround:


  // Assuming $connection is your MySQLI object

  while($connection->next_result()) $connection->store_result();


Web mentions


  • krteQ

    The point is that you must retrieve ALL results of the resultset before sending another SQL query to the server. For example, in PEAR:MDB2 you can use method $result->nextResult() multiple times in a while cycle, until all results are retrieved.
    SP returns always at east one result - the execution result. Any other select inside SP causes another result etc.

    Hope it helps...
  • Evert


    That does make sense, as a first-time stored procedure (ab)user it took me a bit to figure out.
  • hartmut


    A stored procedure always returns an extra empty result set that is needed to transport the exit status of the procedure itself.
  • Dave Marshall

    Dave Marshall

    I've been nailed by this one a few times, seem to forget everytime!
  • smartov


    Thank you VERY much! I was close to getting INSANE :-E trying to understand whats going on and why do I get this error...
  • Folly


    Thank you!