#43050 [Opn]: OCI8 persistent connections cannot recover from disconnect

[david at acz dot org]

 ID:               43050
 User updated by:  david at acz dot org
 Reported By:      david at acz dot org
 Status:           Open
 Bug Type:         OCI8 related
 Operating System: SuSE Linux
 PHP Version:      5.2.4
 New Comment:

I wrote a patch that adds a new flag, use_ociping:

http://david.acz.org/tmp/php-5.2.4-oci8.patch

This solves the issue and seems to be the correct solution.


Previous Comments:
------------------------------------------------------------------------

[2007-10-23 22:08:25] david at acz dot org

I found the root cause: OCIServerVersion() succeeds even though queries
fail.  You can verify this by calling oci_server_version() after the
"reconnected" line in the test script.  It works but the following query
returns ORA-01012.

------------------------------------------------------------------------

[2007-10-19 23:08:54] david at acz dot org

Description:
------------
OCI8 persistent connections cannot, by design, be closed.  The
consequence is that if the connection fails then the only recourse is to
restart PHP.  oci_pconnect always returns the same dead connection.  The
PHP application itself cannot correct the issue.  Connections can fail
due to idle timeouts, session kills, etc.

Two suggested fixes:

1) Add an oci_pclose() that always closes persistent connections.
2) Close or allow the connection to be closed after certain errors such
as 28, 1012, 2396, 3114, etc.

There are at least two ways to run the test and reproduce the error:

1) After you see the "sleeping" line, kill the session with ALTER
SYSTEM KILL SESSION.
2) Increase the sleep to 300 seconds and lower the idle timeout in the
database to 1 minute.

The code was tested with OCI 1.3.0 Beta (Revision:
1.269.2.16.2.38.4.1).

Reproduce code:
---------------
<?
$o = oci_pconnect(DB_USER, DB_PASS, DB_NAME);
var_dump($o);
if ($o === false)
{
    $err = oci_error();
    print_r($err);
}

$sql = "ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'";
query($o, $sql);

echo "sleeping\n";
sleep(60);

if (!query($o, $sql))
{
    echo "query failed, closing connection\n";
    var_dump(oci_close($o));
    $o = oci_pconnect(DB_USER, DB_PASS, DB_NAME);
    var_dump($o);
    if ($o === false)
    {
        $err = oci_error();
        print_r($err);
    }
    echo "reconnected\n";
    var_dump(query($o, $sql));
}

function query($o, $sql)
{
    $s = @oci_parse($o, $sql);
    if ($s === false)
    {
        $err = oci_error($o);
        print_r($err);
        return false;
    }
    if ([EMAIL PROTECTED]($s, OCI_COMMIT_ON_SUCCESS))
    {
        $err = oci_error($s);
        print_r($err);
        return false;
    }
    oci_free_statement($s);
    return true;
}
?>


Actual result:
--------------
resource(4) of type (oci8 persistent connection)
sleeping
Array
(
    [code] => 28
    [message] => ORA-00028: your session has been killed
    [offset] => 0
    [sqltext] => ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD
HH24:MI:SS'
)
query failed, closing connection
bool(true)
resource(8) of type (oci8 persistent connection)
reconnected
Array
(
    [code] => 1012
    [message] => ORA-01012: not logged on
    [offset] => 0
    [sqltext] => ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD
HH24:MI:SS'
)
bool(false)



------------------------------------------------------------------------


-- 
Edit this bug report at http://bugs.php.net/?id=43050&edit=1