View Issue Details

IDProjectCategoryView StatusLast Update
0005392SymmetricDSBugpublic2022-08-04 15:15
Reporterclloyd2 Assigned Tocquamme  
Priorityurgent 
Status resolvedResolutionopen 
Product Version3.14.0 
Summary0005392: Unable to write to sym_outgoing_batch when using Postgres
DescriptionI 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 InformationPostgres version 13.6
No sign of this issue on SymmetricDS 3.13.6
Tagsdialect: postgresql

Activities

cquamme

2022-08-03 16:30

developer   ~0002141

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.

cquamme

2022-08-03 18:00

developer   ~0002142

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

clloyd2

2022-08-04 15:09

reporter   ~0002143

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

Issue History

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