Bug #54444 [Com]: Multiple Queries on a single conenction
Edit report at http://bugs.php.net/bug.php?id=5edit=1
ID: 5
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
Bug #54444 [Com]: Multiple Queries on a single conenction
Edit report at http://bugs.php.net/bug.php?id=5edit=1
ID: 5
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:
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;
}
}
}
Previous Comments:
[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
[2011-04-01 16:13:48] peter dot colclough at toolstation dot com
Description:
Hi, trying to build a generic DB object handler for mySqli, and have hit
an issue where we can't have more than one open query on the same
connection. Is this a bug or 'expected behaviour'?
Looking at the mysqli.c source code, it looks like it should have been
possible, but it looks like the second object overwrites the first...
I have put a sample snippet below
Bug #54444 [Com]: Multiple Queries on a single conenction
Edit report at http://bugs.php.net/bug.php?id=5edit=1
ID: 5
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:
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
Previous Comments:
[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
[2011-04-01 16:13:48] peter dot colclough at toolstation dot com
Description:
Hi, trying to build a generic DB object handler for mySqli, and have hit
an issue where we can't have more than one open query on the same
connection. Is this a bug or 'expected behaviour'?
Looking at the mysqli.c source code, it looks like it should have been
possible, but it looks like the second object overwrites the first...
I have put a sample snippet below of what I am trying to achieve if
this helps.
Any help greatly appreciated
OS: 2.6.32-21-generic #32-Ubuntu SMP Fri Apr 16 08:09:38 UTC 2010 x86_64
GNU/Linux
PHP Version = 5.3.2-1ubuntu4.5
Test script:
---
-- Code Snippet -
$sqlstock = 'select foo1 from bar1 where foo1 =?';
$sqltime = 'select foo2, foo3 from bar2 where foo4 =?';
$varinp = ;
$abindVars = array(0=$varinp);
$varProd = '';
$conn = dbi-db_conn;
$sprod = '';
$timestart = microtime_float();
// Get a statement
$aRes = array();
$aRes2 = array();
// Init 2 Statements
$stmt = mysqli_stmt_init($conn);
$stmt2 = mysqli_stmt_init($conn);
// Prepare 2 statements
mysqli_stmt_prepare($stmt,$sqlstock);
mysqli_stmt_prepare($stmt2,$sqltime);
// Set the bind variable
$varinp = PXX00019263;
// Bind the statements
mysqli_stmt_bind_param($stmt,'s', $varinp);
mysqli_stmt_bind_param($stmt2,'s', $varinp);
// Execute - Second one fails
mysqli_stmt_execute($stmt);
mysqli_stmt_execute($stmt2);
// Set up field Defs
$aFieldDefs = array();
$aFieldDefs2 = array();
// Get result Metadata
$result = mysqli_stmt_result_metadata($stmt);
$result2 = mysqli_stmt_result_metadata($stmt2);
$nCount = 0;
while($aFieldDefs[$nCount] = mysqli_fetch_field($result)){
echo('Field = '.print_r($aFieldDefs, true).\r\n);
$aRes[$aFieldDefs[$nCount++]-name] = null;
}
$nCount = 0;
while($aFieldDefs2[$nCount] = mysqli_fetch_field($result2)){
echo('Field = '.print_r($aFieldDefs2, true).\r\n);
$aRes2[$aFieldDefs2[$nCount++]-name] = null;
}
// Bind Results
mysqli_stmt_bind_result($stmt, $aRes['foo1']);
mysqli_stmt_bind_result($stmt2, $aRes2['foo2'], $aRes2['foo3'])
// Fetch Results
mysqli_stmt_fetch($stmt);
mysqli_stmt_fetch($stmt2);
echo(print_r($aRes, true).\r\n);
echo(print_r($aRes2, true).\r\n);
--- End Code Snippet --
Expected result:
Array
(
[foo1] = 'PXX00019263'
)
Array
(
[foo2] = 2009-09-15 12:05:02
[foo3] = -00-00 00:00:00
)
Actual result:
--
Array
(
[foo1] =
)
Array
(
[foo2] = 2009-09-15 12:05:02
[foo3] = -00-00 00:00:00
)
--
Edit this bug report at http://bugs.php.net/bug.php?id=5edit=1
