DPY-5000: internal error: unknown protocol message under very subtile conditions

[AntonOvsyannikov]

1. What versions are you using?

docker run --name oracle -d -p 1521:1521 -e ORACLE_PASSWORD=password gvenzl/oracle-xe

SELECT version FROM V$INSTANCE
│ VERSION      │
│ '21.0.0.0.0' │

platform.platform: Windows-10-10.0.22631-SP0
also reproduced at
platform.platform: Linux-5.15.153.1-microsoft-standard-WSL2-x86_64-with-glibc2.36

sys.maxsize > 2**32: True
platform.python_version: 3.9.12

oracledb.__version__: 2.4.1 (2.5.1 reproduced too)
sqlalchemy.__version__: 2.0.35

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

DPY-5000: internal error: unknown protocol message under very subtile conditions, see comments in code.

4. What error(s) or behavior you are seeing?

DPY-5000: internal error: unknown protocol message under very subtile conditions, see comments in code.

5. Does your application call init_oracle_client()?

No

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

import os
import sqlalchemy as sa

os.environ["PYO_DEBUG_PACKETS"] = "1"
engine = sa.create_engine("oracle+oracledb://system:password@localhost/?service_name=XEPDB1", echo=True)

with engine.begin() as con:
    try:
        con.execute(sa.text('DROP USER "foo_test" CASCADE'))
    except Exception:
        pass
    con.execute(sa.text('CREATE USER "foo_test" QUOTA UNLIMITED ON USERS'))

with engine.begin() as con:
    try:
        con.execute(sa.text('DROP TABLE "foo_test"."import_test" CASCADE CONSTRAINTS'))
    except Exception:
        pass
    con.execute(sa.text('CREATE TABLE "foo_test"."import_test" ("id" NUMBER, "name" CLOB)'))
    con.execute(
        sa.text('INSERT INTO "foo_test"."import_test" ("id", "name") VALUES (:id, :name)'),
        {'id': '1', 'name': 'vasya'},
    )
    con.execute(sa.text('SELECT * FROM "foo_test"."import_test" FETCH FIRST 100 ROWS ONLY'))

with engine.begin() as con:
    con.execute(sa.text('DROP TABLE "foo_test"."import_test" CASCADE CONSTRAINTS'))

    # create statement should differ from first one
    con.execute(sa.text('CREATE TABLE "foo_test"."import_test" ("id" NUMBER, "name" VARCHAR2(100))'))

    con.execute(
        sa.text('INSERT INTO "foo_test"."import_test" ("id", "name") VALUES (:id, :name)'),
        {'id': '1', 'name': 'vasya'},
    )

    # select statement should be EXACTLY THE SAME as first one
    # caching issue?
    # !!!! The following causes DPY-5000: internal error: unknown protocol message type 0 at position 137
    con.execute(sa.text('SELECT * FROM "foo_test"."import_test" FETCH FIRST 100 ROWS ONLY'))

output https://gist.github.com/AntonOvsyannikov/4deaea7388d1e1ebc49c9e676a8f10e1

if use fetch 101 (instead of 100) rows in second select script works fine https://gist.github.com/AntonOvsyannikov/57bf73afb7a31345cdfb4fc693ab450e see op 58

[cjbj]

Yes, quite likely the well-known statement caching artifact. Try Thin mode, or disable the statement cache by setting its size to 0.

Hopefully you are not dropping & creating tables in production, but just for testing!

The equivalent code without SQLALchemy:

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:

    with connection.cursor() as cursor:

        cursor.execute('DROP TABLE if exists import_test CASCADE CONSTRAINTS')
        cursor.execute('CREATE TABLE import_test (id NUMBER, name CLOB)')

        cursor.execute('INSERT INTO import_test (id, name) VALUES (:id, :name)', {'id': '1', 'name': 'vasya'})
        cursor.execute('SELECT * FROM import_test FETCH FIRST 100 ROWS ONLY')
        for r in cursor.fetchall():
            print(r)

        cursor.execute('DROP TABLE if exists import_test CASCADE CONSTRAINTS')
        cursor.execute('CREATE TABLE import_test (id NUMBER, name VARCHAR2(100))')

        cursor.execute('INSERT INTO import_test (id, name) VALUES (:id, :name)', {'id': '1', 'name': 'vasya'})
        cursor.execute('SELECT * FROM import_test FETCH FIRST 100 ROWS ONLY')
        for r in cursor.fetchall():
            print(r)

Gives this in Thin mode:

$ python ~/p/issue424.py 
(1, 'vasya')
(1, 'vasya')

and this in Thick mode:

$ python ~/p/issue424.py 
Using Thick mode
(1, 'vasya')
Traceback (most recent call last):
  File "/Users/cjones/p/issue424.py", line 56, in <module>
    for r in cursor.fetchall():
             ^^^^^^^^^^^^^^^^^
  File "/Users/cjones/.pyenv/versions/3.12.7/lib/python3.12/site-packages/oracledb/cursor.py", line 779, in fetchall
    row = fetch_next_row(self)
          ^^^^^^^^^^^^^^^^^^^^
  File "src/oracledb/impl/base/cursor.pyx", line 554, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
    self._fetch_rows(cursor)
  File "src/oracledb/impl/thick/cursor.pyx", line 151, in oracledb.thick_impl.ThickCursorImpl._fetch_rows
    _raise_from_odpi()
  File "src/oracledb/impl/thick/utils.pyx", line 456, in oracledb.thick_impl._raise_from_odpi
    _raise_from_info(&error_info)
  File "src/oracledb/impl/thick/utils.pyx", line 446, in oracledb.thick_impl._raise_from_info
    raise error.exc_type(error)
oracledb.exceptions.DatabaseError: ORA-03106: fatal two-task communication protocol error
Help: https://docs.oracle.com/error-help/db/ora-03106/

If I set connection.stmtcachesize = 0 and run again in Thick mode there is no issue.

This is really a DB issue outside the scope of python-oracledb, though we have tried to work around it, and have got some DB side improvements made in various scenarios