Real-Time Inventory Sync in Practice: Pulling Stock Details from Wangdiantong into MySQL

What This Strategy Solves (Scenario & Value)

A retail customer runs Wangdiantong as its e-commerce fulfillment hub, while a MySQL data warehouse feeds BI and reconciliation. Operations asks questions like "how much can we still sell right now" or "which warehouse is about to run out" — exporting data manually is not realistic. We use the Qeasy Data Integration Platform to land Wangdiantong inventory details into MySQL on a 13-minute cadence, providing near-real-time data to BI while centralizing the mapping rules for warehouse codes and SKU codes in the platform to prevent data drift between systems.

Data Flow & Field Mapping

The pipeline is a single-direction landing: Wangdiantong Qimen (wdt.stock.query) as the query source → Qeasy as the middle layer for time windowing, pagination, and mapping → MySQL target table jry_wdt_stock_query written via REPLACE INTO for idempotency.

Key field mapping (excerpt, source → target):

The source start_time / end_time can be left empty, but in production you must bind them to time variables, otherwise every run becomes a full pull.

How to Configure in Qeasy

Source (Wangdiantong query)

• API: wdt.stock.query, POST; effect = QUERY.
• Bind start_time to LAST_SYNC_TIME and end_time to CURRENT_TIME; enable idCheck on the id field.
• Put page_size in otherRequest as {{PAGINATION_PAGE_SIZE}}; the platform auto-paginates.
• warehouse_no and spec_no can be passed through for single-warehouse or single-SKU testing.

Target (MySQL write)

• API: execute, effect = EXECUTE.
• Use REPLACE INTO jry_wdt_stock_query (...) with named parameters (:field). Never concatenate strings to avoid injection.
• Include rec_id in the column list so REPLACE can dedup by primary key.
• With effect = EXECUTE, the platform treats lastInsertId as success; no further query is needed.

Platform-level conventions common among Qeasy customers: centralize code mapping in the platform's mapping table for cross-strategy reuse; phase header and line items into separate strategies; keep incremental and full loads on parallel tracks — production runs incremental on crontab, while a one-off full load covers the initial backfill and any post-incident recovery.

Implementation Steps

1. Prepare the mapping table: build warehouse code and SKU code mappings in Qeasy, do an offline reconciliation first, then go live.
2. Smoke test: set page_size = 1 and pin start_time/end_time to a 5-minute window, confirm a single record lands correctly.
3. Incremental seed: initialize LAST_SYNC_TIME to 24 hours before go-live and run a backfill to flush historical stock into MySQL.
4. Full load trigger: use a one-off script to top up all stock before go-live, then reset LAST_SYNC_TIME to the go-live moment to avoid overlap with the incremental window.
5. Schedule cadence: source crontab */13 * * * *, target 3-59/13 * * * *. The 3-minute offset gives the source fetch + middle layer processing enough headroom.
6. Monitoring & alerting: track "rows written this run", "retry count", and "is LAST_SYNC_TIME advancing". Alert on any anomaly.

Pitfalls Recap

1. Time window not bound: a typical mistake is leaving start_time empty, which causes a full pull every run and burns the API quota within hours. The safe approach is to explicitly bind LAST_SYNC_TIME and CURRENT_TIME, and validate the variables in the platform.
2. Page size set too large: bumping page_size to 100 makes the source API time out, and the task retries forever. Start with 50 for a week, observe the success rate, then tune.
3. REPLACE INTO field spelling: the source field is avaliable_num (missing an 'l'), and the target table keeps that spelling. Do not "helpfully" rename it to available, otherwise the column will be empty.
4. Both crontabs hit at the same minute: source pulls at :00, target writes at :00, the middle layer has no time to process, and data piles up. Offsetting by 3-5 minutes is the safe play.
5. idCheck not enabled: if rec_id repeats within the same window, REPLACE handles it, but during pagination some pages may be skipped. With idCheck on, the platform dedups and re-pulls as needed.

When It Fits — and When It Doesn't

Fits: e-commerce fulfillment scenarios where Wangdiantong stock needs to be landed into MySQL/warehouse at high frequency for BI, reconciliation, and stockout alerting; SKU and warehouse dimensions in the hundreds to tens of thousands. Doesn't fit: sub-second real-time inventory (this strategy runs at ~13-minute granularity at best); complex multi-ERP aggregation that requires pre-aggregation before landing — in those cases split into multiple strategies.

Reference Material (Desensitized)

Strategy metadata excerpt:

{
  "source": {
    "platform": "Wangdiantong Qimen",
    "api": "wdt.stock.query",
    "effect": "QUERY",
    "crontab": "*/13 * * * *"
  },
  "target": {
    "platform": "MySQL",
    "api": "execute",
    "effect": "EXECUTE",
    "table": "jry_wdt_stock_query",
    "crontab": "3-59/13 * * * *",
    "main_sql": "REPLACE INTO jry_wdt_stock_query (rec_id, spec_no, warehouse_no, stock_num, avaliable_num, ...) VALUES (:rec_id, :spec_no, :warehouse_no, :stock_num, :avaliable_num, ...)"
  }
}

(All tenant names, platform IDs, and table names above are taken from the raw configuration JSON; replace with the customer's own environment values when implementing.)