I want to help triage stale/invalid cursor description

[charliejacobsen]

1. What versions are you using?

   • cx Oracle 7.3.0
   • Python 2.7.11
   • Oracle Instant Client 18.5
   • CentOS 6, 64 bit, x86 (for client)
   • Oracle 19c Enterprise Edition

2. Describe the problem

   • Problems not exactly the same but similar to those found in https://github.com/oracle/python-cx_Oracle/issues/226 ; https://github.com/oracle/python-cx_Oracle/issues/322
   • In my case, when I iterate through the cursor description, the column names are all \x00’s. They are the correct length / number of characters, but all characters are \x00. (So, not Unicode errors like those above, but similar issues.)
   • Like the others, it takes some time (hours, days) for the problem to appear, it’s not immediate.
   • And like the others, I’m caching cursors in application code, rather than leveraging OCI cache. (This would be risky for me to change right now though, large code base, etc., so I’m hoping I can help resolve the issue instead.)

3. Theory

   • My theory is that when the cursor description is constructed, queryInfo.name is somehow NULL in this line: https://github.com/oracle/python-cx_Oracle/blob/cb1c3aaa73c3b9e996702b0a1b995148240a0fe5/src/cxoCursor.c#L525 .
   • For Python 2.7.11, this ultimately is a call to https://github.com/python/cpython/blob/11fc030b6f9294030f0baa15f3bd4e2293e260e4/Objects/stringobject.c#L57.
   • You can see when the string argument is NULL, the function returns a string of \x00’s of the right length.
   • I looked in ODPI to see how queryInfo.name could be NULL, and yet queryInfo.nameLength be valid.
   • I didn’t see anything that stood out, other than an issue that could be inside OCI libraries (perhaps this call https://github.com/oracle/odpi/blob/ed3623c56c7a5387e8afefeef3b158e9d68e5aee/src/dpiStmt.c#L863 , which ultimately is a call into OCI, is getting a valid length but the name itself has been zero’d out for some reason).

4. Next steps

   • I’m sort of volunteering because I know this is an older Python, and I know the resolution suggested is to not cache cursors inside application code.
   • Fixing either of those would be a challenge (large codebase, risk, etc.).
   • But I’m hoping it will trigger some ideas on your side of what I could investigate. Perhaps the issue is in OCI itself, and there’s no workaround, and I’m stuck.
   • This issue does not happen when using cx Oracle 5.2.1 and Oracle Instant Client 11.2 (all else equal).

[anthony-tuininga]

Interesting situation! I'm not sure what your codebase is like, but if it is in fact a bug in the Oracle Client libraries that clobbers the column name pointer, one possible solution is to simply acquire the cursor description and store the bits and pieces you need on your cached cursor, rather than calculate it every time you need it. This should have the additional benefit of improving performance, if only slightly. :-) Depending on how frequently you access this attribute in your code this could be a simple solution. If it occurs frequently, another possibility is to create a subclass of Connection and Cursor that cache the item description. This way the API in your application doesn't change -- except where you connect to the database. I can give further direction if this sounds good to you and you need it.

[charliejacobsen]

Thanks for the quick response! That’s an elegant solution and I think that might work.

It’s certainly possible I have memory corruption due to a bug elsewhere. I was hoping to avoid building an instrumented application, but I might have to, to make sure there isn’t something more serious flying under the radar.

Longer term I’ll need to look at OCI client side caching. Probably just set up an oraacess.xml with auto tuned cache size.

I’ll close for now and report back if I find anything interesting with an instrumented application.

[cjbj]

Instant Client 18 is 'old'. Try at least the latest 19 release.

Just a comment on terminology to aid searching: the statement cache is a cache of the statement text & metadata about statements. The size can be configured in the app, or auto-tuned via oraacess.xml. Client Result Set Caching is a data cache that is configurable with various SQL and init.ora settings.

[charliejacobsen]

Thanks Christopher, yeah that’s what I was referring to, the statement caching. I’m using an older Oracle Instant client library because newer requires newer glibc than what is normally on CentOS 6. (In fact, I saw 19.5 libs on Oracle Linux 6 repos for a short time I believe, but it looks like they were pulled down.) Hoping to be off CentOS 6 soon :)

[chrisovalantise]

Hello everyone.

We 've encountered same issue with same setup, except Centos which is 7. Is there any update on that or any advice you can give after all that time ? What we exactly see is the column names being padded with \x00. Some of them entirely and some not.

Regards, Chris

[anthony-tuininga]

Do you have a test case that can reliably replicate the issue? Or is it intermmittent and random? What version of the Oracle Client libraries ae you using? You can also (now) try python-oracledb in both the thin and thick modes to see if the problem replicates there as well.

[chrisovalantise]

Hi Anthony. Thanks for you immediate response: So setup is: Python 2.7.11 x32 cx_Oracle: 7.3.0 ( Latest version supported for python 2.7 Centos: Centos Linux 7 SQL Plus: Version 19.16.0.0.0 Oracle Client: 19.16 Oracle DB: Version 19.14.0.0.0

What we do is we store the queries at the initiation of our application and then execute them whenever needed. Again that's how our system is build and it wouldn't be easy to alter that

Unfortunately the issue appears Randomly and we 're not able to reproduce it.

Previously we had oracle 11.1 with client 11.2 with CX_oracle version 5.1.3 but we had to upgrade oracle to v19.

After updating oracle we thought it was good to upgrade cx_Oracle to 7.3 as well , but unfortunately we got this issue.

Today we tried downgrading cx_Oracle back to 5.1.3, with rest setup staying the same and so far so good. But we 're not 100% yet that with cx_Oracle 5.1.3 we 'll not get this issue as we cannot possibly reproduce it.

Unfortunately upgrading to python3+ is not something that we can do in the near future so we 've to leave with that restrictions.

Given our current condition, I though it worth asking if there was anything newer on that issue or any advice you could give , given our restrictions..

[anthony-tuininga]

My earlier suggestion is probably the best one: cache the cursor description on your own object so that it doesn't need to be read after a long period of time -- that allows you to workaround the issue, anyway! The ability to use Python 2.7 at all is going to become more and more difficult over time -- good luck!