Search code examples
pythonoracle-databasecachingnatcx-oracle

cx_Oracle CQN Callback usage across a router

I am using writing a flask webpage in python which maintains cached query results from an Oracle DB (v11.2). I want to use continuous query notification (CQN) to obtain notification when my caches become invalid. I am able to register the CQN notification listener, but my callback is not being called when the table changes. Note that the database is hosted externally on AWS and my dev (and eventual prod) computers are on a LAN behind the company router.

I use this code to register the CQN callback:

import cx_Oracle
def CQNCallback(message):
    print("Callback triggered.")

ops = cx_Oracle.OPCODE_INSERT | cx_Oracle.OPCODE_DELETE | cx_Oracle.OPCODE_UPDATE
conn = cx_Oracle.connect('userID','passwd','IP:port/ID',events=True)
subs = conn.subscribe(callback = CQNCallback, operations=ops, rowids = False, timeout=3000)
subs.registerquery('select * from MYTABLE')

This code executes sucessfully and I see a new entry in the DB USER_CHANGE_NOTIFICATION_REGS table: 

REGID: 269
REGFLAGS: 0
CALLBACK: net8://(ADDRESS=(PROTOCOL=tcp)(HOST=<gateway>)(PORT=56824))?PR=0
OPERATIONS_FILTER: 14
CHANGELAG: 0
TIMEOUT: 2987
TABLE_NAME: MYTABLE

"gateway" is the public IP address for my company's gateway/router and port 56824 appears to have been assigned by cx_Oracle. To verify that my dev machine is listening for these notifications, I run netstat and see that the computer is listening on port 56824 on both IPv4&6

Proto  Local Address     Foreign Address    State
TCP    0.0.0.0:56824     <devComputer>:0    LISTENING
TCP    [::]:56824        <devComputer>:0    LISTENING

However, when I manually update or insert (and Commit!) changes to MYTABLE using SQLDeveloper, my callback is never called and I don't know why (I made sure the registration hadn't timed out when the changes were made).

My Speculation: I am suspicious that the DB appears to be using the same port number as the local process -- which suggests a NAT problem. Perhaps the tcp connection for the notification is being originated by the database and it is sending notification messages to gateway:56824. If so, the router might not know what to do with the message and therefore drop it. If this is the case how can I configure cx_Oracle to correctly handle the intervening router?

I found an OTN thread suggesting that this is a known bug in Oracle 11.2.0.1 (https://community.oracle.com/thread/2292328?tstart=0). I guess my new task is to convince the DB admin to upgrade the database and try this again. My question now becomes, "What is the best way to bribe/convince a DB admin to upgrade Oracle on a system that he hasn't had to think about since 2009 without being told to 'go pound sand'?"

Solution

  • I was able to receive change notifications using a test version of the code above by performing two tasks:

    1. invoke the "set events" hack provided at the link above. This is nice since I can still move forward even if I can't convince the DBA to update the server software to 11.2.0.4.
    2. set up port forwarding at the router to forward incoming connections on the specified port to my internal IP. I can control this while on my laptop at home, but I will need to convince my company's IT support to implement port forwarding for my production server at work.

    Item 2 above confirms my original suspicion that change notifications cannot be used (without hacking your router) if your database is on a network external to your LAN. The reason being that instead of the local client opening a long-lived connection that serves to communicate changes, change notification connections are initiated from the DB-side and a new connection is created for each change notification

    I was then able to implement the 2 steps above on the work LAN and successfully trigger my callback code. I needed to specify a fixed port for the subscription by modifying the subscription creation so that I could correctly implement port forwarding on the company firewall:

    subs = conn.subscribe(callback = CQNCallback, operations=ops, rowids = False, timeout=3000, port=50000)

    One other thing I learned that could help others is that you can't just close the python shell or ctrl-c code that registers a change notification using cx_Oracle. You need to explicitly call "del subs" otherwise the subscription will remain permanently registered in the database (forever?) with no mechanism to remove it. To get around this limitation during development be sure to explicitly specify a short timeout to the subscription. That way if you mistakenly hit ctrl-c you only have to wait a short period for the subscriptions to timeout before you can keep working. I do not intend to use a timeout for the production code, but I'm not yet sure how I will be able to clean up orphaned subscriptions that will arise from a crash.

    This effectively closes this question. There are still problems to getting this to work such as Oracle refusing to handle FIN and RST on the notification port, but that will be the subject for another question.