executemany() with /*+ append_values */ and strings over 32767 bytes stores another row's value or raises ORA-03106 (Thin)

Aperta
  • Qualcosa non va?
#633 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
55/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
python
Ambito
backend, database

Direzione di ricerca

Start with the runnable script in the issue, which isolates the failure to Thin mode executemany() with the append_values hint and LONG-bound strings over 32767 bytes. Compare those results with conventional inserts and hinted batches of shorter strings, then trace the Thin mode array-bind and direct-path handling. Done means the script stores each row’s correct value or returns a clear error instead of corrupting data or raising ORA-03106.

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

Descrizione

  1. What versions are you using?
database: 23.26.1.0.0 (Oracle Database 23ai Enterprise Edition, container-registry.oracle.com/database/enterprise)
platform.platform: Linux-6.18.54.1-microsoft-standard-WSL2-x86_64-with-glibc2.41
sys.maxsize > 2**32: True
platform.python_version: 3.14.10
oracledb.__version__: 26.1.0

We first saw it on Oracle Database 19.17 (RAC on Exadata) with the Rust driver (rust-oracledb 26.0.0-beta.5),
which shares the Thin protocol implementation: same ORA-03106 there.

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

Both an error and silent wrong data.

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

executemany() of INSERT /*+ append_values */ ... VALUES (:1, :2) into a CLOB column, when a batch contains a
string longer than 32767 bytes (bound as LONG):

  • if other rows in the batch are short, there is no error, but the long row gets another row's value (the
    first row of the batch is repeated);
  • if several rows are long, ORA-03106: fatal two-task communication protocol error.

The same batches without the hint (conventional insert), and batches whose values are all <= 32767 bytes with the
hint, are inserted correctly. Output of the script below (expected: row N has the letter of row N and its size):

database: 23.26.1.0.0
OK  [100, 5000]            conventional   1:100x'a' 2:5000x'b'
OK  [100, 5000]            append_values  1:100x'a' 2:5000x'b'
OK  [100, 40000]           conventional   1:100x'a' 2:40000x'b'
BAD [100, 40000]           append_values  1:100x'a' 2:100x'a'
OK  [40000, 100]           conventional   1:40000x'a' 2:100x'b'
BAD [40000, 100]           append_values  1:40000x'a' 2:40000x'a'
OK  [40000, 40000, 40000]  conventional   1:40000x'a' 2:40000x'b' 3:40000x'c'
BAD [40000, 40000, 40000]  append_values  ORA-03106: fatal two-task communication protocol error

I have not tested Thick mode (no Instant Client available here), so I don't know whether this is a Thin mode
issue or a database restriction on direct-path inserts with LONG binds. Even if direct path cannot take LONG binds,
I would expect an error rather than another row's value being stored.

  1. Does your application call init_oracle_client()?

No (Thin mode).

  1. Include a runnable Python script that shows the problem.
import os
import oracledb

TABLE = "append_values_long_test"


def connect():
    return oracledb.connect(
        user=os.environ["PYO_USER"],
        password=os.environ["PYO_PASSWORD"],
        dsn=os.environ["PYO_DSN"],
    )


with connect() as conn:
    print("database:", conn.version)
    cursor = conn.cursor()
    cursor.execute(f"drop table if exists {TABLE} purge")
    cursor.execute(f"create table {TABLE} (id number, c clob)")

cases = [
    [100, 5000],             # all values <= 32767 bytes
    [100, 40000],            # second value > 32767 bytes
    [40000, 100],
    [40000, 40000, 40000],
]
for sizes in cases:
    for hint in ("", "/*+ append_values */ "):
        # new connection for each case, as ORA-03106 may leave it unusable
        with connect() as conn:
            cursor = conn.cursor()
            cursor.execute(f"truncate table {TABLE}")
            rows = [(i + 1, "abcdefghij"[i] * size) for i, size in enumerate(sizes)]
            try:
                cursor.executemany(
                    f"insert {hint}into {TABLE} (id, c) values (:1, :2)", rows
                )
                conn.commit()
                cursor.execute(
                    f"select id, dbms_lob.getlength(c), substr(c, 1, 1) "
                    f"from {TABLE} order by id"
                )
                result = " ".join(f"{i}:{n}x'{ch}'" for i, n, ch in cursor)
            except oracledb.Error as e:
                result = str(e).splitlines()[0]
            expected = " ".join(
                f"{i + 1}:{size}x'{'abcdefghij'[i]}'" for i, size in enumerate(sizes)
            )
            label = "append_values" if hint else "conventional"
            status = "OK " if result == expected else "BAD"
            print(f"{status} {str(sizes):<22} {label:<14} {result}")

with connect() as conn:
    conn.cursor().execute(f"drop table {TABLE} purge")
Lingua principale
Python
Stelle
458
Fork
123
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.