Org Web Adapter

Editing hungryroot/issues/BE-8049-mysql-collation-mismatch.org

:PROPERTIES:
:END:
#+title: BE-8049 — MySQL utf8mb3/utf8mb4 collation mismatch on fuzzed string input
#+filetags: :contract-testing:

* Status

- *Jira:* filed as [[https://hungryroot.atlassian.net/browse/BE-8049][BE-8049]] on 2026-07-01, after the Atlassian MCP recovered
  (it had been blocked by Cloudflare's WAF, then timed out repeatedly, while
  working BE-7836 earlier the same day).
- Two confirmed occurrences below; likely more endpoints share the same root
  cause (any legacy utf8mb3 column compared against a fuzzed/user string), so
  scope the fix at the column/collation level, not endpoint-by-endpoint.

* Summary

Several endpoints 500 when a request contains a string value that forces a
utf8mb4 comparison against a utf8mb3-collated column. MySQL cannot compare the
two collations, so the query raises instead of the endpoint returning a normal
(possibly empty) response. Found by the Schemathesis contract suite
([[https://hungryroot.atlassian.net/browse/BE-7836][BE-7836]]) while fuzzing endpoints. Tracked as its own bug,
[[https://hungryroot.atlassian.net/browse/BE-8049][BE-8049]], since it's a genuine backend defect rather than a schema-annotation
gap (those are tracked under BE-7837).

** Occurrence 1 — GET /api/v2/dynamic_configs/ (name filter)

~GET /api/v2/dynamic_configs/~ returns a 500 when the ~name~ (or
~name__icontains~) query filter value contains a 4-byte unicode character
compared against the utf8mb3 ~name~ column.

** Occurrence 2 — PUT /api/v2/customers/{customer_id}/

Fuzzing a PUT request body against ~/api/v2/customers/{customer_id}/~ also
raises the same collation error inside a related-field lookup
(~RelatedField.to_internal_value -> queryset.get(pk=data)~) when the generated
value contains non-ASCII characters. The specific FK/field involved was not
pinned down further before the story wrapped; re-run
~test_customer_detail_put_conforms~ in
=app/tests/rest/contract/test_authenticated_contract.py= to get a fresh
Falsifying example and curl repro.

* Error

: django.db.utils.OperationalError: (1267, "Illegal mix of collations
: (utf8mb3_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation
: 'like'")   # occurrence 1 (icontains); occurrence 2 raises the same error for '='

* Root cause

Occurrence 1: ~DynamicConfigListFilter~ in =app/rest/dynamic_config/filters.py=
defines ~name~ as a ~CharFilter~ with ~lookup_expr="icontains"~, producing a SQL
~LIKE~. The ~DynamicConfig.name~ column collation is ~utf8mb3_general_ci~ while
the connection/value collation is ~utf8mb4~. MySQL cannot compare the two
collations for the ~LIKE~, so any filter value carrying a 4-byte / utf8mb4-only
character errors instead of returning an empty result or a 400.

Occurrence 2: a related-field PK lookup on the customer PUT serializer performs
an ~=~ comparison against another utf8mb3-collated column with a fuzzed
utf8mb4 value, hitting the same collation error. Likely the same class of bug
wherever a legacy utf8mb3 text/char column is queried with request-supplied
string data.

* Reproduce

- Occurrence 1: send a GET to =/api/v2/dynamic_configs/= with a ~name~ query
  param whose value contains a 4-byte unicode character (e.g. an emoji).
- Occurrence 2: send a PUT to =/api/v2/customers/{customer_id}/= with a body
  field containing a 4-byte unicode character; re-run
  ~test_customer_detail_put_conforms~ for a fresh Falsifying example.

* Expected

The endpoint returns a valid response (empty result set or 400), not a 500.

* Fix options

- Normalize/cast the filter/field value's collation for the lookup, or
- Align the affected column collations with the connection collation
  (utf8mb4) across the schema, since this is likely to recur elsewhere.

* Notes

Two contract tests are marked ~@unittest.expectedFailure~ referencing BE-8049:
- ~test_v2_dynamic_configs_list_conforms~ in
  =app/tests/rest/contract/test_public_contract.py= (occurrence 1)
- ~test_customer_detail_put_conforms~ in
  =app/tests/rest/contract/test_authenticated_contract.py= (occurrence 2, mixed
  with a genuine BE-7837 schema-annotation gap in the same test)

Once fixed, remove the ~expectedFailure~ markers (test_customer_detail_put_conforms
may still need one for the remaining BE-7837 gap).

A related but distinct gap — the generic error envelope's ~msg_id~ documented as
a required string but always returning null — was also found during BE-7836 and
filed separately as [[https://hungryroot.atlassian.net/browse/BE-8050][BE-8050]]
(not a collation issue; see that ticket / ~test_auth_contract.py~ instead).