"unknown encoding" crash without encoding listed for complex query

[rgoodman-qontigo]

1. What versions are you using?

>>> print(conn.version)
19.21.0.0.0

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

Crash

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

>>> cursor.fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/rogo_a/work/Cassandra-RiskModelTesting/SignalsOutlierDetection/venv_outlier/lib/python3.12/site-packages/oracledb/cursor.py", line 968, in fetchall
    row = fetch_next_row(self)
          ^^^^^^^^^^^^^^^^^^^^
  File "src/oracledb/impl/base/cursor.pyx", line 576, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
  File "src/oracledb/impl/thin/cursor.pyx", line 268, in oracledb.thin_impl.ThinCursorImpl._fetch_rows
  File "src/oracledb/impl/thin/protocol.pyx", line 501, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 502, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 470, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 446, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/messages/base.pyx", line 790, in oracledb.thin_impl.Message.process
  File "src/oracledb/impl/thin/messages/base.pyx", line 1263, in oracledb.thin_impl.MessageWithData._process_message
  File "src/oracledb/impl/thin/messages/base.pyx", line 1349, in oracledb.thin_impl.MessageWithData._process_row_data
  File "src/oracledb/impl/thin/messages/base.pyx", line 1112, in oracledb.thin_impl.MessageWithData._process_column_data
  File "src/oracledb/impl/base/converters.pyx", line 540, in oracledb.base_impl.convert_oracle_data_to_python
  File "src/oracledb/impl/base/converters.pyx", line 429, in oracledb.base_impl.convert_str_to_python
LookupError: unknown encoding:

4. Does your application call init_oracle_client()?

No

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

This is very difficult as it is a complex query and uses temporary tables as well as proprietary schema and data. However I have narrowed it down to one set of records. If you can clue me into what I could look for in the database that might cause this crash, perhaps I can correct our data as a workaround.

Additional information :

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8

SQL> select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL16UTF16

[rgoodman-qontigo]

I do note that there is a code path where the encoding is not set before calling convert_oracle_data_to_python(), at line 1108 in impl/thin/messages/base.pyx.

[anthony-tuininga]

What version of python-oracledb are you using? The line numbers in the traceback don't seem to match. So line 1108 doesn't mean a whole lot at the moment!

[rgoodman-qontigo]

Sorry I meant line 1108 in the current main branch. The version I am using is :

>>> oracledb.__version__
'4.0.0'

Thanks for your response.

[anthony-tuininga]

Thanks.

The encoding value is set at the top of the function to the value NULL, so it is always set. The only time it is changed away from NULL is when you are fetching NCHAR data. Is that the case here? If you are able to build yourself you can let me know what the value of encoding is. Based on the queries shown above it should be utf_16_be if you are fetching NCHAR data. It seems that it is the empty string? Something looks "off", that's for sure!

[rgoodman-qontigo]

The part that seems sketchy to me looking at the current code on main is that in base.py it sets the encoding value only under this condition :

            if metadata.dbtype._csfrm == CS_FORM_NCHAR:
                encoding = buf._caps._get_nencoding()

but in converters.pyx it uses the value for encoding under these conditions :

        # Oracle CHAR, LONG and VARCHAR
        if ora_type_num in (
            ORA_TYPE_NUM_CHAR,
            ORA_TYPE_NUM_LONG,
            ORA_TYPE_NUM_VARCHAR
        ):
            return convert_str_to_python(&data.buffer, encoding,
                                         encoding_errors)

I don't quite know why it fails for only some (maybe 5-7%) values/records for me, but I will continue investigating too.

[anthony-tuininga]

As mentioned in my last comment, the value is initialized to NULL so the encoding always has a value! I found another possible issue when fetching Arrow data which I will correct -- but that isn't what you are doing. Can you find out whether you are indeed fetching NCHAR data and what the value of encoding is when the failure occurs?

[rgoodman-qontigo]

My point was that the error message not containing a value for the encoding was an indication that it might be expecting something other than NULL at that point.

I have done more testing and unfortunately it doesn't seem to be deterministic as to when it will fail or for what values. When I do many queries with different values in a loop, some fail, but when I run a single query with one of the failing values, it succeeds.

I will try to do more checking but debugging C code on Linux is not my specialty.

[anthony-tuininga]

The value NULL is acceptable and is equivalent to specifying the value utf_8 (the default value). If you can build the code yourself (and that should be fairly straightforward on Linux) then you can modify the code to display the value of the encoding variable. For ease of handling you can set the default value to "utf_8" instead of NULL. Also find out whether or not NCHAR encoding is being used by adding the appropriate print() statements. That should allow you to avoid the complexity of debugging C code. :-)

If the error is not deterministic, though, it may have nothing to do with this particular bit of code and may be a result of some other code that is overwriting memory. That is far more difficult to diagnose! If you can create a test case that results in the error regularly -- even if that is only 5-7% of the time -- then I can look into that further myself.

[rgoodman-qontigo]

Here are the logging statements I added :

$ git diff
diff --git a/src/oracledb/impl/base/converters.pyx b/src/oracledb/impl/base/converters.pyx
index f71def9..1cc526a 100644
--- a/src/oracledb/impl/base/converters.pyx
+++ b/src/oracledb/impl/base/converters.pyx
@@ -29,6 +29,8 @@
 # form returned by the decoders to an appropriate Python value.
 #------------------------------------------------------------------------------

+from libc.stdio cimport printf
+
 cdef object convert_arrow_to_oracle_data(OracleMetadata metadata,
                                          OracleData* data,
                                          ArrowArrayImpl array_impl,
@@ -490,6 +492,8 @@ cdef object convert_str_to_python(OracleDataBuffer *buffer,
     buffer to Python string.
     """
     cdef OracleRawBytes *rb = &buffer.as_raw_bytes
+    printf('in convert_str_to_python, rb.ptr=%s, encoding=%s, encoding_errors=%s\n',
+           rb.ptr[:rb.num_bytes], encoding, encoding_errors)
     return rb.ptr[:rb.num_bytes].decode(encoding, encoding_errors)


@@ -606,6 +610,8 @@ cdef object convert_oracle_data_to_python(OracleMetadata from_metadata,
             ORA_TYPE_NUM_LONG,
             ORA_TYPE_NUM_VARCHAR
         ):
+            printf('converting as type %d, encoding is %s\n', ora_type_num, encoding)
             return convert_str_to_python(&data.buffer, encoding,
                                          encoding_errors)

And here is the output I got. It now crashes regularly on the first execution of the query so I guess adding these statements messed with memory enough that whatever is blanking the encoding between the fifth and sixth lines now happens all the time.

converting as type 1, encoding is utf_16_be
in convert_str_to_python, rb.ptr=, encoding=utf_16_be, encoding_errors=(null)
converting as type 96, encoding is (null)
in convert_str_to_python, rb.ptr=N, encoding=(null), encoding_errors=(null)
converting as type 1, encoding is utf_16_be
in convert_str_to_python, rb.ptr=, encoding=, encoding_errors=(null)

followed by the same traceback as before.

However, this time I logged the output and error to a file and determined that the error message looks like this :

LookupError: unknown encoding: ^A

That is, there is the hex value 01 listed for the encoding. Perhaps this will help track down where the corruption is happening.

Thanks for your time.

[cjbj]

Can you confirm what datatypes the table used for the columns being queried?

[rgoodman-qontigo]

Can you confirm what datatypes the table used for the columns being queried?

Here is what cursor.description returns after the error happens. I hope this is what you need.

>>> pprint.pprint(cursor.description)
[('ID', <DbType DB_TYPE_NVARCHAR>, 12, 24, None, None, True),
 ('ITEM_CODE', <DbType DB_TYPE_NUMBER>, 39, None, 38, 0, True),
 ('DT', <DbType DB_TYPE_DATE>, 23, None, None, None, True),
 ('VALUE', <DbType DB_TYPE_NUMBER>, 127, None, 0, -127, True),
 ('CURRENCY_ID', <DbType DB_TYPE_NUMBER>, 39, None, 38, 0, True),
 ('EFF_DT', <DbType DB_TYPE_DATE>, 23, None, None, None, True),
 ('EFF_DEL_FLAG', <DbType DB_TYPE_CHAR>, 1, 1, None, None, True),
 ('NAME', <DbType DB_TYPE_NVARCHAR>, 32, 64, None, None, True),
 ('DESCRIPTION', <DbType DB_TYPE_NVARCHAR>, 512, 1024, None, None, True),
 ('SOURCE_TABLE', <DbType DB_TYPE_CHAR>, 30, 30, None, None, True)]

[rgoodman-qontigo]

I attempted to find a minimal query that would produce the error. I found a small query that produced the error and I found when I removed a join, the error went away. I have created gists with the log from the error query and the successful query, including the logging shown above. I hope these are helpful.

error log : https://gist.github.com/rgoodman-qontigo/52664437c65a428fa4a7353db80fc098

success log : https://gist.github.com/rgoodman-qontigo/ea9a160e643e509d6b2144b05855f7ab