Re: [Sqlrelay-discussion] Fix Firewall problem

[David M. <dav...@fi...>]

Hey Dave,

Is it a Cisco PIX firewall?

Many firewalls don't actually disconnect idle connections, rather they
just start dropping packets from both sides.  To actually disconnect an
idle session, the firewall would need to send a "close" packet to both
sides of the connection.  But since firewalls don't generally do that,
SQL Relay thinks it's still connected to the database and the database
thinks that it's still connected to SQL Relay, but when SQL Relay sends
a query to the database, the query just vanishes.

Some database API's detect a vanishing query via a timeout and report an
obscure error.  Others don't detect it and just sit there forever
waiting for a response from the server.

I believe that Oracle does detect and report the error, so it should be
very easy to modify SQL Relay to catch that error and re-log in, just
like it currently does for a other db-is-down errors.  We'd need to run
some tests though to figure out what the error is.

SQL Relay catches a couple of db-is-down errors already.  There's an
outside chance that Oracle just returns one of those errors when it
detects this condition and that as a result SQL Relay already handles
this situation with Oracle.

Give it a try and let me know if it works or not.  If it doesn't already
work, let me know what error the SQL Relay client returns and I'll
modify it for you so that it does work.

David Muse
dav...@fi...

On Wed, 2006-03-01 at 23:19 -0500, Dave Sugar wrote:
> I have an interesting situation that I'm looking for a solution.  I'm  
> wondering if SQLRealy will be able to solve my problem.  Please let  
> me know what you think.
> 
> I have a client with the following setup.
> 1) Back end database running Oracle 9i - running on Solaris
> 2) 4 front end web servers running Linux RedHat Enterprise, Apache,  
> PHP 4, etc...
> 3) between the database server and the web servers is a firewall
> 
> Now - the problem
> The firewall is setup in such a way that it will disconnect idle  
> connections between the web server and oracle server after 30  
> minutes.  For efficiency reasons the connection created from PHP to  
> Oracle is a persistent connection (oci_pconnect).
> 
> The obvious solution is to get rid of the firewall or at least  
> reconfigure it to not drop these connections.  I have been asking for  
> this for about 2 years now and my client is not willing to make this  
> change for whatever reason.  But, now they are asking me to look into  
> other solutions.
> 
> SQLRelay was suggested to me as a possible solution and while it  
> looks like it might work.  It looks like it might silently reconnect  
> the connection to Oracle if the firewall has decided to drop the  
> connection.  I'm just trying to see if anyone has any sort of  
> experience of this or a similar situation and can let me know if they  
> have had success in solving such a situation.
> 
> The real test is of course to give it a try and see.  But, before I  
> go through the setup and modifying code to use SQLRelay, I was hoping  
> for some more info from other users.
> 
> Thanks,
> 
> Dave Sugar
> dsu...@co...
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live webcast
> and join the prime developer group breaking into this new coding territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>