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

未关闭
  • 哪里不对？
#628 0 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

还没有人认领这个 Issue。

评估

难度
4/5
预计耗时
3-5 天
新手友好度
22/100
Issue 类型
缺陷
描述清晰度
基本清楚
活跃度
活跃
技术栈
python, sql
领域
databases

调研方向

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.

由索引模型根据 Issue 内容生成。

描述

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
主要语言
Python
星标
456
派生
119
30 天内没有已合并 PR

环境准备

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

oracle/python-oracledb 的其他 Issue

查看 oracle/python-oracledb 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。