Thin client: query caching problem when new table columns are added · Issue #125 · oracle/python-oracledb (original) (raw)

1. What versions are you using?
   cffi==1.15.1
   cryptography==39.0.0
   oracledb==1.2.1
   pycparser==2.21

platform.platform: Linux-5.4.0-136-generic-x86_64-with-glibc2.29
sys.maxsize > 2**32: True
platform.python_version: 3.8.10

1. Is it an error or a hang or a crash?
   A crash - IndexError: list index out of range
2. What error(s) or behavior you are seeing?
   If I:

• use a specific query on a table
• add a new column to that table
• use that same query again
  then, the following exception occurs:

Traceback (most recent call last):
  File "/home/mihau/.config/JetBrains/IntelliJIdea2021.2/scratches/scratch_9.py", line 23, in <module>
    cursor.execute(f"select * from {table}")
  File "/home/mihau/venv/lib/python3.8/site-packages/oracledb/cursor.py", line 378, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 133, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 383, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 384, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 344, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 323, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/messages.pyx", line 282, in oracledb.thin_impl.Message.process
  File "src/oracledb/impl/thin/messages.pyx", line 823, in oracledb.thin_impl.MessageWithData._process_message
  File "src/oracledb/impl/thin/messages.pyx", line 718, in oracledb.thin_impl.MessageWithData._process_describe_info
IndexError: list index out of range

My best guess is that the query cache reader probably has problems when the table description (columns) are modified.
https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_b.html#statement-caching-in-thin-and-thick-modes

The problem disappears when:

a) I change just one character in the 2nd query (last cursor.execute in code example)
b) I use thick client
c) I don't make a query before adding the new table column
d) I change the table modification from adding a column to deleting a column

1. Does your application call init_oracle_client()?
   No, it's fine on thick client, the issue concerns thin client only.
2. Include a runnable Python script that shows the problem.

import oracledb


MY_DSN = "xxxxxxxxxxxxxxxxxxx"
TABLE_NAME = "MY_TABLE_NAME"


def prepare_table(cursor):
    cursor.execute(f"CREATE TABLE {TABLE_NAME} (COL1 NVARCHAR2(7))")
    cursor.execute(f"INSERT INTO {TABLE_NAME} VALUES ('example')")
    connection.commit()


connection = oracledb.connect(MY_DSN)
cursor = connection.cursor()
prepare_table(cursor)
cursor.execute(f"select * from {TABLE_NAME}")
print(cursor.fetchall())
cursor.execute(f"ALTER TABLE {TABLE_NAME} ADD COL2 nvarchar2(100) DEFAULT 'one more example' NOT NULL")
connection.commit()

cursor.execute(f"select * from {TABLE_NAME}")
print(cursor.fetchall())