Airflow MySqlHook DAG and idempotent retries

Installation, connection and versions

Reference versions are Airflow 3.3.2, MySQL provider 6.7.0 and mysql-connector-python 26.7.0. Prior checks executed a real DAG/tasks and MySqlHook. Row-set checks within a task are not an equal number of scheduler failure tests; queued heavy DAG runs do not count as passes.

Install using the matching Python constraints in the Airflow installation guide, with MySQL and standard providers. Complete the shared fixtures. Create connection matrixone_demo in the UI or secret backend: MySQL type and the host/port/user/password/schema from MO_*. Select the validated driver in Extra below. The default is mysqlclient, a different route.

{"client":"mysql-connector-python","ssl_disabled":true}

Use ssl_disabled:true only in the isolated local environment. For remote access, set it to false and supply ssl_ca, ssl_verify_cert:true and ssl_verify_identity:true, with the CA available to every executing worker.

Save and run the DAG

Save as $AIRFLOW_HOME/dags/matrixone_demo.py, confirm there are no import errors in your Airflow deployment, and trigger it. Writing fixed final values avoids repeated rev=rev+1 effects on retry. A separate task reads through a new connection.

from datetime import datetime, timedelta, timezone
from decimal import Decimal
from airflow.sdk import DAG
from airflow.providers.standard.operators.python import PythonOperator
from airflow.providers.mysql.hooks.mysql import MySqlHook

def write():
    d = MySqlHook(mysql_conn_id="matrixone_demo").get_conn()
    c = d.cursor()
    try:
        c.execute("INSERT INTO lake_demo(id,amount,payload,label,rev) VALUES(%s,%s,%s,%s,%s) "
                  "ON DUPLICATE KEY UPDATE amount=VALUES(amount),payload=VALUES(payload),"
                  "label=VALUES(label),rev=VALUES(rev)",
                  (401,Decimal("0.000000000000000001"),b"\x00\xff\x80","Airflow😀",1))
        d.commit()
    except Exception:
        d.rollback()
        raise
    finally:
        c.close()
        d.close()

def verify():
    d = MySqlHook(mysql_conn_id="matrixone_demo").get_conn()
    c = d.cursor()
    try:
        c.execute("SELECT amount,payload,label,rev FROM lake_demo WHERE id=401")
        row = c.fetchone()
        assert row == (Decimal("0.000000000000000001"),b"\x00\xff\x80","Airflow😀",1)
    finally:
        c.close()
        d.close()

with DAG("matrixone_demo",start_date=datetime(2026,1,1,tzinfo=timezone.utc),
         schedule=None,catchup=False,max_active_runs=1) as dag:
    load = PythonOperator(task_id="write",python_callable=write,retries=1,
                          retry_delay=timedelta(seconds=3),execution_timeout=timedelta(minutes=5))
    check = PythonOperator(task_id="verify",python_callable=verify)
    load >> check
airflow dags list-import-errors
airflow dags trigger matrixone_demo

Recovery, schema and load

After a successful run, exercise task exit after commit, worker restart, retries and duplicate triggers in an isolated environment. Check final values, duplicates and task logs. This UPSERT is idempotent for the same final value; production out-of-order events require a version condition or event ledger.

Missing source rows do not automatically delete destination rows. Supply keyed delete tasks/events. Migrate destination DDL before updating DAG field mappings. SQL transactions and Airflow scheduler state do not commit atomically: task success and retries are not cross-system exactly-once. Bound concurrency with pools, max_active_runs and driver pools separately, and measure connections and database load during retries.