Xinchuang Database Migration: MySQL→Kingbase & Oracle→Dameng 5-Phase Guide

01 Choose Route: Compatibility Mode Determines Refactoring Effort

Two main migration routes dominate China's Xinchuang (indigenous innovation) database replacement projects:

Oracle → Dameng (DM8, Oracle compatibility mode) — minimal refactoring due to high syntax compatibility.

MySQL → Kingbase (PostgreSQL kernel) or openGauss (MySQL mode) — largest dialect gap, requiring extensive SQL rewrites.

Critical decision: the target database's compatibility mode is set once at installation or database creation and is extremely difficult to change later. Changing it mid-project effectively forces a full re-migration.

Dameng: COMPATIBLE_MODE = 2 (Oracle) / 4 (MySQL)

Kingbase: databasecompat = oracle / mysql / pg For mixed-source environments, define the strategy upfront; do not switch modes during migration.

02 Five-Phase Migration Process

Phase 1: Assessment & Inventory (Determines Timeline)

Catalog objects: table count/volume, views, stored procedures/functions (graded by line count), triggers, scheduled jobs, driver & ORM dialect features.

Tool scanning: Dameng DTS / Kingbase KDTS built-in assessment, producing a compatibility report.

Deliverable: compatibility ratio → three-tier list: direct migrate / needs refactor / rewrite .

Phase 2: Schema Conversion

Tool converts schema + mandatory manual verification (tools often mishandle defaults, charsets, auto-increment).

Phase 3: Data Migration

Small databases: DTS/KDTS direct migration.

Large databases: export to CSV → bulk load; create primary keys and indexes after data load (an order of magnitude faster).

Post-migration: row-count comparison + sampling verification.

Phase 4: Application Refactoring

Switch to official JDBC driver.

SQL dialect refactoring: MyBatis change databaseId / JPA change dialect.

Bare SQL (raw SQL in code) is the primary hotspot.

Phase 5: Regression Verification & Cutover

Full regression + stress test (indigenous DB default parameters are conservative; tune first, then compare).

Dual-run reconciliation → gradual read cutover → full switch → legacy observation period.

Dual-run + gradual cutover is the safety baseline ; "direct cut" accounts for the most Xinchuang project failures.

03 MySQL → Kingbase: High-Frequency Dialect Refactoring Checklist

PostgreSQL-family vs. MySQL differences are the largest refactoring workload:

-- 1. Auto-increment PK
AUTO_INCREMENT                → GENERATED ALWAYS AS IDENTITY
LAST_INSERT_ID()              → INSERT ... RETURNING id

-- 2. Upsert
ON DUPLICATE KEY UPDATE       → ON CONFLICT (uk) DO UPDATE SET x=EXCLUDED.x

-- 3. Date functions (format tokens completely different)
DATE_FORMAT(d,'%Y-%m-%d')     → TO_CHAR(d,'YYYY-MM-DD')
DATE_ADD(NOW(),INTERVAL 7 DAY) → NOW() + INTERVAL '7 days'
DATEDIFF(a,b)                 → a::date - b::date

-- 4. LIKE default case-sensitive → ILIKE

-- 5. GROUP BY strict mode: non-aggregated columns must appear in GROUP BY → ANY_VALUE

-- 6. Remove backticks; camelCase identifiers all lowercase (PG folds to lowercase)

-- 7. Type mapping: TINYINT→SMALLINT/BOOLEAN, DATETIME→TIMESTAMP

Note: Application-side bare SQL accounts for ~80% of refactoring effort — count SQL statements before migration, then work through the dialect checklist item by item.

04 Oracle → Dameng: High Compatibility, Pitfalls in Behavior

Syntax layer is mostly pass-through: ROWNUM pagination, NVL/DECODE, sequences, PL/SQL stored procedures largely run unchanged. However, behavioral layer has frequent pitfalls:

Empty string: Oracle treats '' as NULL; Dameng requires confirming EMPTY_STR_AS_NULL parameter. Code mixing '' / NULL logic must be fully reviewed.

Case sensitivity: Oracle stores unquoted identifiers in uppercase; Dameng's default case-sensitive switch ( CASE_SENSITIVE) causes script case-matching issues.

Implicit commit: Some DDL/session behaviors differ from Oracle; long-transaction code must be validated.

Pagination mix: ROWNUM works, but new code should standardize on LIMIT/OFFSET.

Stored procedures: Syntax compatible, but internal packages/system functions (e.g., UTL_FILE) replacement extent must be verified individually.

If stored procedures exceed 30% of the Oracle system, Dameng still requires ample refactoring time.

05 Regression Verification Essentials

1. Data reconciliation: row count → key table checksum → sampling field-level comparison (amounts/times mandatory)
2. Functional regression: full API regression + stored procedures/scheduled jobs manually triggered one by one
3. Performance stress test: equal data volume stress core interfaces, PG family watch for missing covering index benefits, update stats when plans deviate (ANALYZE / Dameng SP_TAB_STAT_INIT)
4. Failover drills: primary-standby switchover, backup recovery drills (new DB backup strategy established simultaneously)
5. Observation period metrics: slow query count, connection count, replication lag, compared against legacy baseline

Xinchuang projects most often cut corners on regression verification — but it is exactly this step that determines whether a safe cutover is possible.

06 Lessons Learned

Assess first, migrate later: Compatibility assessment report decides "can we migrate" not "let's try".

Compatibility mode is the anchor: Set once at install/creation; changing mid-way = re-migration.

Tool-converted schema must be manually reviewed; defaults/charsets/auto-increment are distortion hotspots.

Application-side bare SQL accounts for 80% of refactoring — count SQL statements before migration.

Dual-run reconciliation + gradual cutover is the safety baseline; "direct cut" yields most disasters.

Migrate ops system (backup/monitoring/HA) together with data — migrating only data just moves the single point.

07 Quick Reference Summary

Two routes: Oracle→Dameng (behavior pits: empty string/case sensitivity), MySQL→Kingbase (syntax pits: auto-increment/date/upsert/case folding).

Compatibility mode fixed at creation: Dameng COMPATIBLE_MODE, Kingbase databasecompat.

Five phases: assess → schema convert (manual review) → data migrate (data first, index later + reconciliation) → app refactor (bare SQL cataloged) → regression cutover (dual-run + gradual).

Verification four items: data checksum, functional regression, performance stress, backup/recovery drill.

Dialect quick-fix reference: IDENTITY, RETURNING, ON CONFLICT, TO_CHAR, ILIKE.

Closing insight: Xinchuang migration tests not database knowledge but project management rigor — assessment, reconciliation, gradual cutover, ops; each step lowers the probability of disaster.