Bug #48065 [Com]: mysqli can not have two called procedure queries active at once

Edit report at https://bugs.php.net/bug.php?id=48065&edit=1 ID: 48065 Comment by: johannes dot braunias at gmail dot com Reported by: schwern at pobox dot com Summary: mysqli can not have two called procedure queries active at once Status: Bogus Type: Bug Package: MySQLi related Operating System: OS X PHP Version: 5.2CVS-2009-04-23 (snap) Block user comment: N Private report: N New Comment: Related? http://bugs.mysql.com/bug.php?id=21543 Previous Comments: ------------------------------------------------------------------------ [2009-04-28 21:40:10] schwern at pobox dot com http://news.php.net/php.internals/43773 contains a patch to fix this issue. ------------------------------------------------------------------------ [2009-04-24 16:58:24] schwern at pobox dot com I strongly disagree with the conclusion in #35203. Querying a called procedure should not act any different than querying a normal statement. That's the point of an database abstraction layer. * To the user, its a single query. There's no user visible reason to suspect I might need to use multi_query() and pull out some invisible second result. That's all internal details that I should not be privy to. * It violates OO encapsulation to have one query effect another. * DB abstraction layers like Drupal's do not allow me to tweak how it calls mysqli. There's no db_multi_query(). I have no solution but to make a complete end run around Drupal's DB layer and drop into mysqli just to call a stored procedure safely. This is a mysqli implementation detail leaking out breaking encapsulation, and its a user trap. I understand resources are tight to fix it, but it would be nice if it was at least acknowledged as a bug/misfeature. ------------------------------------------------------------------------ [2009-04-24 13:35:45] jani@php.net See bug #35203 ------------------------------------------------------------------------ [2009-04-23 23:25:10] schwern at pobox dot com For reference, here's the MySQL 5.0 docs on "Commands out of sync". http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html ------------------------------------------------------------------------ [2009-04-23 23:10:26] schwern at pobox dot com Description: ------------ mysqli does not like having two query results active at the same time if a stored procedure was queried. MySQL balks with "Commands out of sync; you can't run this command now". The equivalent SELECT does not have this problem, nor does Perl's DBD::mysql for comparison. Work arounds appear to be explicitly free'ing the result before calling the next one, but that is not easily possible through the Drupal DB API, for example. Also I might want to return a query result from a function and have it live on out of my function's control. And its a general violation of OO to have the two result objects interfere with each other. Reproduce code: --------------- http://rafb.net/p/XnP3AN31.html (Writes to the test database and user) The script creates a table with a few rows and a stored procedure doing a "select *" on that table. It then illustrates that two concurrent calls to that procedure cause an error while two "SELECT *" queries do not. Replicated on OS X with their stock PHP 5.2.6 and a fresh php5.2-200904232230 snapshot with --with-mysqli. Using a local mysql server compiled from MacPorts. mysql Ver 14.12 Distrib 5.0.77, for apple-darwin9.6.0 (i386) using readline 6.0 Expected result: ---------------- Doing call get_stuff Done Doing select * from stuff Done Actual result: -------------- Doing call get_stuff Second query FAILED: Commands out of sync; you can't run this command now Done Doing select * from stuff Done ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=48065&edit=1