ID: 39858
Comment by: mike at we11er dot co dot uk
Reported By: develar at gmail dot com
Status: Assigned
Bug Type: PDO related
Operating System: Windows XP SP2
PHP Version: 5.2.0
Assigned To: wez
New Comment:
Hi everyone. We have a fixed php_pdo_mysql.dll in our svn repos - but
the guy that compiled it all hasn't got the source together because it
involved a massive overhaul of the php and extension code to support
the latest mysql libs.
I know your first thoughts will be "There's no way I'm going to use a
.dll from a *hackthissite*!", that's fine... but the guy that compiled
it can be trusted, and I've been using it on my windows machine for a
week or two now.
http://source.hackthissite.org/wsvn/HTSv4/trunk/PDO/php_pdo_mysql.dll
I'll see if we can get some sort of source code version together. Until
then, use this at your own risk.
Previous Comments:
------------------------------------------------------------------------
[2007-02-27 16:29:58] james dot cordon at btinternet dot com
What I have stated previously works, but only with the PHP vers. of
libmysql.dll {error lost connection}
mysql vers. {error unbuffered queries} just keeps making new
connections until it is maxed out, have found no solution to this.
I am using the very latest vers of everything.
------------------------------------------------------------------------
[2007-02-23 12:09:20] james dot cordon at btinternet dot com
previous doesn't address stored procedure prob (from what i've read
this is a win prob only).
my revised approach:
Essentialy after you use a stored procedure call, burn another query
to force an exception.
###
private $connect_a=array();
private $connected=0;
public function __construct($dsn=NULL, $user=NULL, $pass=NULL){
if(is_array($dsn)){
$this->connect_a['DSN']=$dsn[0];
$this->connect_a['U']=$dsn[1];
$this->connect_a['P']=$dsn[2];
} else {
$this->connect_a['DSN']=$dsn;
$this->connect_a['U']=$user;
$this->connect_a['P']=$pass;
}
}//
public function query($q){
if($this->connected==0){
$this->connect();
}
return parent::query($q);
}//
public function dropConnection($stmt){
$stmt->closeCursor();
$this->connected=0;
}##
public function callStoredProcedure($stmt){
$resultset;
try{
#multi array
while ($row= $stmt->fetch()) {
$resultset[]=$row;
echo '<br />';
var_dump($row);
echo '<br />';
}
#burn
if(stripos($_SERVER['SERVER_SOFTWARE'], 'win') ){
$stmt=$this->query("select 1+1");
}
}catch(PDOException $e){
if($e->getCode()=='HY000' AND strpos ($e->getMessage(), 'Lost
connection' )){
print "<-!!!- Error!: Caught 'Lost connection error, dropConnection()
-!!!-><br />";
$this->dropConnection($stmt);
} else {
throw $e;
}
}
return $resultset;
}//
public function connect(){
#if($this->connected==1) return true;
try{
parent::__construct($this->connect_a['DSN'], $this->connect_a['U'],
$this->connect_a['P']);
} catch (Exception $e) {
throw($e);
}
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->connected=1;
}//
#EXAMPLE
#just to prove obj is same one
$pdodl_1->temp_id='AAAAAA';
$i=100;
do{
echo 'LOOP NUM:'.$i.'<br />';
echo '<br />PDODL OBJ: ';
var_dump($pdodl_1);
echo '<br /><br />';
$stmt=$pdodl_1->query("call testMany()");
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$rset=$pdodl_1->callStoredProcedure($stmt);
$i--;
} while($i>0);
This is all from another project, so may need some tinkering.
------------------------------------------------------------------------
[2007-02-23 11:29:16] martin dot schmitz at uni-bielefeld dot de
I've got a dirty solution for your problem (works under linux, not
tested on windows)
if i do:
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute( PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$datasets = $dbh->query( 'SELECT * FROM table_name_1 WHERE id=5);
$datasets->execute();
$result_1 = $datasets->fetch( PDO::FETCH_ASSOC);
$datasets = $dbh->query( 'SELECT * FROM table_name_2 WHERE id=7);
$datasets->execute();
$address = $datasets->fetch( PDO::FETCH_ASSOC);
it gets the same unbuffered error. But if I do a fetch twice on the
first result and store it to a dummy array, the second query works:
$datasets = $dbh->query( 'SELECT * FROM table_name_1 WHERE id=5);
$datasets->execute();
$result_1 = $datasets->fetch( PDO::FETCH_ASSOC);
// test, remove if bug is fixed
$dummy = $datasets->fetch( PDO::FETCH_ASSOC);
$datasets = $dbh->query( 'SELECT * FROM table_name_2 WHERE id=7);
$datasets->execute();
$address = $datasets->fetch( PDO::FETCH_ASSOC);
I think the cursor-pointer does not work correctly?? You can also do a
fetchAll(), but then your result looks like:
array[0] => ( array=>([prename] => 'Martin' [name] => 'Schmitz'))
instead of
array=>([prename] => 'Martin' [name] => 'Schmitz')
Hope it works for you
------------------------------------------------------------------------
[2007-02-22 12:39:23] james dot cordon at btinternet dot com
AHHHHHH!!!!
My bodge-it above doesn't work correctly, it just opens but doesn't
close many connections.
This does work (tried several times on 200 consec' queries)
added closeCursor().
$i=100;
while($i>0){
echo 'LOOP NUM:'.$i.'<br />';
try{
$stmt=$pdodl_1->query("call testMany()");
$stmt->setFetchMode(PDO::FETCH_ASSOC);
echo '<br />PDODL OBJ: ';
var_dump($pdodl_1);
echo '<br />PDO::STATEMENT OBJ: <br />';
var_dump($stmt);
echo '<br /><br />';
while ($row= $stmt->fetch()) {
echo '<br />';
var_dump($row);
echo '<br />';
}
$i--;
}catch(PDOException $e){
if($e->getCode()=='HY000'){
$stmt->closeCursor();
$pdodl_1->connect();
$i--;
} else {
throw $e;
}
}
}
------------------------------------------------------------------------
[2007-02-22 11:37:01] james dot cordon at btinternet dot com
php 5.2.1
win xp pro
mysql 5x
apache 2x
I also built a project assuming stored procedures would work
as they are mentioned in the php docs, anyway.
to cut a long story short, I extended PDO with methods that included
not connectimg to DB until actually needed.
public function connect(){
try{
parent::__construct($this->connect_a['DSN'], $this->connect_a['U'],
$this->connect_a['P']);
} catch (Exception $e) {
throw($e);
}
### always use exception error handling
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->connected=1;
}//
When calling a 2nd query (after a stored procedure) I get the
"SQLSTATE[HY000]:
General error: 2013 Lost connection to MySQL server during query" every
time.
I discovered calling "$pdoextended->connect();" after each use of a
stored procedure revived the connection (without instantiating another
PDOextended obj) with no errors.
This is ONLY a bodge-it but it is a very lightweight one.
------------------------------------------------------------------------
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/39858
--
Edit this bug report at http://bugs.php.net/?id=39858&edit=1