View Issue Details

IDProjectCategoryView StatusLast Update
0005392SymmetricDSBugpublic2022-10-04 15:15
Reporterclloyd2 Assigned Toelong  
Priorityurgent 
Status closedResolutionfixed 
Product Version3.14.0 
Target Version3.14.2Fixed in Version3.14.2 
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

elong

2022-08-31 14:12

developer   ~0002176

Fixed in code by adding a missing bind type. That way stringtype=unspecified is not required.

Related Changesets

SymmetricDS: 3.14 de11f15d

2022-08-31 14:10:47

admin

Details Diff
0005392: Unable to write to sym_outgoing_batch when using Postgres Affected Issues
0005392
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/OutgoingBatchService.java Diff File

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
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