26.0.0: Regression in LOB handling can lead to ORA-01461, DPY-3013 and data loss

Abierto
  • ¿Algo no está bien?
#628 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
22/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Activo
Stack tecnológico
python, sql
Área
databases

Línea de trabajo

Start with the attached test_insert_lob.py, which runs each INSERT form with and without setinputsizes(BLOB) and with createlob(). Compare the 4.0.2 and 26.0.0 results in the table to find the cases that changed. The key paths are Var.setvalue() when the variable type is a LOB, and the handling of a single byte followed by a createlob() value. Done means the regression rows pass without ORA-01461, DPY-3013 or NULL-stored data, and the rest of the matrix still matches 4.0.2.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

bug

What versions are you using?

Oracle 23 on the database.

platform.platform: Windows-11-10.0.26200-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.13.16
oracledb.version: 26.0.0

Is it an error or a hang or a crash?

Error.

What error(s) or behavior you are seeing?

We discovered a regression in LOB handling from oracle 4.0.2 to 26.0.0, which can lead to data loss in an edge case.

We are running an ETL job that is loading messages into a table. We are using INSERT INTO ... SELECT ... from dual WHERE ... because we are applying a filter condition that is supposed to run in the database. We are using executemany and setinputsizes(BLOB). Most messages are below 32KB, some exceed that.

After upgrading to 26.0.0, our job started crashing with "ORA-01461: The value at bind position 2 exceeded the maximum VARCHAR2 length.". We saw in the release notes:

Dropped support for calling Var.setvalue() with strings/bytes when the variable type is a LOB type. Call the method with the value returned by Connection.createlob() instead.

So we added createlob() as instructed. This turned out to be too slow, so we added a check if we are above 32KB. Then we started experiencing "DPY-3013: unsupported Python type LOB for database type DB_TYPE_RAW".

We tried isolating the issue, and while investigating, we discovered an edge case that can lead to data loss.

I attached a set of unit tests that I used to investigate.

Does your application call init_oracle_client()?

No

Include a runnable Python script that shows the problem.

test_insert_lob.py

Overview over tests and results, which illustrate the error follows.

  • I compared INSERT INTO ... VALUES (marked VALUES) to INSERT INTO ... SELECT ... (marked SELECT).
  • The setinputsizes column indicates if setinputsizes(BLOB) is called prior to insert.
  • In the "Inserted data" column, "LOB" means the 32KB (32 768B) payload is converted through Connection.createlob(), "Bytes" means that the payload is inserted as bytes.
  • Arrows indicate order in executemany, if there are multiple inserted messages.

Differences between 4.0.2 and 26.0.0 are highlighted.

Name Insert type setinputsizes Inserted data Status 4.0.2 Status 26.0.0
select__no_setinputsizes SELECT No Bytes ORA-01461 ORA-01461
value__no_setinputsizes VALUES No Bytes OK OK
select__setinputsizes__regression SELECT BLOB Bytes OK ORA-01461
value__setinputsizes VALUES BLOB Bytes OK OK
select__no_setinputsizes__createlob SELECT No LOB OK OK
value__no_setinputsizes__createlob VALUES No LOB OK OK
select__setinputsizes__createlob SELECT BLOB LOB OK OK
value__setinputsizes__createlob VALUES BLOB LOB OK OK
select__no_setinputsizes__first_byte_then_createlob SELECT No Single byte → LOB DPY-3013 DPY-3013
value__no_setinputsizes__first_byte_then_createlob VALUES No Single byte → LOB DPY-3013 DPY-3013
select__setinputsizes__first_byte_then_creatlob__regression SELECT BLOB Single byte → LOB OK DPY-3013
value__setinputsizes__first_byte_then_creatlob__regression VALUES BLOB Single byte → LOB OK DPY-3013
select__setinputsizes__first_createlob_then_byte__regression__dataloss SELECT BLOB LOB → single byte OK Data loss: first BLOB stored as NULL
value__setinputsizes__first_createlob_then_byte__regression__dataloss VALUES BLOB LOB → single byte OK Data loss: first BLOB stored as NULL
Lenguaje dominante
Python
Estrellas
456
Forks
119
Sin PR fusionados en 30 d

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de oracle/python-oracledb

Todos los issues de oracle/python-oracledb

Issues similares

Más issues de Python

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.