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

Aberta
  • Algo errado?
#628 0 comentários 0 reações 0 responsáveis Ver no GitHub

Ninguém assumiu esta issue ainda.

Avaliação

Dificuldade
4/5
Tempo estimado
3-5 dias
Facilidade para iniciantes
22/100
Tipo de issue
Bug
Clareza
Razoavelmente clara
Status de atividade
Ativa
Stack de tecnologia
python, sql
Domínio
databases

Direção de pesquisa

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.

Escrita pelo modelo de indexação a partir do texto da issue.

Descrição

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
Linguagem predominante
Python
Estrelas
457
Forks
120
Nenhum PR com merge em 30d

Preparar o ambiente

Primeiros passos

  1. Leia a issue inteira e depois o guia de contribuição do projeto.
  2. Comente na issue dizendo que vai assumir — evita que duas pessoas façam o mesmo trabalho.
  3. Faça um fork do repositório e trabalhe em uma branch.
  4. Abra um pull request que referencie o número da issue.

Mais de oracle/python-oracledb

Todas as issues de oracle/python-oracledb

Issues semelhantes

Mais issues de Python

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.