ID: 42548 Updated by: [EMAIL PROTECTED] Reported By: garethjo at usc dot edu Status: Assigned Bug Type: MySQLi related Operating System: Windows XP, Windows 2003 PHP Version: 5.2.4 Assigned To: georg New Comment:
See also http://dev.mysql.com/doc/refman/5.0/en/mysql-set-server-option.html Enabling multiple-statement support with MYSQL_OPTION_MULTI_STATEMENTS_ON does not have quite the same effect as enabling it by passing the CLIENT_MULTI_STATEMENTS flag to mysql_real_connect(): CLIENT_MULTI_STATEMENTS also enables CLIENT_MULTI_RESULTS. If you are using the CALL SQL statement in your programs, multiple-result support must be enabled; this means that MYSQL_OPTION_MULTI_STATEMENTS_ON by itself is insufficient to allow the use of CALL. Previous Comments: ------------------------------------------------------------------------ [2007-11-19 18:21:19] [EMAIL PROTECTED] The change to 5.2.4 was correct, previously the CLIENT_MULTI_STATEMENTS flag was actually inverted instead of reset, and as the default value for flags is 0 it was actually set, not reset, most of the time. Multiple statements are only temporarily enabled using the MYSQL_OPTION_MULTI_STATEMENTS_ON and _OFF arguments to mysql_set_server_option(). The problem here is that CLIENT_MULTI_STATEMENTS in mysql_real_connects() implicitly enables CLIENT_MULTI_RESULTS, too, but MYSQL_OPTION_MULTI_STATEMENTS_ON only enables multiple statements, *not* multiple results. So the solution is to always remove CLIENT_MULTI_STATEMENTS on connect but at the same time to always enable CLIENT_MULTI_RESULTS as this can't be modified later (patch against latest 5.2 CVS): $ cvs diff -u mysqli_api.c Index: mysqli_api.c =================================================================== RCS file: /repository/php-src/ext/mysqli/mysqli_api.c,v retrieving revision 1.118.2.22.2.18 diff -u -u -r1.118.2.22.2.18 mysqli_api.c --- mysqli_api.c 17 Oct 2007 08:19:50 -0000 1.118.2.22.2.18 +++ mysqli_api.c 19 Nov 2007 18:20:28 -0000 @@ -1438,6 +1438,8 @@ MYSQLI_FETCH_RESOURCE(mysql, MY_MYSQL *, &mysql_link, "mysqli_link", MYSQLI_STATUS_INITIALIZED); + /* set some required options */ + flags |= CLIENT_MULTI_RESULTS; /* needed for mysql_multi_query() */ /* remove some insecure options */ flags &= ~CLIENT_MULTI_STATEMENTS; /* don't allow multi_queries via connect parameter */ if ((PG(open_basedir) && PG(open_basedir)[0] != '\0') || PG(safe_mode)) { ------------------------------------------------------------------------ [2007-10-21 14:35:13] ajs at ictpro dot ch Will this bug be fixed in 5.2.5? Thanks... ------------------------------------------------------------------------ [2007-10-02 20:47:37] Al dot Smith at aeschi dot ch dot eu dot org Ok, so it turns out that this bug is amazingly easy to figure out. I just started looking at changes between 5.2.3 and 5.2.4 in ext/mysqli. It turns out that there really aren't many changes in the mysqli code, and of course one of them deals with the CLIENT_MULTI_STATEMENTS flag. Reverting this change between 5.2.3 and 5.2.4 means that 5.2.4 is once again able to do multi-queries. Goody gumdrops. diff -ur php-5.2.4-orig/ext/mysqli/mysqli_api.c php-5.2.4/ext/mysqli/mysqli_api.c --- php-5.2.4-orig/ext/mysqli/mysqli_api.c 2007-07-24 11:22:16.000000000 +0200 +++ php-5.2.4/ext/mysqli/mysqli_api.c 2007-10-02 22:43:49.000000000 +0200 @@ -1433,7 +1433,7 @@ MYSQLI_FETCH_RESOURCE(mysql, MY_MYSQL *, &mysql_link, "mysqli_link", MYSQLI_STATUS_INITIALIZED); /* remove some insecure options */ - flags &= ~CLIENT_MULTI_STATEMENTS; /* don't allow multi_queries via connect parameter */ + flags ^= CLIENT_MULTI_STATEMENTS; /* don't allow multi_queries via connect parameter */ if ((PG(open_basedir) && PG(open_basedir)[0] != '\0') || PG(safe_mode)) { flags &= ~CLIENT_LOCAL_FILES; } ------------------------------------------------------------------------ [2007-09-12 16:42:58] garethjo at usc dot edu This is the example of the code from my initial bug report reworked to use the multi_query. It uses the same database setup script as the original script in the first bug report and produces the same error: //------------ BUG TEST START -------------------------- $mysqli = mysqli_init(); $mysqli->real_connect('localhost', 'root', 'root_pass', 'test'); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } if($mysqli->multi_query ("CALL spGetProducts();")) { do { if($objResult = $mysqli->store_result()) { while($row = $objResult->fetch_assoc()) { print $row["strProductName"]." ".$row["douProductPrice"]."<br>\r\n"; } $objResult->close(); if($mysqli->more_results()) { print "------------------------<BR>"; } } else { print "no results found"; } }while ( $mysqli->next_result()); } else { print $mysqli->error; } $mysqli->close(); ?> ------------------------------------------------------------------------ [2007-09-12 08:55:24] uwendel at mysql dot com Your code snippets does not show proper usage of mysqli_multi_query(). Stored Procedures that return n result sets will return n + 1 result sets. In your case, it's two result sets to fetch and eat up before you can reuse the line. The error message from the server is exactly about that. Proper usage of mysqli_multi_query() looks like this: if (mysqli_multi_query($link, 'CALL p()')) { do { if ($res = mysqli_store_result($link)) { while ($row = mysqli_fetch_assoc($res)) var_dump($row); mysqli_free_result($res); } } while (mysqli_more_results($link) && mysqli_next_result($link)); } else { printf("Cannot call SP, [%d] %s\n", mysqli_errno($link), mysqli_error($link)); } I see you replacing mysqli_[real_]query() with mysqli_multi_query() but I do not see the more_results()/next_result() loop etc. You continue using the syntax for SPs which do not return a result set. Ulf ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42548 -- Edit this bug report at http://bugs.php.net/?id=42548&edit=1
