PHP oci_connect() stuck / no time-out

Question

We are using a Lumen 5.2.x (Laravel) application to get data from a Oracle Database. For that reason we use oci_connect() to connect to the database. (Extra info: we use Oracle instantclient)

For a reason unknown, the application was not responsive and wouldn't return any data. After lots of hours debugging we found out that it got stuck in that very same method: oci_connect(). Apparently the function did not return a 'time-out'-message or anything similar.

Later, it seemed the database moved to another host, which is the reason it couldn't connect. However, we expected a error, instead of a huge amount of waiting.

This is the reason we are trying to force a time-out to be set, until now this has not worked out.

Things we have tried:

Adding this to the connection string: (CONNECT_TIMEOUT=10)(RETRY_COUNT=3) which is completely ignored.
Setting max_execution_time and set_time_limit to 1

Adding a sqlnet.ora with settings:

TCP.CONNECT_TIMEOUT=10

SQLNET.INBOUND_CONNECT_TIMEOUT=10

SQLNET.OUTBOUND_CONNECT_TIMEOUT=10

Everything we have tried failed, does anyone know how to work around this bug? Any help is appreciated!

Edit: System info: Windows Server 2012 R2, IIS 8, PHP 5.6

On what system is your web server on (on what kind of operating system is running your webserver with PHP ?)? — Cedric, Apr 11 '18 at 08:16
Can you see if your php is configured with the following? `./configure --with-oci8=shared,instantclient` otherwise it's known to give problems — Sander Visser, Apr 11 '18 at 09:09
btw more information http://php.net/manual/en/oci8.installation.php Also you could take a look into the source code: https://github.com/php/php-src/blob/master/ext/oci8/oci8.c#L1669 — Sander Visser, Apr 11 '18 at 09:12

score 3 · Answer 1 · answered Apr 11 '18 at 11:21

3

below is laravel package used for oracle, you can try this,

laravel package for oracle

answered Apr 11 '18 at 11:21

M Usman Nadeem

415
2
13

Thanks, we used this package a while ago, but didn't quite like it (i cannot remember why though). We might want to check it out again! – AuStrike Apr 12 '18 at 09:18

score 1 · Answer 2 · answered Apr 25 '18 at 11:51

I copied the oracle array from oracle.php to the database.php config file and the issue has gone away.

Contents of my oracle.php file:

return [
'oracle' => [
    'driver'   => 'oracle',
    'tns'      => env('DB_TNS', ''),
    'host'     => env('DB_HOST', 'localhost'),
    'port'     => env('DB_PORT', '1521'),
    'database' => env('DB_DATABASE', ''),
    'username' => env('DB_USERNAME', ''),
    'password' => env('DB_PASSWORD', ''),
    'charset'  => env('DB_CHARSET', 'AL32UTF8'),
    'prefix'   => env('DB_PREFIX', ''),
],
];

score 1 · Answer 3 · answered Jan 19 '21 at 21:24

Unfortunately, oci_connect is already too high-level a function to allow timeout control - OSI model layer 5, if you consider it is there to establish a session for what follows. I suggest you try fsockopen on port 1521, level 4, which 5th argument sets the timeout in seconds. If fsockopen() returns a valid resource, then proceed with oci_connect(), otherwise report error / throw exception.

I checked it today, part of a "preflight assessment" when establishing four Oracle connections with various remote sites. It actually gives up after timeout seconds!

Cedric · Answer 4 · 2018-04-12T06:45:26.520

You have tried several approach, which is great. The max_execution_time is a good one. You can register a shutdown function so that you can log the error if any - or do whatever you need.

<?php 
function shutdown(){ 
     $error=error_get_last(); 
     if(is_null($error))   
         echo "No errors"; //or do nothing
     else 
         print_r($a); //or log it properly

 } 

register_shutdown_function('shutdown'); 

ini_set('max_execution_time',3 );//max 3 seconds

sleep(5); //just for test it out

In any case, according to the web (for instance : "don't loose your head, move to Linux"), you should try using Linux to run your webserver when working with Oracle connection, if possible).

(I know that there were, at some time, a lot of rage between Linux and Windows People. But if operating system is more suited for some use case, why bother using the other)

score 0 · Answer 5 · edited Jul 04 '21 at 05:34

From the docs:

"Sometimes Oracle doesn't cleanup shadow processes when accessed from PHP. To avoid that, check your $ORACLE_HOME/network/admin/tnsnames.ora file in your Oracle Client directory and remove the (SERVER=DEDICATED) token if is set.

To let Oracle delete shadow process on timeouts, add the following line in your

$ORACLE_HOME/network/admin/sqlnet.ora

found in your ORACLE Server directory:

SQLNET.EXPIRE_TIME=n

Where 'n' is the number of minutes to let connection idle befor shutting them out."

Have you tried this?

PHP oci_connect() stuck / no time-out

5 Answers5