MySQL row semantics
01Correctness often hides in connection settings.
A MySQL route often bridges operational tables and analytical or partner destinations. Query scope, indexes, character sets, collations, time handling, batching, and destination write policy all affect whether the movement is correct and whether it places acceptable load on the source.
| Decision | Integration approach |
|---|---|
| Query through an indexed path | Define database, tables or queries, selected columns, indexed filters, ordering, keys, character set, collation, time-zone assumptions, and type conversions. Selection should be stable enough to reconcile later. |
| Restart after a committed batch | Break large work into deterministic batches or checkpoints and set transaction boundaries deliberately. A restart must distinguish committed destination rows from a batch that failed before completion. |
| Freeze text and time meaning | Agree on nulls, unsigned and precise numeric values, date and time semantics, text encoding, keys, insert or update behavior, constraint failures, rejected rows, and source-to-destination counts. |
| Authentication and access | Configure the selected SQL driver and connection string. Validate authentication, TLS and least-privilege grants on the exact database version; keep credentials outside the flow example. |
MySQL-specific Flower path
02Turn operational tables into a controlled incremental stream.
Flower's MySQL adapter provides structured queries, writes, and transaction boundaries with MySQL identifiers and placeholders. The flow defines discovery, conversions, quality gates, checkpointing, and evidence instead of embedding them in application code.
Build a deterministic window
Use an indexed watermark plus a unique tie-breaker and explicit ordering. Overlap windows where timestamps are coarse or clocks differ; reconcile keys so overlap produces proof rather than duplicate business rows.
Normalize MySQL edge cases
Validate unsigned ranges, DECIMAL precision, zero or invalid date policy, BIT and binary values, JSON, and nullability. Convert under a declared session time zone and character set before downstream systems reinterpret values.
Write inside a known transaction
Prepared writes run in managed transactions. Row counts, constraint failures, rejects, and committed keys are reconciled before the checkpoint moves, so connection loss does not automatically become a duplicate load.
MySQL production playbook
03Control source load, drift, and duplicate behavior.
MySQL commonly serves latency-sensitive applications, so an integration must be a predictable database client. Fast polling, large offsets, implicit conversions, or broad scans can hurt the product while still looking successful to the pipeline.
Consistent-read policy
State whether each batch may see a fresh READ COMMITTED view or a run needs one REPEATABLE READ snapshot. Keep transactions short enough for operations, and define how DDL or schema changes fail the route.
Index and pool budget
Tune the access path, connection pool, query timing, batch size, and concurrent flows using representative volumes. Monitor extraction age and throughput together to support predictable freshness and efficient use of the source database.
Duplicate-key and delete contract
Choose insert, update, upsert, append, or staged replacement intentionally. Define what duplicate-key errors mean, how hard deletes are represented, whether updates can arrive out of order, and which reconciliation proves the final state.