Description: ------------ DSN parser does not parse client sent flags. Code Sample 1 taken from mysql.php (mysql driver for MDB2) contains lines to pass "client_flags" to the connect function but the dsn parser has no way of populating this dictionary because it does not parse for client provided flags. This is a problem because the CLIENT_MULTI_STATEMENTS CLIENT_MULTI_RESULTS are not set by default in mysql, and without them one cannot run a stored procedure. To do this I had to manual edit the driver file as you can see in Code Sample 2. Test script: --------------- Code Sample 1 (line 437 mysql.php): if (version_compare(phpversion(), '4.3.0', '>=')) { $params[] = isset($this->dsn['client_flags']) ? $this->dsn['client_flags'] : null; } Code Sample 2 (modified mysql.php): // define that client supports the multiple statements define('CLIENT_MULTI_STATEMENTS',65536); // define that client supports multiple results define('CLIENT_MULTI_RESULTS',131072); $params[] = CLIENT_MULTI_STATEMENTS|CLIENT_MULTI_RESULTS; //if (version_compare(phpversion(), '4.3.0', '>=')) { // $params[] = isset($this->dsn['client_flags']) // ? $this->dsn['client_flags'] : null; //} Expected result: ---------------- None Actual result: -------------- None

If I understand you correctly then this is a problem with MDB2 Assigning the proper package.

Yes, sorry. I'm actually also not sure if the CLIENT_MULTI_STATEMENTS is a separate bug or not. It's mostly a misconfiguration (unless this is a security matter) because it isn't on by default and php doesn't send any flags by default and doesn't parse any flags therefore there's no way to turn it on unless you edit the code like I have above.

Actually, you can set those flags using the DSN array syntax. Anyway, what are the consequences on the "normal" usage (i.e. without multi statements/results) if I enable those flags by default?

Could you explain the DSN array syntax? Is that a way of sending an array directly to the connection without going through the parser which will allow one to include the "client_flags"? Here's the documentation on those two flags from the mysql website... CLIENT_MULTI_RESULTS Tell the server that the client can handle multiple result sets from multiple-statement executions or stored procedures. This is automatically set if CLIENT_MULTI_STATEMENTS is set. See the note following this table for more information about this flag. CLIENT_MULTI_STATEMENTS Tell the server that the client may send multiple statements in a single string (separated by “;”). If this flag is not set, multiple-statement execution is disabled. See the note following this table for more information about this flag. Extra Note: If your program uses the CALL SQL statement to execute stored procedures that produce result sets, you must set the CLIENT_MULTI_RESULTS flag, either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS when you call mysql_real_connect(). This is because each such stored procedure produces multiple results: the result sets returned by statements executed within the procedure, as well as a result to indicate the call status. If you enable CLIENT_MULTI_STATEMENTS or CLIENT_MULTI_RESULTS, you should process the result for every call to mysql_query() or mysql_real_query() by using a loop that calls mysql_next_result() to determine whether there are more results. For an example, see Section 23.2.9, “C API Handling of Multiple Statement Execution”. ... ... The summary from my point of view though is that without those flags set, or at least MULTI_RESULTS stored procedures don't work inside of the MDB2 object and give you the following error: MDB2 Unknown Error PROCEDURE --- can't return a result set in the given context.

> Could you explain the DSN array syntax? > Is that a way of sending an array directly to > the connection without going through the parser > which will allow one to include the "client_flags"? http://pear.php.net/manual/en/package.database.mdb2.intro-connect.php the example is missing the 'client_flags' option, but you get the idea. Regarding the default flags, my question is: apart from making working with multiple result sets possible (in which case you have to loop through them), do you know if it has any effect on the single-statement / single-resultset case?

Thanks! In practice, although I have a relatively small sample set (only a few query and update calls) none of them were impacted by the change/addition of these flags. In theory, it should not affect any single statement because no single statement except for a stored procedure should be able to return multiple result sets as far as I understand. My only potential concern is in my various googling on this topic I did find mentions of problems running two of the same or maybe even different queries in the same connection causing a problem because the previous result set was not cleared out, so that could be there although that issue was present without these flags... this may just aggravate that issue. So... I'm not 100% sure, but in most cases it seem like it would cause any issues. Personally, I'm not sure if it's even necessary to turn them on by default. My biggest issues with the problem was the lack of visibility of what was causing it and then subsequent problems of finding a way to actually change those flags. So if a way to parse/add flags was added to the DSN (without having to use the raw flag values through the array) it would probably significantly reduce the impact of the issue. Then again... stored procedures not working by default is pretty bothersome.

After a bit more testing I did find a few problems with the multiple result set. It's similar to what I mentioned was a theoretical problem before. Essentially with multiple result sets, you always have to clear the results before any other query can be sent to the server. Documented here: http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html This is not a problem if multi results is turned off, but with it on I guess there are some additional results that are being tacked on somewhere which need to be freed before any other query can be made. In my case specifically I was trying to call an update statement which would fail with the following error: Commands out of sync; you can't run this command now

In attempting to fix this I tried both a while loop of fetchRow() statements and nextResult() statements. The nextResult is really the appropriate result to use here but I was greeted with a friendly "not implemented" error. It appears that turning those flags on is not currently an option until nextResult is implemented and we can properly parse multiple results returned from mysql. Wishlist?

Actually nextResult() is implemented in the mysqli driver... are you using the mysql driver instead? The (old) mysql extension isn't capable, AFAIK, please try with the mysqli one.

Hi Yori, can you send me some simple tests to reproduce this bug (e.g. some sample data and a stored procedure returning multiple resultset)? Thanks!

Hi Yori, I'd like to add support for SP returning multiple result sets before the next release, could you send me some sample data and a SP? It would help me a lot, thanks.