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

Aperta
  • Qualcosa non va?
#628 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
22/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
python, sql
Ambito
databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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
Lingua principale
Python
Stelle
456
Fork
119
Nessuna PR unita negli ultimi 30g

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di oracle/python-oracledb

Tutte le issue di oracle/python-oracledb

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.