Jira REST API throws sql server exception

[Jaswanth P]

Hello.

I am getting the following error when calling the JIRA search API ('/rest/api/latest/search').  If I rerun the script, it eventually works but the lack of reliability greatly hinders any sort of automation. 

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'jiradbschema.cwd_membership' with unique index 'uk_mem_parent_child_type'. The duplicate key value is (18477, 82910, GROUP_USER).

                at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) [mssql-jdbc-6.2.1.jre8.jar:?]

                at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535) [mssql-jdbc-6.2.1.jre8.jar:?]

                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467) [mssql-jdbc-6.2.1.jre8.jar:?]

                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409) [mssql-jdbc-6.2.1.jre8.jar:?]

                at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) [mssql-jdbc-6.2.1.jre8.jar:?]

                at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478) [mssql-jdbc-6.2.1.jre8.jar:?]

                at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219) [mssql-jdbc-6.2.1.jre8.jar:?]

                at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199) [mssql-jdbc-6.2.1.jre8.jar:?]

                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:356) [mssql-jdbc-6.2.1.jre8.jar:?]

                at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) [commons-dbcp2-2.1.jar:2.1]

                at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) [commons-dbcp2-2.1.jar:2.1]

                at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:562) [entityengine-1.3.10.jar:?]

                ... 149 more

[Earl McCutcheon · Accepted solution]

Hello Jaswanth,

The speciffic error you are receiving "Cannot insert duplicate key row in object 'jiradbschema.cwd_membership' with unique index 'uk_mem_parent_child_type'. " looks like the behavior described in the following KB article where an LDAP sync failure is occuring usually related to renaming a group within the LDAP Directory causing a sync failure to occur.  Then the API Search endpoint you are hitting is more than likely issuing a permissions check and erring on the group verification for the group that failed to sync properly:

• "LDAP Sync Fails due to Cannot Insert Duplicate Key Row Object Error"

Please check this out and let us know if this is the case.

Regards,
Earl

[Jaswanth P]

I am not sure if the bug you referenced is the root cause.

One additional piece of information that may help is that the script running gets the total records from the initial query and makes a number of calls to the search service in parallel with different offsets to get the rest of the records.  The number of parallel calls is driven by the number of processors on the local system or the overall number of records to be retrieved.  

I can reduce the likelihood of getting the error by reducing the number parallel calls to 4 (vs. 6).  

The pattern of errors is as follows:

1. At least one of the threads errors on the first execution.

2. At least one thread errors on subsequent runs of the script in the same context when more than 4 threads are run in parallel

3. Subsequent runs in the same context with 4 threads tends to complete without error 

[Earl McCutcheon]

Hey Jaswanth,

We were doing a bit more digging on this one as well, and found an older case where another user was triggering this same error and the issue was tracked back to the add-on "Elements Copy & Sync for Jira issues" it was noted that the app developers released a fix for this error in in the version 2.16.1 of the app.

Do you by chance have this add-on installed on your instance on a version prior to 2.16.1?  And if so can you try updating to a later version?  If the add-on is on a newer version try temporarily disabling the add-on to see if the behavior is corrected with the add-on disabled, as this could be a new bug or regression in the tool that we should bring up with the app's developers if this is the case.

If you do not have this specific add-on it could still be an alternate add-on triggering a similar conflict on a permissions check, and I would recommend also enabling "Add-on Safe mode" for a follow up test with all add-ons disabled to rule this out.

One more point however is that disabling add-ons can be fairly disruptive to end users on a production site, so I would also recommend setting up a staging server to test and verify the outcomes.

Let us know what you find.

Regards,
Earl