Edit report at http://bugs.php.net/bug.php?id=54444&edit=1
ID: 54444 Comment by: peter dot colclough at toolstation dot com Reported by: peter dot colclough at toolstation dot com Summary: Multiple Queries on a single conenction Status: Bogus Type: Bug Package: MySQLi related Operating System: Ubuntu 10 64 bit PHP Version: 5.3SVN-2011-04-01 (SVN) Block user comment: N Private report: N New Comment: Johannes, I accept what you, and Ulf, are saying, however.... do you think it right that the language shouold dictate what the application developer can do? While it would be really dumb to have 6 x 10 gig results stored on the server, it may be a necessity, and memory is not a main issue these days. Its up to the app developer how they store the data, and where it is stored mid query. We are processing, on average, 1000 queries a second over any 24 hr period. Some of those have complex joins, which in turn cause table locks. By using multiple prepared statements, we can get around that issue, and speed up total throughput. Will let you know when the driver is available in alpha... so you can pass comments, if you are interested. Previous Comments: ------------------------------------------------------------------------ [2011-05-11 11:12:25] johan...@php.net The server won't process a second query unless all data from the previous has been fetched. As Ulf said you can buffer the result set locally so you can send the next query. We don't do this intentionally by default as loading result sets in memory can be very hard when they are large. ------------------------------------------------------------------------ [2011-05-10 12:01:00] peter dot colclough at toolstation dot com True... but it doesn't do this ($conn is a connection) ... and don't copy this code.. its not good.. but gets the point across: $basesql = 'SELECT SQL_NO_CACHE id from table1 '; $baseqry = bmysqli_query($basesql, $conn); $qry = bmysqli_query('Select SQL_NO_CACHE * from table2 where id2 = ? ',$conn); $qry1 = bmysqli_query('Select SQL_NO_CACHE * from table3 where id3 = ? ',$conn); $qry2 = bmysqli_query('Select SQL_NO_CACHE * from table4 where id4 = ? ',$conn); while(TRUE){ $nBaseRes = bmysqli_fetch($baseqry); if(empty($nBaseRes)){ break; } $aId = array(0 =>$nBaseRes['id']); // The first bound query bmysqli_execute($qry, $aId); $nRes = bmysqli_fetch($qry); $aVals = array(0=>$nRes['id2']); bmysqli_execute($qry1, $aVals); while(TRUE){ // careful Peter $nRes1 = bmysqli_fetch($qry1); if(empty($nRes1)){ break; } $aItems = array(0 => $nRes1['id3']); bmysqli_execute($qry2, $aItems); while(TRUE){ // careful Peter $nRes2 = bmysqli_fetch($qry2); if(empty($nRes2)){ break; } } } ------------------------------------------------------------------------ [2011-05-09 17:00:40] u...@php.net Hmm, you can prepare as many statements as you want per connection. But once you have executed a statement on a connection you must fetch its results before you can execute another statement. The result set "blocks" the line. You can, of course, do an implicit fetch on the C level upon execute but that's the exact opposite of the default fetch method (unbuffered) used for prepared statements by MySQL. Its a one-liner to do that fetch in user land. No need for changes on the C level. $mysqli = new mysqli("localhost", "root", "root", "test"); $stmt1 = $mysqli->prepare("SELECT 1 AS _one FROM DUAL"); $stmt2 = $mysqli->prepare("SELECT 2 AS _two FROM DUAL"); /* execute */ if (!$stmt1->execute()) printf("[001] [%d] %s\n", $stmt1->errno, $stmt1->error); /* clear line by fetching result set */ $res1 = $stmt1->get_result(); /* execute */ if (!$stmt2->execute()) printf("[002] [%d] %s\n", $stmt2->errno, $stmt2->error); /* clear line by fetching result set */ $res2 = $stmt2->get_result(); /* fetching second first */ while ($row = $res2->fetch_assoc()) var_dump($row); $res2->free(); while ($row = $res1->fetch_assoc()) var_dump($row); $res1->free(); ------------------------------------------------------------------------ [2011-05-09 16:35:37] peter dot colclough at toolstation dot com Thanks for teh feedback. I was also getting that error, just wanted to make sure it wasn't 'me'... but actually expected behaviour. Am now devbeloping my own, that allows multiple statements per connection, as well as multiple 'prepare' statements. This will be open sourced when ready. The current mysqli interface should have been able to do this, but it was obviously decided not to allow it... which is a bit of a pain. Thanks again for your input ------------------------------------------------------------------------ [2011-05-09 16:14:45] u...@php.net You can answer this question yourself by adding a bit of error handling to your script such as ... $mysqli = new mysqli("localhost", "root", "root", "test"); $stmt1 = $mysqli->prepare("SELECT 1 AS _one FROM DUAL"); $stmt2 = $mysqli->prepare("SELECT 2 AS _two FROM DUAL"); if (!$stmt1->execute() || !($meta1 = $stmt2->result_metadata())) printf("[001] [%d] %s\n", $stmt1->errno, $stmt1->error); if (!$stmt2->execute() || !($meta2 = $stmt2->result_metadata())) printf("[002] [%d] %s\n", $stmt2->errno, $stmt2->error); ... and the answer is: [002] [2014] Commands out of sync; you can't run this command now ------------------------------------------------------------------------ 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/bug.php?id=54444 -- Edit this bug report at http://bugs.php.net/bug.php?id=54444&edit=1