"""Regression: a migration whose table FKs to parents with DIFFERENT collations must still apply. Production failure this pins (2026-07-29, PR #2098's first deploy): migration 437 creates ``cmdb_cloud_grant_renewal_requests`` with the table charset borrowed from ``cmdb_cloud_access_grants`` (``utf8mb4_bin`` on prod TiDB) and four columns FK'ing to ``identity_persons`` (``utf8mb4_unicode_ci``). A column declared without an explicit ``COLLATE`` inherits the *table* default, so ``person_id`` came out ``utf8mb4_bin`` and TiDB rejected ``fk_3`` with error 3780 ("Referencing column 'person_id' and referenced column 'id' in foreign key constraint 'fk_3' are incompatible"). Deploy aborted after migration 436 had already committed. Local MySQL 8 could not reproduce it because every parent there shares one collation — the same blind spot that let migration 126 fail the same way on 2026-05-07, and the reason ``test_migration_126_idempotent_resume`` settles for asserting the *shape* of its fix. This test closes that gap: it builds the mixed-parent situation explicitly, with two scratch parents given deliberately different collations, so the failure mode is reachable on plain MySQL. What it locks: * ``id_type_for`` carries the parent's collation, not just its data type — the fix must live in the helper, because the helper is what every migration already calls. * The mixed-parent CREATE TABLE actually succeeds against a real server, and — separately — that the same server does reject the mismatched version with 3780. Both halves are needed: the success case alone would also pass on a server that had stopped enforcing the rule, i.e. while guarding nothing. * In the scratch tables, every FK column ends up byte-identical in DATA_TYPE and COLLATION_NAME to the parent id it references — including the columns whose parent DISAGREES with the table default, which is the only interesting case. * The real migration 437, run unmodified against prod-shaped parents, creates all six FKs. That is the artifact that actually broke, so it is the one worth replaying rather than a hand-written stand-in. It also pins the invariant across the real schema: no FK column anywhere may collate differently from its parent. That pass is trivially green on single-collation MySQL, but it is the check that would have caught this on prod-shaped data, and it costs one query. """ from __future__ import annotations import importlib.util import os import pathlib import uuid import pytest import sqlalchemy as sa from alembic.migration import MigrationContext from alembic.operations import Operations from app.core.migration_helpers import id_type_for, table_kw_for from ..conftest import _TEST_SYNC_URL _MIGRATION_437 = ( pathlib.Path(__file__).resolve().parents[2] / "alembic" / "versions" / "437_cmdb_cloud_grant_renewal_requests.py" ) # Two collations in the same charset family, mirroring prod TiDB's split: # cmdb tables are binary, identity_persons is unicode_ci. _BIN = "utf8mb4_bin" _CI = "utf8mb4_unicode_ci" @pytest.fixture def conn(): engine = sa.create_engine(_TEST_SYNC_URL) with engine.begin() as c: yield c engine.dispose() def _column_meta(conn, table: str, column: str) -> tuple[str, str]: row = conn.execute( sa.text( "SELECT DATA_TYPE, COLLATION_NAME FROM information_schema.COLUMNS " "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t " "AND COLUMN_NAME = :c" ), {"t": table, "c": column}, ).fetchone() assert row is not None, f"{table}.{column} not found" return (row[0] or "").lower(), row[1] def test_mixed_parent_collations_still_apply(conn) -> None: suffix = uuid.uuid4().hex[:8] bin_parent = f"zz_fkcol_bin_{suffix}" ci_parent = f"zz_fkcol_ci_{suffix}" child = f"zz_fkcol_child_{suffix}" # Parents deliberately disagree — this is the prod shape. conn.execute(sa.text( f"CREATE TABLE {bin_parent} (id CHAR(36) NOT NULL, PRIMARY KEY (id)) " f"CHARSET=utf8mb4 COLLATE={_BIN}" )) conn.execute(sa.text( f"CREATE TABLE {ci_parent} (id CHAR(36) NOT NULL, PRIMARY KEY (id)) " f"CHARSET=utf8mb4 COLLATE={_CI}" )) try: ctx = MigrationContext.configure(conn) with Operations.context(ctx) as op: bin_t = id_type_for(bin_parent) ci_t = id_type_for(ci_parent) kw = table_kw_for(bin_parent) # The helper must have picked the collation up, not just the # data type. Asserting this before the DDL makes a failure # readable: "helper dropped the collation" rather than a raw # 3780 from the server. assert ci_t.collation == _CI assert bin_t.collation == _BIN # Table default follows the BIN parent while ``ci_ref`` # points at the CI parent — the exact mismatch that killed # the deploy. Without the collation on ``ci_t`` this raises # OperationalError 3780. op.create_table( child, sa.Column("id", sa.CHAR(36), primary_key=True), sa.Column( "bin_ref", bin_t, sa.ForeignKey(f"{bin_parent}.id"), nullable=False, ), sa.Column( "ci_ref", ci_t, sa.ForeignKey(f"{ci_parent}.id"), nullable=False, ), **kw, ) # Byte-identical to the parents, including the one that disagrees # with the table default. assert _column_meta(conn, child, "bin_ref") == ("char", _BIN) assert _column_meta(conn, child, "ci_ref") == ("char", _CI) # The PK is nobody's child: it takes the table default. assert _column_meta(conn, child, "id") == ("char", _BIN) finally: for t in (child, ci_parent, bin_parent): conn.execute(sa.text(f"DROP TABLE IF EXISTS {t}")) def test_server_really_rejects_a_collation_mismatch(conn) -> None: """The premise, checked rather than asserted from memory. ``test_mixed_parent_collations_still_apply`` catches a regression on the helper's return value, which fails fast and readably — but that means it never reaches the DDL, so on its own it does not prove the server would have objected. This one does: a bare ``CHAR(36)`` column inheriting the table's binary default, FK'd to a ``utf8mb4_unicode_ci`` parent, must be refused with 3780. If this test ever starts passing-by-not-raising, the invariant the other tests defend has evaporated and they are guarding nothing. """ suffix = uuid.uuid4().hex[:8] ci_parent = f"zz_fkcol_reject_p_{suffix}" child = f"zz_fkcol_reject_c_{suffix}" conn.execute(sa.text( f"CREATE TABLE {ci_parent} (id CHAR(36) NOT NULL, PRIMARY KEY (id)) " f"CHARSET=utf8mb4 COLLATE={_CI}" )) try: with pytest.raises(sa.exc.OperationalError) as excinfo: conn.execute(sa.text( f"CREATE TABLE {child} (" f" id CHAR(36) NOT NULL, ref CHAR(36) NOT NULL, " f" PRIMARY KEY (id), " f" FOREIGN KEY (ref) REFERENCES {ci_parent} (id)" f") CHARSET=utf8mb4 COLLATE={_BIN}" )) assert "3780" in str(excinfo.value), str(excinfo.value) finally: conn.execute(sa.text(f"DROP TABLE IF EXISTS {child}")) conn.execute(sa.text(f"DROP TABLE IF EXISTS {ci_parent}")) def test_varchar_parent_keeps_its_collation(conn) -> None: """CI MySQL has VARCHAR(36) ids where prod TiDB has CHAR(36). The helper branches on data type, so the VARCHAR side needs the same guarantee — otherwise the fix would hold on prod and quietly not on the CI shards that gate it. """ suffix = uuid.uuid4().hex[:8] parent = f"zz_fkcol_vc_{suffix}" conn.execute(sa.text( f"CREATE TABLE {parent} (id VARCHAR(36) NOT NULL, PRIMARY KEY (id)) " f"CHARSET=utf8mb4 COLLATE={_CI}" )) try: ctx = MigrationContext.configure(conn) with Operations.context(ctx): t = id_type_for(parent) assert isinstance(t, sa.String) and not isinstance(t, sa.CHAR) assert t.collation == _CI finally: conn.execute(sa.text(f"DROP TABLE IF EXISTS {parent}")) def test_missing_parent_yields_uncollated_type(conn) -> None: """Fresh-DB defence: nothing to match, so no COLLATE is emitted. Keeps the helper usable mid-chain before the parent exists; the FK the caller declares would fail on the missing table first anyway. """ ctx = MigrationContext.configure(conn) with Operations.context(ctx): t = id_type_for(f"zz_absent_{uuid.uuid4().hex[:8]}") assert t.collation is None def test_migration_437_applies_under_prod_shaped_parents() -> None: """End-to-end: run the SHIPPED migration 437 against prod's collations. The helper-level tests above pin the primitive; this one pins the actual failing artifact. It stands up a scratch schema whose parents have prod TiDB's shape — ``identity_persons.id`` CHAR(36) ``utf8mb4_unicode_ci`` next to ``cmdb_cloud_*`` VARCHAR(36) ``utf8mb4_bin`` — and executes ``437.upgrade()`` unmodified. Before the fix this raises 3780 on ``fk_3``, which is exactly how the 2026-07-29 deploy died; after it, all six FKs land and each column matches the parent it points at. Needs a connection that may create a schema: CI supplies ``TEST_DATABASE_ADMIN_URL`` (root), otherwise we try the ordinary test user, which locally is unprivileged. Skips (rather than fails) when neither can — the invariant is already covered by the tests above, and a privilege difference is not a code defect. """ scratch = f"helix_fkshape_{uuid.uuid4().hex[:8]}" admin_url = os.environ.get("TEST_DATABASE_ADMIN_URL") base = ( admin_url.rstrip("/") if admin_url else _TEST_SYNC_URL.rpartition("/")[0] ) admin = sa.create_engine(base + "/", isolation_level="AUTOCOMMIT") try: with admin.connect() as c: c.execute(sa.text( f"CREATE DATABASE {scratch} CHARACTER SET utf8mb4" )) except sa.exc.SQLAlchemyError as exc: admin.dispose() pytest.skip(f"cannot create a scratch schema: {exc}") eng = sa.create_engine(f"{base}/{scratch}", isolation_level="AUTOCOMMIT") try: with eng.connect() as c: c.execute(sa.text( "CREATE TABLE identity_persons " "(id CHAR(36) NOT NULL, PRIMARY KEY (id)) " f"CHARSET=utf8mb4 COLLATE={_CI}" )) for parent in ("cmdb_cloud_access_grants", "cmdb_cloud_scopes"): c.execute(sa.text( f"CREATE TABLE {parent} " "(id VARCHAR(36) NOT NULL, PRIMARY KEY (id)) " f"CHARSET=utf8mb4 COLLATE={_BIN}" )) spec = importlib.util.spec_from_file_location( "m437_prodshape", _MIGRATION_437, ) assert spec and spec.loader module = importlib.util.module_from_spec(spec) spec.loader.exec_module(module) ctx = MigrationContext.configure(c) with Operations.context(ctx): module.upgrade() table = "cmdb_cloud_grant_renewal_requests" # Person FKs must NOT have inherited the binary table default. for col in ( "person_id", "requested_by_person_id", "owner_decided_by_person_id", "admin_decided_by_person_id", ): assert _column_meta(c, table, col) == ("char", _CI), col for col in ("grant_id", "open_for_grant_id", "scope_id"): assert _column_meta(c, table, col) == ("varchar", _BIN), col fks = c.execute(sa.text( "SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE " "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t " "AND REFERENCED_TABLE_NAME IS NOT NULL" ), {"t": table}).scalar() # 2 cmdb + 4 person. A silently-dropped FK would leave the # renewal rows referentially unanchored. assert fks == 6, fks finally: eng.dispose() with admin.connect() as c: c.execute(sa.text(f"DROP DATABASE IF EXISTS {scratch}")) admin.dispose() def test_no_fk_column_in_the_live_schema_disagrees_with_its_parent( conn, ) -> None: """Whole-schema sweep of the invariant, COLLATION only. Deliberately not comparing DATA_TYPE: this schema is full of ``VARCHAR(36) -> CHAR(36)`` pairs (the ORM declares ``String(36)`` while the alembic-authored parents are ``CHAR(36)``), ~90 of them, and the server accepts every one — which is the proof that same-width CHAR/VARCHAR is not what 3780 is about. Collation is. Asserting the stricter thing would mean a permanently-red test, i.e. a deleted test. Green by construction on single-collation local MySQL — its value is that it fires on any server whose tables diverge (prod TiDB), and on any future migration that hand-rolls a type instead of asking ``id_type_for``. """ rows = conn.execute(sa.text( """ SELECT k.TABLE_NAME, k.COLUMN_NAME, c.DATA_TYPE, c.COLLATION_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, p.DATA_TYPE, p.COLLATION_NAME FROM information_schema.KEY_COLUMN_USAGE k JOIN information_schema.COLUMNS c ON c.TABLE_SCHEMA = k.TABLE_SCHEMA AND c.TABLE_NAME = k.TABLE_NAME AND c.COLUMN_NAME = k.COLUMN_NAME JOIN information_schema.COLUMNS p ON p.TABLE_SCHEMA = k.TABLE_SCHEMA AND p.TABLE_NAME = k.REFERENCED_TABLE_NAME AND p.COLUMN_NAME = k.REFERENCED_COLUMN_NAME WHERE k.TABLE_SCHEMA = DATABASE() AND k.REFERENCED_TABLE_NAME IS NOT NULL """ )).fetchall() assert rows, "no FKs found — query or schema is wrong, not a pass" bad = [ f"{r[0]}.{r[1]} ({r[2]}/{r[3]}) -> {r[4]}.{r[5]} ({r[6]}/{r[7]})" for r in rows if r[3] != r[7] ] assert not bad, ( "FK columns collate differently from the id they reference — " "MySQL/TiDB rejects these with error 3780 on creation, so on this " "server they are a latent deploy failure. Fix by typing the " "column with id_type_for(parent), or by calling " "normalize_fk_column_collation before create_foreign_key:\n " + "\n ".join(bad) )