View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0001711SymmetricDSBugpublic2014-05-08 15:182023-09-13 17:25
ReporterColin Assigned To 
Priorityurgent 
Status closedResolutionopen 
Product Version3.5.21 
Summary0001711: Cannot synchronise the Postgres timestamptz data type to Oracle
DescriptionWhen I synchronise data from a "timestamp with time zone" Postgres column to an Oracle datetime column (date, timestamp, or timestamp with time zone), I receive the following error:

==============================================================================
2014-05-05 11:55:10,200 ERROR [dgdn-000] [DataLoaderService] [ajp-bio-127.0.0.1-8009-exec-7] Failed to load batch 001-1107 because: org.jumpmind.exception.ParseException: Unable to parse the date: 2014-05-05 11:54:42.390936 -04:00
java.lang.RuntimeException: org.jumpmind.exception.ParseException: Unable to parse the date: 2014-05-05 11:54:42.390936 -04:00
at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValues(AbstractDatabasePlatform.java:358)
at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValues(AbstractDatabasePlatform.java:339)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.execute(DatabaseWriter.java:1129)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.insert(DatabaseWriter.java:491)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.write(DatabaseWriter.java:190)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.write(DatabaseWriter.java:167)
at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:65)
at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:217)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:164)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:114)
at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:779)
at org.jumpmind.symmetric.io.data.writer.StagingDataWriter.notifyEndBatch(StagingDataWriter.java:75)
at org.jumpmind.symmetric.io.data.writer.AbstractProtocolDataWriter.end(AbstractProtocolDataWriter.java:220)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:124)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:407)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPush(DataLoaderService.java:325)
at org.jumpmind.symmetric.web.PushUriHandler.push(PushUriHandler.java:79)
at org.jumpmind.symmetric.web.PushUriHandler.handle(PushUriHandler.java:66)
at org.jumpmind.symmetric.web.SymmetricServlet.service(SymmetricServlet.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
Caused by: org.jumpmind.exception.ParseException: Unable to parse the date: 2014-05-05 11:54:42.390936 -04:00
at org.jumpmind.util.FormatUtils.parseDate(FormatUtils.java:289)
at org.jumpmind.util.FormatUtils.parseDate(FormatUtils.java:256)
at org.jumpmind.db.platform.AbstractDatabasePlatform.parseDate(AbstractDatabasePlatform.java:536)
at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValue(AbstractDatabasePlatform.java:378)
at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValues(AbstractDatabasePlatform.java:351)
... 37 more
2014-05-05 11:55:10,207 ERROR [dgdn-000] [DataLoaderService] [ajp-bio-127.0.0.1-8009-exec-7] Failed while parsing batch
java.lang.RuntimeException: org.jumpmind.exception.ParseException: Unable to parse the date: 2014-05-05 11:54:42.390936 -04:00
at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValues(AbstractDatabasePlatform.java:358)
at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValues(AbstractDatabasePlatform.java:339)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.execute(DatabaseWriter.java:1129)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.insert(DatabaseWriter.java:491)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.write(DatabaseWriter.java:190)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.write(DatabaseWriter.java:167)
at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:65)
at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:217)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:164)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:114)
at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:779)
at org.jumpmind.symmetric.io.data.writer.StagingDataWriter.notifyEndBatch(StagingDataWriter.java:75)
at org.jumpmind.symmetric.io.data.writer.AbstractProtocolDataWriter.end(AbstractProtocolDataWriter.java:220)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:124)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:407)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPush(DataLoaderService.java:325)
at org.jumpmind.symmetric.web.PushUriHandler.push(PushUriHandler.java:79)
at org.jumpmind.symmetric.web.PushUriHandler.handle(PushUriHandler.java:66)
at org.jumpmind.symmetric.web.SymmetricServlet.service(SymmetricServlet.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
Caused by: org.jumpmind.exception.ParseException: Unable to parse the date: 2014-05-05 11:54:42.390936 -04:00
at org.jumpmind.util.FormatUtils.parseDate(FormatUtils.java:289)
at org.jumpmind.util.FormatUtils.parseDate(FormatUtils.java:256)
at org.jumpmind.db.platform.AbstractDatabasePlatform.parseDate(AbstractDatabasePlatform.java:536)
at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValue(AbstractDatabasePlatform.java:378)
at org.jumpmind.db.platform.AbstractDatabasePlatform.getObjectValues(AbstractDatabasePlatform.java:351)
... 37 more
==============================================================================
Steps To Reproduce-- On Postgres:
create table testdate ( testcol timestamptz );

-- On Oracle:
create table testdate ( testcol date );

-- Create SymmetricDS config:
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, contains_big_lob, description, create_time, last_update_time)
values('master_channel', 1, 100000, 1, 1, 'Channel for data', current_timestamp, current_timestamp);

insert into sym_router
(router_id, source_node_group_id, target_node_group_id, router_type, create_time, last_update_time)
values('local_to_central', 'site', 'dgdn', 'default', current_timestamp, current_timestamp);

insert into sym_trigger
(trigger_id, source_table_name, channel_id, last_update_time, create_time)
values('testdate', 'testdate', 'master_channel', current_timestamp, current_timestamp);

insert into sym_trigger_router
(trigger_id, router_id, initial_load_order, last_update_time, create_time)
values('testdate', 'local_to_central', 100, current_timestamp, current_timestamp);

-- On Postgres:
insert into testdate values (current_timestamp);

-- Upon receiving the batch, the root node (dgdn) will throw the error noted in the description of this issue.
Additional InformationI created a community help forum thread originally, at https://sourceforge.net/p/symmetricds/discussion/739236/thread/4129b60b/. Since then, I realised my initial testing had improperly configured the trigger, and that's why it worked at that time. Additionally, I find this to likely be an issue in the code, due to the fact that the Oracle "timestamp with time zone" data type fails as well as the "date" data type, the latter of which I tried originally.

For context, my setup consists of four servers, all running on SLES11 SP3 and using Apache SSL to communicate between the nodes:
- One root node consisting of a SymmetricDS 3.5.21 war file running in Tomcat, pointing to a database server running Oracle 11g.
- One client node consisting of a SymmetricDS 3.5.21 war file running in Tomcat, pointing to a database server running Postgres 9.3.
Tagsdata type, dialect: postgresql

Activities

emiller

2022-08-09 18:55

developer   ~0002148

I tested this using SymmetricDS 3.14.0, Postgres 9.3.25, and Oracle 11. SymmetricDS successfully converted Postgres timestamp with time zone columns to Oracle date, timestamp, and timestamp with time zone columns. It's likely that this issue has already been resolved since 3.5.21.

Can you upgrade to the latest version of SymmetricDS and see if you still encounter this issue?

Issue History

Date Modified Username Field Change
2014-05-08 15:18 Colin New Issue
2019-04-22 17:44 elong Tag Attached: data type
2019-04-22 17:44 elong Tag Attached: postgresql
2019-04-23 20:50 admin Tag Renamed postgresql => dialect: postgresql
2022-08-09 18:55 emiller Note Added: 0002148
2022-08-09 18:55 emiller Status new => feedback
2023-09-13 17:25 emiller Status feedback => closed