Sync Wangdiantong Original Orders to MySQL: A Single-Strategy Walkthrough
What This Strategy Solves
In one of our projects, a retail client generated a large volume of original orders every day, with Wangdiantong as the source and a self-built MySQL database as the analytics target. Moving the data over sounds straightforward, but small oversights quickly lead to mismatched totals or missed status fields. This strategy aims to land Wangdiantong original orders into MySQL under a unified contract — traceable, re-runnable, and reconcilable — providing a clean foundation for downstream BI and after-sales analytics.
Data Flow and Field Mapping
The data flow is unidirectional: System B (Wangdiantong) → Qeasy Data Integration Platform (the middleware layer responsible for cleansing, transformation, and mapping) → System A (the target MySQL database).
Key field mapping (typical scenario; adjust per project whitelist):
| Business Meaning | Wangdiantong (Source) | MySQL (Target) | Handling Notes |
|---|---|---|---|
| Original order number | order_no | src_order_no | Passed through; serves as idempotency key |
| Store code | shop_no | shop_code | Routed through unified code mapping |
| Order time | created_time | order_time | Normalize timezone to UTC+8 |
| Receiver | receiver | receiver_name | Trim leading/trailing spaces |
| Order status | status | order_status | Dictionary mapping, centrally maintained |
| Amount | total_amount | amount | Numeric validation, 2 decimal places |
| Remarks | remark | memo | Truncate overlong text |
Centralized code mapping is a common pattern among Qeasy customers — keeping store, status, and payment dictionaries in a single mapping table means future field changes only need one edit, rather than scattered fixes across multiple strategies.
Configuring It on Qeasy
On the Qeasy Data Integration Platform, this strategy follows a three-stage model: source → mapping → target.
- Source configuration: Pick Wangdiantong · Qiimen as the data source and pull original orders via an incremental cursor (typically the update-time field). When configuring request parameters, always confirm the incremental cursor field is indexed to avoid full-table scans.
- Middleware mapping: In Qeasy's transformation canvas, map source fields to the target schema per the table above. We recommend a phased header-then-line-items approach: persist the header first, then write line items linked by src_order_no. This simplifies both troubleshooting and per-order queries.
- Target configuration: On the MySQL side, use batch writes (Qeasy commits in batches by default), disable auto-commit, and set a reasonable batch size. Add a unique index on the idempotency key (src_order_no + store code) so duplicate pushes are simply skipped.
Implementation Steps
We typically split the rollout into three phases:
- Incremental starting point: Before the first go-live, align with the client on the exact starting timestamp. Backfill everything before that point via a full sync. Once the starting point is fixed, Qeasy's cursor begins from there, and each subsequent run only pulls new data.
- Full sync trigger: Historical orders are loaded in one shot, ideally during off-peak hours. Switch to incremental mode immediately after the full sync completes.
- Schedule frequency: For retail workloads, every 15 minutes is typical; if order volume is lower and near-real-time is required, push it to 5 minutes. Qeasy supports crontab expressions for fine-grained control.
The safe pattern is the dual-track approach: a full-sync task as a safety net, run periodically for reconciliation, and an incremental task for daily syncs. The two streams don't interfere with each other, so you can switch quickly if something goes wrong.
Lessons Learned
Here are real issues we hit in the field:
- Timezone mismatch: Wangdiantong's default timezone differed from the MySQL server's, causing an 8-hour offset on order times. The safe practice is to convert timezones explicitly in the middleware layer rather than relying on the database's implicit conversion.
- Hard-coded dictionaries: Early on, status mappings were baked directly into transformation scripts. When statuses changed, every strategy had to be patched. We moved to a centralized mapping table so a single edit takes effect everywhere.
- Overly broad idempotency key: Using only the order number worked for same-store duplicates, but missed cross-store duplicate order numbers. The safe pattern is a composite idempotency key of order number plus store code.
- Full sync running alongside incremental: Starting incremental jobs before the full sync finished let the cursor get pushed forward by the full sync, skipping some records. This is a common trap — a typical mistake is underestimating historical data volume; in one case the full sync ran for 6 hours.
- Oversized batches: Large MySQL transactions caused table locks that impacted live business. Reducing batch size from 5,000 to 1,000 resolved the issue.
When to Use and When Not to
This strategy fits retail or distribution scenarios with stable order volume that need BI or after-sales analytics and want to preserve original documents. It is not suitable for projects requiring strict real-time inventory deduction, cross-store real-time aggregation, or projects where source-side fields change frequently — the latter should be driven by event streams rather than scheduled sync.