View Issue Details

IDProjectCategoryView StatusLast Update
0001654SymmetricDSBugpublic2014-06-16 20:27
ReportercronjefourieAssigned Toelong 
PriorityhighSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version3.5.17 
Target Version3.6.0Fixed in Version3.6.0 
Summary0001654: Restarting server crashes due to primary key violation
DescriptionPrimary key violation detection doesn't seem to work properly for PostgreSQL. After restarting on Unix a primary key violation on sym_lock and/or sym_sequence causes the start to fail.
Steps To ReproduceInstall symmetric ds on Ubuntu 12.04 and Postgres 9.1
Configure as a client and run as standalone
Config sym_service.initd to start service
Start service allow all tables etc to be created
Restart service BOOM
TagsNo tags attached.

Activities

cronjefourie

2014-03-27 10:35

reporter  

issue-1654.patch (3,718 bytes)
Index: symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java
===================================================================
--- symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java	(revision 8112)
+++ symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java	(working copy)
@@ -34,7 +34,10 @@
             DatabaseInfo databaseInfo) {
         super(dataSource, settings, lobHandler, databaseInfo);
         this.requiresAutoCommitFalseToSetFetchSize = true;
-        primaryKeyViolationSqlStates = new String[] { "23505" };
+        primaryKeyViolationSqlStates = new String[] { "23000", "23505" };
+        primaryKeyViolationMessageParts = new String[] {"duplicate key value violates"};
+        
+        foreignKeyViolationMessageParts = new String[] {"violates foreign key constraint"};
     }
 
     @Override
Index: symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java
===================================================================
--- symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java	(revision 8112)
+++ symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java	(working copy)
@@ -77,10 +77,14 @@
     protected int[] primaryKeyViolationCodes;
 
     protected String[] primaryKeyViolationSqlStates;
+    
+    protected String[] primaryKeyViolationMessageParts;
 
     protected int[] foreignKeyViolationCodes;
 
     protected String[] foreignKeyViolationSqlStates;
+    
+    protected String[] foreignKeyViolationMessageParts;
 
     protected int isolationLevel;
 
@@ -784,7 +788,7 @@
             if (sqlEx != null) {
                 if (primaryKeyViolationCodes != null) {
                     int errorCode = sqlEx.getErrorCode();
-                    for (int primaryKeyViolationCode : primaryKeyViolationCodes) {
+                    for (int primaryKeyViolationCode : primaryKeyViolationCodes) {                    	
                         if (primaryKeyViolationCode == errorCode) {
                             primaryKeyViolation = true;
                             break;
@@ -804,6 +808,19 @@
                         }
                     }
                 }
+                
+                if (primaryKeyViolationMessageParts != null) {
+                	String sqlMessage = sqlEx.getMessage();
+                	if (sqlMessage != null) {
+                		sqlMessage = sqlMessage.toLowerCase();
+                		for (String primaryKeyViolationMessagePart : primaryKeyViolationMessageParts) {
+                			if (primaryKeyViolationMessagePart != null && sqlMessage.contains(primaryKeyViolationMessagePart.toLowerCase())) {
+                				primaryKeyViolation = true;
+                				break;
+                			}
+                		}
+                	}
+                }
             }
         }
 
@@ -837,6 +854,19 @@
                         }
                     }
                 }
+                
+                if (foreignKeyViolationMessageParts != null) {
+                	String sqlMessage = sqlEx.getMessage();
+                	if (sqlMessage != null) {
+                		sqlMessage = sqlMessage.toLowerCase();
+                		for (String foreignKeyViolationMessagePart : foreignKeyViolationMessageParts) {
+                			if (foreignKeyViolationMessagePart != null && sqlMessage.contains(foreignKeyViolationMessagePart.toLowerCase())) {
+                				foreignKeyViolation = true;
+                				break;
+                			}
+                		}
+                	}
+                }                
             }
         }
 
issue-1654.patch (3,718 bytes)

cronjefourie

2014-03-27 10:37

reporter   ~0000501

I created a patch and added it issue-1654.patch. This will add primary/foreign key violation message part strings to jdbcTemplate and validate the returned sqlexception against these values to check for primary/foreign key violation.

Related Changesets

SymmetricDS: master db86207a

2014-03-27 13:19:56

erilong

Details Diff
0001654: Restarting server crashes due to primary key violation
0001654
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java Diff File

Issue History

Date Modified Username Field Change
2014-03-27 10:34 cronjefourie New Issue
2014-03-27 10:35 cronjefourie File Added: issue-1654.patch
2014-03-27 10:37 cronjefourie Note Added: 0000501
2014-03-27 14:00 Changeset attached => SymmetricDS trunk r8115
2014-03-27 15:21 elong Assigned To => elong
2014-03-27 15:21 elong Status new => assigned
2014-03-27 15:21 elong Fixed in Version => 3.6.0
2014-03-27 15:21 elong Target Version => 3.6.0
2014-03-27 15:22 elong Status assigned => resolved
2014-03-27 15:22 elong Resolution open => fixed
2014-06-16 20:27 chenson Status resolved => closed
2015-07-30 21:49 Changeset attached => SymmetricDS master db86207a