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:
None 
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
Description:
MySQL is unable to execute a prepared statement calling a stored procedure that returns INOUT or OUT parameters to a middle-ware, e.g. PHP. No matter what I do, I always get the following error:

SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 3 for routine test_table.baz is not a variable or NEW pseudo-variable in BEFORE trigger

This problem has been reported several times before, dating back many years, and unfortunately all the bugs I found related to the error are either (erroneously) closed or marked as duplicate of others that are (erroneously) closed. Some sample bugs are #11638, #50031, #17898 (one of the most often repeated among all the related bugs), #27632, and #24724.

In some of those reports, the error is described by the MySQL team as being related to the inability, prior to the 5.5.3 release, to return multiple result sets from stored procedures to the C API , but that is an erroneous assessment. This is the case of bug #11638, where the problem was declared as fixed (and the bug report closed) in 5.5.3, due to the unrelated fix I'm referencing, but another person and I demonstrated at the (current) end of the bug entry that it is still a problem as of 5.6.10 and 5.5.30. Other bug reports are in a similar situation.

I'm opening a new bug report for this problem since I've seen (or, rather, have the impression) that commenting on a closed or duplicate bug report is very unlikely to catch the attention of the MySQL team.

How to repeat:
This is the sample PHP code I'm using:

$connectOpts = array
(
    PDO::ATTR_PERSISTENT => FALSE,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8, @@sql_mode = STRICT_ALL_TABLES, @@foreign_key_checks = 1'
);
$pdo = new PDO('mysql:host=' . $dbHost . ';dbname=' . $dbName, $dbUsername, $dbPassword, $connectOpts);

$foo = 1;
$bar = 1;
$query = '
    CALL
        baz(?, ?, ?)';

$stmt = $pdo->prepare($query);
$stmt->bindValue(1, $foo, PDO::PARAM_INT);
$stmt->bindValue(2, $bar, PDO::PARAM_INT);
$stmt->bindParam(3, $returnVal, PDO::PARAM_INT, 4000);

$result = $stmt->execute();
print "Result set:\n";
print_r($resultl);
print "Out param: $returnVal\n";
die();

The "baz" stored procedure is defined as accepting two IN parameters and one OUT parameter, the last one. Internally, the procedure performs a simple select (not stored in a local variable, i.e. buffered back to the caller) and then sets the OUT parameter to some test value, which is the one I'm expecting to receive back in PHP thanks to the PDOStatement->bindParam() method.

However, when executing that code I get the following exception:

SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 3 for routine test_table.baz is not a variable or NEW pseudo-variable in BEFORE trigger.

I'm using PDO in PHP 5.4.15, compiled with the mysqlnd option, on Mac OS X 10.8.3, against a 5.5.30 MySQL release.
My 5.4.14
[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!