Migrating Sharded Mycat Data to ActionDB Using ActionOMS with Primary Key Deduplication

A client needed to migrate data that was sharded by Mycat middleware to ActionDB using the ActionOMS migration tool, while ensuring that duplicate primary key values on the source side would not be directly transferred but regenerated uniquely on the target side.

The article first reviews key sharding concepts such as logical schemas, logical tables, sharded tables, non‑sharded tables, ER tables, and global tables, providing the necessary background for the migration design.

Three migration scenarios are discussed: (1) global tables where a single shard’s data is sufficient, (2) sharded tables without primary‑key duplication, and (3) sharded tables with duplicate primary keys. For the third case, three possible schema‑modification strategies are presented, and the approach of modifying both source and target tables is chosen.

Source schema modification : the column c2 is set to NOT NULL and a unique key is added to enable data validation. Example SQL: ALTER TABLE test02 MODIFY c2 varchar(36) COLLATE utf8mb4_bin NOT NULL; ALTER TABLE test02 ADD UNIQUE KEY (c2); Verification is performed with show create table test02; .

Target schema modification : a new column c1_new replaces the original primary key, c2 is defined as a unique key and used for partitioning, and the table is created with four partitions. After data validation, a composite primary key (c1_new, c2) is added. Example SQL: CREATE TABLE `test02` ( `c1_new` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(36) NOT NULL, `c3` datetime DEFAULT NULL, UNIQUE KEY `c2` (`c2`) ) PARTITION BY KEY(c2) PARTITIONS 4; ALTER TABLE test02 ADD PRIMARY KEY (c1_new, c2);

ActionOMS configuration : the migration tool is set to ignore the original c1 column ( sink.ignoreRedunantColumnsReplicate=true ) for both full and incremental sync. Validation components are configured to check fields c2 and c3 while ignoring c1 . Screenshots of the UI are omitted for brevity.

Post‑migration steps : after successful validation, the target ActionDB adds the new primary key, the source stops writes, and the temporary column c1_new is renamed back to c1 .

The article also provides brief introductions to ActionDB—a distributed database built on the OceanBase kernel with MySQL compatibility—and ActionOMS, a customized version of OMS with full source code access for debugging and extensions.