[Bug][dbt-doris] Large SQL statements are silently corrupted by mysql-connector-python's C extension — force use_pure=True

Start in dbt/adapters/doris/connections.py, especially the open() method that calls mysql.connector.connect(**kwargs), and reproduce the boundary case with the provided CTAS probe or a compiled model over 8188 bytes. Done means the connection uses pure mode, large statements preserve their bytes, and the reported project models pass as they did with the documented workaround.

Environment

• dbt-doris 1.0.0 (latest) / dbt-core 1.10.x / Python 3.12
• mysql-connector-python 26.7.0 (latest as of 2026-09-05)
• Apache Doris 3.1.4-rc02
• Reproduced on macOS arm64 and Linux x86_64 (python3.12-bookworm container)

Summary

dbt/adapters/doris/connections.py opens connections via mysql.connector.connect(**kwargs) without setting use_pure. mysql-connector-python bundles a C extension on all platforms and defaults to it (use_pure: False). Statements larger than 8188 bytes sent through the C extension are corrupted in transit; Doris rejects them with mismatched input '…' expecting {<EOF>, ';'} where the reported error position drifts with statement length. The identical SQL executes fine via the mysql CLI, and dbt parse passes — only real runs fail, which makes this extremely hard to diagnose.

Minimal repro

CTAS probe with a sentinel inside the string literal; verify by fetching the value back and byte-comparing:

import mysql.connector
conn = mysql.connector.connect(host=..., port=9030, user=..., password=..., database=...)
n = 8188                                  # statement length in bytes
stmt = f"create table probe as select '{'A'*(n-42)}ZZZZZZ' as pad"
cur = conn.cursor()
cur.execute(stmt)                         # fails / corrupts at n >= 8188 with use_pure=False
cur.execute("select pad from probe")
assert cur.fetchone()[0].endswith("ZZZZZZ")

Boundary scan (2 runs per size, 3000–9200):

(8188 = 8192-byte socket buffer minus the COM_QUERY header — consistent with the known 8K-boundary corruption class, cf. MariaDB CONC-771.)

Real-world impact

A 253-line dbt model (~8.3 KB compiled CTAS) failed on every run under the default config; the same SQL succeeded via mysql CLI. Adding "use_pure": True to the connect kwargs made all 9 project models pass.

Suggested fix

One line in connections.py open():

kwargs = {
    "host": ..., "port": ..., "user": ..., "password": ...,
    "buffered": True, "charset": "utf8", "get_warnings": True,
    "use_pure": True,   # <-- C extension corrupts large SQL (>8188 bytes)
}

Alternatively, migrate the adapter to pymysql (pure Python only, structurally immune). Until fixed, a documented workaround would help users a lot — the symptom (drifting mismatched input on large models only) is nearly impossible to connect to the connector.

Related upstream bugs (C-extension send path / 8K boundary)

• MariaDB Connector/C CONC-771 (Critical, fixed in 3.1.29 / 3.3.17 / 3.4.7) — InterfaceError raised when a statement reaches the 8192-byte socket buffer boundary, in the C connector's send path (mariadb_lib.c). Same defect class as observed here.
• MySQL Bug #77789 — Connector/Python's send path historically used 8 KB chunks (cmd_stmt_send_long_data, chunk_size = 8192), raised to 128 KB in 8.0.33. Shows the 8K-chunk heritage of the connector's send code.
• MySQL Bug #96588 (fixed 8.0.29) — C-extension-only data corruption (BIGINT truncated); upstream explicitly notes "This problem does not apply to pure mode — it is only relevant to the C extension." Direct precedent that C-extension bugs bypass use_pure=True.
• MySQL Bug #80238 (dup of #77622) — large statements fail with 2055 Lost connection through the connector's send path while pymysql handles the same statements fine.

Why use_pure=True avoids the bug: mysql-connector-python contains two independent implementations of the client protocol — pure Python (connection.py/network.py) and the C extension (_mysql_connector). use_pure selects which one executes; the corruption lives only in the C implementation's send path, so the pure-Python path (a separate codebase that never had this defect) passes the identical bytes untouched. Empirically: same connector version, same server, same statements — C extension fails at ≥ 8188 bytes, pure Python passes 3000–9200+ and all production models.