0

This code is in python but basically it's using OCI so should be reproducible in any other language:

import cx_Oracle as db
dsn = '(DESCRIPTION =(CONNECT_TIMEOUT=3)(RETRY_COUNT=1)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = SOME_HOST)(PORT = 1531)))(CONNECT_DATA =(SERVICE_NAME = SOME_NAME)))'
connect_string = "LOGIN/PASSWORD@%s" % dsn
conn = db.connect(connect_string)
conn.ping() # WILL HANG FOREVER!!!

If SOME_HOST is down, this will hang forever!

And it's not related to OCIPing - if I replace:

ping()

with:

cursor = conn.cursor()
cursor.execute('SELECT 1 FROM DUAL') # HANG FOREVER AS WELL

This will hang as well.

I'm using SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 6 12:17:09 2013.

I tried wrapping this code in thread and waiting for same time than killing the thread but this doesn't work. This code creates a thread itself and it's impossible from python to kill it. Do you have any ideas how to recover?

mnowotka
  • 16,430
  • 18
  • 88
  • 134

3 Answers3

0

The short answer is to use try/except/finally blocks but if part of your code is truly awaiting for a condition that would never be satisfied, what you need to do, is implement an internal timeout. There are numerous methods to do this. You can adapt the solution to this problem to your needs to get this done.

Hope this helps.

Community
  • 1
  • 1
Spade
  • 2,220
  • 1
  • 19
  • 29
  • 1
    If you think about it, it will turn out you can implement internal timeout that is good enough for this situation. – mnowotka Jan 21 '14 at 08:29
  • Are you endorsing the answer, asking for more help, or rejecting the answer? – Spade Jan 21 '14 at 21:35
0

I had the same problems with interrupting conn.ping().
Now I use next construction:

from threading import Timer

pingTimeout = 10 # sec

# ...

def breakConnection():
    conn.cancel()
    connection = False

try:
    t = Timer(pingTimeout, breakConnection)
    cursor = conn.cursor()
    cursor.execute('SELECT 1 FROM DUAL')
    t.close()
    cursor.close()
except Exception:
    connection = False

if not connection:
    print 'Trying to reconnect...'
    # ...

It's a dirty way, but it works.

And real way to check if a connection is usable is to execute the application statement you want run (I don't mean SELECT 1 FROM DUAL). Then try retry, if you catch the exception.

Pavlo Bashynskyi
  • 329
  • 2
  • 5
-1

if you want close connection try

conn.close()
GrandMasterFlush
  • 6,269
  • 19
  • 81
  • 104
Артем Романюк
  • 11
  • 1