php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #41579 PDO timeout lost connection
Submitted: 2007-06-04 04:25 UTC Modified: 2007-06-05 09:05 UTC
From: vtsupermok at gmail dot com Assigned:
Status: Closed Package: PDO related
PHP Version: 5.2.3 OS: Window
Private report: No CVE-ID: None
 [2007-06-04 04:25 UTC] vtsupermok at gmail dot com
Description:
------------
I create a pdo object and set PDO::ATTR_PERSISTENT => true, after serveral hours I run my programme again. Suppose the connection will be timeout and then the program throw a PDOException. 

But the real situation is it said time out lost connection but it wouldn't return a PDOException.

Reproduce code:
---------------
$db=array(
    'name'=>'abc',
    'host'=>'127.0.0.1',
    'port'=>'3306',
    'userID'=>'123',
    'userPwd'=>'123',
    'charset'=>'latin1',
    'encoding'=>'big-5'
);
 
try{
    $database="mysql:host={$db['host']};port={$db['port']};dbname={$db['name']}";
    $pdo = new PDO($database, $db['userID'], $db['userPwd'], array(PDO::ATTR_PERSISTENT => true));
} catch (PDOException $e) {
    throw $e;
}

Expected result:
----------------
throw a exception.

Actual result:
--------------
the mysql error is timeout lost connection but it would't throw a exception

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-06-04 08:30 UTC] tony2001@php.net
What exactly do I need to do in order to reproduce it?
 [2007-06-04 09:38 UTC] vtsupermok at gmail dot com
Dear sir

Sorry for my incomplete example, let me give you a full set program.

<?
function dbConnect($db){
    try{
        $database="mysql:host={$db['host']};port={$db['port']};dbname={$db['name']}";
        $pdo = new PDO($database, $db['userID'], $db['userPwd'], array(PDO::ATTR_PERSISTENT => true));
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_TIMEOUT,5);
        return $pdo;
    } catch (PDOException $e) {
        throw $e;
    }
}

function dbException($e,$db){
    echo "<p>";
    echo "Message : " . $e->getMessage() . "<br/>";
    echo "File : " . $e->getFile() . "<br/>";
    echo "Line : " . $e->getLine() . "<br/>";
    echo "</p>";
    die();
}       

function dbQuery($pdo,$sql,$db){
    try{
        return $pdo->query($sql);
    } catch (PDOException $e) {
        throw $e;
    }
}

function dbRecord($result,$db){       
    $resultSet=$result->fetchAll(PDO::FETCH_ASSOC);
    $counter=count($resultSet);
    if ($counter>0){
        for ($i=0;$i<$counter;$i++){
            foreach ($resultSet[$i] as $key => $value){
                echo "get data: "."$value"."<Br>";
            }
        }
    }
}

try {
    $db=array(
        'name'=>'test',
        'host'=>'127.0.0.1',
        'port'=>'3306',
        'userID'=>'abc',
        'userPwd'=>'abc',
        'charset'=>'latin1',
        'encoding'=>'big-5'
    );
    echo 'Step 1<Br />';
    $pdo=dbConnect($db);
    echo 'Step 2<Br />';
    $sql="select 'a','b','c'";
    $result=dbQuery($pdo,$sql,$db);
    echo 'Step 3<Br />';
    dbRecord($result,$db); 
    echo 'Step 4<Br />';
} catch (PDOException $e) {
    dbException($e,$db);   
}
     
?>

the testing process should be like this
1.run this script

2.the screen output:
Step 1
Step 2
Step 3
get data: a
get data: b
get data: c
Step 4

3. the connection is PERSISTENT so I kill it in db server by myself

4. run the script again, the result is like this.
Step 1
Step 2

Message : SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query
File : D:\AppServ\www\report\92\testing.php
Line : 25



The problem is, the connection was killed so it return 2013 Lost connection. I suppose the exception was throw after step 1 since the connection was fail,but the real case is this exception was throw after step 2 rather than step 1.

I have no ways to know the PERSISTENT connection is still alive or not
 [2007-06-04 11:06 UTC] tony2001@php.net
Cannot reproduce.
After restarting MySQL server, PDO reconnects successfuly and I don't see any error at all.
If I put MySQL server down, the error/exception appears on step 1. 
 [2007-06-05 06:43 UTC] vtsupermok at gmail dot com
here are my actual result 
---------------------------------------------------------------
1. Start 
2. Connect to DB by a PERSISTENT connection
3. query "select 'a','b','c'"
4. list all of the result
a b c 
5. query "show processlist"
7. suppose the thread id of this connection is 2654. (Are there any ways to get the thread id? I use a very stupid way to GUESS the thread id)
8. kill this connection query 'kill 2654'
9. the PERSISTENT connection was kill.
10. Now, when you refresh your page, I SUPPOSE there are 2 result may happened.
Case 1, since the connection was kill so it will create a new connection automatically without throw any error. 
Case 2, it will throw a exception in function dbConnect() since the connection was lost when it call create PDO object
but sadly the real case is, there are no exception was throw until you make another query.
So the problem is there are no ways check the PERSISTENT connection is still alive or not in case the connection was killed or timeout
by the way one more question, are there any function to get the thread id?

after refresh
-------------------------------------------------------------
1. Start 
2. Connect to DB by a PERSISTENT connection
3. query "select 'a','b','c'"

Message : SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query
File : D:\AppServ\www\report\92\testing.php
Line : 25
 [2007-06-05 07:33 UTC] tony2001@php.net
I'm still unable to replicate it.
mysql_ping() always fails, so the connection is actually re-established on each request.
 [2007-06-05 07:34 UTC] tony2001@php.net
What MySQL version are you using?
 [2007-06-05 07:52 UTC] vtsupermok at gmail dot com
you have kill my previous post???thank you so much~

I have try mysql version
5.0.27
5.0.18
4.1.12a
but all have the same result

you can re-established a connection automatically without any errors after you kill the connection? let me post my code again.

<?
function dbConnect($db){
    try{
        $database="mysql:host={$db['host']};port={$db['port']};dbname={$db['name']}";
        $pdo = new PDO($database, $db['userID'], $db['userPwd'], array(PDO::ATTR_PERSISTENT => true));
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_TIMEOUT,5);
        return $pdo;
    } catch (PDOException $e) {
        throw $e;
    }
}

function dbException($e,$db){
    echo "<p>";
    echo "Message : " . $e->getMessage() . "<br/>";
    echo "File : " . $e->getFile() . "<br/>";
    echo "Line : " . $e->getLine() . "<br/>";
    echo "</p>";
    die();
}       

function dbQuery($pdo,$sql,$db){
    try{
        return $pdo->query($sql);
    } catch (PDOException $e) {
        throw $e;
    }
}

function dbRecord($result,$db){       
    $resultSet=$result->fetchAll(PDO::FETCH_ASSOC);
    $counter=count($resultSet);
    if ($counter>0){
        echo "<table border=1>";
        for ($i=0;$i<$counter;$i++){
            echo "<tr>";
            foreach ($resultSet[$i] as $key => $value){
                echo "<td>"."$value"."</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
    }
}

function dbGetThreadID($result,$db){       
    $id=0;
    $resultSet=$result->fetchAll(PDO::FETCH_ASSOC);
    $counter=count($resultSet);
    if ($counter>0){
        for ($i=0;$i<$counter;$i++){
            if ($resultSet[$i]['Id']>$id){
                $id=$resultSet[$i]['Id'];
            }
        }
    }
    return $id;
}

try {
    $db=array(
        'name'=>'abc',
        'host'=>127.0.0.1',
        'port'=>'3306',
        'userID'=>'abc',
        'userPwd'=>'123',
        'charset'=>'latin1',
        'encoding'=>'big-5'
    );
    echo '1. Start <Br />';
    
    echo '2. Connect to DB by a PERSISTENT connection<Br />';
    $pdo=dbConnect($db);
    
    echo '3. query "select \'a\',\'b\',\'c\'"<Br />';
    $sql="select 'a','b','c'";
    $result=dbQuery($pdo,$sql,$db);
    
    echo '4. list all of the result<Br />';
    dbRecord($result,$db); 
    
    echo '5. query "show processlist"<br>';
    $sql="show processlist";
    $result=dbQuery($pdo,$sql,$db);
    
    $threadId=dbGetThreadID($result,$db);
    echo "7. suppose the thread id of this connection is {$threadId}. (Are there any ways to get the thread id? I use a very stupid way to GUESS the thread id)<Br />";
    
    echo "8. kill this connection query 'kill $threadId'<Br />";
    $sql="kill $threadId";
    $result=dbQuery($pdo,$sql,$db);
    
    echo "9. the PERSISTENT connection was kill.<Br />";
    echo "10. Now, when you refresh your page, I SUPPOSE there are 2 result may happened.<br />";
    echo "Case 1, since the connection was kill so it will create a new connection automatically without throw any error. <br />";
    echo "Case 2, it will throw a exception in function dbConnect() since the connection was lost when it call create PDO object<Br>";
    echo "but sadly the real case is, there are no exception was throw until you make another query.<Br>";
    echo "So the problem is there are no ways check the PERSISTENT connection is still alive or not in case the connection was killed or timeout<br>";
    echo "by the way one more question, are there any function to get the thread id?<br>";
    
} catch (PDOException $e) {
    dbException($e,$db);   
}
      
?>
 [2007-06-05 08:02 UTC] tony2001@php.net
Please reduce the script to 20 lines max.
 [2007-06-05 08:05 UTC] vtsupermok at gmail dot com
my php version is 5.1.2
and mysql client api version version is 4.1.7
 [2007-06-05 09:05 UTC] vtsupermok at gmail dot com
o..yes...it works in php 5.2.2
thank you so much

but are there any ways can get the thread id by PDO?
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri May 03 00:01:31 2024 UTC