This is an automated email from the ASF dual-hosted git repository. vatsrahul1001 pushed a commit to branch fix-mysql-downgrade-deadline-interval-json in repository https://gitbox.apache.org/repos/asf/airflow.git
commit 3f9e4673c21e3e1d8574296d60cfb051a682d916 Author: Rahul Vats <[email protected]> AuthorDate: Wed Jun 10 18:09:57 2026 +0530 Fix MySQL downgrade from 3.3.0 on deadline_alert.interval JSON conversion The 3.3.0 migration 0117 (8812eb67b63c) converts deadline_alert.interval to JSON on upgrade and back to FLOAT on downgrade. On MySQL the downgrade unwrapped the value with CAST(... AS DOUBLE) inside an UPDATE that runs while the column is still typed JSON. Writing a non-JSON DOUBLE into a JSON column makes MySQL reject the whole statement with ER_INVALID_JSON_TEXT (3140), so `airflow db downgrade` fails for any deployment that has at least one deadline_alert row. Keep the unwrapped value as a JSON number in the UPDATE and let the subsequent column retype to FLOAT perform the cast, mirroring how the PostgreSQL branch defers the cast to its ALTER ... USING clause. The offline-mode MySQL instructions had the same defect and are corrected too. Adds a MySQL-backend regression test that seeds rows and runs the migration's conversion SQL; the existing migration stairway missed this because it runs against an empty schema (zero rows updated never trips MySQL's per-row JSON validation). --- .../0117_3_3_0_change_deadline_interval_to_json.py | 37 +++++---- .../test_0117_deadline_interval_json_migration.py | 88 ++++++++++++++++++++++ 2 files changed, 110 insertions(+), 15 deletions(-) diff --git a/airflow-core/src/airflow/migrations/versions/0117_3_3_0_change_deadline_interval_to_json.py b/airflow-core/src/airflow/migrations/versions/0117_3_3_0_change_deadline_interval_to_json.py index 04e5a35aa31..2c25fc50e47 100644 --- a/airflow-core/src/airflow/migrations/versions/0117_3_3_0_change_deadline_interval_to_json.py +++ b/airflow-core/src/airflow/migrations/versions/0117_3_3_0_change_deadline_interval_to_json.py @@ -38,6 +38,21 @@ depends_on = None airflow_version = "3.3.0" +def _mysql_downgrade_interval_value_sql(table_name: str = "deadline_alert") -> str: + """Unwrap the serialized interval to a bare JSON number; the FLOAT cast happens at the column retype.""" + return f""" + UPDATE {table_name} + SET `interval` = + CASE + WHEN JSON_EXTRACT(`interval`, '$.__data__') IS NOT NULL + THEN JSON_EXTRACT(`interval`, '$.__data__') + WHEN JSON_EXTRACT(`interval`, '$.__classname__') IS NULL + THEN `interval` + ELSE NULL + END + """ + + def upgrade(): """Apply change deadline interval to JSON.""" conn = op.get_bind() @@ -173,18 +188,20 @@ def downgrade(): MySQL: - Step 1: Convert values + Step 1: Convert values. Leave them as JSON numbers; the column is still JSON + here, so casting to DOUBLE fails with ER_INVALID_JSON_TEXT (3140). The cast + happens in Step 2 instead. UPDATE deadline_alert SET `interval` = CASE WHEN JSON_EXTRACT(`interval`, '$.__data__') IS NOT NULL - THEN CAST(JSON_EXTRACT(`interval`, '$.__data__') AS DOUBLE) + THEN JSON_EXTRACT(`interval`, '$.__data__') WHEN JSON_EXTRACT(`interval`, '$.__classname__') IS NULL - THEN CAST(`interval` AS DOUBLE) + THEN `interval` ELSE NULL END; - Step 2: Convert column type + Step 2: Convert column type. This casts the JSON numbers to DOUBLE. ALTER TABLE deadline_alert MODIFY COLUMN `interval` DOUBLE; @@ -221,17 +238,7 @@ def downgrade(): """) elif dialect == "mysql": - op.execute(""" - UPDATE deadline_alert - SET `interval` = - CASE - WHEN JSON_EXTRACT(`interval`, '$.__data__') IS NOT NULL - THEN CAST(JSON_EXTRACT(`interval`, '$.__data__') AS DOUBLE) - WHEN JSON_EXTRACT(`interval`, '$.__classname__') IS NULL - THEN CAST(`interval` AS DOUBLE) - ELSE NULL - END - """) + op.execute(_mysql_downgrade_interval_value_sql()) # Serialized VariableInterval objects do not contain a numeric "__data__" field # and therefore cannot be converted back to a float representation. diff --git a/airflow-core/tests/unit/migrations/test_0117_deadline_interval_json_migration.py b/airflow-core/tests/unit/migrations/test_0117_deadline_interval_json_migration.py new file mode 100644 index 00000000000..3d7c39c2e96 --- /dev/null +++ b/airflow-core/tests/unit/migrations/test_0117_deadline_interval_json_migration.py @@ -0,0 +1,88 @@ +# +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +""" +Regression test for migration 0117 (8812eb67b63c) on MySQL. + +The downgrade must convert ``deadline_alert.interval`` from JSON back to FLOAT without +raising ``ER_INVALID_JSON_TEXT`` (3140). The failure only reproduces with at least one +row present, so this seeds rows and runs the migration's own conversion SQL. It is a +no-op on SQLite/PostgreSQL, which take different code paths. +""" + +from __future__ import annotations + +import importlib.util +from pathlib import Path + +import pytest +import sqlalchemy as sa + +from airflow import settings + +from tests_common.test_utils.paths import AIRFLOW_CORE_SOURCES_PATH + +pytestmark = pytest.mark.db_test + +_MIGRATION_PATH = ( + Path(AIRFLOW_CORE_SOURCES_PATH) + / "airflow/migrations/versions/0117_3_3_0_change_deadline_interval_to_json.py" +) +_spec = importlib.util.spec_from_file_location("migration_0117", _MIGRATION_PATH) +_migration = importlib.util.module_from_spec(_spec) # type: ignore[arg-type] +_spec.loader.exec_module(_migration) # type: ignore[union-attr] + +# Isolated table so we run the real conversion SQL without seeding the live deadline_alert +# table (which has FK/NOT NULL columns). +_TABLE = "_test_deadline_interval_dg" + +# A serialized timedelta as written by the 0117 upgrade. +_WRAPPED_TIMEDELTA = '{"__classname__": "datetime.timedelta", "__version__": 2, "__data__": 300.0}' + + [email protected]("mysql") +def test_mysql_downgrade_interval_value_update_does_not_reject_on_json_column(): + """The downgrade value-conversion UPDATE must not raise 3140, and must round-trip to FLOAT.""" + create = f"CREATE TABLE {_TABLE} (id INT PRIMARY KEY, `interval` JSON NOT NULL)" + drop = f"DROP TABLE IF EXISTS {_TABLE}" + + with settings.engine.begin() as conn: + conn.execute(sa.text(drop)) + conn.execute(sa.text(create)) + conn.execute( + sa.text(f"INSERT INTO {_TABLE} (id, `interval`) VALUES (1, :v)"), + {"v": _WRAPPED_TIMEDELTA}, + ) + conn.execute( + sa.text(f"INSERT INTO {_TABLE} (id, `interval`) VALUES (2, CAST(:v AS JSON))"), + {"v": "60.0"}, + ) + + try: + with settings.engine.begin() as conn: + # Column is still JSON here; this UPDATE must not raise 3140. The retype casts. + conn.execute(sa.text(_migration._mysql_downgrade_interval_value_sql(_TABLE))) + conn.execute(sa.text(f"ALTER TABLE {_TABLE} MODIFY `interval` FLOAT NOT NULL")) + + with settings.engine.connect() as conn: + rows = dict(conn.execute(sa.text(f"SELECT id, `interval` FROM {_TABLE}")).all()) + + assert rows == {1: 300.0, 2: 60.0} + finally: + with settings.engine.begin() as conn: + conn.execute(sa.text(drop))
