Skip to content

Connect() hangs with SSH tunnels and 11g database #502

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
Thyrvax opened this issue Jun 2, 2025 · 4 comments
Open

Connect() hangs with SSH tunnels and 11g database #502

Thyrvax opened this issue Jun 2, 2025 · 4 comments
Labels
bug Something isn't working

Comments

@Thyrvax
Copy link

Thyrvax commented Jun 2, 2025

  1. What versions are you using?
    Python 3.13.3 (also tried with 3.11)
    Oracledb 3.1.1 (also tried 3.0.0 and 2.5.1)
    Oracle database 11g
    Oracle instant client 18.5 (also tried with 12.2)

    platform.platform: Windows-11-10.0.26100-SP0
    sys.maxsize > 2**32: True
    platform.python_version: 3.13.3
    oracledb.version: 3.1.1

  2. Is it an error or a hang or a crash? Hang

  3. What error(s) or behavior you are seeing?
    While trying to connect to 11g databases through SSHtunnels, oracledb.connect() hangs and doesn't return anything.
    I tried it on multiple tunnels/databases combinaisons, while mixing oracledb / instantclient / python versions.

    If I open the tunnel through putty, oracledb.connect works.
    If I open the tunnel through another script, oracledb.connect works. (!)
    If I try to open a tunnel and connect to a 19c database, it works
    If I do the same thing using python 3.9 and cx_oracle, it works.

    If I provide a wrong username or password, I get an (expected) error.
    If I provide a wrong SID, I get an (expected) error.

    I do see an inactive connection in my v$session linked to my script.

    I tried everything I could think of and do not understand what I'm missing.

  4. Does your application call init_oracle_client()? Yes, I tried with instant client versions 11.2, 12.1, 18.5
    I had success accessing a 19c db using instant client 21.18 through the same tunnel (and same script)

  5. Include a runnable Python script that shows the problem.

from sshtunnel import SSHTunnelForwarder
import oracledb

# ssh config
ssh_host = "###_tunnel.com"
ssh_port = 21
ssh_username = "ssh_user"
ssh_password = "ssh_psw"

# db config
oracle_host = "ip.of.my.db"
oracle_port = 1521
oracle_sid = "DB_SID"
oracle_user = "DB_USER"
oracle_password = "DB_PSW"

#oracle_client_path = r"C:\instant_client18c\instantclient-basic-windows.x64-18.5.0.0.0dbru\instantclient_18_5"
oracle_client_path = r"C:\instant_client11g\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_11_2"
oracledb.init_oracle_client(lib_dir=oracle_client_path)
print("Thin mode enabled?", oracledb.is_thin_mode())

try:
    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            ssh_username=ssh_username,
            ssh_password=ssh_password,
            remote_bind_address=(oracle_host, oracle_port),
    ) as tunnel:
        print(f"binding on {tunnel.local_bind_host}:{tunnel.local_bind_port}")
        print(oracledb.clientversion())
        param = oracledb.ConnectParams(
            user=oracle_user,
            password=oracle_password,
            host="localhost",
            port=tunnel.local_bind_port,
            sid=oracle_sid,
        )
        with oracledb.connect(params=param) as connection:
            print("connected to Oracle 11.2!") # this never prints for tunnel + 11g
            with connection.cursor() as cursor:
                cursor.execute("SELECT 'query success' AS TEST FROM dual")
                result = cursor.fetchone()
                print(result[0])

except Exception as e:
    print(f"Error : {e}")
@Thyrvax Thyrvax added the bug Something isn't working label Jun 2, 2025
@cjbj
Copy link
Member

cjbj commented Jun 4, 2025

If I correctly read the extensive list in point 3 of what you have tried, you can get cx_Oracle to connect to your 11g database over the tunnel:

While trying to connect to 11g databases .... If I do the same thing using python 3.9 and cx_oracle, it works.

If so, make sure the cx_Oracle script calls cx_Oracle.init_oracle_client(lib_dir=oracle_client_path) and can connect. Then change the script in one way only: to use import oracledb as cx_Oracle. Run it in the same terminal & ssh session that cx_Oracle was able to connect in.

In Thick mode, connectivity is handled by the Oracle Client library which is not part of the python-oracledb project, so if it works in cx_Oracle it should (TM) work in python-oracledb Thick mode.

@Thyrvax
Copy link
Author

Thyrvax commented Jun 4, 2025

I just did the test on my 3.9 venv.
I do confirm that the connection to 11g through tunnel using cx_oracle is working

Then I tried with import oracledb as cx_Oracle, and it fails in the same way, hanging when trying to acquire connection.

I do have a workaround, using threading to start the tunnel in another thread works.

This bugs me because I spent too much time trying to make it works, but I also understand that it may be a weird edge case on an EoL version which may not be worth your time. I'm still up for trying thing, but won't blame you if you don't

@Thyrvax Thyrvax closed this as completed Jun 4, 2025
@anthony-tuininga
Copy link
Member

Did you also include the call to init_oracle_client()? That's important in order to enable thick mode.

@Thyrvax
Copy link
Author

Thyrvax commented Jun 4, 2025

Yes, it was already a requirement when using cx_oracle, so it was part of the script. This is exactly what I used :

from sshtunnel import SSHTunnelForwarder
#import cx_Oracle  #--> this works
import oracledb as cx_Oracle #-- this does not work

# ssh config
ssh_host = "###_tunnel.com"
ssh_port = 21
ssh_username = "ssh_user"
ssh_password = "ssh_psw"

# db config
oracle_host = "ip.of.my.db"
oracle_port = 1521
oracle_sid = "DB_SID"
oracle_user = "DB_USER"
oracle_password = "DB_PSW"

oracle_client_path = r"C:\instant_client18c\instantclient-basic-windows.x64-18.5.0.0.0dbru\instantclient_18_5"
cx_Oracle.init_oracle_client(lib_dir=oracle_client_path)
try:

    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            ssh_username=ssh_username,
            ssh_password=ssh_password,
            remote_bind_address=(oracle_host, oracle_port),
    ) as tunnel:
        local_port = tunnel.local_bind_port
        print(f"tunnel connected to port {tunnel.local_bind_port}")

        # Connexion à la base Oracle via le tunnel
        dsn = f"127.0.0.1:{tunnel.local_bind_port}/{oracle_sid}"
        print(f"DSN used : {dsn}")

        with cx_Oracle.connect(
                user=oracle_user,
                password=oracle_password,
                dsn=dsn
        ) as connection:
            print("Connected to Oracle 11.2!")

            with connection.cursor() as cursor:
                cursor.execute("SELECT 'Connected !!' AS TEST FROM dual")
                result = cursor.fetchone()
                print(result[0])

except Exception as e:
    print(f"Error : {e}")

I previously incorrectly stated that it worked using threading. It does not.
The only case I got working was when using a subprocess to make the database call.
Opening the tunnel in a subprocess and then accessing the database from the main script does not work.

edit: I configured a WSL Ubuntu, then used the same script with python 3.12 and instantclient 12_2, and got the same result

@Thyrvax Thyrvax reopened this Jun 4, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

3 participants