I have just upgraded to Symmetric 3.6.3 and now get exceptions when syncing tables with geometry columns.
Turning on SQL debugging I see that the SQL statement has ST_GEOMFROMTEXT(?), but the value of the column is binary not WKT. On PostGIS 1.5 this is OK, but on PostGIS 2.1 this throws the exception.
How do I either force Symmetric to send WKT in the sync statements or remove the ST_GEOMFROMTEXT part in the generated SQL? I am unsure what my post version of Symmetric (3.5.1) did, but I didn't have this issue.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The source is PostGIS, but an earlier version (1.5). Syncing in the other direction it works fine as it seems the statement ST_GEOMFROMTEXT(?) works with a binary input - it just returns the same binary value. It is only newer versions of PostGIS that throw the exception.
The log file looks like this (I have removed all of the fields bar the geometry)
2014-07-31 10:10:32,565 INFO [root-000][DefaultDatabaseWriter][qtp1471810322-18] Failed to process a update event in batch 1146.
Failed sql was: update "instance_w6l8t"."asset" set "geom" = ST_GEOMFROMTEXT(?) where "id" = ?
Failed pk data was: "2fce2a32-ee90-4e04-bbc8-da63855d0fc3"
Failed row data was: "2fce2a32-ee90-4e04-bbc8-da63855d0fc3","0101000020E61000009899999999194F40A8F6E978CC2C4140"
Failed old data was: "2fce2a32-ee90-4e04-bbc8-da63855d0fc3","0101000020E61000009899999999194F40A8F6E978CC2C4140"
2014-07-31 10:10:32,566 ERROR [root-000][DataLoaderService][qtp1471810322-18] Failed to load batch 001-1146 because: ERROR: parse error - invalid geometry
Hint: "0101000020E61000009899999999194F" <-- parse error at position 32 within geometry
org.jumpmind.db.sql.SqlException: ERROR: parse error - invalid geometry
Hint: "0101000020E61000009899999999194F" <-- parse error at position 32 within geometry
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288)
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:279)
at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:410)
at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:788)
at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.update(DefaultDatabaseWriter.java:452)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriterConflictResolver.performFallbackToUpdate(AbstractDatabaseWriterConflictResolver.java:235)
at org.jumpmind.symmetric.io.data.writer.DefaultTransformWriterConflictResolver.performFallbackToUpdate(DefaultTransformWriterConflictResolver.java:88)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriterConflictResolver.needsResolved(AbstractDatabaseWriterConflictResolver.java:108)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:174)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:131)
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:233)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:199)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:169)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:115)
at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:802)
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:129)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:431)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPush(DataLoaderService.java:345)
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.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:235)
at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:77)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:606)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:46)
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(Thread.java:744)
Caused by: org.postgresql.util.PSQLException: ERROR: parse error - invalid geometry
Hint: "0101000020E61000009899999999194F" <-- parse error at position 32 within geometry
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:406)
... 47 more
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I fixed the trigger so that it captures the geometry data in wtk along with the SRID. This means that you would have to regenerate the triggers.
Also, the data that you have cached still isn't going to sync because it was captured using the old trigger.
I did find out that if I get rid of the ST_GEOMFROMTEXT() in the data loader it will properly load the hex data. If you really wanted to load the data that you have that currently won't sync, then I could probably give you a temporary patch that removes ST_GEOMFROMTEXT() so your currently failing data will sync.
Thanks for developing the patch so quickly. I can confirm it is now working correctly for me. I can handle the old data that did not sync correctly myself.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have just upgraded to Symmetric 3.6.3 and now get exceptions when syncing tables with geometry columns.
Turning on SQL debugging I see that the SQL statement has ST_GEOMFROMTEXT(?), but the value of the column is binary not WKT. On PostGIS 1.5 this is OK, but on PostGIS 2.1 this throws the exception.
How do I either force Symmetric to send WKT in the sync statements or remove the ST_GEOMFROMTEXT part in the generated SQL? I am unsure what my post version of Symmetric (3.5.1) did, but I didn't have this issue.
Is the source PostGIS as well? Can you post the log file snippet?
The source is PostGIS, but an earlier version (1.5). Syncing in the other direction it works fine as it seems the statement ST_GEOMFROMTEXT(?) works with a binary input - it just returns the same binary value. It is only newer versions of PostGIS that throw the exception.
The log file looks like this (I have removed all of the fields bar the geometry)
2014-07-31 10:10:32,565 INFO [root-000] [DefaultDatabaseWriter] [qtp1471810322-18] Failed to process a update event in batch 1146.
Failed sql was: update "instance_w6l8t"."asset" set "geom" = ST_GEOMFROMTEXT(?) where "id" = ?
Failed pk data was: "2fce2a32-ee90-4e04-bbc8-da63855d0fc3"
Failed row data was: "2fce2a32-ee90-4e04-bbc8-da63855d0fc3","0101000020E61000009899999999194F40A8F6E978CC2C4140"
Failed old data was: "2fce2a32-ee90-4e04-bbc8-da63855d0fc3","0101000020E61000009899999999194F40A8F6E978CC2C4140"
2014-07-31 10:10:32,566 ERROR [root-000] [DataLoaderService] [qtp1471810322-18] Failed to load batch 001-1146 because: ERROR: parse error - invalid geometry
Hint: "0101000020E61000009899999999194F" <-- parse error at position 32 within geometry
org.jumpmind.db.sql.SqlException: ERROR: parse error - invalid geometry
Hint: "0101000020E61000009899999999194F" <-- parse error at position 32 within geometry
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288)
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:279)
at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:410)
at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:788)
at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.update(DefaultDatabaseWriter.java:452)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriterConflictResolver.performFallbackToUpdate(AbstractDatabaseWriterConflictResolver.java:235)
at org.jumpmind.symmetric.io.data.writer.DefaultTransformWriterConflictResolver.performFallbackToUpdate(DefaultTransformWriterConflictResolver.java:88)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriterConflictResolver.needsResolved(AbstractDatabaseWriterConflictResolver.java:108)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:174)
at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:131)
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:233)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:199)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:169)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:115)
at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:802)
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:129)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:431)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPush(DataLoaderService.java:345)
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.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:235)
at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:77)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:606)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:46)
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(Thread.java:744)
Caused by: org.postgresql.util.PSQLException: ERROR: parse error - invalid geometry
Hint: "0101000020E61000009899999999194F" <-- parse error at position 32 within geometry
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:406)
... 47 more
Any work-arounds? I have several days worth of syncs queued up that I need to figure out how to clear.
I just reproduced this. I'll see if I can fix for the next patch release which should be early next week.
I fixed the trigger so that it captures the geometry data in wtk along with the SRID. This means that you would have to regenerate the triggers.
Also, the data that you have cached still isn't going to sync because it was captured using the old trigger.
I did find out that if I get rid of the ST_GEOMFROMTEXT() in the data loader it will properly load the hex data. If you really wanted to load the data that you have that currently won't sync, then I could probably give you a temporary patch that removes ST_GEOMFROMTEXT() so your currently failing data will sync.
http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric-assemble/3.6.5-SNAPSHOT/symmetric-assemble-3.6.5-20140809.162656-6-server.zip
Thanks for developing the patch so quickly. I can confirm it is now working correctly for me. I can handle the old data that did not sync correctly myself.