connect_async hangs when connecting to Autonomous DB 19c in OCI

[mbhegde-orcl]

Problem: connect with same credentials works, but connect_async hangs.

OS: MacOS Ventura 13
Architecture: M1 (ARM)
Installation type: Both pip install oracledb and editable pip install from source directory
DB details: Oracle Autonomous Transaction Processing (ATP) 19c, on OCI, India - Mumbai region.
Script to reproduce:

import getpass
import sys
import oracledb
import os
import asyncio

dsn = os.environ.get('ORACLE_DSN')

pw = os.getenv("ORACLE_PW") or getpass.getpass("Enter password: ")

assert dsn is not None

async def async_main():
    connection = await oracledb.connect_async(
        user="scott",
        password=pw,
        dsn=dsn,
    )

    print("version: " + connection.version)

    cursor = await connection.cursor()

    # Create a table

    await cursor.execute("""
        begin
            execute immediate 'drop table todoitem';
            exception when others then if sqlcode <> -942 then raise; end if;
        end;""")

    await cursor.execute("""
        create table todoitem (
            id number generated always as identity,
            description varchar2(4000),
            creation_ts timestamp with time zone default current_timestamp,
            done number(1,0),
            primary key (id))""")

    # Insert some data

    rows = [ ("Task 1", 0 ),
             ("Task 2", 0 ),
             ("Task 3", 1 ),
             ("Task 4", 0 ),
             ("Task 5", 1 ) ]

    await cursor.executemany("insert into todoitem (description, done) values(:1, :2)", rows)
    print(cursor.rowcount, "Rows Inserted")

    await connection.commit()

    cursor.close()
    connection.close()

async def sync_main():
    connection = oracledb.connect(
        user="scott",
        password=pw,
        dsn=dsn,
    )

    print("version: " + connection.version)

    cursor = connection.cursor()

    # Create a table

    cursor.execute("""
        begin
            execute immediate 'drop table todoitem';
            exception when others then if sqlcode <> -942 then raise; end if;
        end;""")

    cursor.execute("""
        create table todoitem (
            id number generated always as identity,
            description varchar2(4000),
            creation_ts timestamp with time zone default current_timestamp,
            done number(1,0),
            primary key (id))""")

    # Insert some data

    rows = [ ("Task 1", 0 ),
             ("Task 2", 0 ),
             ("Task 3", 1 ),
             ("Task 4", 0 ),
             ("Task 5", 1 ) ]

    cursor.executemany("insert into todoitem (description, done) values(:1, :2)", rows)
    print(cursor.rowcount, "Rows Inserted")

    connection.commit()

    cursor.close()

functions = {'async_main': async_main, 'sync_main': sync_main}
choice = 'sync'
if (len(sys.argv) == 2):
    choice = sys.argv[1]

coro = functions[f'{choice}_main']()
asyncio.run(coro)    

With this script, set ORACLE_DSN to oracle net format DSN you get from OCI, and ORACLE_PW to password.

$ python3 atp-connect.py sync
version: 19.22.0.1.0
5 Rows Inserted

But async version hangs

$ python3 atp-connect-reprod.py async
<hangs indefinitely>

If I press ^C and cancel it, here's the stack trace.

^CTraceback (most recent call last):
  File "/Users/mbhegde/[redacted]/atp-connect-reprod.py", line 104, in <module>
    asyncio.run(coro)
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 629, in run_until_complete
    self.run_forever()
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 596, in run_forever
    self._run_once()
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 1854, in _run_once
    event_list = self._selector.select(timeout)
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/selectors.py", line 562, in select
    kev_list = self._selector.control(None, max_ev, timeout)
KeyboardInterrupt

I suppose this may have to do with some protocol peculiarity. I can provide test ADB credentials over DM if needed. Thanks.

[anthony-tuininga]

I am unable to reproduce the hang myself using the credentials you provided. Can you set the environment variable PYO_DEBUG_PACKETS to any value to see where the hang is taking place for you? That might be instructive.

[anthony-tuininga]

I am able to reproduce with Python 3.10 and lower. Python 3.11 and higher do not experience a hang. The uvloop alternative implementation also works without trouble.