search

oracle / python-oracledb

Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle
https://oracle.github.io/python-oracledb
Other
328 stars 66 forks source link

Intermittent errors when using oracledb via DJango persistence - IndexError: list assignment index out of range #173

Closed unicman closed 1 year ago

unicman commented 1 year ago
  1. What versions are you using? 1.3.0

Error is coming Version details from build server:

py38 create: /sparta/input/.tox/py38
py38 installdeps: -r/sparta/input/requirements.txt, -r/sparta/input/requirements_test.txt
py38 installed: amqp==5.1.1,asgiref==3.6.0,assertpy==1.1,astroid==2.15.3,async-timeout==4.0.2,atlassian-python-api==3.36.0,backports.zoneinfo==0.2.1,beautifulsoup4==4.12.2,billiard==3.6.4.0,celery==5.2.7,certifi==2022.12.7,cffi==1.15.1,charset-normalizer==3.1.0,circuitbreaker==1.4.0,click==8.1.3,click-didyoumean==0.3.0,click-plugins==1.1.1,click-repl==0.2.0,contourpy==1.0.7,convertdate==2.4.0,coverage==7.2.3,cryptography==39.0.2,cx-Oracle==8.3.0,cycler==0.11.0,defusedxml==0.7.1,Deprecated==1.2.13,dill==0.3.6,Django==4.2,django-extensions==3.2.1,django-redis==5.2.0,fonttools==4.39.3,hijri-converter==2.2.4,holidays==0.23,idna==3.4,importlib-resources==5.12.0,isort==5.12.0,Jinja2==3.1.2,jira==3.5.0,kiwisolver==1.4.4,kombu==5.2.4,korean-lunar-calendar==0.3.1,lazy-object-proxy==1.9.0,ldap3==2.9.1,MarkupSafe==2.1.2,matplotlib==3.7.1,matplotlib-venn==0.11.9,mccabe==0.7.0,numpy==1.24.2,oauthlib==3.2.2,oci==2.99.0,oracledb==1.3.0,packaging==23.1,pandas==2.0.0,pexpect==4.8.0,Pillow==9.5.0,pip-licenses==4.2.0,pipdeptree==2.7.0,platformdirs==3.2.0,prettytable==3.7.0,prompt-toolkit==3.0.38,ptyprocess==0.7.0,pyasn1==0.5.0,pycodestyle==2.10.0,pycparser==2.21,pylint==2.17.2,pylint-django==2.5.3,pylint-plugin-utils==0.7,PyMeeus==0.5.12,pyOpenSSL==23.1.1,pyparsing==3.0.9,python-dateutil==2.8.2,python-json-logger==2.0.7,pytz==2023.3,redis==4.5.4,requests==2.28.2,requests-oauthlib==1.3.1,requests-toolbelt==0.10.1,retrying==1.3.4,scipy==1.10.1,six==1.16.0,snakeviz==2.1.2,soupsieve==2.4.1,sqlparse==0.4.4,tomli==2.0.1,tomlkit==0.11.7,tornado==6.3,typing_extensions==4.5.0,tzdata==2023.3,UpSetPlot==0.8.0,urllib3==1.26.15,vaultpythonsdk==1.0.3,vine==5.0.0,wcwidth==0.2.6,whitenoise==6.4.0,wrapt==1.15.0,XlsxWriter==3.1.0,zipp==3.15.0
py38 run-test-pre: PYTHONHASHSEED='1633860691'
pycodestyle create: /sparta/input/.tox/pycodestyle
pycodestyle installdeps: -r/sparta/input/requirements.txt, -r/sparta/input/requirements_test.txt
pycodestyle installed: amqp==5.1.1,asgiref==3.6.0,assertpy==1.1,astroid==2.15.3,async-timeout==4.0.2,atlassian-python-api==3.36.0,backports.zoneinfo==0.2.1,beautifulsoup4==4.12.2,billiard==3.6.4.0,celery==5.2.7,certifi==2022.12.7,cffi==1.15.1,charset-normalizer==3.1.0,circuitbreaker==1.4.0,click==8.1.3,click-didyoumean==0.3.0,click-plugins==1.1.1,click-repl==0.2.0,contourpy==1.0.7,convertdate==2.4.0,coverage==7.2.3,cryptography==39.0.2,cx-Oracle==8.3.0,cycler==0.11.0,defusedxml==0.7.1,Deprecated==1.2.13,dill==0.3.6,Django==4.2,django-extensions==3.2.1,django-redis==5.2.0,fonttools==4.39.3,hijri-converter==2.2.4,holidays==0.23,idna==3.4,importlib-resources==5.12.0,isort==5.12.0,Jinja2==3.1.2,jira==3.5.0,kiwisolver==1.4.4,kombu==5.2.4,korean-lunar-calendar==0.3.1,lazy-object-proxy==1.9.0,ldap3==2.9.1,MarkupSafe==2.1.2,matplotlib==3.7.1,matplotlib-venn==0.11.9,mccabe==0.7.0,numpy==1.24.2,oauthlib==3.2.2,oci==2.99.0,oracledb==1.3.0,packaging==23.1,pandas==2.0.0,pexpect==4.8.0,Pillow==9.5.0,pip-licenses==4.2.0,pipdeptree==2.7.0,platformdirs==3.2.0,prettytable==3.7.0,prompt-toolkit==3.0.38,ptyprocess==0.7.0,pyasn1==0.5.0,pycodestyle==2.10.0,pycparser==2.21,pylint==2.17.2,pylint-django==2.5.3,pylint-plugin-utils==0.7,PyMeeus==0.5.12,pyOpenSSL==23.1.1,pyparsing==3.0.9,python-dateutil==2.8.2,python-json-logger==2.0.7,pytz==2023.3,redis==4.5.4,requests==2.28.2,requests-oauthlib==1.3.1,requests-toolbelt==0.10.1,retrying==1.3.4,scipy==1.10.1,six==1.16.0,snakeviz==2.1.2,soupsieve==2.4.1,sqlparse==0.4.4,tomli==2.0.1,tomlkit==0.11.7,tornado==6.3,typing_extensions==4.5.0,tzdata==2023.3,UpSetPlot==0.8.0,urllib3==1.26.15,vaultpythonsdk==1.0.3,vine==5.0.0,wcwidth==0.2.6,whitenoise==6.4.0,wrapt==1.15.0,XlsxWriter==3.1.0,zipp==3.15.0

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

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

DJango persistence is configured to run using oracledb thin client in build server to avoid installing cx_Oracle driver. Build has started failing while fetching rows even if there is no change in the logic or test that is run. oracledb driver fails with error 2-3 times and after that it works ... so it is intermittent error:

  File "/sparta/input/devops/models.py", line 812, in get_releases
    raise ex
  File "/sparta/input/devops/models.py", line 770, in get_releases
    for release in patch_releases:
  File "/sparta/input/.tox/inttest/lib/python3.8/site-packages/django/db/models/query.py", line 398, in __iter__
    self._fetch_all()
  File "/sparta/input/.tox/inttest/lib/python3.8/site-packages/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/sparta/input/.tox/inttest/lib/python3.8/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
  File "/sparta/input/.tox/inttest/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1560, in execute_sql
    cursor.execute(sql, params)
  File "/sparta/input/.tox/inttest/lib/python3.8/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/sparta/input/.tox/inttest/lib/python3.8/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/sparta/input/.tox/inttest/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/sparta/input/.tox/inttest/lib/python3.8/site-packages/django/db/backends/oracle/base.py", line 557, in execute
    return self.cursor.execute(query, self._param_generator(params))
  File "/sparta/input/.tox/inttest/lib64/python3.8/site-packages/oracledb/cursor.py", line 378, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 138, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 382, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 385, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 343, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 322, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/messages.pyx", line 292, in oracledb.thin_impl.Message.process
  File "src/oracledb/impl/thin/messages.pyx", line 814, in oracledb.thin_impl.MessageWithData._process_message
  File "src/oracledb/impl/thin/messages.pyx", line 896, in oracledb.thin_impl.MessageWithData._process_row_data
IndexError: list assignment index out of range

  1. Does your application call init_oracle_client()? No. We use thin client.

  2. Include a runnable Python script that shows the problem. Will share via DM.

anthony-tuininga commented 1 year ago

I took a look at the code in the location where this is happening. Both options (var_impl._values is too small to support the data being fetched or self.row_index is too large for the list) don't seem likely. The fact that it is happening intermittently makes it more complicated, of course!

Are you able to supply the Python script that demonstrates the problem? You can e-mail me directly if you wish. If not, are you able to build yourself? I can suggest some debugging lines to add.

Prathyvadlamudi commented 1 year ago

Is there any solution for this? I face the same issue . I am just trying to insert values into oracle table.

impl.execute(self) File "src\oracledb\impl/thin/cursor.pyx", line 138, in oracledb.thin_impl.ThinCursorImpl.execute File "src\oracledb\impl/thin/protocol.pyx", line 382, in oracledb.thin_impl.Protocol._process_single_message 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 376, in oracledb.thin_impl.Protocol._process_message

cjbj commented 1 year ago

@Prathyvadlamudi can you share more details about what you were doing, the versions, the data, etc? Are you also using Django?

stdrone commented 1 year ago

Same error occurs when prefetchrows greater then arraysize

cjbj commented 1 year ago

@stdrone Got a testcase? What values are you setting and what number of rows are you returning? I'm not seeing the error.

stdrone commented 1 year ago

We had code like this

def get_cursor(connect, arraysize):
  cursor = connect.cursor()
  cursor.arraysize = arraysize
  cursor.prefetchrows = arraysize + 1 # i dont know why

cur = get_cursor(connect, 500)
res = cur.execute('select * from table').fetchall() # table with over 500 rows

removing + 1 fixes our problem

cjbj commented 1 year ago

@stdrone for those 'big' fetches, don't change prefetchrows at all, IMHO: see https://asktom.oracle.com/pls/apex/f?p=100:551:::NO:551:P551_CLASS_ID:3701: at the 25:08 minute mark.

In simple tests, I still don't see a problem:

    connection = oracledb.connect(user=un, password=pw, dsn=cs)
    cursor = connection.cursor()
    cursor.arraysize = 500
    cursor.prefetchrows = 500 + 1
    res = cursor.execute('select level from dual connect by level <= 1000').fetchall()
    print(res)

gives:

[(1,), (2,), (3,), ..., (1000,)]

so if you have any other details to help reproduce, please let us know

stdrone commented 1 year ago

Yep, direct creation of cursor not raise error.

We got cursor from SQLAlchemy. 

import oracledb

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from fastapi import APIRouter, Depends

pool = oracledb.SessionPool(
    user=USER,
    password=PASSWORD,
    dsn=DSN,
    min=POOL_SIZE,
    getmode=oracledb.SPOOL_ATTRVAL_FORCEGET,
)

def creator():
    return pool.acquire(cclass="MYCLASS", purity=oracledb.ATTR_PURITY_SELF)

sync_engine = create_engine(
    "oracle+oracledb://",
    creator=creator,
    pool_pre_ping=True,
    pool_size=POOL_SIZE,
    pool_timeout=CALL_TIMEOUT,
)

sync_session = sessionmaker(autocommit=False, autoflush=False, bind=sync_engine)

def get_sync_session() -> Session:
    with sync_session() as session:
        yield session

api = APIRouter()

@api.post("/test")
async def test(session: Session = Depends(get_sync_session)) -> Any:
    cur = session.connection().connection.cursor()
    cur.arraysize = 500
    cur.prefetchrows = 501
    res = cur.execute("select level from dual connect by level <= 10000")
    return res
python --version
Python 3.11.4

pip list
SQLAlchemy                  2.0.18
starlette                   0.27.0
fastapi                     0.100.0
oracledb                    1.3.2
  File "/app/api/jrpc.py", line 61, in test
    res = cur.execute("select level from dual connect by level <= 10000")
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.11/site-packages/oracledb/cursor.py", line 378, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 138, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 385, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 386, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 346, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 325, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/messages.pyx", line 291, in oracledb.thin_impl.Message.process
  File "src/oracledb/impl/thin/messages.pyx", line 817, in oracledb.thin_impl.MessageWithData._process_message
  File "src/oracledb/impl/thin/messages.pyx", line 899, in oracledb.thin_impl.MessageWithData._process_row_data
IndexError: list assignment index out of range
anthony-tuininga commented 1 year ago

Thanks for the sample code. The problem is due to the fact that an output type handler is being specified and that output type handler is passing the arraysize value when it creates the variable. The execute, however, uses the prefetchrows value -- which in this case is higher. So that needs to be addressed. Doing so will also mean that the arraysize parameter to cursor.var() will become optional, since it will be adjusted as needed before the rows are processed. I'll work on a fix for this issue now that I know how to solve it!

anthony-tuininga commented 1 year ago

I have pushed a patch that should correct this issue. If you are able to build from source you can verify that it corrects your issue as well.

anthony-tuininga commented 1 year ago

This has been included in python-oracledb 1.4.0 which was just released.