Description:
mysql-connector-python produces a malformed final SQL statement — not a data/escaping
error surfaced cleanly, but literal SQL syntax corruption (MySQL error 1064) — when
executing a sufficiently large multi-row INSERT ... VALUES (...), (...), ... statement
built from real-world text content, both via cursor.executemany() (which rewrites into
one statement via MySQLCursor._batch_insert()) and via a single cursor.execute() call
with a large dict of %(name)s-style named parameters (e.g. as produced by SQLAlchemy's
Core insertmanyvalues compilation). The corruption is visible in the server's own error
message: text that should never appear together — either the tail of the ON DUPLICATE
KEY UPDATE clause spliced with adjacent column names, or a fragment of what should be
one row's data appearing where different SQL is expected — indicating the driver's
client-side statement assembly, not the server, produced invalid SQL text.
Environment: mysql-connector-python 9.5.0, Python 3.13.9, MySQL server 8.0.39 (AWS RDS).
Reproduced both with and without SQLAlchemy 2.0.51 in front of it — this is a
mysql-connector-python issue, not an SQLAlchemy issue. Also reproduced with
SQLAlchemy's insertmanyvalues execution option explicitly disabled, ruling out that
specific SQLAlchemy optimization as the cause.
What's ruled out:
- Not a single "bad" character. Individually tested a lone % (as in a Java
String.format("%32s", ...) call), a lone single-quote (Java char literal ' '), a lone
backslash-escape sequence, and a zero-width space (U+200B) embedded in a text value —
none reproduce the corruption alone in a single-row statement, nor in isolated small
multi-row statements containing them.
- Not the ON DUPLICATE KEY UPDATE clause specifically — reproduces identically with a
plain multi-row INSERT with no ON DUPLICATE KEY UPDATE at all.
- Not the MySQL-8.0.19+ "... AS new ... ON DUPLICATE KEY UPDATE col = new.col"
alias-required rendering vs. the legacy "col = VALUES(col)" form — both produce the
identical corruption shape.
- Not executemany()'s internal multi-row rewrite specifically — a single
cursor.execute() call with one large dict of %(name)s-style named parameters (no
executemany() involved) reproduces it too, at sufficient scale.
- Not pure row count or pure total byte size. A 500-row batch of short, synthetic
(non-representative) text succeeds. A 250-row prefix of the real triggering dataset
succeeds standalone; the following ~125 rows of the same real dataset, further split
into a 62-row and a 63-row half, each succeed independently — but the same rows
concatenated into one statement fail. Two independent attempts to anonymize the real
triggering text (replacing every identifier token with either a same-length or
different-length placeholder, preserving all special characters, punctuation, and
structure) both stopped reproducing the bug — i.e. it is sensitive to the actual real
byte content in a way we have not reduced to a minimal synthetic case.
Suspected code location: MySQLCursor._batch_insert() in mysql/connector/cursor.py — it
locates the first-row "VALUES (%s, ...)" template via a regex against the statement
with ON DUPLICATE KEY UPDATE stripped out, renders each row by substituting into a copy
of that template, joins all rendered rows, then does stmt.replace(fmt, joined_values, 1)
against the original, unstripped statement text. Given that the identical corruption
also reproduces via a single execute() call (which does not go through _batch_insert()
at all for a dict-of-named-params statement), the same class of defect likely also
exists in the pyformat/named-parameter substitution path used by execute() for large
statements (see _bytestr_format_dict / RE_PY_MAPPING_PARAM in the same file).
Impact: silently aborts a legitimate bulk write with MySQL error 1064 that superficially
looks like an application bug in the calling code's SQL construction, when the actual
generated SQL text is correct until the driver's own client-side assembly step. Can
affect any application doing bulk executemany()-style inserts, or a single large
multi-row INSERT via a dict of named parameters, against real-world text data at
moderate scale (order of ~100+ rows in one statement).
How to repeat:
Environment: mysql-connector-python 9.5.0, Python 3.13.9, MySQL server 8.0.39
(also reproduces via SQLAlchemy 2.0.51 sitting on top of the same driver,
and with SQLAlchemy's insertmanyvalues optimization explicitly disabled, so
this is a driver issue, not an SQLAlchemy issue).
1. Create a table with a mix of VARCHAR and TEXT columns plus a unique key,
e.g.:
CREATE TABLE repro (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
service_key VARCHAR(100) NOT NULL,
branch VARCHAR(255) NOT NULL,
file_path VARCHAR(2000) NOT NULL,
node_type VARCHAR(50) NOT NULL,
node_name VARCHAR(1024) NOT NULL,
node_qualified_name TEXT,
node_signature TEXT,
parent_class VARCHAR(1024),
decorator_hints TEXT,
start_line INT,
end_line INT,
node_body_hash VARCHAR(64) NOT NULL,
node_key_hash VARCHAR(64) NOT NULL,
is_public TINYINT,
is_active TINYINT,
UNIQUE KEY uniq_key (service_key, branch, node_key_hash)
);
2. Build an INSERT ... ON DUPLICATE KEY UPDATE statement with %s positional
placeholders for all 15 columns, and an ON DUPLICATE KEY UPDATE clause
that updates every column except the three forming the unique key
(col = VALUES(col) for each).
3. Populate rows with REAL source-code-like text, not randomly generated
text — this is the part that matters and is hard to describe precisely,
because we could not reduce the trigger to a clean synthetic case
ourselves (see "What's ruled out" in the Description: neither
deliberately-inserted special characters in isolation, nor
randomly-generated text of a similar length distribution, reproduced
it). What did reproduce it: taking ~500 real AST nodes (classes, fields,
methods, with their actual signatures/bodies as parsed text) out of a
real Java codebase, in the order they appear across several source
files. The rows vary naturally in length — many short one-liners
(getters/setters/field declarations, 40-150 characters) interleaved with
a smaller number of longer method bodies (several hundred to ~1500
characters), containing ordinary Java syntax: annotations (@Autowired,
@Getter), generics (List<X>, Map<K,V>), string literals with embedded
quotes, method chaining, occasional Javadoc comments.
Recommendation for reproducing without our specific (proprietary) data:
take any real, reasonably large open-source Java (or similar) repository,
parse out ~500 consecutive top-level declarations (classes/fields/
methods) preserving their real source text verbatim, and use those as
the row values in the order they were parsed.
4. Using cursor.executemany() (a single call, one INSERT per statement,
MySQL rewrites this into one multi-row INSERT internally), insert the
rows in three passes against the same real dataset from step 3:
a. Insert row-index range [0:250] alone -> expected: succeeds.
b. Insert row-index range [250:500] alone -> expected: succeeds.
c. Insert the full [0:500] range as ONE executemany() call -> this is
where we observed the failure: MySQL error 1064, with the
malformed SQL text visible in the error message (either the ON
DUPLICATE KEY UPDATE clause corrupted mid-clause, or a fragment of
one row's data appearing where different SQL syntax is expected).
If step (c) succeeds with your own substitute dataset, the row range
that fails may differ — try narrowing further by bisecting the row range
(e.g. rows [250:375] as one statement, then [250:312] and [312:375]
independently) until you find a combined range that fails while both
of its halves succeed independently. That was our approach: we
confirmed via bisection that this is not tied to any single "bad" row,
but to some combination of real rows once assembled into one statement
of sufficient size (our smallest reliable combination was 64 rows: 1
specific row plus 63 others, each half succeeding alone, only the
union failing).
5. For comparison: every row in our dataset also succeeds when inserted
completely individually (one row per execute() call, no
executemany()/multi-row batching at all) -- that is in fact the
workaround we shipped in our own application to route around this bug.
This is honest about the limitation: I couldn't get it down to an exact deterministic recipe without our real data, since random/synthetic text of similar shape didn't trigger it. It gives a maintainer (or you, with a substitute open-source codebase) everything needed to reconstruct an equivalent failing case, plus the bisection technique to narrow down whatever range fails in their own dataset.
Suggested fix:
We do not have a confirmed root-cause-level patch — the trigger depends on the actual byte content in a way we couldn't isolate to a single defective code path with confidence (see "What's ruled out"). Two starting points for a maintainer with fuller
context:
1. MySQLCursor._batch_insert()'s template-replace approach (stmt.replace(fmt,
joined_values, 1)) assumes the extracted single-row template `fmt` appears in `stmt` exactly where expected and is safe to blindly replace; worth auditing whether anything in the escaped/joined `values` content, or the ON-DUPLICATE-stripped `tmp` text used only to locate `fmt`, can desync the replace target from the actual VALUES clause at scale.
2. Since a single execute() with a large %(name)s-keyed dict reproduces the same
corruption without going through _batch_insert() at all, the pyformat substitution
path (_bytestr_format_dict / RE_PY_MAPPING_PARAM) likely shares the same class of
defect and should be reviewed in parallel.
Our own workaround (not a fix to this driver): switched to one INSERT statement per
row, parallelized across a small pool of connections to recover throughput — every
single-row statement in our testing succeeded without exception.
© 2026, Oracle Corporation and/or its affiliates
Description: mysql-connector-python produces a malformed final SQL statement — not a data/escaping error surfaced cleanly, but literal SQL syntax corruption (MySQL error 1064) — when executing a sufficiently large multi-row INSERT ... VALUES (...), (...), ... statement built from real-world text content, both via cursor.executemany() (which rewrites into one statement via MySQLCursor._batch_insert()) and via a single cursor.execute() call with a large dict of %(name)s-style named parameters (e.g. as produced by SQLAlchemy's Core insertmanyvalues compilation). The corruption is visible in the server's own error message: text that should never appear together — either the tail of the ON DUPLICATE KEY UPDATE clause spliced with adjacent column names, or a fragment of what should be one row's data appearing where different SQL is expected — indicating the driver's client-side statement assembly, not the server, produced invalid SQL text. Environment: mysql-connector-python 9.5.0, Python 3.13.9, MySQL server 8.0.39 (AWS RDS). Reproduced both with and without SQLAlchemy 2.0.51 in front of it — this is a mysql-connector-python issue, not an SQLAlchemy issue. Also reproduced with SQLAlchemy's insertmanyvalues execution option explicitly disabled, ruling out that specific SQLAlchemy optimization as the cause. What's ruled out: - Not a single "bad" character. Individually tested a lone % (as in a Java String.format("%32s", ...) call), a lone single-quote (Java char literal ' '), a lone backslash-escape sequence, and a zero-width space (U+200B) embedded in a text value — none reproduce the corruption alone in a single-row statement, nor in isolated small multi-row statements containing them. - Not the ON DUPLICATE KEY UPDATE clause specifically — reproduces identically with a plain multi-row INSERT with no ON DUPLICATE KEY UPDATE at all. - Not the MySQL-8.0.19+ "... AS new ... ON DUPLICATE KEY UPDATE col = new.col" alias-required rendering vs. the legacy "col = VALUES(col)" form — both produce the identical corruption shape. - Not executemany()'s internal multi-row rewrite specifically — a single cursor.execute() call with one large dict of %(name)s-style named parameters (no executemany() involved) reproduces it too, at sufficient scale. - Not pure row count or pure total byte size. A 500-row batch of short, synthetic (non-representative) text succeeds. A 250-row prefix of the real triggering dataset succeeds standalone; the following ~125 rows of the same real dataset, further split into a 62-row and a 63-row half, each succeed independently — but the same rows concatenated into one statement fail. Two independent attempts to anonymize the real triggering text (replacing every identifier token with either a same-length or different-length placeholder, preserving all special characters, punctuation, and structure) both stopped reproducing the bug — i.e. it is sensitive to the actual real byte content in a way we have not reduced to a minimal synthetic case. Suspected code location: MySQLCursor._batch_insert() in mysql/connector/cursor.py — it locates the first-row "VALUES (%s, ...)" template via a regex against the statement with ON DUPLICATE KEY UPDATE stripped out, renders each row by substituting into a copy of that template, joins all rendered rows, then does stmt.replace(fmt, joined_values, 1) against the original, unstripped statement text. Given that the identical corruption also reproduces via a single execute() call (which does not go through _batch_insert() at all for a dict-of-named-params statement), the same class of defect likely also exists in the pyformat/named-parameter substitution path used by execute() for large statements (see _bytestr_format_dict / RE_PY_MAPPING_PARAM in the same file). Impact: silently aborts a legitimate bulk write with MySQL error 1064 that superficially looks like an application bug in the calling code's SQL construction, when the actual generated SQL text is correct until the driver's own client-side assembly step. Can affect any application doing bulk executemany()-style inserts, or a single large multi-row INSERT via a dict of named parameters, against real-world text data at moderate scale (order of ~100+ rows in one statement). How to repeat: Environment: mysql-connector-python 9.5.0, Python 3.13.9, MySQL server 8.0.39 (also reproduces via SQLAlchemy 2.0.51 sitting on top of the same driver, and with SQLAlchemy's insertmanyvalues optimization explicitly disabled, so this is a driver issue, not an SQLAlchemy issue). 1. Create a table with a mix of VARCHAR and TEXT columns plus a unique key, e.g.: CREATE TABLE repro ( id BIGINT PRIMARY KEY AUTO_INCREMENT, service_key VARCHAR(100) NOT NULL, branch VARCHAR(255) NOT NULL, file_path VARCHAR(2000) NOT NULL, node_type VARCHAR(50) NOT NULL, node_name VARCHAR(1024) NOT NULL, node_qualified_name TEXT, node_signature TEXT, parent_class VARCHAR(1024), decorator_hints TEXT, start_line INT, end_line INT, node_body_hash VARCHAR(64) NOT NULL, node_key_hash VARCHAR(64) NOT NULL, is_public TINYINT, is_active TINYINT, UNIQUE KEY uniq_key (service_key, branch, node_key_hash) ); 2. Build an INSERT ... ON DUPLICATE KEY UPDATE statement with %s positional placeholders for all 15 columns, and an ON DUPLICATE KEY UPDATE clause that updates every column except the three forming the unique key (col = VALUES(col) for each). 3. Populate rows with REAL source-code-like text, not randomly generated text — this is the part that matters and is hard to describe precisely, because we could not reduce the trigger to a clean synthetic case ourselves (see "What's ruled out" in the Description: neither deliberately-inserted special characters in isolation, nor randomly-generated text of a similar length distribution, reproduced it). What did reproduce it: taking ~500 real AST nodes (classes, fields, methods, with their actual signatures/bodies as parsed text) out of a real Java codebase, in the order they appear across several source files. The rows vary naturally in length — many short one-liners (getters/setters/field declarations, 40-150 characters) interleaved with a smaller number of longer method bodies (several hundred to ~1500 characters), containing ordinary Java syntax: annotations (@Autowired, @Getter), generics (List<X>, Map<K,V>), string literals with embedded quotes, method chaining, occasional Javadoc comments. Recommendation for reproducing without our specific (proprietary) data: take any real, reasonably large open-source Java (or similar) repository, parse out ~500 consecutive top-level declarations (classes/fields/ methods) preserving their real source text verbatim, and use those as the row values in the order they were parsed. 4. Using cursor.executemany() (a single call, one INSERT per statement, MySQL rewrites this into one multi-row INSERT internally), insert the rows in three passes against the same real dataset from step 3: a. Insert row-index range [0:250] alone -> expected: succeeds. b. Insert row-index range [250:500] alone -> expected: succeeds. c. Insert the full [0:500] range as ONE executemany() call -> this is where we observed the failure: MySQL error 1064, with the malformed SQL text visible in the error message (either the ON DUPLICATE KEY UPDATE clause corrupted mid-clause, or a fragment of one row's data appearing where different SQL syntax is expected). If step (c) succeeds with your own substitute dataset, the row range that fails may differ — try narrowing further by bisecting the row range (e.g. rows [250:375] as one statement, then [250:312] and [312:375] independently) until you find a combined range that fails while both of its halves succeed independently. That was our approach: we confirmed via bisection that this is not tied to any single "bad" row, but to some combination of real rows once assembled into one statement of sufficient size (our smallest reliable combination was 64 rows: 1 specific row plus 63 others, each half succeeding alone, only the union failing). 5. For comparison: every row in our dataset also succeeds when inserted completely individually (one row per execute() call, no executemany()/multi-row batching at all) -- that is in fact the workaround we shipped in our own application to route around this bug. This is honest about the limitation: I couldn't get it down to an exact deterministic recipe without our real data, since random/synthetic text of similar shape didn't trigger it. It gives a maintainer (or you, with a substitute open-source codebase) everything needed to reconstruct an equivalent failing case, plus the bisection technique to narrow down whatever range fails in their own dataset. Suggested fix: We do not have a confirmed root-cause-level patch — the trigger depends on the actual byte content in a way we couldn't isolate to a single defective code path with confidence (see "What's ruled out"). Two starting points for a maintainer with fuller context: 1. MySQLCursor._batch_insert()'s template-replace approach (stmt.replace(fmt, joined_values, 1)) assumes the extracted single-row template `fmt` appears in `stmt` exactly where expected and is safe to blindly replace; worth auditing whether anything in the escaped/joined `values` content, or the ON-DUPLICATE-stripped `tmp` text used only to locate `fmt`, can desync the replace target from the actual VALUES clause at scale. 2. Since a single execute() with a large %(name)s-keyed dict reproduces the same corruption without going through _batch_insert() at all, the pyformat substitution path (_bytestr_format_dict / RE_PY_MAPPING_PARAM) likely shares the same class of defect and should be reviewed in parallel. Our own workaround (not a fix to this driver): switched to one INSERT statement per row, parallelized across a small pool of connections to recover throughput — every single-row statement in our testing succeeded without exception.