The Hidden Data-Loss Traps in Cross-Engine Migration

The dangerous migration bugs aren't the ones that crash. A crash gets noticed, retried, fixed. The dangerous ones complete successfully, report green, and quietly hand you different data than you started with. While certifying all 49 of our engine pairs, the harness surfaced a collection of exactly these. Five favourites:

1. The TINYINT that isn't symmetrical

SQL Server's TINYINT is unsigned: 0–255. MySQL's is signed: −128–127. Map them name-to-name in either direction and the edges silently clip — 255 becomes 127, −128 overflows. The fix is asymmetric on purpose: MySQL → MSSQL widens to SMALLINT, and MSSQL sources tag the column unsigned so every target widens it correctly.

2. Bare VARBINARY means VARBINARY(1)

Emit VARBINARY without a length on SQL Server and you've declared a one-byte column. Worse, with ANSI padding semantics inserts may truncate without erroring. Our generator now always carries the source length through, capping into VARBINARY(MAX) past 8000 bytes.

3. SQLite's NUMERIC affinity rounds your money

SQLite stores values, not types. Insert -922337203685477.5800 (a SQL Server MONEY max) into a NUMERIC-affinity column and SQLite converts it to a float64 — which can't represent it, so you get …477.6 back. High-precision decimals have to land in TEXT columns, where SQLite's affinity rules leave the exact string untouched.

4. The driver that returns your NUMBER as a float

Oracle's NUMBER holds 38 significant digits. The default driver fetch returns… a Python float, with ~15. The value stored in Oracle is exact; everything you read out of it is rounded. One configuration line — fetch decimals, not floats — and a class of phantom checksum mismatches (and real downstream corruption) disappears.

5. One failed statement poisons every statement after it

On SQL Server via pymssql, a failed DDL statement leaves the connection inside an aborted implicit transaction. The next, perfectly valid statement then dies with COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION — an error pointing at the wrong statement entirely. Roll back on every failure, or debug ghosts.

The meta-lesson

Not one of these is exotic. They're boring, well-documented engine behaviours that only become visible when real data crosses a real pair of engines. Which is the argument for live certification: a type-mapping table can't catch what it doesn't know to look for — a checksum over migrated rows can.