Unable to parse ISO-8601 datetime strings

Help
2014-07-08
2014-07-09
  • Hello,

    My SymmetricDS 3.5.22 installation cannot parse ISO-8601 strings. It chokes up with the following error:

    2014-07-07 22:36:57,875 WARN [main] [IncomingBatchService] [qtp27587325-26] Retrying batch post-korpus30-28691
    2014-07-07 22:36:57,921 ERROR [main] [MsSqlDatabasePlatform] [qtp27587325-26] Could not convert a value of 2014-07-07T13:44:36.734 for column event_datetime of type TIMESTAMP
    2014-07-07 22:36:57,921 ERROR [main] [MsSqlDatabasePlatform] [qtp27587325-26] Unable to parse the date: 2014-07-07T13:44:36.734
    org.jumpmind.exception.ParseException: Unable to parse the date: 2014-07-07T13:44:36.734
    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)
    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:820)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:565)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:479)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:524)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:227)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1031)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:406)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:186)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:965)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:111)
    at org.eclipse.jetty.server.Server.handle(Server.java:348)
    at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:452)
    at org.eclipse.jetty.server.BlockingHttpConnection.handleRequest(BlockingHttpConnection.java:47)
    at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:894)
    at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:948)
    at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:851)
    at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:230)
    at org.eclipse.jetty.server.BlockingHttpConnection.handle(BlockingHttpConnection.java:66)
    at org.eclipse.jetty.server.bio.SocketConnector$ConnectorEndPoint.run(SocketConnector.java:254)
    at org.eclipse.jetty.server.ssl.SslSocketConnector$SslConnectorEndPoint.run(SslSocketConnector.java:665)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:603)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:538)
    at java.lang.Thread.run(Unknown Source)

     
  • Chris Henson
    Chris Henson
    2014-07-08

    We don't currently support that format for the date. You would need to add a transform to put the timestamp in yyyy-MM-dd hh:mm:ss.SSS format.

     
  • Could you please be more specific on transformations? Can I use 'lookup' transformation, or the datetime value parsing happens before passing it to 'lookup'?
    Or should I use 'lookup' transformation on Extract stage, not on Load stage, in order to transform datetime string where it's originated?

     
  • Chris Henson
    Chris Henson
    2014-07-09

    Use a bsh transform. The expression would be something like this:
    java.util.Date date = new java.text.SimpleDateFormat("yyyy-MM-ddThh:mm:ss.S").parse(currentValue);
    return new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S").format(date);

    On a side note, I think you are correct in that there is a bug. The bug is in the sqlite trigger. For datetime columns it should always format the date in the format symmetric expects. I am checking in a fix for 3.6.2.