View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005392 | SymmetricDS | Bug | public | 2022-08-03 09:52 | 2022-10-04 15:15 |
| Reporter | clloyd2 | Assigned To | elong | ||
| Priority | urgent | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.14.0 | ||||
| Target Version | 3.14.2 | Fixed in Version | 3.14.2 | ||
| Summary | 0005392: Unable to write to sym_outgoing_batch when using Postgres | ||||
| Description | I have been using SymmetricDS without issue on Postgres up until 3.13.6. However taking version 3.14.0 I find I am unable to use SymmetricDS any longer due to this error writing an outgoing batch Caused by: java.sql.BatchUpdateException: Batch entry 0 update sym_outgoing_batch set status='OK', load_id=-1, extract_job_flag=0, load_flag=0, error_flag=0, byte_count=0, extract_count=0, sent_count=0, load_count=0, data_row_count=1, reload_row_count=0, data_insert_row_count=0, data_update_row_count=1, data_delete_row_count=0, other_row_count=0, ignore_count=0, router_millis=11, network_millis=0, filter_millis=0, load_millis=0, extract_millis=0, extract_start_time=NULL, transfer_start_time=NULL, load_start_time=NULL, sql_state=NULL, sql_code=0, sql_message=NULL, failed_data_id=0, failed_line_number=0, last_update_hostname='DESKTOP', last_update_time='2022-08-02 11:36:05.024+01', summary='sym_node_host', load_row_count=0, load_insert_row_count=0, load_update_row_count=0, load_delete_row_count=0, fallback_insert_count=0, fallback_update_count=0, ignore_row_count=0, missing_delete_count=0, skip_count=0, extract_row_count=0, extract_insert_row_count=0, extract_update_row_count=0, extract_delete_row_count=0, transform_extract_millis=0, transform_load_millis=0, bulk_loader_flag=0 where batch_id='6' and node_id='-1' was aborted: ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 1069 Call getNextException to see other errors in the batch. at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:559) at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:887) at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910) at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1638) at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:241) at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:241) at org.jumpmind.db.sql.JdbcSqlTransaction.flush(JdbcSqlTransaction.java:160) ... 16 more Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 1069 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ... 23 more | ||||
| Steps To Reproduce | - Install version of 3.14.0 of SymmetricDS using a Postgres backend. - Add a second replication node. - Once registration of the second node is complete, see the failure immediately writing an outgoing batch replicating the config | ||||
| Additional Information | Postgres version 13.6 No sign of this issue on SymmetricDS 3.13.6 | ||||
| Tags | dialect: postgresql | ||||
|
|
Can you look at your node's engine file and make sure your Postgres JDBC URL's contain this parameter: stringtype\=unspecified I was able to replicate the error you're getting by erasing this parameter from my database's JDBC URL. |
|
|
Full stack trace for error: 2022-08-03 14:00:01,795 ERROR [target] [RouterService] [target-job-3] org.jumpmind.db.sql.SqlException: Batch entry 0 update sym_outgoing_batch set status='OK', load_id=-1, extract_job_flag=0, load_flag=0, error_flag=0, byte_count=0, extract_count=0, sent_count=0, load_count=0, data_row_count=1, reload_row_count=0, data_insert_row_count=0, data_update_row_count=1, data_delete_row_count=0, other_row_count=0, ignore_count=0, router_millis=5, network_millis=0, filter_millis=0, load_millis=0, extract_millis=0, extract_start_time=NULL, transfer_start_time=NULL, load_start_time=NULL, sql_state=NULL, sql_code=0, sql_message=NULL, failed_data_id=0, failed_line_number=0, last_update_hostname='LAPTOP-TD398AMS', last_update_time='2022-08-03 14:00:01.79-04', summary='sym_node_host', load_row_count=0, load_insert_row_count=0, load_update_row_count=0, load_delete_row_count=0, fallback_insert_count=0, fallback_update_count=0, conflict_win_count=0, conflict_lose_count=0, ignore_row_count=0, missing_delete_count=0, skip_count=0, extract_row_count=0, extract_insert_row_count=0, extract_update_row_count=0, extract_delete_row_count=0, transform_extract_millis=0, transform_load_millis=0, bulk_loader_flag=0 where batch_id='589' and node_id='-1' was aborted: ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 1118 Call getNextException to see other errors in the batch. at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297) at org.jumpmind.db.sql.JdbcSqlTransaction.flush(JdbcSqlTransaction.java:167) at org.jumpmind.symmetric.service.impl.OutgoingBatchService.updateOutgoingBatches(OutgoingBatchService.java:269) at org.jumpmind.symmetric.service.impl.RouterService.completeBatches(RouterService.java:625) at org.jumpmind.symmetric.service.impl.RouterService.completeBatchesAndCommit(RouterService.java:599) at org.jumpmind.symmetric.service.impl.RouterService.routeDataForChannel(RouterService.java:550) at org.jumpmind.symmetric.service.impl.RouterService.routeDataForEachChannel(RouterService.java:261) at org.jumpmind.symmetric.service.impl.RouterService.routeData(RouterService.java:196) at org.jumpmind.symmetric.job.RouterJob.doJob(RouterJob.java:47) at org.jumpmind.symmetric.job.AbstractJob.invoke(AbstractJob.java:201) at org.jumpmind.symmetric.job.AbstractJob.run(AbstractJob.java:268) at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: java.sql.BatchUpdateException: Batch entry 0 update sym_outgoing_batch set status='OK', load_id=-1, extract_job_flag=0, load_flag=0, error_flag=0, byte_count=0, extract_count=0, sent_count=0, load_count=0, data_row_count=1, reload_row_count=0, data_insert_row_count=0, data_update_row_count=1, data_delete_row_count=0, other_row_count=0, ignore_count=0, router_millis=5, network_millis=0, filter_millis=0, load_millis=0, extract_millis=0, extract_start_time=NULL, transfer_start_time=NULL, load_start_time=NULL, sql_state=NULL, sql_code=0, sql_message=NULL, failed_data_id=0, failed_line_number=0, last_update_hostname='LAPTOP-TD398AMS', last_update_time='2022-08-03 14:00:01.79-04', summary='sym_node_host', load_row_count=0, load_insert_row_count=0, load_update_row_count=0, load_delete_row_count=0, fallback_insert_count=0, fallback_update_count=0, conflict_win_count=0, conflict_lose_count=0, ignore_row_count=0, missing_delete_count=0, skip_count=0, extract_row_count=0, extract_insert_row_count=0, extract_update_row_count=0, extract_delete_row_count=0, transform_extract_millis=0, transform_load_millis=0, bulk_loader_flag=0 where batch_id='589' and node_id='-1' was aborted: ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 1118 Call getNextException to see other errors in the batch. at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165) at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:871) at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910) at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1638) at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:241) at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:241) at org.jumpmind.db.sql.JdbcSqlTransaction.flush(JdbcSqlTransaction.java:160) ... 17 more Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 1118 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:315) at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:868) ... 22 more |
|
|
thanks for the prompt reply. I can confirm that adding "stringtype\=unspecified" into my connection params does indeed resolve the issue. I'm certainly happy to accept that as the solution and modify my url accordingly (although I've not had to use this in previous SymmetricDS versions). Thanks again |
|
|
Fixed in code by adding a missing bind type. That way stringtype=unspecified is not required. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-08-03 09:52 | clloyd2 | New Issue | |
| 2022-08-03 09:52 | clloyd2 | Tag Attached: dialect: postgresql | |
| 2022-08-03 16:30 | cquamme | Note Added: 0002141 | |
| 2022-08-03 16:31 | cquamme | Status | new => feedback |
| 2022-08-03 17:17 | cquamme | Assigned To | => cquamme |
| 2022-08-03 17:17 | cquamme | Status | feedback => assigned |
| 2022-08-03 17:18 | cquamme | Status | assigned => feedback |
| 2022-08-03 18:00 | cquamme | Note Added: 0002142 | |
| 2022-08-04 15:09 | clloyd2 | Note Added: 0002143 | |
| 2022-08-04 15:09 | clloyd2 | Status | feedback => assigned |
| 2022-08-04 15:15 | cquamme | Status | assigned => resolved |
| 2022-08-10 17:23 | jvanmeter | Status | resolved => closed |
| 2022-08-10 17:23 | jvanmeter | Resolution | open => no change required |
| 2022-08-31 14:11 | elong | Assigned To | cquamme => elong |
| 2022-08-31 14:11 | elong | Status | closed => resolved |
| 2022-08-31 14:11 | elong | Resolution | no change required => fixed |
| 2022-08-31 14:11 | elong | Fixed in Version | => 3.14.2 |
| 2022-08-31 14:11 | elong | Target Version | => 3.14.2 |
| 2022-08-31 14:12 | elong | Note Added: 0002176 | |
| 2022-08-31 15:00 | admin | Changeset attached | => SymmetricDS 3.14 de11f15d |
| 2022-10-04 15:15 | admin | Status | resolved => closed |