php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #57907 ping functionality is broken
Submitted: 2007-11-12 12:24 UTC Modified: 2009-01-22 12:44 UTC
From: david at acz dot org Assigned:
Status: Closed Package: oci8 (PECL)
PHP Version: 5.2.1 OS: SuSE Linux
Private report: No CVE-ID:
 [2007-11-12 12:24 UTC] david at acz dot org
Description:
------------
This is a follow-up to PHP #43050 (http://bugs.php.net/43050).

The ping functionality is broken because OCIServerVersion() succeeds even though the server has terminated the connection.  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.

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

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

This patch uses OCIPing() instead of OCIServerVersion().

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 (!@oci_execute($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)

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-11-12 13:22 UTC] tony at daylessday dot org
In my opinion it's clearly not PHP or OCI8 issue, but a problem that should be addressed by Oracle.
There are in fact two bugs:
1) OCIPing() crashes older servers
2) OCIServerVersion() behaves weird.

We obviously can provide dirty and half-working workarounds, but doing it on our side is simply wrong.
 [2007-11-12 17:22 UTC] david at acz dot org
OCIServerVersion() is not guaranteed to work as the code expects.  In fact, it might not even call the server.

Using OCIPing() is the right thing to do.  We can provide a work-around for users with older servers, perhaps even as the default, but that shouldn't prevent using the correct method.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci16msc007.htm#i540674

"This call returns the version string of the Oracle server. It can be in Unicode if the environment handle so determines."

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci16msc007.htm#BABIFFIC

"OCIPing() simply makes a dummy round trip call to the server, that is, a dummy packet is sent to the server for response. OCIPing() returns after the round trip is completed."
 [2008-02-18 11:27 UTC] jeff_dafoe at harte-hanks dot com
We are having the same issue here.  Looks like this was introduced in 1.2.0 when OCIPing was replaced with OCIServerVersion.  It's nearly impossble to work around this issue, from php-space we don't have a way to force the bad connections to reconnect.  Restarting the http process seems to be the only option.  If OCIServerVersion can succeed without a logged-in connection, then OCIServerVersion can't be used to test the connection status.  I agree with Tony that the problem is on the Oracle side, so PHP OCI8 should revert back to using OCIPing and the systems who cannot use OCIPing need to address the issue through Oracle.  FWIW, we have been using the PHP OCI implementation for over 5 years, 3 of which were against Oracle 8i on HP-UX, and the PHP OCI library's use of OCIPing never caused our server to crash.
 [2008-02-18 11:32 UTC] tony at daylessday dot org
Please test OCI8 CVS version, a fix's been recently applied there.
New release is expected to follow soon.
 [2009-01-21 15:10 UTC] jeff_dafoe at harte-hanks dot com
Rev 1.269.2.16.2.38.2.27 in CVS does fix this .  Release 1.3.4 (rev 1.269.2.16.2.38.2.20) doesn't have the fix implemented, perhaps you were waiting for a response from me, which I failed to provide.
 [2009-01-21 15:27 UTC] christopher dot jones at oracle dot com
Jeff, the OCI8 1.3.4 file version you referenced calls OCIPing in php_oci_connection_ping().
 [2009-01-22 10:21 UTC] jeff_dafoe at harte-hanks dot com
Yes, I see you are right.  What we were actually seeing in 1.3.4 is some intermittent failure that we cannot reproduce in a single-process environment.  We were able to reproduce it in the latest CVS version also, but not reliably, and only by "clicking furiously" inside our web app after disconnecting all sessions from Oracle. When it happens, it exhibits the same symptom set as originally reported in this issue, queries return "ORA-01012: not logged on".  However, it's not a scenario that I'd feel comfortable reporting yet, with no reliable way to reproduce it.

Looking over everything now, the thing that brought me back to this issue in the first place is that releases of PHP as recent as 5.2.8 is shipping with the older OCI 1.2.5 .  Do you think that would that be something for which I should open an issue on the bugs.php.net system?
 [2009-01-22 12:44 UTC] christopher dot jones at oracle dot com
PHP 5.2 has been accepting critical fixes for some time. OCI8 1.3 introduced new functionality and won't be in any 5.2 release.
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Thu Apr 17 16:02:22 2014 UTC