SymmetricDS not using Lookup Transform on primary key

Question

I'm syncing records between two MySQL databases using SymmetricDS, but need to keep the primary key (fieldname = "id") fields unique. Symmetric doesn't seem to want to obey the "lookup" transform_type - it always seems to "copy" the source table id to the target table id field (as well as to the "masterId" field in the target table).

Both tables have an id field that is AUTO-INCREMENT (but there are different numbers of records in the two tables).

I want the "internal" accounts table record to be copied to the "external" accounts table with the "internal" id copied to the "external" masterId field. Then, I want the "external" accounts id field to be auto-populated by the "external" id AUTO-INCREMENT value. I've defined this sym_transform_table.column_policy to be 'IMPLIED', meaning that all fields are copied unless a specific sym_transform_column transformation is defined. I've defined a transform for the 'id' field (different transformations for I/U/D).

But the result is ALWAYS the "internal" id is copied to the "external" id field as well as the "external" masterId field.

How can I make the "external" id populate using it's own AUTO-INCREMENT value and not the source table id value?

Here is the pertinent transformation definitions:

-- Transform Tables:
INSERT INTO sym_transform_table (transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name, target_table_name, update_first, delete_action, transform_order, column_policy, create_time, last_update_time )
VALUES
('int2ext_accounts_trans', 'crminternal', 'crmexternal', 'EXTRACT', 'x2_accounts', 'x2_accounts', 1, 'DEL_ROW', 1, 'IMPLIED', current_timestamp, current_timestamp),
('ext2int_accounts_trans', 'crmexternal', 'crminternal', 'EXTRACT', 'x2_accounts', 'x2_accounts', 1, 'DEL_ROW', 2, 'IMPLIED', current_timestamp, current_timestamp);

-- Transform Columns:    
INSERT INTO sym_transform_column(transform_id, include_on, target_column_name, source_column_name, pk, transform_type, transform_expression, transform_order, create_time, last_update_time)
VALUES
('int2ext_accounts_trans', 'I', 'id', null, 0, 'lookup', 'SELECT MAX(id) + 1 FROM mwstage_x2ext.x2_accounts;', 1, current_timestamp, current_timestamp),
('int2ext_accounts_trans', 'U', 'id', 'id', 0, 'copy', null, 1, current_timestamp, current_timestamp),
('int2ext_accounts_trans', 'D', 'id', 'id', 0, 'copy', null, 1, current_timestamp, current_timestamp),
('int2ext_accounts_trans', '*', 'masterId', 'id', 0, 'copy', null, 2, current_timestamp, current_timestamp),
('int2ext_accounts_trans', '*', 'nameId', 'nameId', 1, 'copy', null, 3, current_timestamp, current_timestamp),
('ext2int_accounts_trans', 'I', 'id', null, 0, 'remove', null, 1, current_timestamp, current_timestamp),
('ext2int_accounts_trans', 'U', 'id', 'masterId', 0, 'copy', null, 1, current_timestamp, current_timestamp),
('ext2int_accounts_trans', 'D', 'id', 'masterId', 0, 'copy', null, 1, current_timestamp, current_timestamp),
('ext2int_accounts_trans', '*', 'nameId', 'nameId', 1, 'copy', null, 2, current_timestamp, current_timestamp);

SymmetricDS transform table:

CREATE TABLE `sym_transform_table` (
  `transform_id` varchar(50) NOT NULL,
  `source_node_group_id` varchar(50) NOT NULL,
  `target_node_group_id` varchar(50) NOT NULL,
  `transform_point` varchar(10) NOT NULL,
  `source_catalog_name` varchar(255) DEFAULT NULL,
  `source_schema_name` varchar(255) DEFAULT NULL,
  `source_table_name` varchar(255) NOT NULL,
  `target_catalog_name` varchar(255) DEFAULT NULL,
  `target_schema_name` varchar(255) DEFAULT NULL,
  `target_table_name` varchar(255) DEFAULT NULL,
  `update_first` smallint(6) DEFAULT '0',
  `delete_action` varchar(10) NOT NULL,
  `transform_order` int(11) NOT NULL DEFAULT '1',
  `column_policy` varchar(10) NOT NULL DEFAULT 'SPECIFIED',
  `create_time` datetime DEFAULT NULL,
  `last_update_by` varchar(50) DEFAULT NULL,
  `last_update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`transform_id`,`source_node_group_id`,`target_node_group_id`),
  KEY `sym_fk_tt_2_grp_lnk` (`source_node_group_id`,`target_node_group_id`),
  CONSTRAINT `sym_fk_tt_2_grp_lnk` FOREIGN KEY (`source_node_group_id`, `target_node_group_id`) REFERENCES `sym_node_group_link` (`source_node_group_id`, `target_node_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SymmetricDS transform table:

CREATE TABLE `sym_transform_column` (
  `transform_id` varchar(50) NOT NULL,
  `include_on` char(1) NOT NULL DEFAULT ''*'',
  `target_column_name` varchar(128) NOT NULL,
  `source_column_name` varchar(128) DEFAULT NULL,
  `pk` smallint(6) DEFAULT ''0'',
  `transform_type` varchar(50) DEFAULT ''copy'',
  `transform_expression` mediumtext,
  `transform_order` int(11) NOT NULL DEFAULT ''1'',
  `create_time` datetime DEFAULT NULL,
  `last_update_by` varchar(50) DEFAULT NULL,
  `last_update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`transform_id`,`include_on`,`target_column_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

The solution was found on SourceForge. I simplified the column transformation from this:

('int2ext_accounts_trans', 'I', 'id', null, 0, 'identity', null, 1, current_timestamp, current_timestamp),
('int2ext_accounts_trans', 'U', 'id', 'id', 0, 'remove', null, 1, current_timestamp, current_timestamp),
('int2ext_accounts_trans', 'D', 'id', 'id', 0, 'remove', null, 1, current_timestamp, current_timestamp),
('int2ext_accounts_trans', '*', 'masterId', 'id', 0, 'copy', null, 2, current_timestamp, current_timestamp),

to this:

('int2ext_accounts_trans', '*', 'masterId', 'id', 1, 'copy', null, 1, current_timestamp, current_timestamp),

However, the reverse synchronization ('ext2int_accounts_trans') must then be explicitly set for each column. Note the sym_transform_table.column_policy must be set to 'SPECIFIED' because 'IMPLIED' will not ignore the id field using sym_transform_column.transform_type 'remove' nor 'identity' (Then each column needs a record in the sym_transform_column table).