View Issue Details

IDProjectCategoryView StatusLast Update
0002462SymmetricDSBugpublic2016-01-03 21:44
Reportermbsilva Assigned Tochenson  
Priorityhigh 
Status closedResolutionfixed 
Product Version3.7.27 
Target Version3.7.28Fixed in Version3.7.28 
Summary0002462: Replication does not restart after network reconnection between servers
DescriptionWhen we lose connection with any server due to some instability in our internet, in some cases replication does not restart, because of errors in insert in the table sym_incoming_batch. The following log is generated:

01-retaguarda-pull-1 Failed to record status of batch concentrador-1241548
org.jumpmind.db.sql.SqlException: ERROR: duplicate key value violates unique constraint "sym_incoming_batch_pkey"
        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.executeCallback(JdbcSqlTransaction.java:338)
        at org.jumpmind.db.sql.JdbcSqlTransaction.prepareAndExecute(JdbcSqlTransaction.java:285)
        at org.jumpmind.symmetric.service.impl.IncomingBatchService.insertIncomingBatch(IncomingBatchService.java:246)
        at org.jumpmind.symmetric.service.impl.IncomingBatchService.insertIncomingBatch(IncomingBatchService.java:271)
        at org.jumpmind.symmetric.service.impl.IncomingBatchService.acquireIncomingBatch(IncomingBatchService.java:196)
        at org.jumpmind.symmetric.service.impl.DataLoaderService$ManageIncomingBatchListener.beforeBatchStarted(DataLoaderService.java:890)
        at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:94)
        at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:850)
        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:130)
        at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:458)
        at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPull(DataLoaderService.java:268)
        at org.jumpmind.symmetric.service.impl.PullService.execute(PullService.java:132)
        at org.jumpmind.symmetric.service.impl.NodeCommunicationService$2.run(NodeCommunicationService.java:394)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERRO: duplicar valor da chave viola a restrição de unicidade "sym_incoming_batch_pkey"
        at org.postgresql.core.v2.QueryExecutorImpl.receiveErrorMessage(QueryExecutorImpl.java:574)
        at org.postgresql.core.v2.QueryExecutorImpl.processResults(QueryExecutorImpl.java:497)
        at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:364)
        at org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:258)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:616)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:466)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:459)
        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$4.execute(JdbcSqlTransaction.java:293)
        at org.jumpmind.db.sql.JdbcSqlTransaction$4.execute(JdbcSqlTransaction.java:285)
        at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:336)
        ... 17 more
TagsNo tags attached.

Activities

chenson

2015-12-04 22:35

administrator   ~0000740

Last edited: 2015-12-04 22:36

View 2 revisions

Is this reproduceable? I wonder if it happens with 3.7.25? We upgraded the postrges jdbc jar file after that version. If you can't back up to 3.7.25, can you try the jdbc jar packaged with it?

chenson

2015-12-05 16:56

administrator   ~0000741

Looking at the code for postgres, it detects unique key violations by looking at the message for the text "duplicate key value violates". Because your exception message is in what looks like Portuguese, SymmetricDS is not behaving correctly.

I'm guessing the message is in Portuguese because the locale of the database is set to Portuguese.

We probably need to put infrastructure in place to have a resource bundle for exception messages that the code is dependent on.

chenson

2015-12-05 17:50

administrator   ~0000742

It looks like you can set lc_messages for a specific user using "alter role set". That might be the solution for you.

mbsilva

2015-12-07 22:14

reporter   ~0000743

This happens since migrated to version 3.7. The message in English I put manually(both were in Portuguese), do not worry about it.

chenson

2015-12-08 01:10

administrator   ~0000744

I added support for Portuguese. Not sure if this is the correct long term solution as each user that comes along that has the database set to a different locale would have this.

mbsilva

2015-12-09 00:15

reporter   ~0000745

The problem is not the language of the message, here everything is ok. The problem is the duplicate key error in the table sym_incoming_batch when the Internet is unstable.

chenson

2015-12-09 00:39

administrator   ~0000746

No. It really is a language issue. Most databases/drivers have error codes that we key off of and throw a UniqueKeyException. The code to "acquire" an incoming batch attempts an insert (99% of the time it succeeds) and then catches a UniqueKeyException and updates if the batch has already been loaded. Postgres checks for a unique key exception by looking at the error message. If it see the text "duplicate key value violates unique" it throws the UniqueKeyException. Your case is failing because the message is in another language and doesn't match.

Related Changesets

SymmetricDS: 3.7 bf5f5d18

2015-12-07 20:08:16

chenson

Details Diff
0002462: Replication does not restart after network reconnection between
servers
Affected Issues
0002462
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java Diff File

Issue History

Date Modified Username Field Change
2015-12-04 22:31 mbsilva New Issue
2015-12-04 22:35 chenson Note Added: 0000740
2015-12-04 22:36 chenson Note Edited: 0000740 View Revisions
2015-12-05 16:56 chenson Note Added: 0000741
2015-12-05 17:50 chenson Note Added: 0000742
2015-12-07 22:14 mbsilva Note Added: 0000743
2015-12-08 01:10 chenson Note Added: 0000744
2015-12-08 01:10 chenson Status new => resolved
2015-12-08 01:10 chenson Resolution open => fixed
2015-12-08 01:10 chenson Assigned To => chenson
2015-12-08 01:10 chenson Target Version => 3.7.28
2015-12-08 01:10 chenson Fixed in Version => 3.7.28
2015-12-08 02:00 chenson Changeset attached => SymmetricDS 3.7 bf5f5d18
2015-12-09 00:15 mbsilva Note Added: 0000745
2015-12-09 00:15 mbsilva Status resolved => feedback
2015-12-09 00:15 mbsilva Resolution fixed => reopened
2015-12-09 00:39 chenson Note Added: 0000746
2015-12-09 00:39 chenson Status feedback => resolved
2015-12-09 00:39 chenson Resolution reopened => fixed
2016-01-03 21:44 chenson Status resolved => closed