Heterogeneous migration: types, objects, and conversion acceptance¶
This page verifies MO v4.2.5 target types/boundaries; it does not certify complete Oracle/PostgreSQL/SQL Server migration. Retain source semantics, target representation, conversion rules, and rejection/compensation policies.
Mapping review¶
For each column, record source declaration, actual min/max, NULL/empty values, encoding/collation, time precision/timezone, defaults, and constraints. Map database/schema/table names and alternatives for views/indexes/sequences/programs/privileges without collisions. Parseable converted DDL still requires actual-schema and business-query checks.
Exact numeric boundary experiment¶
Full Oracle NUMBER(5,0), NUMBER(10,0), and NUMBER(38,0) ranges do not fit SMALLINT, INT, and BIGINT respectively. This target experiment retains exact values and proves the previous mappings fail at boundaries:
CREATE DATABASE mo_numeric_migration_lab;
USE mo_numeric_migration_lab;
CREATE TABLE exact_values(n5 DECIMAL(5,0),n10 DECIMAL(10,0),n38 DECIMAL(38,0));
INSERT INTO exact_values VALUES(99999,9999999999,'99999999999999999999999999999999999999');
-- Expected-Values: 99999, 9999999999, 99999999999999999999999999999999999999
SELECT CAST(n5 AS CHAR),CAST(n10 AS CHAR),CAST(n38 AS CHAR) FROM exact_values;
CREATE TABLE narrow_values(n5 SMALLINT,n10 INT,n38 BIGINT);
-- Expected-Success: false
INSERT INTO narrow_values(n5) VALUES(99999);
-- Expected-Success: false
INSERT INTO narrow_values(n10) VALUES(9999999999);
-- Expected-Success: false
INSERT INTO narrow_values(n38) VALUES('99999999999999999999999999999999999999');
DROP DATABASE mo_numeric_migration_lab;
Negative scale, scale greater than precision, unconstrained NUMBER, and PG numeric require explicit target precision, rounding, and overflow rules from actual distributions. Global NUMBER/numeric-to-decimal replacement is insufficient. Do not route currency/large exact identifiers through float/double/JavaScript Number.
Time and characters¶
Oracle DATE includes time-of-day, so pure DATE loses information. Oracle empty-string/NULL semantics differ. SQL Server datetime2/datetimeoffset may have seven fractional digits; MO DATETIME(6) reduces fractional precision to six digits, requiring an explicit rounding/truncation policy. Retain raw text/extra precision and define UTC versus original offset; TIMESTAMP or session timezone does not preserve per-row offsets automatically.
Choose representations for NVARCHAR, CLOB, BYTEA, RAW, XML, JSONB, arrays, domains, and composites. Trial supplementary Unicode, binary zero bytes, NULL, and empty values through actual drivers/exporters. Similar type names do not establish identical comparison/index/operator semantics.
Preserve spatial SRID¶
MO v4.2.5 supports spatial types, but PG geometric/PostGIS, Oracle Spatial, and SQL Server geometry/geography formats, coordinate systems, and operators differ. Preserve WKT/WKB and source SRID. For mixed SRIDs, design separate columns/tables or retained raw metadata rather than silently unifying coordinates.
CREATE DATABASE mo_spatial_migration_lab;
USE mo_spatial_migration_lab;
CREATE TABLE unqualified(g GEOMETRY);
CREATE TABLE declared(g GEOMETRY SRID 4326);
INSERT INTO unqualified VALUES(ST_GeomFromText('POINT(1 2)',4326));
INSERT INTO declared VALUES(ST_GeomFromText('POINT(1 2)',4326));
-- Expected-Values: POINT(1 2), 0
SELECT ST_AsText(g),ST_SRID(g) FROM unqualified;
-- Expected-Values: POINT(1 2), 4326
SELECT ST_AsText(g),ST_SRID(g) FROM declared;
DROP DATABASE mo_spatial_migration_lab;
The unqualified column returns SRID 0; the declared column retains 4326. Assigning SRID does not reproject coordinates. Verify axis order, distance units, planar/geodesic behavior, and required functions.
Converters and constraints¶
Review generated SQL before import; do not directly SOURCE it on production. pg2mysql is Perl. A simple conversion at pinned commit dd956ff207178dc67750568a375f50025cc37b38 emits DROP DATABASE. The tool does not cover all triggers/views/special types and does not guarantee lossless migration.
Do not delete CHECK/index lines with sed or globally replace Unicode N prefixes, schema names, or NUMBER. These can remove business constraints, damage multiline DDL, or alter literals. Define alternatives, rejection conditions, and tests for unsupported objects before importing into an isolated target.
Verification scope¶
On 2026-10-14, MO numeric boundaries and spatial SRID positive/negative controls executed, and the Perl converter/output were checked. Oracle/PostgreSQL/SQL Server, their exporters, DBeaver/DataX/CDC were not executed. Source-semantic conversion and production cutover still need verification. See the common migration workflow.