| Description | "We have a test group in Asia and they have found that SymmetricDS does not handle a time zone that has a positive offset (e.g. +07:00 .vs. -05:00) correctly. The time zone is missing the ""+"" sign for positive time zones. For example, in a Postgresql DB, These 2 updates work: mcpdb=# update om_group_hist set time_stamp = '2012-02-24 00:15:01-06:00' where oid = 34090127; UPDATE 1 mcpdb=# update om_group_hist set time_stamp = '2012-02-24 00:15:01+06:00' where oid = 34090127; UPDATE 1 However this update fails (no ""+"" or ""-"" sign for the timezone): mcpdb=# update om_group_hist set time_stamp = '2012-02-24 00:15:01 06:00' where oid = 34090127; ERROR: invalid input syntax for type timestamp with time zone: ""2012-02-24 00:15:01 06:00"" LINE 1: update om_group_hist set time_stamp = '2012-02-24 00:15:01 0... (Extracted from the SymmetricDS log file) =========================================== 2012-03-15 12:25:22,733 ERROR [SymmetricDS] [SymmetricDS-job-20] Failed to load batch 00000-9 because: PreparedStatementCallback; SQL [update mcpdbschema.""admin_journal"" set ""last_updated"" = cast(? as timestamp with time zone), ""lastlogindate"" = ? where ""oid"" = ?]; ERROR: invalid input syntax for type timestamp with time zone: ""2012-03-15 10:26:18.277327 07:00""; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for type timestamp with time zone: ""2012-03-15 10:26:18.277327 07:00"" org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [update mcpdbschema.""admin_journal"" set ""last_updated"" = cast(? as timestamp with time zone), ""lastlogindate"" = ? where ""oid"" = ?]; ERROR: invalid input syntax for type timestamp with time zone: ""2012-03-15 10:26:18.277327 07:00""; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for type timestamp with time zone: ""2012-03-15 10:26:18.277327 07:00"" at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:867) at org.jumpmind.symmetric.load.TableTemplate.execute(TableTemplate.java:285) at org.jumpmind.symmetric.load.TableTemplate.update(TableTemplate.java:154) at org.jumpmind.symmetric.load.csv.CsvLoader.update(CsvLoader.java:455) at org.jumpmind.symmetric.load.csv.CsvLoader.load(CsvLoader.java:196) at org.jumpmind.symmetric.service.impl.DataLoaderService.loadBatch(DataLoaderService.java:479) at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataAndReturnBatches(DataLoaderService.java:258) at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPull(DataLoaderService.java:150) at org.jumpmind.symmetric.service.impl.PullService.pullData(PullService.java:71) at org.jumpmind.symmetric.job.PullJob.doJob(PullJob.java:39) at org.jumpmind.symmetric.job.AbstractJob.invoke(AbstractJob.java:172) at org.jumpmind.symmetric.job.AbstractJob.run(AbstractJob.java:209) at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:51) at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.util.concurrent.FutureTask$Sync.innerRunAndReset(Unknown Source) at java.util.concurrent.FutureTask.runAndReset(Unknown Source) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(Unknown Source) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(Unknown Source) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type timestamp with time zone: ""2012-03-15 10:26:18.277327 07:00"" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:321) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:817) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586) ... 23 more The underlying issue is with what the extract function is returning, which is being used in the SymmetricDS triggers. When the time zone is positive, the extract function does NOT return the ""+"" sign. Note that I believe the code below will also have an issue if the time zone is -11:00 (since only 2 characters are being captured). (From one of the triggers that captures changes) ====================================== case when new.""last_updated"" is null then '' else '""' || to_char(new.""last_updated"", 'YYYY-MM-DD HH24:MI:SS.US ')||lpad(cast(extract(timezone_hour from new.""last_updated"") as varchar),2,'0')||':'||lpad(cast(extract(timezone_minute from new.""last_updated"") as varchar), 2, '0') || '""' end||','||+ From a DB with a negative Time zone ==================================== mcpdb=# select oid, last_updated from admin_journal where oid = 222; oid | last_updated -----+------------------------------- 222 | 2012-03-15 08:43:52.693984-05 (1 row) mcpdb=# select lpad(cast(extract(timezone_hour from last_updated) as varchar),2,'0') from admin_journal where oid = 222; lpad ------ -5 (1 row) From a DB with a positive Time zone ==================================== mcpdb=# select oid, last_updated from admin_journal where oid = 21144408; oid | last_updated ----------+------------------------------- 21144408 | 2011-10-17 15:59:06.196874+07 (1 row) mcpdb=# select lpad(cast(extract(timezone_hour from last_updated) as varchar),2,'0') from admin_journal where oid = 21144408; lpad ------ 07 (1 row)" |
|---|