1fanwang opened a new pull request, #71249:
URL: https://github.com/apache/airflow/pull/71249
closes: #31373
The `name`, `uri` and `group` columns on the asset tables hard-code the
`latin1_general_cs` collation on MySQL. The choice is sound — the values are
ASCII, and a single-byte charset keeps the 1500-character unique indexes
inside
the 3072-byte index limit that `utf8mb4` would blow past — but it is not
overridable, because it is baked into the ORM column definitions. A
deployment
whose MySQL-compatible engine does not ship that particular collation cannot
create Airflow's schema at all:
```
(1273, "Unsupported collation when new collation is enabled:
'latin1_general_cs'")
```
`sql_engine_collation_for_ids` already exists for exactly this reason on the
`StringID` columns. This adds the equivalent knob for the asset columns,
defaulting to today's value so nothing changes unless it is set.
The collation was repeated inline at ten sites; they now share one constant,
which is where the net line reduction comes from.
Requested in #31373 by @zhangyangyu, with this design suggested by
@hussein-awala in that thread ("Adding a new Airflow configuration to
configure
the table collation and set its default value to `latin1_general_cs` can do
the
job"), and independently reported by @taodaling.
### Testing Done
Against TiDB v8.5.1, which offers 13 collations and no `latin1_general_cs`.
Its
`latin1_bin` is the equivalent substitute: single-byte, case-sensitive, and
it
indexes `VARCHAR(1500)` — verified by inserting `'Abc'` and `'abc'` under a
unique key and confirming both are accepted.
<details><summary>Raw logs</summary>
**Before**, `airflow db migrate` cannot create the first asset table:
```
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError)
(1273, "Unsupported collation when new collation is enabled:
'latin1_general_cs'")
[SQL:
CREATE TABLE asset_alias (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(1500) COLLATE latin1_general_cs NOT NULL,
`group` VARCHAR(1500) COLLATE latin1_general_cs NOT NULL,
CONSTRAINT asset_alias_pkey PRIMARY KEY (id)
)
]
```
**After**, with
`AIRFLOW__DATABASE__SQL_ENGINE_COLLATION_FOR_ASSET_NAMES=latin1_bin`:
```
[info] Creating Airflow database tables from the ORM
[info] Running stamp_revision -> 7a98f1b7dbd3
[info] Airflow database tables created
[info] Database migration done!
real 0m10.077s
```
```
tables created: 71
alembic head : 7a98f1b7dbd3
foreign keys : 86
latin1 columns:
('asset', 'group', 'latin1_bin')
('asset', 'name', 'latin1_bin')
('asset', 'uri', 'latin1_bin')
('asset_active', 'name', 'latin1_bin')
('asset_active', 'uri', 'latin1_bin')
('asset_alias', 'name', 'latin1_bin')
('asset_alias', 'group', 'latin1_bin')
('asset_watcher', 'name', 'latin1_bin')
```
**Default unchanged.** With the option unset the emitted DDL is
byte-identical to
before; MySQL 8.4 still gets `COLLATE latin1_general_cs`.
**A real DAG through the resulting schema** (dynamic task mapping, XCom,
fan-in):
```
[DAG TEST] end task task_id=total map_index=-1
sum of squares = 55
Done. Returned value was: 55
DagRun Finished: dag_id=tidb_smoke, run_duration=4.42, state=success
```
**Regressions**: `tests/unit/models/test_base.py`,
`tests/unit/models/test_asset.py`,
`tests/unit/core/test_configuration.py` and
`tests/unit/utils/test_sqlalchemy.py`
— 242 passed. `prek` static checks and `mypy-airflow-core` clean.
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
