Bug #69206 | Failure to execute prepared statement with OUT or INOUT parameters from PHP | ||
---|---|---|---|
Submitted: | 12 May 2013 23:50 | Modified: | 16 May 2013 18:09 |
Reporter: | Juan Manuel Palacios | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | Connectors: mysqlnd ( PHP ) | Severity: | S4 (Feature request) |
Version: | 5.5.30 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | IN parameter, out parameter, prepared statement, stored procedure |
[12 May 2013 23:50]
Juan Manuel Palacios
[12 May 2013 23:53]
Juan Manuel Palacios
I wanted to edit the steps to reproduce section of the report to delete the comment at the end saying "My 5.4.14", since that's a copy & paste error, but I don't seem to be able to...
[13 May 2013 16:15]
Alexander Nozdrin
Juan, thank you for opening a new bug report for this problem and drawing our attention to it! As I wrote in Bug#11638, this is a generic connector issue. Support of OUT-parameters span between three layers: - the server itself -- the server started to support OUT-parameters since 5.5.3 - low-level C-connector (libmysql) -- it also started to support OUT-parameters since 5.5.3 - high-level connectors (PHP, ODBC, ...) -- they haven't caught up yet. So, the category for this bug should not be "C API", but "MySQL Connector/PHP", "MySQL Connector/ODBC", ... Since we don't support multiple categories per bug, I leave it in "C API".
[13 May 2013 17:23]
Juan Manuel Palacios
Hi Alexander! Thanks again for replying. So you're saying this is indeed a PHP bug? Should I report it there? Or is the MySQL Connector/PHP layer to which the bug belongs something that's taken care of by the MySQL team, too? Like I said, hoping I'm not wrong, I don't have the impression this issue is related to the ability to return multiple result sets from a statement, as was added in 5.5.3, but actually the ability to see the bound (IN)OUT parameter in a prepared stored procedure as a legal SQL-level variable where to store the returned data. Or am I completely misunderstanding the issue? Thanks again for your time and help, appreciated!
[14 May 2013 10:14]
Alexander Nozdrin
Hi Juan, > So you're saying this is indeed a PHP bug? Yes, but see below... > Should I report it there? Or is the MySQL Connector/PHP layer > to which the bug belongs something that's taken care of by > the MySQL team, too? It's a bit complicated. On the one hand, this is a PHP-connector bug, thus, it should be reported on bugs.php.net. MySQL PHP connectors/extensions reside in the PHP code repository, so bugs.php.net is the proper place. On the other hand however, it turned out, there is little PHP-connector developers can do without additional support from the server. The level of support that was added in 5.5.3 is enough for "low-level" connector (like C API or libmysql), but it turned out to be insufficient for "high-level" connectors (like PHP connector). So, the server should be adjusted too. That's why I verified this bug report. > Like I said, hoping I'm not wrong, I don't have the impression > this issue is related to the ability to return multiple result > sets from a statement, as was added in 5.5.3, Right. You can take advantage of OUT-parameters using C API (libmysql). > but actually the ability to see the bound (IN)OUT parameter > in a prepared stored procedure as a legal SQL-level variable > where to store the returned data. Or am I completely misunderstanding > the issue? Well.. if you have a look into the query log on the server, you'll see that the server does not receive prepared statements at all. In other words, the PHP-connector has "client-side" prepared statements, which has little to do with "server-side" prepared statements. I don't know if that's true in all cases for all connectors, but that's definitely the case for CALL in the PHP connector. So, when the server receives the CALL statements, all the parameters (or rather arguments) are set: - let's say, there is "CALL p1(?, ?, ?)" in PHP code; - the server does not receive anything when the statement is prepared in the PHP code; - the server receives "CALL p1(1, 2, 3)" each time when the statement is executed in the PHP code. Thus, in order to support OUT-parameters, the PHP-connector firstly should start to support "true" or "server-side" prepared statements. Hope, this clarifies things a bit...
[14 May 2013 19:19]
Juan Manuel Palacios
Hi Alexander! Thanks for the very detailed comments, that clears up all the confusion! >> So you're saying this is indeed a PHP bug? > Yes, but see below... >> Should I report it there? Or is the MySQL Connector/PHP layer >> to which the bug belongs something that's taken care of by >> the MySQL team, too? > It's a bit complicated. > On the one hand, this is a PHP-connector bug, thus, it should be > reported on bugs.php.net. MySQL PHP connectors/extensions reside > in the PHP code repository, so bugs.php.net is the proper place. I have enough information in your response to create a detailed bug report with the PHP team, so I'll be doing that soon enough to bring their attention to the issue, and hopefully encourage them to keep an eye on related MySQL developments and switch to server-side prepared CALL statements once the suppor for (IN)OUT arguments is complete in MySQL. > On the other hand however, it turned out, there is little PHP-connector > developers can do without additional support from the server. > The level of support that was added in 5.5.3 is enough for "low-level" > connector (like C API or libmysql), but it turned out to be insufficient > for "high-level" connectors (like PHP connector). So, the server > should be adjusted too. That's why I verified this bug report. Do you happen to have any ETA on when this support for "high level" connectors will be complete, even if only a very rough one? >> but actually the ability to see the bound (IN)OUT parameter >> in a prepared stored procedure as a legal SQL-level variable >> where to store the returned data. Or am I completely misunderstanding >> the issue? > Well.. if you have a look into the query log on the server, > you'll see that the server does not receive prepared statements at all. > In other words, the PHP-connector has "client-side" prepared statements, > which has little to do with "server-side" prepared statements. > I don't know if that's true in all cases for all connectors, but that's > definitely the case for CALL in the PHP connector. > So, when the server receives the CALL statements, all the parameters > (or rather arguments) are set: > - let's say, there is "CALL p1(?, ?, ?)" in PHP code; > - the server does not receive anything when the statement > is prepared in the PHP code; > - the server receives "CALL p1(1, 2, 3)" each time when > the statement is executed in the PHP code. > Thus, in order to support OUT-parameters, the PHP-connector firstly should > start to support "true" or "server-side" prepared statements. I can confirm this. I enabled the query log on my server and saw exactly what you're saying after executing the test PHP code I originally posted, i.e.. "test_sp(1, NULL)", where in this case "test_sp" is defined as (IN in_val int, OUT out_val int). This view of things clearly explains why MySQL is complaining about the second argument not being a legal SQL variable where to store the returned data. But I ask about the ETA on the solution to the MySQL side of the problem because, if I report this to the PHP team, they're probably going to claim there's very little they can do about it, since the support for "high level" connectors that you talk about is not yet in place: I wouldn't be surprised if they replied saying there's no point in switching to server-side prepared statements as of now. And as long as that doesn't happen, it's clear that the use pattern initially proposed in this bug report will remain out of reach. So, what do you suggest we/I/whoever says to them when reporting the issue, other than the obvious need to switch to server-side prepared statements once support in MySQL is complete? > Hope, this clarifies things a bit... Very much so, thanks a bunch! Regards!
[16 May 2013 10:46]
Alexander Nozdrin
Hi Juan, > Do you happen to have any ETA on when this support for "high level" > connectors will be complete, even if only a very rough one? Unfortunately I can not comment on that. It's ready when it's ready. Sorry, but I hope you can understand that. Thanks!
[16 May 2013 18:09]
Juan Manuel Palacios
> Unfortunately I can not comment on that. It's ready when it's ready. > Sorry, but I hope you can understand that. Hi Alexander! Yes, I can perfectly undersand confidentiality, NDA's, and even unpredictability due to fuzzy roadmaps & timelines. But it'd be great to at least be able to say something to the PHP team, 'cause otherwise they're just gonna tell me to come back with my report whenever MySQL is ready... Thanks!