View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004787 | SymmetricDS | Bug | public | 2021-01-26 20:09 | 2023-09-27 17:37 |
| Reporter | elong | Assigned To | |||
| Priority | normal | ||||
| Status | new | Resolution | open | ||
| Product Version | 3.9.0 | ||||
| Summary | 0004787: Mixed up columns and error if load transform column order doesn't match table definition | ||||
| Description | For a load transform, the order of columns specified must match the table definition, otherwise the data is put in the wrong column and can produce an error. This does not affect extract transforms. A user claimed to experience this after using 3.8.32 successfully and upgrading to 3.12.4. Looking at commit history, not much has changed since 3.8.32 for core transform classes. It looks like load transforms have always used the transform column order to generate the transformed data. In TransformWriter, the start() is called with a Table object, which has the correct ordering of columns that will match the SQL. However, in write(), the following lines get the order wrong: // This table will have columns ordered incorrectly, because it is based on the transform column order Table transformedTable = transformedData.buildTargetTable(); // This CSV data is also based on transform column order CsvData csvData = transformedData.buildTargetCsvData(); | ||||
| Steps To Reproduce | CREATE TABLE my_summary ( id BIGINT NOT NULL, mintimestamp TIMESTAMP, maxtimestamp TIMESTAMP, numevents INTEGER, flag BOOLEAN, duration REAL, interface INTEGER, PRIMARY KEY (id) ); Create LOAD transform that is IMPLIED. Create column transforms for update on mintimestamp, maxtimestamp, duration, and numevents in that order. Insert a row into my_summary. Update the row and change the numevents and duration fields. The values for those fields will bind to wrong position in the SQL. | ||||
| Tags | transformation | ||||
|
|
I have been unable to reproduce this issue in the latest version 3.14 running in Eclipse, as well as the latest 3.9 version running in Eclipse. I am currently looking at code changes over time on a couple of classes to see if it "accidentally" was fixed. I am almost ready to close this issue as not reproducible. |
|
|
Here is the current configuration that I am using. config-export-20221212161657.csv (11,663 bytes)
nodeid,server
binary,BASE64
channel,config
batch,-9999
catalog,
schema,
table,sym_job
keys,JOB_NAME
columns,JOB_NAME,JOB_TYPE,REQUIRES_REGISTRATION,JOB_EXPRESSION,DESCRIPTION,DEFAULT_SCHEDULE,DEFAULT_AUTO_START,NODE_GROUP_ID,CREATE_BY,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_job
catalog,
schema,
table,sym_notification
keys,NOTIFICATION_ID
columns,NOTIFICATION_ID,NODE_GROUP_ID,EXTERNAL_ID,SEVERITY_LEVEL,TYPE,EXPRESSION,ENABLED,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_notification
catalog,
schema,
table,sym_monitor
keys,MONITOR_ID
columns,MONITOR_ID,NODE_GROUP_ID,EXTERNAL_ID,TYPE,EXPRESSION,THRESHOLD,RUN_PERIOD,RUN_COUNT,SEVERITY_LEVEL,ENABLED,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_monitor
catalog,
schema,
table,sym_extension
keys,EXTENSION_ID
columns,EXTENSION_ID,EXTENSION_TYPE,INTERFACE_NAME,NODE_GROUP_ID,ENABLED,EXTENSION_ORDER,EXTENSION_TEXT,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_extension
catalog,
schema,
table,sym_file_trigger_router
keys,TRIGGER_ID,ROUTER_ID
columns,TRIGGER_ID,ROUTER_ID,ENABLED,INITIAL_LOAD_ENABLED,TARGET_BASE_DIR,CONFLICT_STRATEGY,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME,DESCRIPTION
sql,delete from sym_file_trigger_router
catalog,
schema,
table,sym_file_trigger
keys,TRIGGER_ID
columns,TRIGGER_ID,CHANNEL_ID,RELOAD_CHANNEL_ID,BASE_DIR,RECURSE,INCLUDES_FILES,EXCLUDES_FILES,SYNC_ON_CREATE,SYNC_ON_MODIFIED,SYNC_ON_DELETE,SYNC_ON_CTL_FILE,DELETE_AFTER_SYNC,BEFORE_COPY_SCRIPT,AFTER_COPY_SCRIPT,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME,DESCRIPTION
sql,delete from sym_file_trigger
catalog,
schema,
table,sym_trigger_router_grouplet
keys,GROUPLET_ID,TRIGGER_ID,ROUTER_ID,APPLIES_WHEN
columns,GROUPLET_ID,TRIGGER_ID,ROUTER_ID,APPLIES_WHEN,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_trigger_router_grouplet
catalog,
schema,
table,sym_grouplet_link
keys,GROUPLET_ID,EXTERNAL_ID
columns,GROUPLET_ID,EXTERNAL_ID,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_grouplet_link
catalog,
schema,
table,sym_grouplet
keys,GROUPLET_ID
columns,GROUPLET_ID,GROUPLET_LINK_POLICY,DESCRIPTION,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_grouplet
catalog,
schema,
table,sym_conflict
keys,CONFLICT_ID
columns,CONFLICT_ID,SOURCE_NODE_GROUP_ID,TARGET_NODE_GROUP_ID,TARGET_CHANNEL_ID,TARGET_CATALOG_NAME,TARGET_SCHEMA_NAME,TARGET_TABLE_NAME,DETECT_TYPE,DETECT_EXPRESSION,RESOLVE_TYPE,PING_BACK,RESOLVE_CHANGES_ONLY,RESOLVE_ROW_ONLY,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_conflict
catalog,
schema,
table,sym_transform_column
keys,TRANSFORM_ID,INCLUDE_ON,TARGET_COLUMN_NAME
columns,TRANSFORM_ID,INCLUDE_ON,TARGET_COLUMN_NAME,SOURCE_COLUMN_NAME,PK,TRANSFORM_TYPE,TRANSFORM_EXPRESSION,TRANSFORM_ORDER,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME,DESCRIPTION
sql,delete from sym_transform_column
catalog,
schema,
table,sym_load_filter
keys,LOAD_FILTER_ID
columns,LOAD_FILTER_ID,LOAD_FILTER_TYPE,SOURCE_NODE_GROUP_ID,TARGET_NODE_GROUP_ID,TARGET_CATALOG_NAME,TARGET_SCHEMA_NAME,TARGET_TABLE_NAME,FILTER_ON_UPDATE,FILTER_ON_INSERT,FILTER_ON_DELETE,BEFORE_WRITE_SCRIPT,AFTER_WRITE_SCRIPT,BATCH_COMPLETE_SCRIPT,BATCH_COMMIT_SCRIPT,BATCH_ROLLBACK_SCRIPT,HANDLE_ERROR_SCRIPT,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME,LOAD_FILTER_ORDER,FAIL_ON_ERROR
sql,delete from sym_load_filter
catalog,
schema,
table,sym_transform_table
keys,TRANSFORM_ID,SOURCE_NODE_GROUP_ID,TARGET_NODE_GROUP_ID
columns,TRANSFORM_ID,SOURCE_NODE_GROUP_ID,TARGET_NODE_GROUP_ID,TRANSFORM_POINT,SOURCE_CATALOG_NAME,SOURCE_SCHEMA_NAME,SOURCE_TABLE_NAME,TARGET_CATALOG_NAME,TARGET_SCHEMA_NAME,TARGET_TABLE_NAME,UPDATE_FIRST,UPDATE_ACTION,DELETE_ACTION,TRANSFORM_ORDER,COLUMN_POLICY,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME,DESCRIPTION
sql,delete from sym_transform_table
catalog,
schema,
table,sym_trigger_router
keys,TRIGGER_ID,ROUTER_ID
columns,TRIGGER_ID,ROUTER_ID,ENABLED,INITIAL_LOAD_ORDER,INITIAL_LOAD_SELECT,INITIAL_LOAD_DELETE_STMT,PING_BACK_ENABLED,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME,DESCRIPTION
sql,delete from sym_trigger_router
catalog,
schema,
table,sym_router
keys,ROUTER_ID
columns,ROUTER_ID,TARGET_CATALOG_NAME,TARGET_SCHEMA_NAME,TARGET_TABLE_NAME,SOURCE_NODE_GROUP_ID,TARGET_NODE_GROUP_ID,ROUTER_TYPE,ROUTER_EXPRESSION,SYNC_ON_UPDATE,SYNC_ON_INSERT,SYNC_ON_DELETE,USE_SOURCE_CATALOG_SCHEMA,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME,DESCRIPTION
sql,delete from sym_router
catalog,
schema,
table,sym_trigger
keys,TRIGGER_ID
columns,TRIGGER_ID,SOURCE_CATALOG_NAME,SOURCE_SCHEMA_NAME,SOURCE_TABLE_NAME,CHANNEL_ID,RELOAD_CHANNEL_ID,SYNC_ON_UPDATE,SYNC_ON_INSERT,SYNC_ON_DELETE,SYNC_ON_INCOMING_BATCH,NAME_FOR_UPDATE_TRIGGER,NAME_FOR_INSERT_TRIGGER,NAME_FOR_DELETE_TRIGGER,SYNC_ON_UPDATE_CONDITION,SYNC_ON_INSERT_CONDITION,SYNC_ON_DELETE_CONDITION,CUSTOM_BEFORE_UPDATE_TEXT,CUSTOM_BEFORE_INSERT_TEXT,CUSTOM_BEFORE_DELETE_TEXT,CUSTOM_ON_UPDATE_TEXT,CUSTOM_ON_INSERT_TEXT,CUSTOM_ON_DELETE_TEXT,EXTERNAL_SELECT,TX_ID_EXPRESSION,CHANNEL_EXPRESSION,EXCLUDED_COLUMN_NAMES,INCLUDED_COLUMN_NAMES,SYNC_KEY_NAMES,USE_STREAM_LOBS,USE_CAPTURE_LOBS,USE_CAPTURE_OLD_DATA,USE_HANDLE_KEY_UPDATES,STREAM_ROW,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME,DESCRIPTION
sql,delete from sym_trigger
catalog,
schema,
table,sym_node_group_channel_wnd
keys,NODE_GROUP_ID,CHANNEL_ID,START_TIME,END_TIME
columns,NODE_GROUP_ID,CHANNEL_ID,START_TIME,END_TIME,ENABLED
sql,delete from sym_node_group_channel_wnd
catalog,
schema,
table,sym_channel
keys,CHANNEL_ID
columns,CHANNEL_ID,PROCESSING_ORDER,MAX_BATCH_SIZE,MAX_BATCH_TO_SEND,MAX_DATA_TO_ROUTE,EXTRACT_PERIOD_MILLIS,ENABLED,USE_OLD_DATA_TO_ROUTE,USE_ROW_DATA_TO_ROUTE,USE_PK_DATA_TO_ROUTE,RELOAD_FLAG,FILE_SYNC_FLAG,CONTAINS_BIG_LOB,BATCH_ALGORITHM,DATA_LOADER_TYPE,DESCRIPTION,QUEUE,MAX_NETWORK_KBPS,DATA_EVENT_ACTION,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_channel
catalog,
schema,
table,sym_parameter
keys,EXTERNAL_ID,NODE_GROUP_ID,PARAM_KEY
columns,EXTERNAL_ID,NODE_GROUP_ID,PARAM_KEY,PARAM_VALUE,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_parameter
catalog,
schema,
table,sym_node_group_link
keys,SOURCE_NODE_GROUP_ID,TARGET_NODE_GROUP_ID
columns,SOURCE_NODE_GROUP_ID,TARGET_NODE_GROUP_ID,DATA_EVENT_ACTION,SYNC_CONFIG_ENABLED,IS_REVERSIBLE,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_node_group_link
catalog,
schema,
table,sym_node_group
keys,NODE_GROUP_ID
columns,NODE_GROUP_ID,DESCRIPTION,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME
sql,delete from sym_node_group
insert,"client","group that represents multiple client nodes",,,
insert,"server","group that represents the registration server and server node",,,
catalog,
schema,
table,sym_node_group_link
insert,"client","server","P","1","0",,,
insert,"server","client","W","1","0",,,
catalog,
schema,
table,sym_parameter
insert,"ALL","ALL","console.license.key","FkkKM8BkOcUk1hiLgAip3NYtqjlvrzRDfUEyzZR+2LjR9LN0g5K0qwCVGcBg4NUAvN7s10xa5HYC
oi/uqH0nYyn53MMxJLdYXxvQ3ZHhadVLKEM60BqRobkc9rNWdWedOxiUxA238OUP8paM2GuI+PEa
Uu2X2mo6Z9mry8oEiyCcUyG4IXVY5+SAUrHeZTV0vR3jr7ctkRHbrEYkEDjJKq0g+I02dLut5a2A
tNkl8kF/DmgZe4QbNd/ZJrJiocKDM6SmqyRARFZvLV8lhpHS449ErTQwd3LaeQcP9UV4qf5BusWr
kUq2WqT3cppAFX2Zbbvokz4KkFAND+6M9DTD3MYuypgo50ktRQw8kOboaa4QoszVHemNQQgd8QK9
CblCn0tF0CLHuVBFHvsl7mScOgUK1m3AXtcRY576P+epMZvxeWDz9keN3TK4T3AqV2wujFw++sB+
wXVzZ1lGz+YPmCmEFgsfw/bI4wX8XW5qrEqQXuGwVEWYuW98c605UAoGNfOM5c82w+7UUql/SAEl
MsXpnd+vVM5XwLhIIipfaEr7OMZ8bOOU5RbebWx8Xb9q1XpHmqeDKZeFHWzGY4yQjQKMcXPFycdy
mDJalULv2pwNNtPcAhSUD4TIsMNcS6v8Cgmlt0rwlEOBXTpK1YDuTEFFy5Ab8uvoIz2L5/87LS5F
3LkNZI3oikckzgFFUJ/uL3p+y7CGYqz81gvKbSB7ptwSfJqnsharZEgqS5t4Zu3lpPeyFQ3juwD+
aOt8SVAMPMGbHf8iGL+GApGUfoPqZm/N6qlg4JPz8+BaCpzVuuvIqPYosbZvuS22vQB0a7+CBllP
aZaH8hSB+y3eoNkLbmcWU41j/mfjUx/E/wRQkIBVR2keDoTG+k/csahNlhi61s8ihBBIhyeRxye+
5LGyac/FFmAPWBkw
","2021-12-15 12:22:47.620","admin","2022-12-09 12:37:58.730"
catalog,
schema,
table,sym_channel
insert,"config","0","2000","100","10000","0","1","1","1","1","0","0","1","default","default",,"default","0.000",,"2021-12-15 12:22:43.612",,"2021-12-15 12:50:34.397"
insert,"default","99999","1000","100","10000","0","1","1","1","1","0","0","0","default","default",,"default","0.000",,"2021-12-15 12:22:43.618",,"2021-12-15 12:50:34.403"
insert,"dynamic","99999","1000","100","10000","0","1","1","1","1","0","0","0","default","default",,"default","0.000",,"2021-12-15 12:22:43.620",,"2021-12-15 12:50:34.404"
insert,"heartbeat","2","100","100","10000","0","1","1","1","1","0","0","0","default","default",,"default","0.000",,"2021-12-15 12:22:43.617",,"2021-12-15 12:50:34.402"
insert,"monitor","2","100","100","10000","0","1","1","1","1","0","0","1","default","default",,"default","0.000",,"2021-12-15 12:22:43.615",,"2021-12-15 12:50:34.402"
insert,"reload","1","10000","100","10000","0","1","1","1","1","1","0","0","default","default",,"default","0.000",,"2021-12-15 12:22:43.614",,"2021-12-15 12:50:34.400"
catalog,
schema,
table,sym_trigger
insert,"my_summary",,,"my_summary","default","reload","1","1","1","0",,,,"1=1","1=1","1=1",,,,,,,,,,,,,"0","0","1","1","0","2022-12-09 12:38:47.659","admin","2022-12-09 12:38:47.659",
insert,"t1",,,"T1","default","reload","1","1","1","0",,,,"1=1","1=1","1=1",,,,,,,,,,,,,"0","0","1","1","0","2021-12-15 12:24:37.335","no_user","2021-12-15 12:24:37.335",
catalog,
schema,
table,sym_router
insert,"client pushes to server",,,,"client","server","default",,"1","1","1","0","2021-12-15 12:22:43.642","console","2021-12-15 12:22:43.642",
insert,"server waits for pull from client",,,,"server","client","default",,"1","1","1","0","2021-12-15 12:22:43.642","console","2021-12-15 12:22:43.642",
catalog,
schema,
table,sym_trigger_router
insert,"my_summary","server waits for pull from client","1","50",,,"0","2022-12-09 12:38:56.787","admin","2022-12-09 12:38:56.787",
insert,"t1","server waits for pull from client","1","50",,,"0","2021-12-15 12:24:37.339",,"2021-12-15 12:24:37.339",
catalog,
schema,
table,sym_transform_table
insert,"my_summary","server","client","LOAD",,,"my_summary",,,"my_summary","0","UPD_ROW","DEL_ROW","0","IMPLIED","2022-12-09 12:39:26.237","admin","2022-12-09 12:40:41.134",
catalog,
schema,
table,sym_load_filter
insert,"test","BSH","server","client","","","t1","1","1","1","import org.jumpmind.symmetric.io.data.DataEventType;
String description;
log.info(\"ID: \" + ID + \", DESCRIPTION: \" + DESCRIPTION + \", data.getDataEventType(): \" + data.getDataEventType());
if (data.getDataEventType() == DataEventType.DELETE) {
description = OLD_DESCRIPTION;
}
else {
description = DESCRIPTION;
}",,,,,,"2021-12-15 12:27:20.283","no_user","2021-12-15 12:39:46.479","0","1"
catalog,
schema,
table,sym_transform_column
insert,"my_summary","U","DURATION","DURATION","0","copy",,"3","2022-12-09 12:40:41.139",,"2022-12-09 12:40:41.139",
insert,"my_summary","U","MAXTIMESTAMP","MAXTIMESTAMP","0","copy",,"2","2022-12-09 12:40:41.138",,"2022-12-09 12:40:41.138",
insert,"my_summary","U","MINTIMESTAMP","MINTIMESTAMP","0","copy",,"1","2022-12-09 12:40:41.137",,"2022-12-09 12:40:41.137",
insert,"my_summary","U","NUMEVENTS","NUMEVENTS","0","copy",,"4","2022-12-09 12:40:41.139",,"2022-12-09 12:40:41.139",
catalog,
schema,
table,sym_monitor
insert,"SystemBatchErrorMonitor","ALL","ALL","batchError","","1","1","1","300","1","2021-12-15 12:22:47.642","system","2021-12-15 12:22:47.642"
insert,"SystemLogMonitor","ALL","ALL","log","","1","1","1","300","1","2021-12-15 12:22:47.642","system","2021-12-15 12:22:47.642"
insert,"SystemOfflineNodeMonitor","ALL","ALL","offlineNodes","","1","1","1","300","1","2021-12-15 12:22:47.642","system","2021-12-15 12:22:47.642"
commit,-9999
|
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2021-01-26 20:09 | elong | New Issue | |
| 2021-01-26 20:09 | elong | Status | new => assigned |
| 2021-01-26 20:09 | elong | Assigned To | => elong |
| 2021-01-26 20:09 | elong | Tag Attached: transformation | |
| 2021-01-26 20:09 | elong | Assigned To | elong => |
| 2021-01-26 20:09 | elong | Status | assigned => new |
| 2021-01-26 20:09 | elong | Target Version | => 3.13.0 |
| 2021-11-02 20:52 | elong | Target Version | 3.13.0 => 3.14.0 |
| 2022-07-25 16:30 | elong | Target Version | 3.14.0 => 3.14.1 |
| 2022-08-24 18:56 | elong | Target Version | 3.14.1 => 3.14.2 |
| 2022-10-03 14:42 | elong | Target Version | 3.14.2 => 3.14.3 |
| 2022-11-07 13:06 | elong | Target Version | 3.14.3 => 3.15.0 |
| 2022-12-12 21:16 | pmarzullo | Note Added: 0002239 | |
| 2022-12-12 21:17 | pmarzullo | Note Added: 0002240 | |
| 2022-12-12 21:17 | pmarzullo | File Added: config-export-20221212161657.csv | |
| 2023-09-25 19:45 | admin | Target Version | 3.15.0 => 3.15.1 |
| 2023-09-27 17:37 | jvanmeter | Fixed in Version | 3.15.1 => |