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] [email protected]
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
