From: david at acz dot org
Operating system: SuSE Linux
PHP version: 5.2.4
PHP Bug Type: OCI8 related
Bug description: OCI8 persistent connections cannot recover from disconnect
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 bug report at http://bugs.php.net/?id=43050&edit=1
--
Try a CVS snapshot (PHP 4.4):
http://bugs.php.net/fix.php?id=43050&r=trysnapshot44
Try a CVS snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=43050&r=trysnapshot52
Try a CVS snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=43050&r=trysnapshot53
Try a CVS snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=43050&r=trysnapshot60
Fixed in CVS: http://bugs.php.net/fix.php?id=43050&r=fixedcvs
Fixed in release:
http://bugs.php.net/fix.php?id=43050&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=43050&r=needtrace
Need Reproduce Script: http://bugs.php.net/fix.php?id=43050&r=needscript
Try newer version: http://bugs.php.net/fix.php?id=43050&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=43050&r=support
Expected behavior: http://bugs.php.net/fix.php?id=43050&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=43050&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=43050&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=43050&r=globals
PHP 3 support discontinued: http://bugs.php.net/fix.php?id=43050&r=php3
Daylight Savings: http://bugs.php.net/fix.php?id=43050&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=43050&r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=43050&r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=43050&r=float
No Zend Extensions: http://bugs.php.net/fix.php?id=43050&r=nozend
MySQL Configuration Error: http://bugs.php.net/fix.php?id=43050&r=mysqlcfg
